Chapter 3 MySQL Utilities Administrative Tasks

Table of Contents

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?

MySQL Utilities provides a command-line set of tools for working with MySQL Servers and databases. MySQL Utilities fully supports MySQL Server versions 5.1 and above. It is also compatible with MySQL Server 5.0, but not every feature of 5.0 may be supported. It does not support MySQL Server versions 4.x.

In this section, we present a number of example administrative tasks. Included in each is a description of the need, goals, example execution, and a discussion about the specific options and techniques illustrated. Also included is a description of the specific permissions required to execute the utilities demonstrated.