MySQL Percona xtrabackup

Percona XtraBackup是一个MySQL中完全免费开源的热备工具,而备份时并不会锁定数据库(innodb和xtradb)。xtrabackup的下载地址可以在Percona XtraBackup 找到,percona提供了rpm和deb的发行包来进行快速安装。
Percona支持增量、加密、并行和压缩流(compressed Stream)等功能,并具有较快的恢复速度。并适用于MySQL,MariaDB和Percona Server等主流版本,可以完全取代商业产品MySQL Enterprise Backup。二者功能的对比可以参考:About Percona Xtrabackup
XtraBackup由下面4个工具组成:
innobackupex 提供MyISAM,InnoDB和XtraDB表的MySQL数据库实例备份的封装脚本。
xtrabackup 使用C语言开发的热备工具,只能备份InnoDB和XtraDB数据。
xbcrypt 用于对备份文件加密和解密。
xbstream 提供streaming和从xbstream格式中写入/提取数据等功能。
Percona建议使用innobackupex来代替使用xtrabackup这个二进制工具来完成备份的功能。

1. 全量备份

innobackupex/xtrabackup使用非常简单,指定连接参数–host, –user, –password, -socket, –port等参数和备份目录就可以完成全量备份。另外可以使用并行–parallel=n来提高备份速度。
innobackupex –user=root –password=” –no-timestamp /root/backup
……
innobackupex: Backup created in directory ‘/root/backup0′
140724 08:36:51 innobackupex: Connection to database server closed
140724 08:36:51 innobackupex: completed OK!
当指定了–no-timestamp选项时,就会直接将文件备份到/root/backup下。否则就会在/root/backup目录下建立一个带timestamp标记的目录名,例如:/root/backup/2014-07-25_08-38-33.
被备份目录下,可以找到备份的详细信息。由于是全量备份,innodb_from_lsn =0。

2. preparing

关于preparing的解释,可以在Preparing a Full Backup with innobackupex找到:
After creating a backup, the data is not ready to be restored. There might be uncommitted transactions to be undone or transactions in the logs to be replayed. Doing those pending operations will make the data files consistent and it is the purpose of the prepare stage. Once this has been done, the data is ready to be used.
有两个相关参数, –apply-log表示进行preparing过程,–use-memory指的是apply-log使用的内存大小,指定大的内存会提高preparing的速度。默认为100MB。
–apply-log
Prepare a backup in BACKUP-DIR by applying the transaction log file
named “xtrabackup_logfile” located in the same directory. Also,
create new transaction logs. The InnoDB configuration is read from
the file “backup-my.cnf”.
–use-memory=B
This option accepts a string argument that specifies the amount of
memory in bytes for xtrabackup to use for crash recovery while
preparing a backup. Multiples are supported providing the unit (e.g.
1MB, 1GB). It is used only with the option –apply-log. It is passed
directly to xtrabackup’s –use-memory option. See the xtrabackup
documentation for details.
执行preparing
innobackupex –use-memory=2GB –apply-log /root/backup
……
InnoDB: Shutdown completed; log sequence number 9594069164
140725 09:10:36 innobackupex: completed OK!
数据文件lsn被apply到最新的位置。

3. 部分备份

部分备份可以参考Partial Backups,可以进行其中几个数据库(–databases),表(–tables-file 提供一个表的列表),还可以基于正则表达式的匹配(–include)。部分备份也需要进行preparing。
备份tpcc数据库:
innobackupex –database=tpcc100 –user=root –password=” –no-timestamp /root/backup-tpcc
innobackupex –apply-log –use-memory=1G /root/backup-tpcc

4. 增量备份

增量备份需要基于全量目录(incremental-basedir)并扫描数据库当前binlog,是一个比较耗时的过程。执行过程如下:
innobackupex –incremental –incremental-basedir=/root/backup –user=root –password=” –no-timestamp /root/backup-inc0
……
xtrabackup: Creating suspend file ‘/root/backup-inc0/xtrabackup_log_copied’ with pid ‘29032’
xtrabackup: Transaction log of lsn (9594070898) to (9594070898) was copied.
140725 09:23:45 innobackupex: Executing UNLOCK BINLOG
140725 09:23:45 innobackupex: Executing UNLOCK TABLES
140725 09:23:45 innobackupex: All tables unlocked

二级增量备份

二级增量备份需要指定一级增量备份目录为basedir。从备份时间依然十分漫长~··
innobackupex –incremental –incremental-basedir=/root/backup-inc0 –user=root –password=” –no-timestamp /root/backup-inc1
备份完成后,查看backup-inc0的xtrabackup-info
innodb_from_lsn = 9594065461
innodb_to_lsn = 9594070898
查看backup-inc1的xtrabackup-info
innodb_from_lsn = 9594070898
innodb_to_lsn = 9594074574
preparing
对于增量备份需要有说明:
First, only the committed transactions must be replayed on each backup. This will merge the base full backup with the incremental ones.
Then, the uncommitted transaction must be rolled back in order to have a ready-to-use backup.
If you replay the committed transactions and rollback the uncommitted ones on the base backup, you will not be able to add the incremental ones. If you do this on an incremental one, you won’t be able to add data from that moment and the remaining increments.
也就是说,在对于basedir(0级的全量备份)进行preparing之后,可以将1级增量备份合并到全量备份之中,这个合并的速度是非常快的。
innobackupex –apply-log –redo-only /root/backup –incremental-dir=/root/backup-inc0
然后将二级增量备份合并到basedir之中:
innobackupex –apply-log –redo-only /root/backup –incremental-dir=/root/backup-inc1
这时我们再看basedir的xtrabackup_info,会发现


innodb_to_lsn = 9594074574
该lsn已经到达了二级增量备份的位置。

5. 恢复测试

先对数据库进行破坏
mysqladmin -uroot -pdebugo drop tpcc100;
Database “tpcc100″ dropped
……
恢复要求datadir为空:The datadir must be empty; Percona XtraBackup innobackupex –copy-back option will not copy over existing files.
service mysql stop
rm -rf /var/data/mysql/*
rm -rf /var/log/mysql/ib* #binlog目录不在datadir下,即设置了innodb_log_group_home_dir=/var/log/mysql
innobackupex –copy-back /root/backup
等待完成。然后启动mysqld
如果使用其他用户执行copy-back,记得要恢复权限
chown -R mysql:mysql /var/data/mysql
chown -R mysql:mysql /var/log/mysql
service mysql start
Starting MySQL (Percona Server)…….. SUCCESS!
检查数据,完成。~·
对于部分备份的恢复,可以在shutdown mysqld的情况下拷贝相应的表/数据库文件到对应的目录(启动mysqld时候记得权限问题)。

^^

参考:

http://www.percona.com/doc/percona-xtrabackup/2.2/manual.html

http://willvvv.iteye.com/blog/1544043 使用Percona XtraBackup热备和恢复MySQL(一)
http://willvvv.iteye.com/blog/1561124 使用Percona XtraBackup热备和恢复MySQL(二)
http://blog.chinaunix.net/uid-10661836-id-4123246.html 详解Percona的XtraBackup备份工具(上篇)
http://blog.chinaunix.net/uid-10661836-id-4124968.html 详解Percona的XtraBackup备份工具(下篇)

Posted in Database, MySQL.