Free To Feel

Heading to entrepreneur.

Joshua Chi

MySQL Database Backup and Restore

Backup I was trying to find some backup and restore system for our product mysql database. There are a lot of tools can do this. But I think the simple way is using crontab + mysqldump.

So I write a simple script

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /usr/local/bk/database_`data '+%m-%d-%Y'`.sql.gz 

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

This script will vardump all my database into the database_data '+%m-%d-%Y'.sql.gz zip file at 2::15am on every day of every month.

Restoring Restore the databases is simple.

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

To restore from a compress sql:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you'll need to use mysqlimport command. The syntax for mysqlimport is as follows

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]
comments powered by Disqus