How to Setup a Free Backup Solution for MySQL

It doesn't matter if you are running a forum for a small community or your own blog software or even large corporate applications which store data for millions of customers, most of these applications are based on a database which, if fails, everything is gone. I'm sure you are already using a backup solution but if it costs you money, is too difficult to run or plain and simple you're not deeply satisfied with it, then this article is for you. It explains how to setup an easy to use, safe, fast and nevertheless, free backup solution for your MySQL database! It only takes about 15 minutes to complete.



Requirements

- A MySQL server installed and configured

NOTE: Make sure your MySQL daemon has binary log option enabled. To enable it, simply add the following line to /etc/my.cnf, under the [mysqld] section:

log-bin

Optionally, for large databases, you can also set the max_binlog_size = 2G option under the log-bin in my.cnf.

Restart mysqld with # /etc/init.d/mysqld restart

- At least one database
- Perl modules: perl-DBI and perl-XML-parser.


Installing Zmanda Recovery Manager for MySQL

ZMR for MySQL allows a database administrator to automate logical or raw backup process to a local or remote disk and allows easy database recovery at any time.

- Login as root and type the following commands to download and install ZRM for MySQL:
- Go to Zmanda Download Section
- Select the last release (1.1.1 in this case)
- Download the Linux noarch RPM and install it with the command:

# rpm -ihv MySQL-zrm-1.1.1-1.noarch.rpm

The zrm binaries will be installed in /usr/bin, while the configuration files can be found in /etc/mysql-zrm.


Configuring Zmanda Recovery Manager for MySQL

- Login as root
- Create a directory called dailyrun and copy the sample configuration file into it:

# cd /etc/mysq-zrm
# mkdir dailyrun
# cp mysql-zrm.conf dailyrun/

- Open the /etc/mysql-zrm/dailyrun/mysql-zrm.conf file and edit the following parameters. Make sure you uncomment them first:

backup-level=0
backup-mode=logical
retention-policy=10D
compress=1
databases="database-you-want-to-backup"

If you want to backup more than one database:
databases=forum wiki
user="root"
password="root-password"
verbose=1
mailto="your@email.addr"

- Save and close the file.


Perform the backup

- Login as root
- Type the following command to start the dailyrun backup for the database you've chosen:

# mysql-zrm-scheduler --now --backup-set dailyrun

- The output should only print INFO strings, otherwise read carefully what the error or warning strings say.


Schedule a backup

- ZRM scheduler can be configured to run a backup everyday at 4:00am with the command:

# mysql-zrm-scheduler --add --interval daily --start 04:00 --backup-level 0 --backup-set dailyrun

- Verify if the scheduler has been configured correctly:

# mysql-zrm-scheduler --query

- This should print the logging path and a crond-like schedule time (0 4 * * * /command/here).
- To view a summary of your backup jobs:

# mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info

- It will print the backup set, time, level and status.


Perform a full restoration

- To restore the databases defined in ZRM config file, first determine which backup to restore from:

# mysql-zrm-reporter --show restore-info --where backup-set=dailyrun

- This will show an important info for the following restoration: the backup directory.
- Choose the date you want to restore the databases to (recommended: last one, if successful)
- Run the command:

# mysql-zrm --action restore --backup-set dailyrun --source-directory /backup/directory/here

For example:

# mysql-zrm --action restore --backup-set dailyrun --source-directory
/var/lib/mysql-zrm/dailyrun/20061116105925

Your databases are now protected. Enjoy!