xtrabackup.sh

Performs backups of MySQL databases using Percona's xtrabackup. xtrabackup allows for faster, safe backups and restores than mysqldump while databases are in use. For more information, please check out this blog post.

On the first run, the script creates the configuration file ~/.xtrabackup.config (in your home folder) with the following defaults:

MYSQL_USER=your current MySQL username
MYSQL_PASS=(left blank)
MYSQL_DATA_DIR=/var/lib/mysql/
BACKUPS_DIRECTORY=$HOME/mysql-backups
MAX_BACKUP_CHAINS=3

You'll need to customise them accordingly to your setup and needs.

Backing up

Backing up the databases is simple, just run

xtrabackup.sh [incr|full] 

where the single argument determines whether you want to perform a full or incremental backup. If running an incremental backup but no previous backups are found in the target directory, a full backup is performed instead.

Backups are stored with the following directory tree within the backups directory:

├── full
│   ├── 2012-08-04_15-54-23
│   └── 2012-08-04_15-59-29
└── incr
    ├── 2012-08-04_15-55-01
    └── 2012-08-04_15-56-44

Each folder has a file named backup.chain that contains all the folders (full + incrementals) that belong to that backup chain/set. This is useful when restoring from an incremental. E.g.

$ cat incr/2012-08-04_16-04-19/backup.chain 
/backup/mysql//full/2012-08-04_15-59-29
/backup/mysql//incr/2012-08-04_16-02-42
/backup/mysql//incr/2012-08-04_16-03-34
/backup/mysql//incr/2012-08-04_16-04-19

The order of course is the order with which these full + incremental backups should be restored.

To list the available backup chains you can run xtrabackup.sh list:

$ backup/xtrabackup.sh list
Loading configuration from /root/.xtrabackup.config.
Available backup chains (from oldest to latest):

Backup chain 1:
        Full:        2012-08-04_15-54-23
        Incremental: 2012-08-04_15-55-01
        Incremental: 2012-08-04_15-56-44
Backup chain 2:
        Full:        2012-08-04_15-59-29
        Incremental: 2012-08-04_16-02-42
        Incremental: 2012-08-04_16-03-34
        Incremental: 2012-08-04_16-04-19

Latest backup available:
        Incremental: 2012-08-04_16-04-19

Restoring

Restoring a backup is as simple as:

$ backup/xtrabackup.sh restore <BACKUP TIME STAMP> <DESTINATION DIRECTORY>

For example:

$ backup/xtrabackup.sh restore 2012-08-04_19-03-46 /test-restore/
Loading configuration from /root/.xtrabackup.config.
!! About to restore MySQL backup taken on 2012-08-04_19-03-46 to /test-restore/ !!

- Restore of full backup from /backup/mysql//full/2012-08-04_18-50-09
Copying data files to destination...
...done.

Preparing the base backup in the destination...
...done.

- Applying incremental from /backup/mysql//incr/2012-08-04_19-03-46...
...done.

Finalising the destination...
...done.

The destination is ready. All you need to do now is:
  - ensure the MySQL user owns the destination directory, e.g.: chown -R mysql:mysql /test-restore/
  - stop MySQL server
  - replace the content of the MySQL datadir (usually /var/lib/mysql) with the content of /test-restore/
  - start MySQL server again

The backup timestamp passed as argument must be that of a valid full or incremental backup as shown running backup/xtrabackup.sh list.

The restore will first process the base backup of the relevant backup chain (that is, the full backup that started the backup chain) and then, if an incremental has been specified for restore, all incrementals up to that one will be applied to the destination.

If all goes well the destination directory will be ready for use with MySQL. At this point, as suggested by the script you just need to replace the contents of MySQL's data directory with that of the destination directory of your restore, making sure permissions and ownership are properly set.

If something fails during a restore, details of the failure will be available in a log file and the restore will be aborted:

$ backup/xtrabackup.sh restore 2012-08-04_19-03-46 /test-restore/
Loading configuration from /root/.xtrabackup.config.
!! About to restore MySQL backup taken on 2012-08-04_19-03-46 to /test-restore/ !!

- Restore of base backup from /backup/mysql//full/2012-08-04_18-50-09
Copying data files to destination......
FAILED! See /var/log/xtrabackup/restore-2012-08-04_19-03-46.log for details - aborting.