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.
- Consists of normal text files; which means they’re editable/inspectable with regular tools like
- Easy to restore using
- Easy to backup/restore across the network
- Flexible as
mysqldumphas many options and
WHEREsql clause to filter records
- Storage engine independant
- With right options backup is DB neutral
- 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
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
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.
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))
$ AUTO_DOWNLOAD=yes ./utils/build.sh innodb55
Now in order to be able to run those binaries from anywhere they need to be added somewhere on
$ ln -s innobackupex ~/bin/ $ ln -s src/xtrabackup_innodb55 ~/bin/
For production system they probably need to be moved to
/opt/bin for example.
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.
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.
innobackupex options for more details.
But copying files is not enough! Read on…
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
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
MySQL daemon on port
$ mysqld --basedir=/usr/local/mysql --datadir=/tmp/data/ -P 3307 &
pid for future reference is a good idea:
$ echo $! > mysql.pid
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
- seems like there’s no way to restore only specific DBs(if case of partial backup): innobackupex requires destination datadir to be empty