MySQL has the well earned reputation for ease-of-use and “15-minutes-to-success”, since we continually focus making the server easy to use. MySQL Workbench provides the visual tools for database design, development, and administration. However, many DBAs prefer using the command-line, and there are many tasks that require the creation scripts for doing the job.
To make it easier to work with the server, the latest release of the MySQL Workbench—version 5.2.31—contain a set of Python scripts intended to make the life easier for DBAs by providing easy-to-use utilities for common tasks, which were introduced in the blog MySQL Workbench: Utilities. The set currently consists of just a few utilities, but will expand over time.
The utilities available in the Workbench are:
-
<a href="http://wb.mysql.com/utilities/man/mysqldbcopy.html">mysqldbcopy</a> - Copy databases between servers.
<a href="http://wb.mysql.com/utilities/man/mysqldbexport.html">mysqldbexport</a>- Export databases to a file in different formats, including: SQL, comma-separated files, and tab-separated files (and some more).
-
<a href="http://wb.mysql.com/utilities/man/mysqldbimport.html">mysqldbimport</a> - Import object definitions and/or data from a file—in different formats, similar to
<a href="http://wb.mysql.com/utilities/man/mysqldbexport.html">mysqldbexport</a>—into a database. -
<a href="http://wb.mysql.com/utilities/man/mysqlindexcheck.html">mysqlindexcheck</a> - Check for redundant or duplicate indexes on a list of tables or databases. It can also generate
DROPstatements to
remove redundant indexes. -
<a href="http://wb.mysql.com/utilities/man/mysqlmetagrep.html">mysqlmetagrep</a> - Search MySQL servers for objects containing fields matching a pattern.
-
<a href="http://wb.mysql.com/utilities/man/mysqlprocgrep.html">mysqlprocgrep</a> - Search MySQL servers for processes matching a pattern and perform actions.
-
<a href="http://wb.mysql.com/utilities/man/mysqlreplicate.html">mysqlreplicate</a> - Setup replication between two servers.
-
<a href="http://wb.mysql.com/utilities/man/mysqlserverclone.html">mysqlserverclone</a> - Start a new instance of a server to experiment with. This is used to test the utilities, but can be used whenever you need to set up a scratch server to test something.
-
<a href="http://wb.mysql.com/utilities/man/mysqluserclone.html">mysqluserclone</a> - Copy a MySQL user to one or more new users on another server
Finding stuff on servers with ease
In order to search for various things on servers—for example, searching objects and processes—there are two commands that can be used: <a href="http://wb.mysql.com/utilities/man/mysqlprocgrep.html">mysqlprocgrep</a> and <a href="http://wb.mysql.com/utilities/man/mysqlmetagrep.html">mysqlmetagrep</a>. The name “grep” is borrowed from Unix where the grep(1) commands that can be used to search inside files, but in this case you can search among processes and inside metadata on a server.
Example: searching processes using <a href="http://wb.mysql.com/utilities/man/mysqlprocgrep.html">mysqlprocgrep</a>
With <a href="http://wb.mysql.com/utilities/man/mysqlprocgrep.html">mysqlprocgrep</a> you can find all processes that match certain conditions and either just print them out, or kill either the connection or the query. So, for instance, to see all connections that have been idle for more than 2 minutes, we can use:
$ mysqlprocgrep --server=root:password@localhost --match-command=sleep --age=+2m +------------------------+-----+-------+------------+-------+----------+-------+--------+-------+ | Connection | Id | User | Host | Db | Command | Time | State | Info | +------------------------+-----+-------+------------+-------+----------+-------+--------+-------+ | root:*@localhost:3306 | 39 | mats | localhost | None | Sleep | 248 | | None | +------------------------+-----+-------+------------+-------+----------+-------+--------+-------+
In the example above, I would especially like you to note how the server connection information is provided. To provide information on what server to connect to and how, we have introduced a new syntax to represent the connection information which we call a connection specification:
user:password@host:port:socket
In the connection specification, the password, port, and socket are optional and can be excluded, which will make the default being used instead.
This is used instead of having separate switches (for example, –host and –user) that the server client programs are using. In addition to being easier to write, it also means that it is possible to provide multiple servers to command (where it makes sense, of course). You might be interested in knowing that both mysqlprocgrep and mysqlmetagrep accepts multiple servers.
If you now want to kill these idle connections, you can just add the –kill-connection option to the command, and the connection of all matching processes will be killed.
$ mysqlprocgrep --server=root:password@localhost \ > --match-command=sleep --age=+2m
In a similar way, if you have a long-running update from a special user (say, www-data), you can kill all the queries in one shot using the command:
$ mysqlprocgrep --server=root:password@localhost \ > --match-user=www-data --match-command=query \ > --match-state=updating --age=+1m \ > --kill-query
Example: finding objects using <a href="http://wb.mysql.com/utilities/man/mysqlmetagrep.html">mysqlmetagrep</a>
At times, you also find some odd reference to a column or index, you’re not quite sure, or you want to find out what objects are using a column named ‘db’. In those cases, <a href="http://wb.mysql.com/utilities/man/mysqlmetagrep.html">mysqlmetagrep</a> comes in handy.
The utility is used to find all objects that contain a field that matches the provided pattern. The pattern can be given either as a SQL simple pattern as defined by the SQL standard (this is what you usually use with LIKE), or using POSIX regular expressions (which is what you usually use with REGEXP in SQL). The default is to use the SQL simple pattern. So, to search for any objects having a column ‘host’, we can
use the command:
$ mysqlmetagrep --server=root:password@localhost --pattern=host --search=column +------------------------+--------------+---------------+---------------------+-------------+----------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+---------------+---------------------+-------------+----------+ | root:*@localhost:3306 | TABLE | PROCESSLIST | information_schema | COLUMN | HOST | | root:*@localhost:3306 | TABLE | columns_priv | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | db | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | host | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | host | mysql | TABLE | host | | root:*@localhost:3306 | TABLE | procs_priv | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | servers | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | tables_priv | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | user | mysql | COLUMN | Host | +------------------------+--------------+---------------+---------------------+-------------+----------+
Since the SQL simple patterns are default, this require an exact match and you will only find objects with columns exactly named ‘host’. To find all column containing the word ‘host’, you have to add wildcards to the pattern:
$ mysqlmetagrep --server=root:password@localhost --pattern=%host% --search=column +------------------------+--------------+---------------+---------------------+-------------+------------+ | Connection | Object Type | Object Name | Database | Field Type | Matches | +------------------------+--------------+---------------+---------------------+-------------+------------+ | root:*@localhost:3306 | TABLE | PROCESSLIST | information_schema | COLUMN | HOST | | root:*@localhost:3306 | TABLE | columns_priv | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | db | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | general_log | mysql | COLUMN | user_host | | root:*@localhost:3306 | TABLE | host | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | procs_priv | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | servers | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | slow_log | mysql | COLUMN | user_host | | root:*@localhost:3306 | TABLE | tables_priv | mysql | COLUMN | Host | | root:*@localhost:3306 | TABLE | user | mysql | COLUMN | Host | +------------------------+--------------+---------------+---------------------+-------------+------------+
Creating and configuring servers with ease
There are three utilites that I will just mention briefly, because they are not very complicated to use: mysqlserverclone, mysqlreplicate, and mysqluserclone.
To create a scratch servers using <a href="http://wb.mysql.com/utilities/man/mysqlserverclone.html">mysqlserverclone</a> for testing something, it is as easy as:
$ mysqlserverclone --server=root:password@localhost \ > --new-data=/tmp/data # WARNING: Root password for new instance has not been set. # Cloning the MySQL server running on localhost. # Creating new data directory... # Configuring new instance... # Locating mysql tools... # Setting up empty database and mysql tables... # Starting new instance of the server... # Testing connection to new instance... # Success! #...done.
It will create a new server from the original, copy the existing databases, and and start the server. You can supply a new port using the –new-port, but if you do not do that, it will pick the default port 3307.
If you want to set up replication quickly and easily, you can do that using mysqlreplicate:
$ mysqlreplicate --master=root:password@localhost \ > --slave=root@localhost:3307 --rpl-user=repl_user:xyzzy # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done.
When setting up replication, the <a href="http://wb.mysql.com/utilities/man/mysqlreplicate.html">mysqlreplicate</a> does some basic checking to ensure that replication will work. It checks that there is a server ID assigned and also checks that binary logging is enabled. If something is not right, it will abort the setup and report error.
The last utility that is useful in setting servers up is <a href="http://wb.mysql.com/utilities/man/mysqluserclone.html">mysqluserclone</a>. The utility is used to create new users based on an existing one. So, to create a new user ‘chuck’ with password ‘xyzzy’ on localhost from an existing user ‘mats@localhost’,
you can use the command:
$ mysqluserclone --source=root:password@localhost \ > --destination=root:password@localhost \ > mats@localhost chuck:xyzzy@localhost # Source on localhost: ... connected. # Destination on localhost: ... connected. # Cloning 1 users... # Cloning mats@localhost to user chuck:xyzzy@localhost # ...done.
Moving stuff around with ease
There are three utilities that can be used to move data around: <a href="http://wb.mysql.com/utilities/man/mysqldbcopy.html">mysqldbcopy</a>, <a href="http://wb.mysql.com/utilities/man/mysqldbexport.html">mysqldbexport</a>, and <a href="http://wb.mysql.com/utilities/man/mysqldbimport.html">mysqldbimport</a>.
With <a href="http://wb.mysql.com/utilities/man/mysqldbcopy.html">mysqldbcopy</a>, you can copy a database from one server to another, or several databases from one server to another. When copying a database, it not only copies the table definitions, but all associated objects such as triggers, events, routines, and also database-level grants.
With <a href="http://wb.mysql.com/utilities/man/mysqldbexport.html">mysqldbexport</a> you can export one or more databases into various formats, including (but not limited to) pure SQL, comma- and tab-separated values, and also a nice human-readable table.
With <a href="http://wb.mysql.com/utilities/man/mysqldbimport.html">mysqldbimport</a> you can import data in files into a database. In contast to using LOAD DATA INFILE, this will generate the INSERT statements to inject the data into the server.
The road ahead
The current set of utilities are just a small start, and we expect more utilities to be added over time and also improve on the existing utilities, so if you want to help, you can do that by:
- Downloading and try the utilities at http://launchpad.net/mysql-utilities
- Filing Bug Reports and Feature Requests at http://bugs.mysql.com/
- Joining the discussion at http://forums.mysql.com/list.php?155
We are very interested in feedback of any form—bug reports, suggestions for new utilities, suggestions for improving the existing utilities—so if you are a MySQL Expert DBA:
- Let us know how to improve our utilities
- Send us suggestions or ideas for new utilities
- Write your own utilities
- Contribute patches and/or new utilities
and if you are a Python programmer and/or developer:
- Let us know how to be more Pythonic
- Suggest improvements of the code
- Build and/or contribute additional utilities on top of ones we provide