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
Hope it helps. Cheers, Mark
refer : http://www.lynchconsulting.com.au/blog/index.cfm/2007/11/22/HOWTO-Backup-and-restore-MySQL-dbs
