Xaprb

Download MySQL Toolkit

MySQL Toolkit version numbers are based on Subversion revision number. This release is the first past the 1,000-commit milestone. It also marks several days of being in Sourceforge’s top 100 most active projects. It has been in the top 300 for a couple of months, and the top 1000 for, um, a long time. While I would hasten to say I’m not a popularity-contest-focused person, it’s rewarding to see that people think this project is important and useful.

This release of MySQL Toolkit updates MySQL Parallel Dump. I had been using it on a relatively small server; yesterday I took a deep breath and started using it to generate backups from a large server with lots of data and lots of queries. Of course I found a couple bugs and decided I needed more functionality and error handling. The major new functionality is for efficiency; it defers locking as late as possible and releases locks as soon as possible, and with the --setperdb option it treats each database as a set to be locked and dumped together. I also added some information that will be helpful when restoring a table dumped in chunks: the range of values in each chunk. And finally, I made it able to deal with some race conditions like a table being dropped between the time it’s discovered and the time it’s locked (this is very relevant for me because I avoid temporary tables so replication is restartable).

I don’t have a timeline for when I’ll write the corresponding restore utility, but the answer is probably “soon.” This is very much a need-driven project. To begin with, I’m replacing a dump system that didn’t allow point-in-time recovery. Now I’ve got the data I need for point-in-time recovery, but if I have to do that it’ll be a manual job until I write the restore utility.

I am very focused on recovery, not backup, as you’ll see if you buy the second edition of High Performance MySQL :-) I’m just solving my needs in the order of urgency: one must have a backup to do a restoration. I generally don’t like the “urgent, fix now” approach! (For various reasons I won’t get into, I am not able to use ZRM, but I would ordinarily recommend it over rolling your own solution).

Download MySQL Toolkit

This release of MySQL Toolkit fixes some minor bugs, and adds major new functionality to MySQL Parallel Dump.

Big News: MySQL Parallel Dump

I wrote a lot more tests and cleaned up MySQL Parallel Dump a lot (fixed bugs with failed dumps not being reported, for instance) but the really big news is I added chunking functionality to it. Now you can say

mysql-parallel-dump --chunksize 100000

and it will try to divide each table into chunks with 100,000 rows each. It can do the chunks in parallel, so it can actually be running several dumps from one table at the same time. The chunking is fuzzy: it’s a hard problem, and I adapted (and improved) the code from MySQL Table Checksum to do it. If you can improve it, please contribute your fixes (the Sourceforge project page has several ways for you to do that).

You can also dump by size, which is probably more useful for most people. To do 10MB per chunk (approximately), use this command:

mysql-parallel-dump --chunksize 10M

This is a big deal not just because it lets you parallelize dumps from a single table, but because having the dump split up makes it easier to restore in small chunks, which as readers have pointed out is a big help on transactional storage engines.

The parallel restore tool is in incubation. In the meantime, please put this tool through its paces. Clearly it’s not yet well-tested and I look forward to your bug reports!

Changelog

Changelog for mysql-find:

2007-10-03: version 0.9.5

   * The --dbregex parameter didn't work right.

Changelog for mysql-heartbeat:

2007-10-03: version 1.0.1

   * --check hung forever.

Changelog for mysql-parallel-dump:

2007-10-03: version 0.9.6

   * Arguments to external program weren't honored.
   * System exit codes were lost, so errors weren't reported.
   * Added chunking.
   * Modularized and tested.
   * Added documentation.
   * Made --locktables negatable.
   * Changed default output to be less verbose and added --verbose option.
   * Added summary output.

Download MySQL Toolkit

This release of MySQL Toolkit adds a new tool, fixes some minor bugs, and adds new functionality to one of the helper scripts.

New tool: MySQL Parallel Dump

I wrote an introduction to MySQL Parallel Dump yesterday. It’s a much smarter way to dump your data if you have a lot of it, and it’s actually a very usable lightweight multi-threaded backup tool (it can do most dump-oriented backup jobs without a wrapper script, in my opinion).

Changelog

Changelog for mysql-parallel-dump:

2007-10-01: version 0.9.5

   * Initial release.

Changelog for mysql-table-checksum:

2007-10-01: version 1.1.16

   * Made mysql-checksum-filter able to compare tables in different databases.

Changelog for mysql-table-sync:

2007-10-01: version 0.9.7

   * The special command-line syntax didn't allow a bare hostname.
   * Added an informative printout of what is being synced.