Dump & Import a MySQL Database

The below statements assume some options are being used such as –add-drop-table, –add-locks and gzip compression.  These are optional.

Dump Database

mysqldump --single-transaction --quick --add-drop-table --add-locks -u <USERNAME> -p -h <HOST> <DB_NAME> | gzip -9 > <BACKUP_FILENAME>-$(date +"%Y%m%d").sql.gz

NOTES:

• --single-transaction and –quick are added to prevent taking down a site when dumping a large DB (thanks to this post)
• --routines=true tells the dump to include store procedures, if applicable

Import Database

gunzip < <BACKUP_FILE.sql.gz> | mysql -u <USERNAME> -p -h <HOST> <DB_NAME> < <BACKUP_FILE.sql>

View Your Progress

You can run the commands below to use pv (Pipe Viewer) to watch your progress like this…

15.1MiB 0:01:01 [   0 B/s] [========>                          ] 18% ETA 0:12:26

Dump

mysqldump --single-transaction --quick --add-drop-table --add-locks -u <USERNAME> -p <DB_NAME> | pv | gzip -9 > <BACKUP_FILENAME>-$(date +"%Y%m%d").sql.gz

Import

pv <BACKUP_FILE.sql.gz> | gunzip | mysql -u <USERNAME> -p <DB_NAME> < <BACKUP_FILE.sql>

Don’t have pv?  You can install with

yum install pv

or with MacPorts

sudo port install pv

Create Secure Dump-only MySQL User

This is optional, but will add an extra layer of security when dump-only privileges are needed.  To dump a MySQL database the user will need the following minimum privileges…

• SELECT
• SHOW VIEW
• TRIGGER
• LOCK TABLES

Additional may be required depending on the options you select.  More details on the mysqldump Reference Page.