Xtrabackup Installation and Restore For MYSQL + INNODB

Installation

a) Using Percona Software Repositories

Run below command. This will create the Percona YUM repository

sudo rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

Testing the Repository: This will display the repository list

sudo yum list | grep percona

Check the Xtrabackup install or not

rpm -qi xtrabackup

If it is not installed then install using below command and verify again using above command

sudo yum install xtrabackup

b) Using Generic .tar.gz binary packages

If you don’t want to install using Percona YUM repository use the below link to download ready to use Xtrabackup tool

http://www.percona.com/downloads/XtraBackup/XtraBackup-1.5/Linux/binary/

Note: - For Innodb database backup you must have innodb_file_per_table set in my.cnf.

Creating Backup

sudo ./innobackupex-1.5.1 --user=username --password=password /path/to/save/backup --databases=”databaseName”

Note: - the user should have the read/write access to the both data and target folder, also check .frm file copied or not in database backup folder

Backup Completes with following statements:

120209 01:57:22 innobackupex-1.5.1: completed OK!

Restore Backup

Prepare using binary package

Sudo ./innobackupex-1.5.1 --apply-log /path/to/save/backup/timestamp_folder

Copy Data directory

You could simply use cp to copy the files, also make sure the data directory and files are owned by the mysql user.

Stop the server and then run the below copy command

cp -R /path/to/save/backup/timestamp_folder/* /var/lib/mysql/

And run the below command

sudo chown –R mysql:mysql /var/lib/mysql

Start the server and check the database

Reference:

http://www.percona.com/doc/percona-xtrabackup/howtos/recipes_xbk_restore.html

http://agiletesting.blogspot.com/2010/09/mysql-innodb-hot-backups-and-restores.html

http://www.ovaistariq.net/590/on-hot-backups-and-restore-using-xtrabackup/