MySQL Workbench: Introducing Utilities

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:

mysqldbcopy
Copy databases between servers.
mysqldbexport
Export databases to a file in different formats, including: SQL, comma-separated files, and tab-separated files (and some more).
mysqldbimport
Import object definitions and/or data from a file—in different formats, similar to mysqldbexport—into a database.
mysqlindexcheck
Check for redundant or duplicate indexes on a list of tables or databases. It can also generate DROP statements to
remove redundant indexes.
mysqlmetagrep

Search MySQL servers for objects containing fields matching a pattern.

mysqlprocgrep

Search MySQL servers for processes matching a pattern and perform actions.

mysqlreplicate

Setup replication between two servers.

mysqlserverclone

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.
mysqluserclone

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: mysqlprocgrep and mysqlmetagrep. 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 mysqlprocgrep

With mysqlprocgrep 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 mysqlmetagrep

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, mysqlmetagrep 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 mysqlserverclone 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 mysqlreplicate 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 mysqluserclone. 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: mysqldbcopy, mysqldbexport, and mysqldbimport.

With mysqldbcopy, 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 mysqldbexport 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 mysqldbimport 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:

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

7 thoughts on “MySQL Workbench: Introducing Utilities”

  1. No, sorry. The project had not been created. It should be available on launchpad now, but the repository is not mirrored yet (will be very soon).

  2. I hope eventually it will be possible to build GUIs for the utilities. This way one could have drop down lists for most of the options / parameters of the utilities.

    I am especially looking forward to seeing one way DB metadata sync from one DB to another.

  3. Important thing for those who use Launchpad: Connector/Python must be installed to be able to run Utilities

  4. It’s ironic that passwords are required on all Workbench Utilities, which then return warnings about passing passwords on the command line!

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.