How-To: Backup MySQL using AutoMySQLBackup

November 14, 2008 in Computer, How-To, Unix

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:

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:

./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:

$ 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.

Tags: Backup, How-To, mysql, Unix

5 Comments › Leave yours

liz #

Thanks. Now go and upload the others.

Reply
admin #

Oh come on. Ten minutes after I posted?!? Get a job! Ah wait… this is your job

Reply
liz #

I just use a better feed-reader than you do.

Reply
matze #

You forgot to mention how to run the script!

Reply
admin #

ohh… i’m sorry.
add something like
0 1 * * * sh /path/to/automysqlbackup.sh.2.5
to roots/a backup user crontab file. This will run AutoMySQLBackup every day at 1am.

Reply

1 Trackbacks

Chris’ World » My Backup Strategy - [...] Please also read the comments in rsnapshot.conf, the official HowTo and the manual. I already wrote a post about ...