Use mysqldump to backup a database Print

  • 1

Almost all modern websites run with the help of a MySQL backend. Whether it's Wordpress, Magento, Joomla, SilverStripe... they all use MySQL to hold key data. But what if you want to take a backup of that data for safekeeping, or you're about to run an upgrade or some risky changes? We'll show you how to make a backup, and how to reimport that backup, if anything gets messy.

Once you're at the command line (i.e. SSH, or via console) on your web server, or the place where MySQL lives, you're good to go. You'll need the name of your database, the database username, and the accompanying password to be able to make a dump or import.

This command will create a compressed dump, but quickly (with a lower compression algorithm than standard) and will minimise any disruption or locking issues with the single-transaction flag:
mysqldump -u user_name -p database_name --single-transaction | gzip -2 > db.sql.gz

And if you need to restore the dump you've just created, you can do so with the following:
gunzip < db.sql.gz | mysql -u user_name -p database_name

You can do the same without compression, but on a modern server this will probably slow you down. These can be used instead, if you do not want to roll with Gzip compression, but you'll be placing more of an I/O load on the storage system at the same time as reading from it, plus the end result will be much larger!
mysqldump -u user_name -p database_name > db.sql
mysql -u user_name -p database_name < db.sql

Was this answer helpful?

« Back