Xtrabackup to backup/restore MySql

gmarik 4 min
Table Of Contents ↓

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:

Cons:

Raw Backup

Pros:

Cons:

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

Read More
Gem Rails Like Sinatra Talk
Large MySQL migrations with pt-online-schema-change
Comments
read or add one↓