Use MySQL Utilities for better MySQL administration
Oracle, the company behind MySQL, provides MySQL Utilities, a collection of command-line scripts for accomplishing some common database administration tasks. We put the utilities through their paces on CentOS 6 and MySQL 5.6.
Since the MySQL Utilities collection is not included in the default MySQL server and client packages, you have to install it manually. First, download and install MySQL's Python connector. The MySQL Utilities are written in Python, and this connector allows them to connect to a database server. You can install the current version of the Python connector with the command
rpm -ivh http://dev.mysql.com/get/Downloads/Connector-Python/mysql-connector-python-1.0.11-1.el6.noarch.rpm/from/http://cdn.mysql.com/.
Next, download and install the current version of MySQL Utilities with the command
rpm -ivh http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.3.4-1.el6.noarch.rpm/from/http://cdn.mysql.com/. You should then find the newly installed utilities under your default shell path in the directory /usr/bin/. To see all the available scripts, use the command
rpm -ql mysql-utilities |grep bin.
This simple installation, with only one dependency, makes it possible to use MySQL Utilities even on servers with no X environment.
The MySQL Utilities don't require any special configuration, but to avoid having to always specify all connection parameters manually, you can save your MySQL connection details with the mysql_config_editor command. For instance, you can use
mysql_config_editor set --login-path=local --host=localhost --user=root --password, where
--login-path defines the name of the connection. This command saves the connection under the name "local." If you want to be able to access other servers, you have to save their details similarly under a different
The above information, including the password, is stored in the encrypted file ~/.mylogin.cnf, which has permissions of 600, meaning only your user can read it. The ~/.mylogin.cnf file is not a replacement for the similar ~/.my.cnf file; the latter is still needed to store your login details for the MySQL client /usr/bin/mysql. The secure permissions on ~/.mylogin.cnf, plus the fact that the data is encrypted, minimize security risks. Having your connection details stored securely is not only convenient but also helps you automate tasks and include MySQL Utilities in other scripts without the need to re-enter the password manually or store it in clear text.
Now you can use the information of this "local" instance by specifying it in the
--server parameter of the scripts. Take for example mysqlserverinfo, the most basic MySQL Utilities script, which shows general information about the MySQL server. You can run this script with the "local" connection details like this:
Find disk usage with mysqldiskusage
One script you may find useful is mysqldiskusage, which computes the disk usage of MySQL databases:
# Source on localhost: ... connected.
# Database totals:
| db_name | total |
| employees | 448,502,697 |
| mysql | 1,538,014 |
| performance_schema | 489,543 |
Total database disk usage = 450,530,254 bytes or 429.66 MB
You can add the option
--format to get CSV and tab-separated output, which you can then use in automation tasks, feeding the output of mysqldiskusage to other scripts.
Copy databases with mysqldbcopy
Another script, mysqldbcopy, copies a database either on the same server under a different name or to a different server. Here is an example that copies the database employees from the local server to a remote server :
mysqldbcopy --source=local --destination=server2 employees:employees2
# Source on localhost: ... connected.
# Destination on 192.168.204.202: ... connected.
# Copying database employees renamed as employees2
# Copying TABLE employees.departments
# Copying data for TABLE employees.employees
# Copying data for TABLE employees.salaries
The employees database is copied under the new name employees2 on the remote server, which has an IP address of 192.168.204.202.
This handy script saves time and effort you might spend with mysqldump manually exporting a database. If you explicitly need to export a database, such as for creating an offline backup, you can use a script called mysqldbexport. Its use is similar to that of mysqldump: run
mysqldbexport --server=local dbname, where dbname is the database to be exported. To learn how to use more options, such as for locking the tables or creating a slave from the database dump, check the script's manual page with the command
Clone users with mysqluserclone
You can also copy or clone a user with the script mysqluserclone. The command
mysqluserclone --source=local --destination=server2 dolly@localhost server2:dolly@localhost clones the user dolly from the local server to the remote one (server2).
The mysqluserclone script not only copies the user's name and password, but also preserves its privileges, which saves you time and effort compared to performing the operation manually. This can be especially helpful when you have a user with complex rights and permissions and you need a fast way to create a new user with the same privileges.
Compare databases with mysqldbcompare
The script mysqldbcompare compares one database on different servers or different databases on one server. You may need such a comparison when you run MySQL replication and you want to check the data integrity between the servers. It also helps when you are debugging and you want to see the differences between the current live version of a database and a previous version, such as one from a backup.
For example, the command
mysqldbcompare --server1=local --server2=local difftest:difftest2 --run-all-tests compares the databases difftest and difftest2 on the local server.
The powerful mysqldbcompare script can also tell you how to fix any differences it finds between two tables, as long as the two tables have the same primary keys. To see how to fix the differences, add the argument
--difftype=sql, which provides ready-to-run SQL statements, as in this very simple example:
# Data differences found among rows:
UPDATE `difftest`.`test_table` SET `value2` = '7' WHERE `value1` = '5';
By default the statements are meant to be run on the first of the two compared servers, or the first database when the databases are on the same server. If you're going to run these commands, make sure to prevent updates to the relevant tables in the compared databases. You can lock a database table to read-only with the MySQL command
LOCK TABLES table_name READ; Once the comparison is over, unlock the tables with the MySQL command
Find MySQL processes with mysqlprocgrep
If you wish to look for certain MySQL processes and manage them, use mysqlprocgrep. This script allows you to refine search results with multiple parameters such as for how long a query is being executed (
--age), the command of a query (
--match-command), and on which database the query runs (
To see how this script works, run
mysqlprocgrep --server=local --age=60 --match-command='Sleep'. This lists all connections that have been inactive for more than 60 seconds and that are in sleep state:
mysqlprocgrep --server=local --age=60 --match-command='Sleep'
| Connection | Id | User | Host | Db | Command | Time | State | Info |
| root:*@localhost:3306 | 8 | root | localhost | employees | Sleep | 391 | | None |
The mysqlprocgrep script has two powerful options:
--kill-connection to kill a connection and
--kill-query to kill a query. These kill options work only after the search conditions are matched, and can be used to free resources on busy MySQL servers overloaded by poorly written applications.
The MySQL Utilities scripts can help you accomplish some common MySQL tasks easier and faster. Compare them with the usual ways you accomplish the same tasks and decide for yourself which is easier, faster, and less error-prone.
This work is licensed under a Creative Commons Attribution 3.0 Unported License