Mysql Backup and Restore

Mysql Backup and Restore

After getting MySQL up and running, you may want to start backing it up. I’ll briefly talk about backing up and restoring your MySQL databases.

Mysql Backup and Restore with MySQLDump

First off, find out what database you want to backup. If you forgot what one it was, you can always check in the MySQL console:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| demo               |  
| demo_forums        | 
+--------------------+ 
3 rows in set (0.00 sec)

Once we have the database we want, we leave the console, and use mysqldump in the command line interface:

$ mysqldump -u root -p demo > demo.sql

As you can see, the syntax is quite simple:

$ mysqldump [options] <database name> > <filename>

Now we should have a demo.sql file sitting in the present working directory. Let’s say we want to restore that data into a different databases. First, let’s create a new databases:

$ mysqladmin -u root -p create test_database

Last let’s restore the database:

$ mysql -u root -p test_database < demp.sql

Automated Incremental Backups

So now we know how to backup and restore databases in MySQL. Now, we need to talk about actual solutions for backing up your MySQL server, and making sure if you are ever down, it isn’t long.

The first option is to make a MySQL mirror with another MySQL server on another system using replication. MySQL’s built-in replication allows you to have your data replicated on to another server, so if one goes down, the other can be setup to act as the master server while getting the master back to it’s original state. I won’t go any further into that, since it’s an article in its own. I will most likely write how to use it at a later date, not to far away.

The second option, and actually one that can be used with the first; is using AutoMySQLBackup. AMSB is a great script that backs up whatever databases you want and backs them up every day, every week. and every month. It’s set to do all 3, and give you a directory structure like so:

/backups/mysql$ ls

 daily  monthly weekly

After downloading, simply edit the amsb.sh script, and change everything that needs to be changed (database IP, username, password, backup location, etc), and then put it in your scheduled applications.

Edit /etc/crontab, and add:

0  1    * * *   root    /root/amsb.sh > /dev/null

So, now you know what to do, and what you can constantly do, to make sure your data is safe.