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.
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)
If you are using an Ubuntu computer built by SCS Computing Facilities, you can install AutoMySQLBackup using this command: sudo apt-get install 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:
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
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.
AutoMySQLBackup home page
Automatic MySQLBackup on SourceForge
AutoMySQLBackup man page
Ubuntu AutoMySQLBackup man page
How to Back Up MySQL Databases with AutoMySQLBackup
mysqldump - A Database Backup Program
MySQL documentation on mysqldump