Creating MySQL Backups With AutoMySQLBackup |Tutorials

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.

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.

2 Using AutoMySQLBackup

You can download AutoMySQLBackup as follows:

cd /usr/local/bin
wget http://mesh.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5

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

vi automysqlbackup.sh.2.5

[...]
USERNAME=root
[...]
PASSWORD=yourrootsqlpassword
[...]
DBHOST=localhost
[...]
# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="db_ispconfig web1 web2 web3"
[...]
# Backup directory location e.g /backups
BACKUPDIR="/backups"

[...]

# 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.sh.2.5 will create it automatically.

Make sure you fill in the right password and database host. 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 we must make the script executable:

chmod 755 automysqlbackup.sh.2.5

Now you can run automysqlbackup.sh.2.5 like this if you are in the /usr/local/bin directory:

./automysqlbackup.sh.2.5

... or like this from any other directory:

automysqlbackup.sh.2.5

This is a sample output:

server1:~# automysqlbackup.sh.2.5
======================================================================
AutoMySQLBackup VER 2.5
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - server1.example.com
======================================================================
Backup Start Time Fri Oct 17 16:00:51 CEST 2008
======================================================================
Daily Backup of Database ( db_ispconfig )
Rotating last weeks Backup...

Backup Information for /backups/daily/db_ispconfig/db_ispconfig_2008-10-17_16h00m.Friday.sql
compressed uncompressed ratio uncompressed_name
37231 382465 90.3% /backups/daily/db_ispconfig/db_ispconfig_2008-10-17_16h00m.Friday.sql
----------------------------------------------------------------------
Backup End Fri Oct 17 16:00:52 CEST 2008
======================================================================
Total disk space used for backup storage..
Size - Location
68K /backups
======================================================================
If you find AutoMySQLBackup valuable please make a donation at
http://sourceforge.net/project/project_donations.php?group_id=101066
======================================================================
server1:~#

Take a look at the /backups directory...

ls -l /backups

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

server1:~# ls -l /backups/
total 12
drwxr-xr-x 3 root root 4096 2008-10-17 16:00 daily
drwxr-xr-x 2 root root 4096 2008-10-17 16:00 monthly
drwxr-xr-x 3 root root 4096 2008-10-17 16:00 weekly
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 /backups/daily/db_ispconfig containing the database dump:

cd /backups/daily/db_ispconfig
ls -l

server1:/backups/daily/db_ispconfig# ls -l
total 40
-rw-r--r-- 1 root root 37231 2008-10-17 16:00 db_ispconfig_2008-10-17_16h00m.Friday.sql.gz
server1:/backups/daily/db_ispconfig#

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

gunzip db_ispconfig_2008-10-17_16h00m.Friday.sql.gz

... (this will give you the uncompressed dump named db_ispconfig_2008-10-17_16h00m.Friday.sql) 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.sh.2.5 manually all the time. Therefore, we can create a daily cron job for it as follows:

cd /etc/cron.daily/
ln -s /usr/local/bin/automysqlbackup.sh.2.5 automysqlbackup