SCS Computing
 Services and Solutions
  links to the SCS and CMU home pages Carnegie Mellon School of Computer Science Carnegie Mellon University
 
 
 
 » How to… 
 » Accounts & passwords 
 » AFS 
 » AV help 
 » Backups & restores 
 » Calendaring 
 » E-mail 
 » Networking 
 » Printing 
 » Purchasing 
 » Resource management 
 » Security 
 » Software licensing 
 » Support charges 
 » Support lifecycle 
 » Web publishing 
 » Mac support 
 » Linux support 
 » Windows PC support 

Create robust MySQL backups using AutoMySQLBackup

The SCS Archival Backup service may not properly back up files associated with MySQL databases. SCS Computing Facilities strongly encourages anyone running a MySQL server to use a utility like mysqldump to back up MySQL databases.

While the mysqldump utility is well-suited for one-off backups of MySQL databases, we would recommend a more robust approach to ensure that you have the data you expect when you need it. The utility AutoMySQLBackup offers daily, weekly and monthly backups of MySQL databases and the flexibility to customize these backups to meet your specific needs.

NOTE: SCS Computing Facilities did not write AutoMySQLBackup and can make no guarantees about its behavior or be responsible for any data loss as a result of using this tool. We strongly recommend you test restores using the backups created by AutoMySQLBackup.

Prerequisites

In order to use AutoMySQLBackup, you will need to have:

  • Access to a Linux computer with mysqldump installed
  • Credentials to access a MySQL database (running locally on the machine with mysqldump installed or on an external host)

Install AutoMySQLBackup

If you are using an Ubuntu computer built by SCS Computing Facilities, you can install AutoMySQLBackup using this command: sudo apt-get install automysqlbackup

Configure AutoMySQLBackup

On Ubuntu, the configuration file for AutoMySQLBackup can be found at /etc/default/automysqlbackup.

Backup using AutoMySQLBackup

You can run the AutoMySQLBackup utility at any time to perform an on-demand backup. The command to run is:

/usr/sbin/automysqlbackup

You can also have the AutoMySQLBackup utility run periodically as a cron job. A crontab entry that silently backs up databases every day at 3:00 AM would look like this:0 3 * * * /usr/sbin/automysqlbackup >/dev/null 2>&1

The backup files are located in /var/lib/automysqlbackup by default. You can change this location by modifying the appropriate settings in /etc/default/automysqlbackup. For example, if the computer you are running automysqlbackup on is under Archival Backup support, you may wish change this to a directory like /usr0 that is backed up.

Restore using AutoMySQLBackup

AutoMySQLBackup creates a gzip compressed .sql file for each database that it backs up. You can decompress one of these files and import the .sql file using the command line. AutoMySQLBackup creates daily, weekly and monthly backups and places them in /var/lib/automysqlbackup/.

Decompressing a backup

# gunzip database_2016-05-20_13h31m.Friday.sql.gz
# ls
database_2016-05-20_13h31m.Friday.sql

Restoring a backup

# mysql -uroot -p databasename < database_2016-05-20_13h31m.Friday.sql

Alternately, you can decompress and restore the backup all in one command:

# gunzip < database_2016-05-20_13h31m.Friday.sql.gz | mysql -u root -p databasename

NOTE: The database you are restoring must exist. Depending on what kind of failure you are recovering from, you may need to use the CREATE DATABASE command prior to attempting this restore. You may also wish to perform a mysqldump manually prior to restoring a database to preserve the state of the database.

Related Documentation

AutoMySQLBackup home page
Automatic MySQLBackup on SourceForge

AutoMySQLBackup man page
Ubuntu AutoMySQLBackup man page

AutoMySQLBackup HOWTO
How to Back Up MySQL Databases with AutoMySQLBackup

mysqldump - A Database Backup Program
MySQL documentation on mysqldump