AutoMySQLBackup is a shell script that allows daily, weekly and monthly backups of your local and remote MySQL Databases. It’s meant to run on Linux/Unix through a cron job. Its highly configurable, and easy to setup as I will show you today!
First you will need to acquire the shell script and drop it on your server. You can download AutoMySQLBackup on their SourceForge Project Page
Typically I will just do a “wget” into a downloads folder within my root directory just to keep it organized:
[[email protected]:/root]# mkdir download
[[email protected]:/root]# cd download/
[[email protected]:/root/download]# wget “http://internap.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5”
–12:01:24– http://internap.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5
=> `automysqlbackup.sh.2.5′
Resolving internap.dl.sourceforge.net… 66.79.151.7, 66.79.148.7
Connecting to internap.dl.sourceforge.net|66.79.151.7|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 25,316 (25K) [application/x-sh]100%[=======================================================================================================================================>] 25,316 –.–K/s
12:01:25 (180.28 KB/s) – `automysqlbackup.sh.2.5′ saved [25316/25316] [[email protected]:/root/download]#
Now that we have the script, we need to give it a home. Since this is a custom script thats not apart of the distribution, we can place it in “/usr/local/sbin”.
[[email protected]:/root/download]# cp automysqlbackup.sh.2.5 /usr/local/sbin/automysqlbackup.sh
`automysqlbackup.sh.2.5′ -> `/usr/local/sbin/automysqlbackup.sh’
[[email protected]:/root/download]# cd /usr/local/sbin
[[email protected]:/usr/local/sbin]# ls -la
total 44K
drwxrwsr-x 2 root staff 4096 2008-01-17 12:03 ./
drwxrwsr-x 10 root staff 4096 2007-05-29 10:01 ../
-rw-r–r– 1 root staff 25316 2008-01-17 12:03 automysqlbackup.sh
So now the script has a home we must allow it to run. A simple “chmod u+x” will do, you will also want to confirm that only the user has execute permission and not the group or other since we don’t want users on the server running the script or even seeing whats inside the file as it will have a database login.
[[email protected]:/usr/local/sbin]# chmod u+x automysqlbackup.sh
[[email protected]:/usr/local/sbin]# ls -al
total 44K
drwxrwsr-x 2 root staff 4096 2008-01-17 12:03 ./
drwxrwsr-x 10 root staff 4096 2007-05-29 10:01 ../
-rwxr–r– 1 root staff 25316 2008-01-17 12:03 automysqlbackup.sh*
As you can see above, User/Group/Other have read access, which we will have to change.
[[email protected]:/usr/local/sbin]# chmod 700 automysqlbackup.sh
[[email protected]:/usr/local/sbin]# ls -al
total 44K
drwxrwsr-x 2 root staff 4096 2008-01-17 12:03 ./
drwxrwsr-x 10 root staff 4096 2007-05-29 10:01 ../
-rwxr-xr-x 1 root staff 4184 2008-01-01 05:27 addwww*
-rwx—— 1 root staff 25316 2008-01-17 12:03 automysqlbackup.sh*
There, now only the user will be able to execute and read the files contents. Lets move on to configuration.
The configuration is rather simple, you’ll be asked for a username, password and hostname as well as a directory that you would like to have the backups stored. Now this will pose a security concern as you will be providing your login information for your databases, you can choose to use the root login or create a login that will only allow SELECT for all databases. This is mostly up to you, just remember that its possible if you system is compromised or if permissions change on this file that 3rd parties might have access to the login information contained within this file.
Lets go over the basics.
# Username to access the MySQL server e.g. dbuser
USERNAME=root# Username to access the MySQL server e.g. password
PASSWORD=password# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”DB1 DB2 DB3″# Backup directory location e.g /backups
BACKUPDIR=”/backups”# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# – quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT=”stdout”# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″# Email Address to send mail to? ([email protected])
MAILADDR=”[email protected]”
Most of the above is pretty straight forward, you mostly have to just change the username and password and chagne “DBNAMES” to “ALL”. You will also want to keep track of the backups via email incase they break. Here’s and example configuration:
# Username to access the MySQL server e.g. dbuser
USERNAME=root# Username to access the MySQL server e.g. password
PASSWORD=mymysqlpassword# Host name (or IP address) of MySQL server e.g localhost
DBHOST=localhost# List of DBNAMES for Daily/Weekly Backup e.g. “DB1 DB2 DB3″
DBNAMES=”all”# Backup directory location e.g /backups
BACKUPDIR=”/home/backup”# Mail setup
# What would you like to be mailed to you?
# – log : send only log file
# – files : send log file and sql files as attachments (see docs)
# – stdout : will simply output the log to the screen if run manually.
# – quiet : Only send logs if an error occurs to the MAILADDR.
MAILCONTENT=”stdout”# Set the maximum allowed email size in k. (4000 = approx 5MB email [see docs])
MAXATTSIZE=”4000″# Email Address to send mail to? ([email protected])
MAILADDR=”root”
There is detailed information within the configuration file that outlines each option and its purposes and values. The following are a couple that you should keep in mind or that might be relevant.
# Put in the list of DBNAMES(Databases)to be backed up. If you would like
# to backup ALL DBs on the server set DBNAMES=”all”.(if set to “all” then
# any new DBs will automatically be backed up without needing to modify
# this backup script when a new DB is created).
The following has a good point about cron.d on Debian:
# Finally copy automysqlbackup.sh to anywhere on your server and make sure
# to set executable permission. You can also copy the script to
# /etc/cron.daily to have it execute automatically every night or simply
# place a symlink in /etc/cron.daily to the file if you wish to keep it
# somwhere else.
# NOTE:On Debian copy the file with no extention for it to be run
# by cron e.g just name the file “automysqlbackup”
# The list of MDBNAMES is the DB’s to be backed up only monthly. You should
# always include “mysql” in this list to backup your user/password
# information along with any other DBs that you only feel need to
# be backed up monthly. (if using a hosted server then you should
# probably remove “mysql” as your provider will be backing this up)
# NOTE: If DBNAMES=”all” then MDBNAMES has no effect as all DBs will be backed
# up anyway.
The following is the exact rotation schedule.
#=====================================================================
# Backup Rotation..
#=====================================================================
#
# Daily Backups are rotated weekly..
# Weekly Backups are run by default on Saturday Morning when
# cron.daily scripts are run…Can be changed with DOWEEKLY setting..
# Weekly Backups are rotated on a 5 week cycle..
# Monthly Backups are run on the 1st of the month..
# Monthly Backups are NOT rotated automatically…
# It may be a good idea to copy Monthly backups offline or to another
# server..
So now you just need to install a cron for this to run daily! Just enter the following into your /etc/crontab.
59 11 * * * root /usr/local/sbin/automysqlbackup.sh
The about line will run the backup at midnight based on the servers timezone. You can also copy the script to /etc/cron.daily if you’re on Debian or if your distribution supports this method.
[[email protected]:/usr/local/sbin]# mv automysqlbackup.sh /etc/cron.daily/automysqlbackup
You must make sure that you leave the .sh off the end of the filename in order for it to be processed by the cron daemon. Last but not least run the script and see if it works!