October 16, 2010 by Ivan Kristianto
Beginners Guide: Backup And Restore Compressed MySQL Dump File
As you know your website data is stored in your database server, in this case is MySQL. To prevent disaster, backup your data regularly is a must for you and every website owner. There are many ways to backup MySQL data, one of them is using mysqldump to dump your MySQL data to a file.
But mysqldump output is plaint text files with full of SQL commands. And in linux you can compress it using gzip with pipelining command.
To backup and compress MySQL dump file:
$ mysqldump -u [username] -p[pass] --complete-insert [dbname] | gzip -9 > [backupfilename.sql.gz]
Explanation: the mysqldump will dump MySQL data according to the credential given, the output will pipeline to gzip to compress it with highest compression rate and output as backupfilename.sql.gz file.
note: –complete-insert will remove old table and create a new one then fill the data.
To Restore from compressed MysQL dump file:
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
Explanation: gunzip will extract the compressed file and the output (the sql commands) will be run in MySQL database as credential given. The sql command will create or overwrite the existing table and data.
Wise man said: Backup regularly, backup often.