Xtrabackup to backup/restore MySql

Along with widely used mysqldump there are often times when Raw backups need to be performed.

These are my notes on this subject.

Raw Backup vs Logical Backup

Below backup comparison is an excerpt from High Performance Mysql 2nd ed

BTW they released 3rd edition of High Performance Mysql in March 2012.

Logical Backup

Pros:

Consists of normal text files; which means they’re editable/inspectable with regular tools like sed/awk/vim
Easy to restore using mysql or mysqlimport
Easy to backup/restore across the network
Flexible as mysqldump has many options and WHERE sql clause to filter records
Storage engine independant
With right options backup is DB neutral

Cons:

CPU hog: server has to do the work in order to backup/restore/reindex
Logical backups may take more space than raw data due to textual representation (integers for example)
Loss of precision in floating-point representation

Raw Backup

Pros:

Faster than logical backup to generate/restore, as server doesn’t need to do any special processing
Restoring is usually just copying files(with some exceptions)
Pretty portable across platforms/OS/MySQL version

Cons:

Often far larger than “dumps” due to the allocated extra space.
Not always portable across platforms/OS/mysql version

Also

Raw backups are generally easier and more efficient. You should not rely on them for long-term retention or legal requirements, though; you must make Logical backups at least periodically. Don’t consider a backup(especially a Raw backup) to be good until you’ve tested it. For InnoDB, that means starting a MySQL instance and making sure restoring worked with mysqlcheck for example.

A smart way to backup is to use both approaches: make raw copies, start MySQL instance and use it to create Logical backup.

Xtrabackup

Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases

I’m building it on OSX just for learning purpose.

Building Xtrabackup on OSX

Download and extract

$ cd /tmp
$ curl -L http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.1/source/percona-xtrabackup-2.0.1.tar.gz | tar -xz
$ cd percona-xtrabackup-2.0.1/

Now build it.

$ cat BUILD.txt 

Compiling with build.sh
-----------------------
  innodb55           5.5	build against InnoDB in MySQL 5.5

Building is MySQL version dependant so make sure you choose the right one. Mine was

$ mysqld --version
mysqld  Ver 5.5.13 for osx10.6 on i386 (MySQL Community Server (GPL))

Therefore:

$ AUTO_DOWNLOAD=yes ./utils/build.sh innodb55

builds to src/xtrabackup_innodb55.

Now in order to be able to run those binaries from anywhere they need to be added somewhere on PATH.

$ ln -s innobackupex ~/bin/
$ ln -s src/xtrabackup_innodb55 ~/bin/

For production system they probably need to be moved to /opt/bin for example.

Simple my.cnf is used here to point innobackupex to the right data source.

# /tmp/mysql_bak/my.cnf
[mysqld]
port     = 3306
basedir  = /usr/local/mysql
datadir  = /usr/local/mysql/data

But for production systems real /etc/my.cnf must be used.

Backup Procedure

Running backup is straightforward:

$ innobackupex --user=root --databases='an_app' --defaults-file=/tmp/mysql_bak/my.cnf --ibbackup=xtrabackup_innodb55 /tmp/mysql_bak/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

...

innobackupex: Backup created in directory '/tmp/mysql_bak/2012-07-26_13-53-43'
innobackupex: MySQL binlog position: filename '', position 
120726 13:54:01  innobackupex: completed OK!

And it finished. See innobackupex options for more details.

But copying files is not enough! Read on…

Apply Logs

For backup to become restore-ready, transaction logs need to be applied first.

$ innobackupex --apply-log --defaults-file=/tmp/mysql_bak/my.cnf --ibbackup=xtrabackup_innodb55 /tmp/mysql_bak/2012-07-26_13-53-43/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy

...bunch of output

120726 13:55:01  innobackupex: completed OK!

Testing raw backup

In order to test the backup - new MySQL instance needs to be started. So lets recreate data directory copying backup files first:

$ cp -r /tmp/mysql_bak/2012-07-26_13-53-43/ /tmp/data

Now initialize MySQL datadir there:

$ /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/tmp/data

Installing MySQL system tables...  OK
Filling help tables...             OK

Finally run MySQL daemon on port 3307

$ mysqld --basedir=/usr/local/mysql --datadir=/tmp/data/ -P 3307 &

Storing pid for future reference is a good idea:

$ echo $! > mysql.pid

Now launch mysql client to check what we’ve got

$ mysql -uroot -P 3307

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| an_app                        |
| mysql                         |
| information_schema            |
| performance_schema            |
| test                          |
+-------------------------------+

Running full check is a good idea as well.

$ mysqlcheck -P 3307 --user root --all-databases

an_app.countries                                   OK
an_app.users                                       OK
mysql.columns_priv                                 OK
mysql.db                                           OK
...
mysql.user                                         OK

Now it’s a good time to use mysqldump to create Logical backup, if you need to:

$ mysqldump -uroot -P 3307 an_app > dump.sql

Stop instance once done

$ kill `cat mysql.pid`

Restoring from Raw backup

$ innobackupex --copy-back --defaults-file=/tmp/mysql_bak/my.cnf --ibbackup=xtrabackup_innodb55 /tmp/mysql_bak/2012-07-26_13-53-43/

more about restoring

TODO

seems like there’s no way to restore only specific DBs(if case of partial backup): innobackupex requires destination datadir to be empty