MySQL Utilities — The General Operations


The MySQL Utilities are divided into five groups and this blog post covers the second group — General Operations. These include mysqldiskuseage, mysqlfrm, mysqlindexcehck, mysqlmetagrep, mysqlprocgrep, mysqluc, and mysqluserclone.

For checking the disk usage of your data, there is mysqldiskusage. The following example is a on a fresh install of 5.7.5 on Ubuntu.
mysqldiskusage --server=root:hidave@localhost -b -m -i
[sudo] password for dstokes:
# Source on localhost: ... connected.
# Database totals:
+---------------------+-------------+
| db_name | total |
+---------------------+-------------+
| mysql | 13,851,103 |
| performance_schema | 721,458 |
| sakila | 31,865,975 |
| sys | 0 |
| world | 1,783,833 |
+---------------------+-------------+

Total database disk usage = 48,307,605 bytes or 46.07 MB

# Binary logging is turned off on the server.
# InnoDB tablespace information:
+--------------+-------------+
| innodb_file | size |
+--------------+-------------+
| ib_logfile0 | 50,331,648 |
| ib_logfile1 | 50,331,648 |
| ibdata1 | 79,691,776 |
+--------------+-------------+

Total size of InnoDB files = 180,355,072 bytes or 172.00 MB

#...done.

Checking indexes is simple with mysqlindexcheck.
mysqlindexcheck --server=root:hidave@localhost world
# Source on localhost: ... connected.
# The following index is a duplicate or redundant for table world.CountryLanguage:
#
CREATE INDEX `CountryCode` ON `world`.`CountryLanguage` (`CountryCode`) USING BTREE
# may be redundant or duplicate of:
ALTER TABLE `world`.`CountryLanguage` ADD PRIMARY KEY (`CountryCode`, `Language`)

Add -i to get a list of all indexes on the tables in the database or –stats to see the 5 worst performing indexes.

Ever need to look for a column name or trigger quickly then you need to check out mysqlmetagrep. For example lets search for a column named CountryCode.

mysqlmetagrep --pattern="CountryCode" --server=root:hidave@localhost
+------------------------+--------------+------------------+-----------+-------------+--------------+
| Connection | Object Type | Object Name | Database | Field Type | Matches |
+------------------------+--------------+------------------+-----------+-------------+--------------+
| root:*@localhost:3306 | TABLE | City | world | COLUMN | CountryCode |
| root:*@localhost:3306 | TABLE | CountryLanguage | world | COLUMN | CountryCode |
+------------------------+--------------+------------------+-----------+-------------+--------------

And yes it accepts regex!

The ability to search he server process list comes with mysqlpocgrep.
mysqlprocgrep --server=root:hidave@localhost
+------------------------+-----+-------+------------------+--------+----------+-------+------------+------------------------------------------------------------------------------------------------+
| Connection | Id | User | Host | Db | Command | Time | State | Info |
+------------------------+-----+-------+------------------+--------+----------+-------+------------+------------------------------------------------------------------------------------------------+
| root:*@localhost:3306 | 25 | root | localhost:52475 | None | Query | 0 | executing | SELECT
Id, User, Host, Db, Command, Time, State, Info
FROM
INFORMATION_SCHEMA.PROCESSLIST |
| root:*@localhost:3306 | 15 | root | localhost:52307 | None | Sleep | 3 | | None |
| root:*@localhost:3306 | 6 | root | localhost:52031 | world | Sleep | 132 | | None |
| root:*@localhost:3306 | 14 | root | localhost:52306 | None | Sleep | 911 | | None |
| root:*@localhost:3306 | 5 | root | localhost:52030 | world | Sleep | 132 | | None |
+------------------------+-----+-------+------------------+--------+----------+-------+------------+------------------------------------------------------------------------------------------------+

Where this utility shines is the ability look for age of a process in the current state (long running or hung process), process running a certain command, process running against a selected database or from a certain user, and possibly kill that process. Heck it will even output the SQL code for a stored procedure that could fed into the Event Manager.

To clone a user from the local or remote server or to get a list off accounts, there is mysqluserclone.

mysqluserclone --source=root:hidave@localhost --list
# Source on localhost: ... connected.
# All Users:
+----------+------------+
| user | host |
+----------+------------+
| dstokes | % |
| root | localhost |
+----------+------------+

And finally there is mysqluc which us the command client for running the MySQL Utilities, usually from MySQL Workbench.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s