Even More Maatkit for MySQL

June 15, 2010

As MySQL has evolved and added sophisticated and newer features, there are some areas that remain a bit rough around the edges. Maatkit offers a whole slew of tools for doing backup and restore, finding tables, monitoring your database server and many other database administration tasks you may not have even thought of.

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers