Managing Database Dumps Using MySQL

When you're developing applications that use a MySQL database, it's often beneficial to work with real data. Since you never want to develop against your production database, it would be nice if you could take a snapshot of your production database, load it into a local database of your own and develop against the copy.

In fact, it's not hard to do this at all.

Dumping a MySQL Database

Here's the command to dump a MySQL database on a UNIX/Linux system:

       mysqldump -h <host> -u <user> -p <database> | zip > staging.zip

When using the above command, it will prompt you for the password, which seems to work better than trying to pass the password as a parameter.

I typically also pipe the dump output to the zip command because the databases I usually deal with are generally large enough that it's difficult to find enough room to store them on disk in an uncompressed form. Since a MySQL dump file is just text, it tends to compress by 75% or more.

Loading a Database Dump

Download the dump file from whatever machine you created it on. Unzip the file, which will create a local file called "-". Now, this file name was created as an artifact of the original zip process, and it's not a very friendly file name. So rename "-" to something more useful, like "dump.sql".

In order to load the dump file, you're going to have to take care of a few prerequisites, which should be fairly logical. You need to make sure that the database already exists, and is empty.

You also need to make sure that the user name you're going to use when loading the dump file exists and has the privileges to create all of the necessary database objects. If necessary, you can use the following MySQL command to give a user all privileges:

      GRANT ALL ON *.* TO someuser@somehost

To load the dump file, run the following command:

      mysql -h <host> -u <user> --password=<pass> <database> < dump.sql

That's all there is to it. You should now be able to easily dump and load a MySQL database in order to support your development efforts.



Comments

David Keener By ubolt on Friday, June 25, 2010 at 09:55 AM EST

It's nice to have a quick and useful summary of the process available.


David Keener By dkeener on Tuesday, November 02, 2010 at 02:00 PM EST

It's uncanny how many times I've referenced back to this blog entry whenever I need to dump a database and reload it.


David Keener By dkeener on Wednesday, January 25, 2012 at 04:17 PM EST

Useful yet again. Via email, I've provided a link to this article as an aid to a customer in backing up their MySQL database.


Leave a Comment

Comments are moderated and will not appear on the site until reviewed.

(not displayed)