Linux.com

Home Learn Linux Linux Tutorials How to do Painless MySQL Server Backups with AutoMySQLBackup

How to do Painless MySQL Server Backups with AutoMySQLBackup

servers

AutoMySQLBackup is a clever script that takes the pain out of setting up automated incremental daily, weekly, and monthly backups of MySQL databases. Its only dependency is the mysqldumpcommand, which comes with mysql-client. AutoMySQLBackup dumps your databases at whatever intervals you desire, and rolls them up into compressed tarballs. Then you can copy the tarballs to other media, and encrypt them if you wish.

Getting AutoMySQLBackup

Download AutoMySQLBackup from Sourceforge, and then unpack the tarball:

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

Now you should have the automysqlbackup-v3.0_rc6 directory with six files in it: automysqlbackup, automysqlbackup.conf, CHANGELOG, install.sh, LICENSE, and README.

Next, read the README because it contains installation instructions and other useful information. It may not tell you everything you need to know, so hopefully this article will fill the gaps. Then your next step is to run install.sh, and this requires root privileges. If the installation script fails you can easily install AutoMySQLBackup manually by following these steps. These are run from the directory where you unpacked the tarball:

# chown root:root automysqlbackup
# chmod 0700 automysqlbackup
# cp automysqlbackup /usr/local/bin/
# mkdir /etc/automysqlbackup
# cp automysqlbackup.conf /etc/automysqlbackup/automysqlbackup.conf
# cd /etc/automysqlbackup
# cp automysqlbackup.conf servername.conf

Replace "servername.conf" with whatever you want to name your configuration file. Then you will have a copy of the original for reference, and your working configuration file.

Configuration Options

The configuration file is large and well-commented, and you need to change only a few things to start making and testing your backups. First you need to select a MySQL user that has, at a minimum, SELECT privileges, and then enter that user and user's password:

# Username to access the MySQL server e.g. dbuser
CONFIG_mysql_dump_username='db_user'
# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password='sekkritpassword'

There is a complication if you are using Parallels Plesk 10.2 or newer on your server, and you want to authenticate with the admin user. It is better to use a different MySQL user, like one you have created with minimal privileges just for AutoMySQLBackup, but if that is not possible then you can't use the plain-text admin password. Starting with version 10.2 the Plesk admin password is encrypted in /etc/psa/.psa.shadow. You can dump this password in plain text with this command if you need to recover it:

# /usr/local/psa/bin/admin --show-password

But you can't use the plain text password in your AutoMySQLBackup configuration. Instead, use the encrypted password:

# Password to access the MySQL server e.g. password
CONFIG_mysql_dump_password=`cat /etc/psa/.psa.shadow`

Your server is localhost, and you need to create or choose a directory to store your backups in:

# Host name (or IP address) of MySQL server e.g localhost
CONFIG_mysql_dump_host='localhost'
# Backup directory location e.g /backups
CONFIG_backup_dir='/home/mysqlbackups'

You have a lot of control over which databases are backed up. If you have a lot of them, then leaving CONFIG_db_names=() empty copies all of them. Then if there are any you want to exclude, just create an exclude list. One of my servers hosts over 150 virtual Web hosts, and each one has its own MySQL database, so this is a huge timesaver for me:

# List of databases for Daily/Weekly Backup e.g. ( 'DB1' 'DB2' 'DB3' ... )
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_names=()
# List of databases for Monthly Backups.
# set to (), i.e. empty, if you want to backup all databases
CONFIG_db_month_names=()
# List of DBNAMES to EXLUCDE if DBNAMES is empty, i.e. ().
CONFIG_db_exclude=('information_schema' 'test_db' 'demo_db' )

Rotation settings are straightforward. This example performs monthly backups on the 3rd of every month, weekly backups on Saturdays, daily backups are kept for 7 days, weekly backups are kept for 14 days, and monthly backups for 30 days.

# Which day do you want monthly backups? (01 to 31)
# If the chosen day is greater than the last day of the month, it will be done
# on the last day of the month.
# Set to 0 to disable monthly backups.
CONFIG_do_monthly="03"
# Which day do you want weekly backups? (1 to 7 where 1 is Monday)
# Set to 0 to disable weekly backups.
CONFIG_do_weekly="6"
# Set rotation of daily backups. VALUE*24hours
# If you want to keep only today's backups, you could choose 1, i.e. everything older than 24hours will be removed.
CONFIG_rotation_daily=7
# Set rotation for weekly backups. VALUE*24hours
CONFIG_rotation_weekly=14
# Set rotation for monthly backups. VALUE*24hours
CONFIG_rotation_monthly=30

There are two items that control notifications. I like to see the logfiles after every run, as this example shows:

# 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.
CONFIG_mailcontent='log'
# Email Address to send mail to? (
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 )CONFIG_mail_address='
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it
 '

First Run

For the first few runs, while you're getting familiar with AutoMySQLBackup, I recommend backing up just one or two small databases. It's safe and can't hurt your databases, but the larger your databases the longer it takes to try different configuration tweaks. Run it from the command line like this:

# automysqlbackup /etc/automysqlbackup/servername.conf

Then you can check your backup directory to see if it worked. It should have created all the necessary directories:

# ls /home/mysqlbackups/
daily/  
fullschema/  
latest/  
monthly/  
status/  
tmp/  
weekly/

And made entries like this, for each database you backed up, in the daily/ directory:

-rw-r--r-- 1 root root 66428 Oct 25 00:12 daily_db_name_2012-10-22_00h05m_Monday.sql.gz

AutoMySQLBackup generates a detailed log file, so there will be no mysteries about what happened if anything goes wrong.

The README offers a simple backup script:

