Handy reference for backing up and restoring db's via the mysql command line tools as it is by far the quickest way to do it.

We do this regularly as we take copies of our staging db's from a central DB server and put them on our local machines for development work.

 

Backup a database & zip it up

mysqldump -h db.example.com -p mydbname | gzip > mydbname.sql.gz

This will back the mydbname database from the server db.example.com and will prompt for a password (much better than writing it in the command line) and will then gzip it into the file mydbname.sql.gz

 

Create a database (if it doesn't exist)

mysqladmin -h 127.0.0.1 -u root create mydbname
This is only necessary the first you are creating a copy of the db on your machine.

 

Restore DB backup

Note: this will overwrite all local changes to the DB

 

zcat mydbname.sql.gz | mysql -h 127.0.0.1 -u root mydbname
This will take your previously gzipped backup copy and restore it into the local mysql instance to the db called mydbname