Is there a MySql binary dump format? Or anything better than plain text INSERT statements?
Solution 1
Not sure if this is what you're after, but I usually pipe the output of mysqldump
directly to gzip
or bzip2
(etc). It tends to be a considerably faster than dumping to stdout or something like that, and the output files are much smaller thanks to the compression.
mysqldump --all-databases (other options) | gzip > mysql_dump-2010-09-23.sql.gz
It's also possible to dump to XML with the --xml
option if you're looking for "portability" at the expense of consuming (much) more disk space than the gzipped SQL...
Solution 2
Sorry, no binary dump for MySQL. However the binary logs of MySQL are specifically for backup and database replication purposes http://dev.mysql.com/doc/refman/5.5/en/binary-log.html . They are not hard to configure. Only changes such as update and delete are logged, so each log file (created authomatically by MySQL) is also an incremental backup of the changes in the DB. This way you can save from time to time a whole snapshot of the db (once in a month?), and then store just the log files, and in case of crash, restore the latest snapshot and run through the logs.
Solution 3
It's worth noting that MySQL has a special syntax for doing bulk inserts. From the manual:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Would insert 3 rows in a single operation. So loading this way isn't as inefficient as it might otherwise be with one statement per row, and instead of 129 bytes in 3 INSERT statements, this is 59 bytes, and that advantage only gets bigger the more rows you have.
![Ollie Glass](https://i.stack.imgur.com/hVRes.jpg?s=256&g=1)
Ollie Glass
Data scientist, machine learning engineer & data strategist. ollieglass.com @ollieglass
Updated on June 10, 2022Comments
-
Ollie Glass about 2 years
Is there anything better (faster or smaller) than pages of plain text CREATE TABLE and INSERT statements for dumping MySql databases? It seems awfully inefficient for large amounts of data.
I realise that the underlying database files can be copied, but I assume they will only work in the same version of MySql that they come from.
Is there a tool I don't know about, or a reason for this lack?
-
Ollie Glass almost 14 yearsI like it, and I also compress my .sql files, but I'm wondering why MySql can't export in a binary format.
-
codekoala almost 14 yearsyeah... I haven't looked very closely at this project, but perhaps it will be of interest to you: 2ze.us/ym I suspect it's still using the regular mysqldump under the hood.
-
codekoala almost 14 yearsApparently if your tables are all MyISAM, you can use mysqlhotcopy: 2ze.us/hm
-
Joel B Fant about 13 yearsJust to lend some numbers to this: I pipe the output of mysqldump to 7-zip, and the result is often 5-7% of what the uncompressed SQL script would be.
-
Moritz about 9 yearsI'd be extremely careful using the pipe
|
operator with MySQL backups. I've had the pipe|
garble my dump encoding (UTF-8) to something illegible. I strongly suggest the--result-file
flag for the mysqldump command. -
Jonathan about 8 yearsTechnically this is slower than plain text, albeit smaller.