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.