Here are some MySQL tools that we have used several times at Plurk.

a tool that splits a mysqldump into several files (chunks), one file per table

This tool splits a single mysqldump file in several files - one file per table. The idea behind splitting a dump in several files is to be able to import the dump back in parallel processes (see the parallel restore tool below).

The dump can be either piped to the script (so that you can monitor the progress with, let's say, 'pv' utility), or the path to it can be passed as the first argument.

Here is example usage:

$ ls -las
clyde.sql 
$ split_db.pl clyde.sql
$ ls -las
clyde.sql 0000-columns_priv 0001-db 0002-event ... 0095-timelines_active

Here the script split the 'clyde.sql' dump into 96 files starting from 0000 to 0095.

Download the 'split.pl' script: split_db.pl

a tool that imports the tables in parallel

This tool does a parallel import of tables in mysql. The idea here is that the regular 'mysql < dump' restoration is single process, meaning that you can't utilize other processors, if you have more than one.

The tool should be run from the same directory where the chunks are. The only argument it takes is the number of parallel processes to run.

Here is an example usage (based on the previous split):

$ ls -las 
clyde.sql 0000-columns_priv 0001-db 0002-event ... 0095-timelines_active
$ parallel_restore.pl 4
New child doing chunk 0000-columns_priv
New child doing chunk 0001-db
New child doing chunk 0002-event
New child doing chunk 0003-func
Child 0002-event finished working
Child 0003-func finished working
...

In case of an error it will print which child failed and stop.

Download the 'parallel_restore.pl' script: parallel_restore.pl

benchmark

We have an 8 CPU machine and here are various results for a 50GB mysql dump:

the usual way (mysql <dump): 7h 3m 32sec
4 parallel processes: 5h 14m 23s
8 parallel processes: 5h 32m 12s

Who wrote it?

Peteris Krumins wrote it. Come be his friend on plurk, his profile name is pkrumins!

Happy times! :)