MySQL Database Dump Splitter v0.0.1 | Hacker News Reader

MySQL Database Dump Splitter v0.0.1

github.com

24 pointshowlett7 comments

I used to do this with the BSD split(1) utility back in the day. (8+ years ago) I ran a BSD only shop back then but now I have GNU split(1) and it lacks the -p argument for pattern.

Either way, my Macintosh still has BSD split(1) so here's a command example.

    split -p 'Table structure for table `' my_dump-20130913.sql

You can specify tables in the mysqldump command line.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

Without externally locking the database, you cannot split a dump by table with mysqldump. Consistency will only be guaranteed in a single run. This is useful in a single-step dump or post-processing a dump.

Technically, you can set up a full lock (FLUSH TABLES WITH READ LOCK), start the individual table dumps (with --single-transaction), and release the full lock (UNLOCK TABLES), and get a consistent snapshot of the database (this is the method used by mydumper, referenced above).

The individual dumps will be performed within transactions, and the global lock at the beginning will ensure that all transactions started at the same point. Using locks like this does not prevent reads, just writes, so you don't have a major interruption of service.

Of course, this is assuming that you're using InnoDB as your storage engine; otherwise you need to maintain the global lock throughout the dump of the non-InnoDB tables.

Also useful is this PHP Importer for very large MySQL Dumps http://www.ozerov.de/bigdump/

If you have the option of re-dumping it, use mydumper instead. Crazy fast.

https://launchpad.net/mydumper

I second this recommendation. At least partially because its complement (myloader) can also load tables in parallel back into a database.

Also, since mysqldump insert lines all contain the database name, it's fairly trivial to do this splitting with standard 'nix tools as well.

Reply on news.ycombinator.com