Xaprb

I wanted to point out something that might not be obvious from the name: MySQL Parallel Dump can be used as a generic wrapper to discover tables and databases, and fork off worker processes to do something to them in parallel. That “something” can easily be invoking mysqlcheck — or any other program. This makes it really easy for you to do multi-threaded whatever-you-need-to-do on MySQL tables. Here’s how:

mysql-parallel-dump [options] -- 'mysqlcheck --optimize %D %N'

There are several things going on here:

1. You’re running mysql-parallel-dump with all the ordinary options. Some of them are really specific to dumping data, but not all that many — most of the options are about choosing which databases to include and exclude, and so on.
2. You’re adding a double dash -- to make it stop processing any further options.
3. The rest of the arguments are being treated as a system command, but…
4. Not before interpolating the database and table name into them. The %D and %N are a little macro language. There are some other macros too — see the documentation.

The net effect is to loop through all the tables and run OPTIMIZE TABLE on them.

MySQL Parallel Dump takes responsibility for noticing the exit status of the system command, keeping track of times, and reporting it all when it’s done. And its functionality for working on sets of things is also generic. You could easily create a table of “optimization jobs” and point it at that table, perhaps using the --age option, and it would obediently do what the table’s contents specify:

mysql> select setname, db, tbl from test.opti_job;
+-----------+--------+------------+
| setname   | db     | tbl        |
+-----------+--------+------------+
| dvd_store | sakila | film       | 
| dvd_store | sakila | film_actor | 
| set1      | test   | t1         | 
| set1      | test   | t2         | 
+-----------+--------+------------+
$ mysql-parallel-dump --nolocktables --sets set1,dvd_store --settable test.opti_job -- 'mysqlcheck --optimize %D %N > /dev/null'
        set1:              2 tables,     2 chunks,     2 successes,  0 failures,  0.14 wall-clock time,  0.17 dump time
   dvd_store:              2 tables,     2 chunks,     2 successes,  0 failures,  0.51 wall-clock time,  0.85 dump time
Final result:  2 sets,     4 tables,     4 chunks,     4 successes,  0 failures,  0.65 wall-clock time,  1.02 dump time

Much of the code for any kind of parallel tool is generic. I put a little extra time into this tool to make that code reusable, not special-purpose.

You might also like:

1. Introducing MySQL Parallel Restore
2. Introducing MySQL Parallel Dump
3. MySQL Toolkit version 1254 released
4. MySQL Toolkit version 946 released
5. Maatkit version 1297 released

Download MySQL Slave Delay

MySQL Slave Delay implements an oft-requested replication feature: the ability to make a slave lag its master, a.k.a scheduling binlog events for some time in the future. This will probably be built into MySQL replication someday, but in the meantime, you can use this tool. A delayed slave is great if disaster strikes and propagates through replication, because it lets you stop the delayed slave before it gets corrupted, restart the slave until just before the offending statement, and recover quickly. This is a lot faster than restoring last night’s full backup and replaying a day’s worth of binary logs on the master.

This is the first tool largely contributed by someone else. Sergey Zhuravlev sent me the original program over email, and I tweaked it to watch the slave’s relay logs so it doesn’t need to connect to the master, added documentation, and released it.

What it does

MySQL Slave Delay watches the slave’s relay log positions and makes the SQL thread lag the I/O thread by starting and stopping it as needed. It can also watch the master’s log positions, but this is usually not needed unless the slave’s I/O thread delay is large (typically it’s just milliseconds, but it depends on the network speed).

Since you can’t tell how far behind the slave is unless it’s running, MySQL Slave Delay samples and remembers the binlog positions. Later, when it needs to start the slave, it recalls the master’s position at the desired time ago, and makes the slave run until it hits that position.

Another way to think about it is that it schedules replication events for sometime in the future, instead of letting them be applied immediately.

All in all, a simple and easy way to accomplish delayed replication.

About MySQL Toolkit

MySQL Toolkit is a set of essential tools for MySQL users, developers and administrators. The project’s goal is to make high-quality command-line tools that follow the UNIX philosophy of doing one thing and doing it well. They are designed for scriptability and ease of processing with standard command-line utilities such as awk and sed.

You might also like:

1. How to sync tables in master-master MySQL replication
2. How pre-fetching relay logs speeds up MySQL replication slaves
3. MySQL Toolkit version 896 released
4. How to measure MySQL slave lag accurately
5. How fast is MySQL replication?

Download MySQL Table Sync

This release of MySQL Table Sync adds the ability to sync only part of a table, adds minor new functionality, and fixes a bug that could crash the tool.

I will be focusing some effort on this tool soon. If you would like to see particular features, please consider sponsoring them.

You might also like:

1. MySQL Toolkit version 946 released
2. JavaScript number-formatting library updated
3. MySQL Toolkit released as one package
4. I need your advice on how to package MySQL Toolkit as one file
5. MySQL Table Sync 0.9.2 released

Download MySQL Table Checksum

MySQL Table Checksum 1.1.0 adds many improvements, but the most important is a new way to ensure slaves have the same data as their master. Instead of checksumming the slave and the master, it can now insert the checksum results directly on the master via an INSERT.. SELECT statement. This statement will replicate to the slave, where a simple query can find tables that differ from the master. This makes a consistent, lock-free checksum trivially easy.

There are also many other feature improvements and bug fixes, compatibility with MySQL 3.23.2 through 6.0-alpha, and I’ve finally gotten the documentation finished to my satisfaction. Plus I wrote a test suite.

The new checksum method

While I was at last week’s conference, Martin Friebe and I went to supper and he suggested doing checksums via INSERT.. SELECT to avoid all the hassles with locking, waiting for slaves to catch up, and so on. This method guarantees checksums are taken at the same point in replication on the master and slave, and requires no locking or waiting. It’s a much better way to verify your slaves are in sync with the master.

The technique is implemented in two ways: with a variable accumulator (a modified version of the original checksum queries I wrote about in previous articles), and with a BIT_XOR checksum similar to the checksum queries used by MySQL Table Sync. Each method has strengths and weaknesses.

It’s now practical — nay, easy — to run MySQL Table Checksum from a cron job, and just as easy to check whether a slave is out of sync with the master. First, you create a table to hold the checksums:

  CREATE TABLE test.checksum (
     db         char(64)     NOT NULL,
     tbl        char(64)     NOT NULL,
     this_crc   char(40)     NOT NULL,
     this_cnt   int unsigned NOT NULL,
     master_crc char(40)         NULL,
     master_cnt int unsigned     NULL,
     ts         timestamp    NOT NULL,
     PRIMARY KEY (db,tbl)
  );

Then you run MySQL Table Checksum:

mysql-table-checksum --replicate=test.checksum master-hostname

When it’s all done, you check the slave:

SELECT db, tbl, this_cnt-master_cnt AS cnt_diff,
     this_crc <> master_crc AS crc_diff
FROM checksum
WHERE master_cnt <> this_cnt OR master_crc <> this_crc;

That’s it! Is that easy to throw into Nagios, or what?

You might also like:

1. How to know if a MySQL slave is identical to its master
2. Introducing MySQL Table Checksum
3. How to sync tables in master-master MySQL replication
4. How to calculate table checksums in MySQL
5. How MySQL replication got out of sync