Faster way to dump mysql

Question

This may be a dumb question, but I was just watching a screencast on MySQL replication, and I learned that a master database doesn't send SQL over to a slave for replication, it actually sends data over in binary, which makes importing extremely fast. I started wondering, "if a database can export and import binary, why do mysqldumps / imports take so long?" Is there a way to get mysql to dump a database in binary in a similar fashion to speed up that process as well?

Answer 1

I believe that is the way mysqlhotcopy works: It locks down the tables, and then copies the actual binary data across (instead of creating natural-language INSERT statements).

Answer 2

If you use MyISAM tables, you can just copy the files (.MYD, .MYI & .frm) in your data directory. For me this is a huge advantage over InnoDB as I frequently need to move large tables.

It is recommended to shutdown the mysql server before coping the files.

Answer 3

You can get a binary dump of an InnoDB database by copying files from the file system. I have done this a few times when initially setting up a slave database before starting replication.

1. Prevent mysql from writing to your data files

   mysql> FLUSH TABLES WITH READ LOCK;
   

2. Copy all data files, i.e.

   /var/lib/ibdata1
   /var/lib/your-database/*
   /var/lib/ib_logfile0
   /var/lib/ib_logfile1

   Not sure whether the log files are really needed, but I believe so.

3. Let mysql write again

   mysql> UNLOCK TABLES;
   

If uptime is not a problem you can skip steps 1+3 and just shut down mysql while copying the files.

Answer 4

mysqldump has the overhead of having to parse all the data both ways: binary->text and then text->binary. Consider a date field. Internally it might be stored as a 32bit integer (or whatever mysql happens to actually use), but the conversion to "2010-03-26 20:37:45" requires a considerable amount of calculation. On import, there's also the overhead of having to rebuild the table indexes.