Faster mysql reset method

Question

Possible duplicate:
Accelerate dumps and mysql imports

This may be a silly question, but I just watched the screencast on MySQL replication, and I found out that the main database does not send SQL to the subordinate for replication, it actually sends the data in binary format, which makes importing very quickly. I began to wonder: "If the database can export and import binaries, why does mysqldumps / import take so long?" Is there a way to get mysql to flush the database in binary in a similar way to speed up this process?

Answer 1

I believe this is the way mysqlhotcopy works: it locks the tables and then copies the actual binary data (instead of creating INSERT statements in natural language).

Answer 2

If you use MyISAM tables, you can simply copy the files (.MYD, .MYI and .frm) to your data directory. For me, this is a huge advantage over InnoDB, as I often have to move large tables.

It is recommended that you restart the mysql server before copying the files.

Answer 3

mysqldump has the overhead of having to parse all the data in both directions: binary-> text, and then text-> binary. Consider a date field. Internally, it can be saved as a 32-bit integer (or something that mysql really uses), but it takes a significant amount of computation to convert to "2010-03-26 20:37:45". When importing also overhead, you must rebuild the indexes of the tables.

Answer 4

Check out http://www.mydumper.org - multithreaded mysql backup / restore, which is 3-10 times faster than mysqldump http://vbtechsupport.com/1695/