Even More Maatkit for MySQL

Introduction

As MySQL has evolved and
added sophisticated and newer features, there are some areas that remain a bit
rough around the edges.

Last month we introduced Maatkit,
the DBA toolkit for MySQL, and talked about a host of tools available to help
with replication, checksumming tables and keeping them in sync, as well as a
number of different query tools. However, that’s not all Maatkit has to offer.
There are also a whole slew of tools for doing backup and restore, finding
tables, monitoring your database server and many other administrative tasks you
may not have even thought of.

Backup Tools

mk-parallel-dump

Dumping the contents of
your database can be slow because mysqldump performs it’s work serially. Enter
mk-parallel-dump to the rescue. It can send results to multiple files, dump a
single table in chunks, or each table to its own file. The tool includes a
–threads flag telling it how many concurrent processes to start in order to
perform the work in parallel.

mk-parallel-restore

Once you’ve used
mk-parallel-dump to dump a source database, you can then use
mk-parallel-restore to load that data elsewhere. It will also automatically
uncompress the dumped files as necessary.

mk-archiver

This is an interesting
tool. If you want to move data out of your tables and archive it elsewhere, but
do so in small increments that won’t impact your running database, this tool
will help you. It can move the chunks of data into another table or a file
offline and optionally delete the data it moved from the source table.

mk-show-grants

This tool can be put to a
number of different uses including replicating users or putting your user
create and grant statements into version control. It is smarter than show
grants itself, which sometimes mixes up the orders of the permissions. It will
normalize these statements into a standard or canonicalized form so like
statements continue to match.

Administration Tools

mk-merge-mqd-results

If you have a number of
mk-query-digest reports, you can use this tool to merge them into a single
report.

mk-log-player

This is a tool for
stress-testing and load-testing your server. It will split up queries from your
slow query logfile into session files, which can then be replayed concurrently
using multiple threads.

mk-find

Wow, now this is a really
amazing and useful tool. You can use it to search your database for tables
based on engine type, creation time, and size and then perform a function just
like the Unix find command with -exec. Very useful indeed.

mk-deadlock-logger

This tool will fetch
innodb deadlock information from the database and print it or store it in
another table.

mk-duplicate-key-checker

Too many indexes can be a
performance hog for databases that do a lot of INSERT, UPDATE and DELETEs
because those indexes all have to be maintained whenever the table data
changes. In some cases you may have different indexes either covering indexes
or multi-column indexes along with single column indexes that do the same job.
This tool will help you ferret out the ones that are suspicious so you can do
away with them.

mk-error-log

Search for patterns of
error messages in your error log and report on them. It produces a very nicely
formatted report showing the number of times each message occurred, for
instance, the number of times the server was started, number of times it
stopped gracefully, and counts of error messages by error code.

mk-fifo-split

This tool you can use in
conjunction with other maatkit tools. It allows you to read lines of a file in
chunks, and delete them as you go along, chomping your way through until the
file is gone.

mk-kill

Contrary to the ominous
sounding name, you don’t necessarily have to kill the queries in question. The
tool can be used to find active queries based on runtime, status (login,
sleeping, etc.) and report on the results. If you use the –kill option you’ll
kill queries based on those criteria you specified.

mk-loadavg

Here you have a tool that
will monitor the database load average. If it reaches a threshold that you
define, it will execute a script or command of your choosing.

mk-upgrade

This tool allows you to
run queries from your slow log on different servers and report on how they
behave differently based on hardware, or MySQL version number and so forth. It
will show you differences between MySQL servers such as warnings, execution
time or other related to execution. This type of tool is useful when you want
to perform upgrades, migrations and other config changes, allowing you to test
them first before performing them for real.

Conclusion

As you can see from these
past two articles, Maatkit is a phenomenal toolset with a myriad of different
uses. Experimenting with these different tools will surely make your life
easier as a MySQL Database Administrator. You’ll probably learn a lot about the
inner workings of MySQL, and what’s in your own databases that you may not have
known before.

Additional Resources

Maatkit Project Hosting on Google Code
Zmanda Recovery Manager (ZRM) for MySQL

»


See All Articles by Columnist
Sean Hull