Monday, February 6, 2012

Testing Percona Xtrabackup (innobackupex)

My last post dealt with the installation of the Percona Xtrabackup tool for use on a Mysql innodb database.
After some tweaking and help from the Percona Docs, the following is a test backup, prep and restore of the full database (not incremental).

This assumes a test environment, so please don't try this on a running production environment. Before the database can be restored, the existing one will have to be renamed or moved. The options for this are straightforward. Stop the mysql daemon and make a binary copy of the files, or rename the source folder.

0. Login as root/su

1. Prerequisite configuration items

Configure datadir and in my.cnf and reboot mysqld service
nano /etc/my.cnf
 --find section
--add line

service mysqld restart

2. Run full backup of specific database
innobackupex --defaults-file=/etc/my.cnf --user=***** --password=***** --databases=mydbname /path/to/backup/

3. Prepare backup files
innobackupex --apply-log --user=****** --password=****** /path /to/backup

4. Restore backup
service mysqld stop
cp –r /path/to/backup /var/lib/mysql
chown –R mysql:mysql /var/lib/mysql
chmod –R 771 /var/lib/mysql
service mysqld start


Wednesday, February 1, 2012

Installing Percona xtrabackup on CentOS 5.x

This covers the basic installation of the xtrabackup package on CentOS. This is necessary in order to take hot backups of a database using the InnoDB storage engine. By default, Mysql has no options to take a full hot backup of a live InnoDB database.

For more information,  please see Percona's website located here:

0. Login as root

1. Get rpm from Percona
(check your server version before installing, there are specific RPM’s for 32-bit and 64-bit)
see here for rpm list:
cd /tmp

2. Install rpm (32-bit on this test machine)
Rpm –Uvh percona-release-0.0-1.i386.rpm

3. Check repo for package
yum list | grep percona

4. Install xtrabackup
yum install xtrabackup --nogpgcheck
I still have testing to do on actual usage, so I'll create a new post on tests of backups and restoring databases.