Over the years I have looked around for easy to use MySQL database backup tools that I can quickly and reliably automate. I’ve tried many unsatisfactory solutions but finally I have found something I am happy with, Perscona’s XtraBackup.
Unfortunately for Windows users this is a Linux only solution.
Percona is an enterprise focused database company with the aim of delivering their clients faster and more reliable MySQL solutions. Fortunately for the rest of us they also believe in open source and offer a number of free tools suitable for all MySQL and MariaDB users in addition to their own MySQL server platform.
Below is a Markdown formatted cheat sheet that I wrote which enables Linux users to easily back-up their MySQL compatible databases. With simple instructions on how to create, restore and automate backups. While the instructions were written with Ubuntu/Debian in mind they should be useful for all other distributions with the usual adjustments.
Installing and using Percona XtraBackup
For MySQL/MariaDB database servers on Ubuntu.
Set-up and Install
Add Percona to your repository.
Backup your sources.
sudo cp /etc/apt/sources.list /etc/apt/sources.list.backup
Edit your sources.
sudo nano /etc/apt/sources.list
Append Percona’s sources to the end of file.
If needed, change
trusty (Ubuntu 14.04 LTS) to the repo name of your Ubuntu install. Ubuntu 12.04 would be
# Percona deb http://repo.percona.com/apt trusty main deb-src http://repo.percona.com/apt trusty main ``` ###### Add the repository's key to your collection. `sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A` > ``` gpg: Total number processed: 1 gpg: imported: 1 ``` ###### Update your packages list. `sudo apt-get update` ###### Install XtraBackup. `sudo apt-get install percona-xtrabackup` ###### Check that everything installed okay. `xtrabackup --version` > `xtrabackup version 2.2.9 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )` `innobackupex --version` > `InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy` ###### Create a backup staging directory. `sudo mkdir /var/backups/mysql/` ### Backup database ###### Create a daily, weekly, monthly and quarterly set of backups. * `innobackupex` is the backup shell script. * `--compact` saves space but takes longer to compress. * `--no-timestamp` removes the default behavior of appending the current time to the backup directory. * `--user` database user account to grant XtraBackup access. * `--passsword` database user account password. ``` sudo innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/daily ``` > ``` innobackupex: Backup created in directory '/var/backups/mysql/daily' innobackupex: Connection to database server closed innobackupex: completed OK! ``` ``` sudo innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/weekly sudo innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/monthly sudo innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/quarterly ``` ### Create backups CRON jobs aka time-based job schedulers. `sudo crontab -e` Append at the bottom of the file after the following. > ``` # For more information see the manual pages of crontab(5) and cron(8) # # m h dom mon dow command ``` ``` # Percona XtraBackup for database backup @daily mv -f /var/backups/mysql/daily /var/backups/mysql/daily~ && innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/daily @weekly mv -f /var/backups/mysql/weekly /var/backups/mysql/weekly~ && innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/weekly @monthly mv -f /var/backups/mysql/monthly /var/backups/mysql/monthly~ && innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/monthly 0 0 1 1,4,7,10 * mv -f /var/backups/mysql/quarterly /var/backups/mysql/quarterly~ && innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/quarterly ``` The job listed last runs on the first day of the month at midnight on Jan, Apr, Jul and Oct. The other jobs that use @special strings also run at midnight. If you wanted to, you could change the daily to run at 2:30am. ``` 30 2 * * * mv -f /var/backups/mysql/daily /var/backups/mysql/daily~ && innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/daily ``` Or the weekly job to run every Monday evening at 11pm. ``` 0 23 * * 1 mv -f /var/backups/mysql/weekly /var/backups/mysql/weekly~ && innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/weekly ``` ['Further reading, Cron Howto'](https://help.ubuntu.com/community/CronHowto) ### Restore a backup to the database First we must prepare the backup for restoration. This completes any uncommitted transactions and pending operations. `innobackupex --apply-log /var/backups/mysql/daily` The last line of the `--apply-log` returned output should say OK! > ` innobackupex: completed OK!` Then we restore the backup but to do this the database's *datadir* `/var/lib/mysql/` needs to be empty for the restoration to be successful. So we shut down MySQL and move its current data directory into a backup location. ###### Stop MySQL `sudo service mysql stop` > `mysql stop/waiting` ###### Duplicate then delete existing data `sudo cp -aR /var/lib/mysql/ /var/lib/mysql~/` ###### Double check that everything copied okay (no output is good) `sudo diff -qr /var/lib/mysql/ /var/lib/mysql~/` > ` ` ###### Delete the MySQL data and check that its directory is empty ``` sudo rm -R /var/lib/mysql/* ll /var/lib/mysql/ ``` ###### Restore the backed up data to database data `innobackupex --copy-back /var/backups/mysql/daily` > `innobackupex: completed OK!` ###### Grant MySQL ownership permission to the data. `sudo chown -R mysql:mysql /var/lib/mysql` ###### Restart MySQL `sudo service mysql start` > `mysql start/running, process` Congratulations! If there are any issues and MySQL does not start you can can check its log file. ``` tail /var/log/mysql/error.log ``` And if worse comes to worse, restore the pre-deleted data. ``` sudo rm -R /var/lib/mysql sudo mv /var/lib/mysql~ /var/lib/mysql sudo service mysql start ``` ### Learn more Percona XtraBackup offers much more functionality than what is listed here, including encryption, parallel and threading and selective database backups. You can download its [User's Manual](http://form.percona.com/Percona-XtraBackup-22-Series-Manual.html) (requires supplying an e-mail address to access) to learn more about this excellent tool.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
|# version 2.1.0|
|# A simple script that backs up an MySQL compatible database.|
|# Designed for Percona XtraBackup (innobackupex).|
|# Intended to be used as a cron-job.|
|# sudo crontab -e|
|# 12:00am – daily|
|# 00 00 * * * sql-backup -d|
|# 12:30am – weekly|
|# 30 00 * * 1 sql-backup -w|
|# 12:01 – monthly|
|# 00 01 * 1 * sql-backup -m|
|# 1:30am – quarterly|
|# 30 01 1 1,4,7,10 * sql-backup -q|
|PARSED_OPTIONS=$(getopt -n "$0" -o hdwmq –long "help,daily,weekly,monthly,quarterly" — "[email protected]")|
|if [ $? -ne 0 ];|
|echo "$0: unrecognised option '$1'"|
|echo "Try '$0 –help' for more information."|
|if [ "$EUID" -ne 0 ]|
|then echo "Please run as root"|
|if [ -f "$FILE" ]|
|echo "Required $FILE not found."|
|case "$1" in|
|echo "Usage: $0 [OPTION]"|
|echo "Backup the database using a specific method."|
|echo "One option is mandatory."|
|echo " -h, –help display this help and exit"|
|echo " -d, –daily"|
|echo " -w, –weekly"|
|echo " -m, –monthly"|
|echo " -q, –quarterly"|
|echo "Running the $METHOD backup."|
|# if exists move secondary backup to a temporary location that will be deleted afterwards.|
|if [ -d "$BACKUPS/$METHOD~" ]|
|mv -f $BACKUPS/$METHOD~ $BACKUPS/$METHOD~~|
|# move primary backup to the secondary backup.|
|if [ -d "$BACKUPS/$METHOD" ]|
|mv -f $BACKUPS/$METHOD $BACKUPS/$METHOD~|
|# create a new primary backup|
|innobackupex –no-timestamp –user=root –password=$data $BACKUPS/$METHOD|
|# change group so database can be remotely backed up|
|chown -R :ben $BACKUPS/*|
|# delete the former secondary backup|
|if [ -d "$BACKUPS/$METHOD~~" ]|
|rm -rdf $BACKUPS/$METHOD~~|