MySQL Utilities

Abstract

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

If you have not yet installed MySQL Utilities please download your free copy from the download site. MySQL Utilities is available for Windows, Mac 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.

Document generated on: 2014-12-13 (revision: 41024)


Table of Contents

Preface
1 How to Install MySQL Utilities
1.1 Prerequisites
1.2 Source Code
1.3 Oracle and Red Hat Linux 6
1.4 Debian Linux
1.5 Microsoft Windows
1.6 Mac 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
3 MySQL Utilities Administrative Tasks
3.1 Database Operations
3.1.1 How Do I Provision a Slave?
3.1.2 How Do I Make a Copy of a Database on the Same Server?
3.1.3 How Can I Make a Copy of a Database and Change the Storage Engine?
3.1.4 How Can I Tell If a Table on Server X has the same Structure as the Same Table on Server Y?
3.1.5 How Can I Synchronize a Table on Two Servers Where Neither is Up-to-date?
3.2 General Operations
3.2.1 How Can I Find Out How Much Space My Data Uses?
3.2.2 My Server Crashed! I Need to Know the Structure of a Table. How Can I Do That?
3.2.3 Creating a New User With The Same Privileges as Another User
3.2.4 What Options Are Used With Each Utility?
3.2.5 I've Got Too Many Indexes! How Do I Know Which Ones to Drop?
3.2.6 I Need to Find an Object on My Server But All I Have is a Partial Name. How Do I Find All Objects with That Name Prefix?
3.3 How Can I Run a Process Every Night To Kill Certain Connections?
3.3.1 Objectives
3.3.2 Example Execution
3.3.3 Discussion
3.3.4 Permissions Required
3.3.5 Tips and Tricks
3.4 High Availability Operations
3.4.1 How Can I Use Replication?
3.4.2 How Do I Add New Servers to My Topology and Change Master Role
3.4.3 Setup Automatic Failover
3.4.4 Restore the Previous Master After Failover
3.4.5 How Can I Find All of the Slaves Attached to My Master Server?
3.4.6 How To Check If Data Is Correctly Replicated?
3.5 Server Operations
3.5.1 How Do I Make A Temporary Copy of My Server For Testing?
3.5.2 How Can I Find What MySQL Servers Are Running?
3.6 Specialized Operations
3.6.1 How Do I Record Only Login Events?
3.6.2 How Do I Copy/Move The Audit Log?
3.6.3 How Do I Show All INSERT and UPDATE Queries That Failed?
3.6.4 How Do I Display Connections by the User 'root' and Show the Result in CSV Format?
4 Overview of MySQL Utilities
4.1 Database Operations
4.2 General Operations
4.3 High Availability Operations
4.4 Server Operations
4.5 Specialized Operations
5 Manual Pages
5.1 mysqlauditadmin — Allows users to perform maintenance action on the audit log
5.2 mysqlauditgrep — Allows users to search the current or an archived audit log
5.3 mysqldbcompare — Compare Two Databases and Identify Differences
5.4 mysqldbcopy — Copy Database Objects Between Servers
5.5 mysqldbexport — Export Object Definitions or Data from a Database
5.6 mysqldbimport — Import Object Definitions or Data into a Database
5.7 mysqldiff — Identify Differences Among Database Objects
5.8 mysqldiskusage — Show Database Disk Usage
5.9 mysqlfailover — Automatic replication health monitoring and failover
5.10 mysqlfrm — File reader for .frm files.
5.11 mysqlindexcheck — Identify Potentially Redundant Table Indexes
5.12 mysqlmetagrep — Search Database Object Definitions
5.13 mysqlprocgrep — Search Server Process Lists
5.14 mysqlreplicate — Set Up and Start Replication Between Two Servers
5.15 mysqlrplms — Set Up and Start Replication Among a Slave and Multiple Masters
5.16 mysqlrpladmin — Administration utility for MySQL replication
5.17 mysqlrplcheck — Check Replication Prerequisites
5.18 mysqlrplshow — Show Slaves for Master Server
5.19 mysqlrplsync — Replication synchronization checker
5.20 mysqlserverclone — Clone Existing Server to Create New Server
5.21 mysqlserverinfo — Display Common Diagnostic Information from a Server
5.22 mysqluc — Command line client for running MySQL Utilities
5.23 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
8 MySQL Fabric
8.1 Introduction to Fabric
8.1.1 Fabric Prerequisites
8.1.2 Fabric Concepts
8.2 Installing and Configuring MySQL Fabric
8.2.1 Downloading MySQL Fabric
8.2.2 Installing MySQL Fabric
8.2.3 Configuring MySQL Fabric
8.2.4 Starting and Stopping MySQL Fabric Nodes
8.3 Quick Start
8.3.1 Example: Fabric and Replication
8.3.2 Example: Fabric and Sharding
8.4 The mysqlfabric Utility
8.4.1 Getting Help
8.4.2 Group Commands
8.4.3 Manage Commands
8.4.4 Server Commands
8.4.5 Sharding Commands
8.4.6 Event Commands
8.4.7 Dump Commands
8.5 Backing Store
8.5.1 Backing Store Tables
8.5.2 Protecting the Backing Store
8.6 Configuring MySQL Fabric
8.6.1 MySQL Fabric configuration file location
8.6.2 Section DEFAULT
8.6.3 Section storage
8.6.4 Section servers
8.6.5 Section protocol.xmlrpc
8.6.6 Section executor
8.6.7 Section logging
8.6.8 Section sharding
8.6.9 Section statistics
8.6.10 Section failure_tracking
8.6.11 Section connector
8.6.12 Section client
8.7 MySQL Fabric Frequently Asked Questions
8.8 Using Connector/Python with MySQL Fabric
8.8.1 Installing Connector/Python with MySQL Fabric Support
8.8.2 Requesting a Fabric Connection
8.8.3 Providing Information to Choose a MySQL Server
8.9 Using Connector/J with MySQL Fabric
8.9.1 Installing Connector/J with MySQL Fabric Support
8.9.2 Loading the Driver and Requesting a Fabric Connection
8.9.3 Providing Information to Choose a MySQL Server
8.9.4 MySQL Fabric Configuration for Running Samples
8.9.5 Running Tests
8.9.6 Running Demonstration Programs
8.9.7 A Complete Example: Working with Employee Data
8.9.8 How Connector/J Chooses a MySQL Server
8.9.9 Using Hibernate with MySQL Fabric
8.9.10 Connector/J Fabric Support Reference
9 Appendix
9.1 MySQL Utilities Frequently Asked Questions
9.2 MySQL Utilities Change History
9.3 Third Party Licenses
9.3.1 Doctrine DBAL 2.3.4