Creating MySQL Backups With AutoMySQLBackup On Ubuntu 9.10

Want to support HowtoForge? Become a subscriber!

Submitted by falko (Contact Author) (Forums) on Thu, 2010-02-18 12:05. :: Ubuntu | Backup | MySQL

Creating MySQL Backups With AutoMySQLBackup On Ubuntu 9.10

Version 1.0
Author: Falko Timme <ft [at] falkotimme [dot] com>
Follow me on Twitter
Last edited 01/27/2010

AutoMySQLBackup is a shell script that lets you take daily, weekly and monthly backups of your MySQL databases using mysqldump. It can back up multiple databases, compress the backups, back up remote databases, and email the logs. This tutorial explains how to install and use it on an Ubuntu 9.10 server.

I do not issue any guarantee that this will work for you!

1 Preliminary Note

AutoMySQLBackup uses mysqldump to create SQL dumps of your databases. Please note that mysqldump will lock your databases while the backup is being created, and this can take from less than a second up to a few minutes, depending on the size of your database. If you're running a high-traffic web site with a large database, then AutoMySQLBackup is not for you!

This script will not help in the event of a hard drive crash. You should copy your backups offline regularly for best protection.

I'm running all the steps in this tutorial with root privileges, so make sure you're logged in as root:

sudo su

2 Using AutoMySQLBackup

You can install AutoMySQLBackup as follows:

aptitude install automysqlbackup

Then open /etc/default/automysqlbackup and take a look at the configuration options. They are all well explained. You should at least configure the following settings:

vi /etc/default/automysqlbackup

[...]
# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost
[...]
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
# The following is a quick hack that will find the names of the databases by
# reading the mysql folder content. Feel free to replace by something else.
#DBNAMES="db_ispconfig web1 web2 web3"
DBNAMES=`find /var/lib/mysql -mindepth 1 -maxdepth 1 -type d | cut -d'/' -f5 | grep -v ^mysql\$ | tr \\\r\\\n ,\ `
[...]
# Backup directory location e.g /backups
# Folders inside this one will be created (daily, weekly, etc.), and the
# subfolders will be database names.
BACKUPDIR="/var/lib/automysqlbackup"
[...]
# Email Address to send mail to? (user@domain.com)
MAILADDR="user@domain.com"
[...]

DBNAMES can contain one or multiple databases, separated by spaces.

If BACKUPDIR does not exist, automysqlbackup will create it automatically (the default directory is /var/lib/automysqlbackup).

If you want to back up local databases, use localhost; if you want to back up remote databases, use the remote hostname (please note that the remote database server must be configured to allow remote connections!).

Now you can run automysqlbackup:

automysqlbackup

Take a look at the /var/lib/automysqlbackup directory...

ls -l /var/lib/automysqlbackup

... and you should find three subdirectories, daily, weekly, and monthly:

root@server1:~# ls -l /var/lib/automysqlbackup
total 12
drwxr-xr-x 4 root root 4096 2010-01-27 17:20 daily
drwxr-xr-x 2 root root 4096 2010-01-27 17:20 monthly
drwxr-xr-x 4 root root 4096 2010-01-27 17:20 weekly
root@server1:~#

These directories will contain subdirectories named after the databases you chose to backup. For example, if you chose the database db_ispconfig, there will be a directory /var/lib/automysqlbackup/daily/db_ispconfig containing the database dump:

ls -l /var/lib/automysqlbackup/daily/db_ispconfig/

root@server1:~# ls -l /var/lib/automysqlbackup/daily/db_ispconfig/
total 40
-rw------- 1 root root 37016 2010-01-27 17:20 db_ispconfig_2010-01-27_17h20m.Wednesday.sql.gz
root@server1:~#

The .gz extension means it's compressed. To restore a database, you'd first have to uncompress the dump:

gunzip /var/lib/automysqlbackup/daily/db_ispconfig/db_ispconfig_2010-01-27_17h20m.Wednesday.sql.gz

... (this will give you the uncompressed dump named db_ispconfig_2010-01-27_17h20m.Wednesday.sql in the /var/lib/automysqlbackup/daily/db_ispconfig/ directory) and then restore it as described on http://www.howtoforge.com/faq/6_4_en.html.

Of course, you don't want to run automysqlbackup manually all the time. Fortunately, there's a cron job for this in /etc/cron.daily/automysqlbackup which takes care of running automysqlbackup automatically once per day.