MySQL Utilities

Abstract

This is the MySQL™ Utilities Reference Manual. It documents both the GPL and commercial editions of the MySQL Utilities 1.6 release series through 1.6.4.

If you have not yet installed MySQL Utilities please download your free copy from the download site. MySQL Utilities is available for Windows, OS X, and Linux variants.

For notes detailing the changes in each release, see the MySQL Utilities Release Notes.

For legal information, see the Legal Notices.

For help with using MySQL, please visit either the MySQL Forums or MySQL Mailing Lists, where you can discuss your issues with other MySQL users.

For additional documentation on MySQL products, including translations of the documentation into other languages, and downloadable versions in variety of formats, including HTML and PDF formats, see the MySQL Documentation Library.

Licensing information.  This product may include third-party software, used under license. If you are using a Commercial release of MySQL Utilities, see this document for licensing information, including licensing information relating to third-party software that may be included in this Commercial release. If you are using a Community release of MySQL Utilities, see this document for licensing information, including licensing information relating to third-party software that may be included in this Community release.

Document generated on: 2017-03-09 (revision: 51142)


Table of Contents

Preface
1 How to Install MySQL Utilities
1.1 Prerequisites
1.2 Source Code
1.3 Oracle Linux and Red Hat Linux 6
1.4 Debian Linux
1.5 Microsoft Windows
1.6 OS X
2 Introduction
2.1 Introduction to MySQL Utilities
2.2 Connecting to MySQL Servers
2.2.1 Connection Parameters
2.2.2 Specifying Connections in Python Library
2.2.3 Specifying User and Password for Replication Options
3 MySQL Utilities Administrative Tasks
3.1 Binary Log Operations
3.1.1 How do you change the location of the binary log files?
3.1.2 How do you purge obsolete binary log files safely?
3.2 Database Operations
3.2.1 How do you provision a slave?
3.2.2 How do you make a copy of a database on the same server?
3.2.3 How can you make a copy of a database and change the storage engine?
3.2.4 How do you tell if a table on server A has the same structure as the same table on server B?
3.2.5 How do you synchronize a table on two servers where neither is up-to-date?
3.3 General Operations
3.3.1 How do you know how much space your data uses?
3.3.2 How do you recover the CREATE statement from a damaged or offline server?
3.3.3 How do you create a new user with the same privileges as another user?
3.3.4 Is there an easy way to know what options are used with each utility?
3.3.5 How do you find redundant or duplicate indexes and know which ones to drop?
3.3.6 How do you find all objects that start with a given name prefix?
3.3.7 How do you run a process every night to kill certain connections?
3.4 High Availability Operations
3.4.1 How do you setup and use replication?
3.4.2 How do you add new servers to an existing topology and change the master role?
3.4.3 How do you setup and use automatic failover?
3.4.4 How do you restore the previous master to service after failover?
3.4.5 How do you find all of the slaves attached to a master server?
3.4.6 How Can you determine if data was replicated correctly?
3.4.7 How do you fix errant transactions on the replication topology?
3.5 Server Operations
3.5.1 How can you create a temporary copy (running instance) of a server for testing?
3.5.2 How do you find what MySQL servers are running on a local machine?
3.5.3 How do you setup and use a secure (encrypted) connection between Utilities and a MySQL server?
3.6 Specialized Operations
3.6.1 How do you record only login events in the audit log?
3.6.2 How do you copy or move the audit log?
3.6.3 How can you find the INSERT and UPDATE queries that failed in the audit log?
3.6.4 How do you find connections by the user 'root' in the audit log and show the results in CSV format?
4 Overview of MySQL Utilities
4.1 Binary Log Operations
4.2 Database Operations
4.3 General Operations
4.4 High Availability Operations
4.5 Server Operations
4.6 Specialized Operations
5 Manual Pages
5.1 mysqlauditadmin — Allows users to perform maintenance actions on the audit log
5.2 mysqlauditgrep — Allows users to search the current or an archived audit log
5.3 mysqlbinlogmove — Binary log relocate utility
5.4 mysqlbinlogpurge — Binary log purge utility
5.5 mysqlbinlogrotate — Binary log rotate utility
5.6 mysqldbcompare — Compare Two Databases and Identify Differences
5.7 mysqldbcopy — Copy Database Objects Between Servers
5.8 mysqldbexport — Export Object Definitions or Data from a Database
5.9 mysqldbimport — Import Object Definitions or Data into a Database
5.10 mysqldiff — Identify Differences Among Database Objects
5.11 mysqldiskusage — Show Database Disk Usage
5.12 mysqlfailover — Automatic replication health monitoring and failover
5.13 mysqlfrm — File reader for .frm files.
5.14 mysqlgrants — Display grants by object
5.15 mysqlindexcheck — Identify Potentially Redundant Table Indexes
5.16 mysqlmetagrep — Search Database Object Definitions
5.17 mysqlprocgrep — Search Server Process Lists
5.18 mysqlreplicate — Set Up and Start Replication Between Two Servers
5.19 mysqlrplms — Set Up and Start Replication from a Slave to Multiple Masters
5.20 mysqlrpladmin — Administration utility for MySQL replication
5.21 mysqlrplcheck — Check Replication Prerequisites
5.22 mysqlrplshow — Show Slaves for Master Server
5.23 mysqlrplsync — Replication synchronization checker
5.24 mysqlserverclone — Clone Existing Server to Create New Server
5.25 mysqlserverinfo — Display Common Diagnostic Information from a Server
5.26 mysqlslavetrx — Slave transaction skip utility
5.27 mysqluc — Command line client for running MySQL Utilities
5.28 mysqluserclone — Clone Existing User to Create New User
6 Extending MySQL Utilities
6.1 Introduction to extending the MySQL Utilities
6.2 MySQL Utilities copy_server.py sample
6.3 Specialized Operations
6.3.1 mysql.utilities.command.grep — Search Databases for Objects
6.3.2 mysql.utilities.command.proc — Search Processes on Servers
6.4 Parsers
6.4.1 mysql.utilities.parser — Parse MySQL Log Files
7 MySQL Utilities Testing (MUT)
7.1 mut — MySQL Utilities Testing
A MySQL Fabric
8 Appendix
8.1 MySQL Utilities Frequently Asked Questions

List of Figures

2.1 Starting MySQL Utilities from Workbench

List of Tables

5.1 mysqldbexport Export Types
5.2 mysqldbimport Import Types
5.3 External Script Parameters