Quicker Dumping of a Magento MySQL Database for Branching

Any experienced Magento developer will certainly have felt the pain of dumping a multi-gigabyte MySQL database for a Magento store. Which is certainly a tedious process when you want to make a quick branch or rapidly update your staging environment.

We're big advocates of branch often and merge often - but this also means updating the DB relatively frequently too (if there has been DB changes on master/trunk). Usually, its just an excuse to go get a fresh cup of coffee whilst you while away the 2 minutes it will take to dump and however long it then takes to re-import into a new DB. But, unfortunately, there is only so much coffee we should be drinking in the day, so speeding up this process is a must.

Typically, a staging or development branch, doesn't actually required all the data the live site has (sales_quotes, logs, reports, dataflow history), so we gain a big speed advantage by simply ignoring these tables.

Please note. This script is not to serve as a means to backup a database - as it removes data from tables that we deem non-essential when branching/mirroring a store for development.

The features

• Using the script is really easy
• Significantly faster dumps (on a 3GB DB, total time is 0m46s vs 2m10s)
• Auto-population of username, password, database name and host name
• Support for CE and EE
• No table-level locks - so safe to use on a production/live site
• You can use it to re-import the dump too

Dump a compressed database

Running the script is really easy, it will automatically populate the username, password, database name and host from your local.xml file.

cd /path/to/my/magento/store
wget -O mage-dbdump.sh sys.sonassi.com/mage-dbdump.sh
bash mage-dbdump.sh -dz

So in practice, it looks a little like this. Execution on a 3GB database takes just under 50 seconds.

cd /home/sonassi/public_html
wget -O mage-dbdump.sh sys.sonassi.com/mage-dbdump.sh
bash mage-dbdump.sh -dz
######################################
MYSQL DUMP COMPLETE
######################################

Restore a compressed database

cd /home/sonassi/public_html
wget -O mage-dbdump.sh sys.sonassi.com/mage-dbdump.sh
bash mage-dbdump.sh -rz
Are you sure you want to restore ./var/db.sql.gz to example_magliv? [y/N]:
######################################
MYSQL IMPORT COMPLETE
######################################

You can download the file here.