#!/bin/sh
/usr/local/bin/automysqlbackup /etc/automysqlbackup/myserver.conf
chown root.root /var/backup/db* -R
find /var/backup/db* -type f -exec chmod 400 {} \;
find /var/backup/db* -type d -exec chmod 700 {} \;

You'll need to change myserver.conf to your configuration file name, and /var/backup/ to your own backup directory. This script ensures that the backup files owner is root, and that they are read-only, which are simple precautions against accidental changes. Don't forget to make it executable.

Scheduled Backups

Good old cron is still champ for scheduling backups. This example runs the backup script every morning at 12:05AM:

5 0 * * *  /usr/local/bin/backupscript 

Offsite Backups

Now that you have these nice database backups, what do you do with them? I use Horcrux, which we learned about last week, to encrypt and copy them to an offsite backup. So all I have to do to add my database backups to my Horcrux backup is to add this line to my Horcrux configuration:

+ /home/mysqlbackups/

Because these are compressed tarballs, you can manage them like any ordinary files and copy them to whatever backup media you want.

What about restoring from backup? AutoMySQLBackup doesn't offer any special tools for making that any easier, so you'll have to do it manually by first unzipping the backup file, and then using the mysql client to restore your database or databases. The README and forums are helpful, but what will help the most is knowing how to administer MySQL databases.

 

Comments

Subscribe to Comments Feed
  • Nick Lee Said:

    So very useful - can't believe I've not been backing mine up (but it is only a localised test thing). I am now, and will offload monthly's to something somewhere (dropbox, maybe? Less than 300K a day, I think).

  • Ron Said:

    Why not use Percona's Innobackupex, aka XtraBackup? It does a perfect job of taking point-in-time, binary snapshots of a MySQL data directory. Works best with InnoDB storage engine, but why would anyone use anything else for live-updating data?

  • Alfie Besin Said:

    Very helpful - it saves my time a lot. I tried it myself and it just works perfectly. Something that I want to add is that you need to create first the directory when having running directly the install.sh. Say you specify # Backup directory location e.g /backups CONFIG_backup_dir='/home/mysqlbackups' you need to create the mysqlbackups directory, otherwise you get an error like this # Checking for permissions to write to folders: base folder /var/backup ... does not exist. Exiting. Note: Supplied more than one argument, ignoring ALL arguments - using default an d global config file only. Error: No basedir found, i.e. /usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor /usr/local/bin/automysqlbackup: line 836: 7: Bad file descriptor Skipping normal output methods, since the program exited before any log files could be created best regards, alfie

  • Paul Said:

    very helpful and detailed. And it works very nicely. New to Horcrux - being a duplicity wrapper, I assume it can do unattended upload to amazon s3

  • Mick Said:

    I am having the error message '/usr/local/bin/automysqlbackup: line 835: 6: Bad file descriptor' as described by Alfie Besin. His description of how to avoid this is not very clear. Does anyone else know how to resolve this?

  • Alfie Besin Said:

    @Mick, in your config file you have CONFIG_backup_dir='/home/mysqlbackups', under the /home, you need to manually create a folder "mysqlbackups". that will save you. best regards, alfie

  • frank Said:

    After the update to MySQL 5.5.31 there is an issue with some mysql.event table and a warning in automysqlbackup script, see: https://sourceforge.net/projects/automysqlbackup/forums/forum/350022/topic/8033443/index/page/1 and: http://datacharmer.blogspot.de/2013/02/mysql-and-warnings-yet-another.html I had to put two new parameters under: [mysqldump] events ignore-table=mysql.events and restart the mysql server. Hope that helps somebro. frank

  • naveenkathait Said:

    Thanks a lot!! Thanks for sharing the steps! Was really very helpful.

  • suresh Said:

    Hi , I found this tool is very usefull for every linux admin , Please help to how to get incremental back and restore of incremental backup. Thanks in advances

  • Edwin Said:

    I'm starting to use AutoMySQLBackup and I had a question about optimization. Can I pass some optimization parameters to AutoMySQLBackup so that it applies it to the mysqldump command? If so, how do I do this? For example, if I want the --opt switch to be used when AutoMySQLBackup runs mysqldump, how would I specify this to AutoMySQLBackup?

  • pankaj Said:

    its works very well.but its does not contain database names but only tables!!Any thing Suggestion for this.Full Backup data.

  • Sathish Said:

    I use SQLyog from www.webyog.com for this. We can schedule MySQL backups at regular intervals of time.

  • MarekS Said:

    I was having problems in getting this running on the restricted (semi-secure) user. Unfortunately 'SELECT' is not the only privilege that has to be granted. Correct list of privileges: GRANT SELECT, LOCK TABLES, EVENT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'secretpassword';

  • Alex Said:

    If you're set on a Parallels (Plesk) server you should add in servername.conf: - PATH=${PATH}:/var/lib:/var/lib/mysql - CONFIG_mysql_dump_password=`cat /etc/psa/.psa.shadow` - CONFIG_mysql_dump_username='admin'

  • Tom Said:

    When I run my configuration I receive this error: # Parsing databases ... Note: Parsed config file /etc/automysqlbackup/myserver.conf. Error: The mysql server is empty, i.e. no databases found. Check if something is wrong. Exiting. In the error log it says: Error log below.. mysql: unknown variable 'bind-address=127.0.0.1' Any idea why it is saying this? I'm backing the database up on the same server to prep it for aws. I've gone through the configuration file 5 times and everything looks correct.

  • Tom Said:

    Turns out it was a bad configuration in the mysql setup.


Who we are ?

The Linux Foundation is a non-profit consortium dedicated to the growth of Linux.

More About the foundation...

Frequent Questions

Join / Linux Training / Board