How to Backup MySQL using AutoMySQLBackup

Have you ever had a hard disk or server failure and lost important docs? Well… I had.

I’ve been looking for a MySQL backup solution for a few days now, found many scripts, but only one that fits all my needs (Separated daily/weekly backups of all my databases, automatic backup rotation and network support): AutoMySQLBackup.

AutoMySQLBackup is a bash-script of about 700 lines. It has no special depends except mysql-client and Bash of course.

Though the current version (2.5) was last updated in 2006 it works very well. Installation and configuration is also pretty simple, just place the script somewhere on your backup server, open the file with your favorite editor and adjust the following:

1
2
3
4
5
6

USERNAME=yourmysqluser
PASSWORD=keepitverysecret
DBHOST=yourmysqlserver
DBNAMES=all
BACKUPDIR=/path/on/your/backupserver
MAILADDR=you@example.org

I have a seperated user for backup which is only allowed to access mysql from the backup server. (You can set this in PhpMyAdmin’s Privileges section if you don’t know how to do it via cmd). You can also tell AutoMySQLBackup to only backup single databases by replacing DBNAMES=all with a space seperated list of your databases. BACKUPDIR is the path where you want to store your the dumps and MAILADDR will get a status message after every run.

Your backupdir will contain something like:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

./daily
./daily/information_schema
./daily/amarok
./daily/cacti
./daily/mysql
./daily/network
./daily/roundcube
./daily/videodb
./daily/chriskblog
./daily/wordpress_old
./daily/zabbix
./weekly
./weekly/information_schema
./weekly/information_schema/information_schema_week.45.2008-11-08_15h01m.sql.gz
./weekly/amarok
./weekly/amarok/amarok_week.45.2008-11-08_15h01m.sql.gz
./weekly/cacti
./weekly/cacti/cacti_week.45.2008-11-08_15h01m.sql.gz
./weekly/mysql
./weekly/mysql/mysql_week.45.2008-11-08_15h01m.sql.gz
./weekly/network
./weekly/network/network_week.45.2008-11-08_15h01m.sql.gz
./weekly/roundcube
./weekly/roundcube/roundcube_week.45.2008-11-08_15h01m.sql.gz
./weekly/videodb
./weekly/videodb/videodb_week.45.2008-11-08_15h01m.sql.gz
./weekly/chriskblog
./weekly/chriskblog/chriskblog_week.45.2008-11-08_15h01m.sql.gz
./weekly/wordpress_old
./weekly/wordpress_old/wordpress_old_week.45.2008-11-08_15h01m.sql.gz
./weekly/zabbix
./weekly/zabbix/zabbix_week.45.2008-11-08_15h01m.sql.gz
./monthly

any you’ll be able to restore a database using:

1

$ gunzip -c | mysql -u backup -p

Are you using another script to backup your mysql databases? Please let me know. I’d like to hear about it.