Backup MySQL Databases Automatically on a Linux Server

Dumping a MySQL database to a file is not a difficult task, but it is nice to be able to automate the process and have several versions archived. There is a project called “AutoMySQLBackup” available at: http://sourceforge.net/projects/automysqlbackup/.

First you should download the script to your Linux host using wget:

host#> wget http://superb-west.dl.sourceforge.net/sourceforge/automysqlbackup/automy...


Rename the file:


host#> mv automysqlbackup.sh.2.5 to automysqlbackup.sh


Make the file executable:


host#> chmod 755 automysqlbackup.sh


Next you should move the file to a place convenient for you. I personally have a folder called “/etc/cron.other” for such scripts.


host#> mv automysqlbackup.sh /etc/cron.other/


You'll now want to edit the script for your personal preference.


host#> vi /etc/cron.other/automysqlbackup.sh


You will need edit a couple of items.

USERNAME=root
PASSOWRD=
DBHOST=”localhost”
DBNAMES=”all”
BACKUPDIR=”/var/backups/databases”
MAILADDR=”username@hostname.com”


The advantage of using the root MySQL password, and selecting “all” for the databases, is that dyou don't have to create a new script everytime you add a database to system.

Now we need to add the script to the crontab to run everyday.


host#> crontab -e


Add a line such as:

0 1 * * * /etc/cron.other/automysqlbackup.sh


When done type:

:wq


…to save and exit the crontab editing.

This will cause the script to be run nightly at 1am. When the script is finished it will send an email to the address provided above in the MAILADDR variable.