How to backup and restore Mysql database
Recently at work, we lost a server that was running our MySql database for the Rivendell Radio Automation that we run.
Fortunately I had regular backups of the MySql database, so recovering the database with all of the data was much easier than starting over.
Concerning a MySql backup plan, I recommend she shell script AutoMySQLBackup located here. I like this backup scheme for MySql because it creates a running set of database backups so you can go back pretty much as far as you want. Its also quite configurable with features such as email notification backup rotation, backup compression, and encryption.
Once I rebuilt the server that Mysql was running on, it was a simple matter of grabbing the latest copy of the MySql database that had the Rivendell data on it, and un-compressing it with gunzip with the following command:
gunzip -k COMPRESSED_MYSQL_DATABASE.sql.gz
I like to use the -k option when using gunzip, as this will keep the original file intact. Without the -k option, gunzip will delete the original file.
When gunzip is done there will be a file without the .gz option. this is the output that will get loaded into MySql. The first thing is to is to log into MySql with the following command:
mysql -u root -p
Enter the password when asked, and if correct, you will see the following prompt:
Next create a new database in MySql. In my case, the database name is Rivendell.
CREATE DATABASE Rivendell;
Note: all commands within myql end with a semicolon. Make sure you put one at the end of each command.
Mysql will return with: Query OK, 1 row affected (0.10 sec)
Which means that the database has been created. Its now time to import the data into the MySql database. Exit out ot MySql by typing quit.
To import the data into the new Mysql database, type the following:
mysql -u root -p Rivendell < /FULLPATH-TO-BACKUP/MYSQL.sql
Where Rivendell in my case, is the database name I am using, you is probably different. FULLPATH-TO-BACKUP is the full path to the directory where that backup is located, and MYSQL.sql is the actual backup file that is being imported into MySql.
Depending on how much data, this will take a while. When finished, your Mysql database is restored.
This article was originally posted on www.mikestechblog.com Any reproduction on any other site is prohibited and a violation of copyright laws.