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.