MySQL Dump

MySQL Dump Tshirt

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 its 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’s 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 🙂

9 Comments Exporting and Importing Compressed MySQL Dumps

  1. Pingback: IT knowledge base.

  2. Faisal Iqbal

    let me know about below when i used above command.
    `gzip` is not recognized as an internal or external command, operable program or batch file.

    1. Gabriel Somoza

      That means you don’t have “gzip” installed in your system. If you install it then it should work (assuming you’re on *nix)

  3. Shahadat Hossain

    really useful, just add another hint to ignore table like as follows –
    mysqldump -u {mysql user name} -p {database name} –ignore-table={database name}.{table/view name1} –ignore-table={database name}.{table/view name2} | gzip > {database name}.sql.gz

    thanks for your sharing.

  4. Francisco Zarabozo

    Just a tip: You can omit the “<" redirection. The "-c" option for gzip is to write to STDOUT and leave the original file intact. The "<" option is to redirect a buffer to gzip's STDIN. So, it's kind of redundant. You can just use: gzip -dc {database}.sql.gz | mysql -u {user} -p {database}


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.