4

Possible Duplicate:
Speeding up mysql dumps and imports

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?

Community
  • 1
  • 1
joeellis
  • 2,715
  • 7
  • 27
  • 43
  • Also, I'm using InnoDB, not MyISAM unfortunately. It sounds like MyISAM has more options available for dumping. – joeellis Mar 26 '10 at 21:17

4 Answers4

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).

Pekka
  • 431,103
  • 135
  • 960
  • 1,075
1

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.

gdelfino
  • 10,985
  • 5
  • 42
  • 46
1

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

p4guru
  • 1,274
  • 2
  • 17
  • 23
0

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.

Marc B
  • 348,685
  • 41
  • 398
  • 480