Linux MySQL Backup

Create robust MySQL backups using AutoMySQLBackup

The SCS Computing Facilities (SCSCF) Archival Backup service may not properly back up files associated with MySQL databases. We strongly encourage 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. 
Please note that SCSCF 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 setting s in /etc/default/automysqlbackup. For example, if the computer you are running automysqlbackup on is under Archival Backup support, you must change this to a directory like /usr0 that is backed up in order for your database backups to be properly 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 in order to restore the database that has been backed up. AutoMySQLBackup creates daily, weekly and monthly backups and places them in /var/lib/automysqlbackup/.

Decompressing a backup

# gunzip database_2018-05-20_13h31m.Friday.sql.gz

# ls

database_2018-05-20_13h31m.Friday.sql

Restoring a backup

You can use the mysql command line client in order to restore the uncompressed backup into a database:

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

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

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

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. Alternately, you may wish to create a test database and restore the backup to it for testing and validation prior to restoring to the production database.