Wednesday, November 5, 2008

Creating MySQL Backups With AutoMySQLBackup

11/05/2008 05:46:00 PM |

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

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


# List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
DBNAMES="db_ispconfig web1 web2 web3"
# Backup directory location e.g /backups
# Email Address to send mail to? (

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

If BACKUPDIR does not exist, 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

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


... or like this from any other directory:

This is a sample output:

AutoMySQLBackup VER 2.5

Backup of Database Server -
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

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

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

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

Of course, you don't want to run 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

3 Links

AddThis Feed Button
Bookmark and Share

You Might Also Like :