Menu
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?
368
--binary-mode. This option helps when processing mysqlbinlog output that may contain BLOB values. By default, mysql translates \r\n in statement strings to \n and interprets \0 as the statement terminator. --binary-mode disables both features.
To generate the backup using mysqldump, 'Select' to dump the tables, 'Show View' for views, 'Trigger' for the triggers. If you are not using —single-transaction option, then 'Lock Tables' privileges must be granted to the user. -p [password]: The valid password of the MySQL user.
MySQL's unencrypted port is not secure. If you're running mysqldump on your VPS host, and only transferring the resulting dump file to your PC, then you can do this securely. If you can ssh to your VPS, you should be able to use scp too. This gives you the ability to transfer files securely.
Binary data can be stored in a MySQL database in a BLOB field. A BLOB is a binary large object that can hold a variable amount of data.
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...
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
