I believe these are highly simple yet really useful tips for any Unix sysadmin/webmaster. I don’t have to export, transfer and import large MySQL databases very often so today I decided to add these one-liner commands to my blog so I know where to find them next time. Hope it’s useful for someone else too!
An important note: I like to compress dumps with gzip so these commands will only work with gzip of compression. If you’re using some other kind of compression you can probably adjust the commands very easily. In my experience compressing dumps reduces transfer size (and increases speed) by up to 80% - absolutely worth it for large databases.
Without further ado, here are the commands.
Exporting a Compressed MySQL Dump
This command will dump a MySQL database, compress it on-the-fly and save it to a file. Replace keywords between { and } with their proper values (while also removing the brackets of course).
mysqldump -u {user} -p {database} | gzip > {database}.sql.gz
Usually I prefer to use a slightly different variation and prepend the date. This is useful if you’ve done a dump before and want to avoid overwriting it, etc. And most importantly: its cleaner. :)
mysqldump -u {user} -p {database} | gzip > `date -I`.database.sql.gz
Importing a Compressed MySQL Dump
The import command takes a compressed MySQL dump as input, decompresses it and adds it to the database on-the-fly. This differs from the two-step approach (where you first decompress the file and then import it) in that here we’ll decompress and import at the same time (making good use of pipes).
gzip -dc < {database}.sql.gz | mysql -u {user} -p {database}
Following the convention I use for the export command, what I actually do most of the times is:
gzip -dc < `date -I`.{database}.sql.gz | mysql -u {user} -p {database}
If you used either or both commands please leave a comment, bookmark it, like it, etc. And above all make sure you can find this post again easily for the next time :)