Automated MySQL Backups to Amazon S3 with AutoMySQLBackups

This how to will teach you how to take automated backups of all your MySQL databases and send them to Amazon S3

Edit on GitHub
Aug 19, 2014
Databases
4 minutes

aws Amazon S3 mysql backups crontab database

Access (Logins) required

root login for the server (sudo is required to run automysqlbackup, root is req. to add to root user’s cron)
admin login for the mysql server (to provide for backup config file)
IAM access keys to use with s3cmd (to configure s3cmd)

sudo is required to run the AutoMySQLBackup script, so when adding the command to cron, you’d need to add it to the root user’s cron (so that it runs successfully on set intervals without asking for a password).

Tools

AutoMySQLBackup
s3cmd
SSH client (Terminal on Mac / putty on Windows)

Installing S3cmd on Debian

Check the Repos page for install on other systems.

Import S3tools signing key: wget -O- -q http://s3tools.org/repo/deb-all/stable/s3tools.key | sudo apt-key add -
Add the repo to sources.list: sudo wget -O/etc/apt/sources.list.d/s3tools.list http://s3tools.org/repo/deb-all/stable/s3tools.list
Refresh package cache and install the newest s3cmd: sudo apt-get update && sudo apt-get install s3cmd

Run s3cmd --configure to configure settings and add IAM access keys for Amazon S3.

Installing AutoMySQLBackup

Download and extract the latest tarball and run the install script.

Create a directory where you want to download and extract the AutoMySQLBackup file and move to that directory.

1sudo mkdir /automysqlbackup && cd /automysqlbackup

Download automysqlbackup (v3.0_rc6) to the server. (Check Sourceforge for the latest version of AutoMySQLBackup. As of this writing v3.0_rc6 is the latest)

1wget http://kaz.dl.sourceforge.net/project/automysqlbackup/AutoMySQLBackup/AutoMySQLBackup%20VER%203.0/automysqlbackup-v3.0_rc6.tar.gz

Extraxt the downloaded tarball

1tar zxvf automysqlbackup-v3.0_rc6.tar.gz

Run the installer

1./install.sh

By default it will install the configuration files in /etc/automysqlbackup and the executable files in /usr/local/bin.

Configuring AutoMySQLBackup

Edit the /etc/automysqlbackup/myserver.conf file to customise your settings. The myserver.conf file is VERY well documented, all you have to do is read and it’ll tell you what setting is supposed to do what and how you should configure it. You do not need to edit beyond the basics. What you generally edit are basic settings (username, passsword, host and backup directory), database settings (which ones to backups, which ones to not), rotation settings (when to backup), and notification settings (who and what to tell when a backup is complete).

1# Basic Settings
2CONFIG_mysql_dump_username='root'
3CONFIG_mysql_dump_password='password'
4CONFIG_mysql_dump_host='localhost'
5CONFIG_mysql_dump_host_friendly='Main MySQL Server'
6CONFIG_backup_dir='/backup/db'

 1# Databases to backup
 2
 3# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
 4# set to (), i.e. empty, if you want to backup all databases
 5CONFIG_db_names=()
 6
 7# List of databases for Monthly Backups.
 8# set to (), i.e. empty, if you want to backup all databases
 9CONFIG_db_month_names=()
10
11# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
12CONFIG_db_exclude=( 'information_schema' )

 1# Rotation Settings
 2
 3# Which day do you want backups?
 4CONFIG_do_monthly="01"
 5CONFIG_do_weekly="5"
 6
 7# Set rotation of daily backups.
 8CONFIG_rotation_daily=7
 9CONFIG_rotation_weekly=14
10CONFIG_rotation_monthly=12

 1# Notification setup
 2
 3# What would you like to be mailed to you?
 4# - log   : send only log file
 5# - files : send log file and sql files as attachments (see docs)
 6# - stdout : will simply output the log to the screen if run manually.
 7# - quiet : Only send logs if an error occurs to the MAILADDR.
 8CONFIG_mailcontent='log'
 9
10# Email Address to send mail to? (user@domain.com)
11CONFIG_mail_address='hello@aamnah.com'

Don’t forget to create the backup directory. To test run, run the command automysqlbackup /etc/automysqlbackup/myserver.conf

Adding AutoMySQLBackup to Cron

Edit crontab sudo crontab -e and add the following at the end.

1# AutoMySQLbackup run command for backing up databases every day at 12:05am.
25 0 * * * sudo automysqlbackup /etc/automysqlbackup/myserver.conf
3
4# Copy backed up databases to Amazon S3 via s3cmd, daily at 5am.
50 5 * * * sudo s3cmd -r sync /backup/location/ s3://location/

Creating an Alias

To run database backups and send them to S3 with one single word, make an alias for it. Edit your .bash_profile (or .bashrc) and add the following at the end:

1# backup databases and send them to S3
2alias backupdb='automysqlbackup /etc/automysqlbackup/myserver.conf && s3cmd sync -r /backup/location/ s3://location/'

Now that you have made an alias, every time you run backupdb in the terminal, it’ll take database backups and send them to S3. Creating an alias is good for manually taking database backups as it saves you the effort of remembering and writing two different commands.

Notes