HOWTO Backup and restore MySQL db's
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
While just doing a search on similar help I came across a new MySql Migration Toolkit
http://www.mysql.com/products/tools/migration-tool...
Thought I'd pass it along
h
"zcat mydbname.sql.gz | mysql -h 127.0.0.1 -u [uname] -[pass] mydbname"
I get this error "mydbname.sql.gz: no such file or directory"
However, this command works fine for me:
gunzip < [mydbname.sql.gz] | mysql -u [uname] -p[pass] mydbname