How to archive and restore MySQL with gzip

I'm always using these commands to move databases around from production to staging server and to commit to the repository. I'm always forgetting them too so I might as well paste it here for me to remember and help others... Aren't I nice!

To backup your MySQL database to a gzip file you run this in your command line thingy:


mysqldump -u myusername -p mydbname | gzip > myfile.sql.gz


You need to change the: myusername mydbname and myfile.sql.gz to your settings

To restore you do this:


gunzip < myfile.sql.gz | mysql -u myusername -p mydbname


To copy mysql tables from one database to another:

mysqldump -u myusername -p mydatabase mytablename | mysql -u myusername destinationdatabase;


If you're logged into MySQL from the command line you can restore like this:


mysql> source myfile.sql


• Slashdot it!
• Digg it:
  
• Del.icio.us