Xtrabackup

Percona XtraBackup is an high performance, low-profile and non-blocking open-source backup utility for InnoDB and XtraDB databases.

Currently is the only open-source tool that performs backups on 'MySQL-based' servers (MySQL, MariaDB, Percona Server) that doesn't lock your database during it. Backups are online, and queries and transactions continue to run without interruption ('hot backups').

It can back up data from InnoDB, XtraDB, and MyISAM tables on MySQL 5.0 and 5.1 servers, and it has many advanced features like partial, remote, compressed and incremental backups, multi-threaded file copying for performance and Point-in-time recovery.

xtrabackup

Making the backup copies the InnoDB data and log files to the destination and preparing the backup makes the data files consistent and ready to use.

Make a backup:

# xtrabackup --backup

Prepare the backup:

# xtrabackup --prepare

Prepare again, to create fresh InnoDB log files:

# xtrabackup --prepare

The exit status of a successful xtrabackup is 0. In the second --prepare step, you should see InnoDB print messages similar to “Log file ./ib_logfile0 did not exist: new to be created”, followed by a line indicating the log file was created.

You might want to set the --use-memory option to something similar to the size of your buffer pool, if you are on a dedicated server that has enough free memory. The complete documentation of the tool is here.

innobackupex

Copying all your MySQL data from the specified directory in your my.cnf.

It will put the backup in a timestamped subdirectory of /data/backups/ by default (in this example, /data/backups/2010-03-13_02-42-44).

# innobackupex /data/backups

Note: There is a lot of output, but you need to make sure you see this at the end of the backup. If you do not see this output, then your backup failed:

# 100313 02:43:07 innobackupex: completed OK!

If you take the backup from a replica (“slave”), then you might need to see the position relative to the primary (“master”) at the point of the backup:

Note: You can use this procedure to add new slaves to a master. Use the same commands to clone an instance already configured as a slave. Just before setting the master for the slave ('CHANGE MASTER TO') run: