使用上面的备份文件增加一个Slave数据库。
现有环境：
现有一个数据库环境，信息如下：
Master：代号A
IP地址：192.168.115.149
端口：3340
Slave：代号B
IP地址：192.168.115.150
端口：3340
配置文件：/apps/mysql_3340/my.cnf
socket路径：/tmp/mysql_3340.sock
通过本地socket访问用户名：root
通过本地socket访问密码：无
新搭建数据库：代号C
以下实践都在B数据库所在的机器上操作。
xtrabackup相关程序下载地址：
http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.0/binary/xtrabackup-1.0.tar.gz
下载后解压，将三个可执行文件放到/usr/local/sbin/即可。
当前目录：/apps/backup/
使用innobackupex脚本对数据库进行备份，相关参数可以通过--help参数了解。
以下程序输出经过筛选，略去不必要信息：
[root@slave150 backup]# innobackupex-1.5.1 --defaults-file=/apps/mysql_3340/my.cnf --slave-info --socket=/tmp/mysql_3340.sock 3340 InnoDB Backup Utility v1.5.1-xtrabackup;
Copyright 2003, 2009 Innobase Oy. All Rights Reserved.
...版权信息...
innobackupex-1.5.1: Using mysql Ver 14.14 Distrib 5.1.43, for unknown-linux-gnu (x86_64) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
innobackupex-1.5.1: Created backup directory /apps/backup/3340/2010-03-19_16-51-33 100319 16:51:33
innobackupex-1.5.1: Starting mysql with options: --unbuffered --socket=/tmp/mysql_3340.sock -- 100319 16:51:33
innobackupex-1.5.1: Connected to database with mysql child process (pid=25246) 100319 16:51:37
innobackupex-1.5.1: Connection to database server closed 100319 16:51:37
innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --defaults-file="/apps/mysql_3340/my.cnf" --backup --suspend-at-end --target-dir=/apps/backup/3340/2010-03-19_16-51-33
innobackupex-1.5.1: Waiting for ibbackup (pid=25252) to suspend
innobackupex-1.5.1: Suspend file '/apps/backup/3340/2010-03-19_16-51-33/xtrabackup_suspended'
xtrabackup Ver 1.0 Rev 113 for 5.0.84 unknown-linux-gnu (x86_64) xtrabackup: uses posix_fadvise().
xtrabackup: cd to /apps/mysql_3340
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: use O_DIRECT >> log scanned up to (23 3600029795)
Copying ./ibdata1 to /apps/backup/3340/2010-03-19_16-51-33/ibdata1 >>
log scanned up to (23 3600060713) >>
log scanned up to (23 3600087337) >>
log scanned up to (23 3600121153) ...
done Copying ./xjqy2/configs.ibd to /apps/backup/3340/2010-03-19_16-51-33/xjqy2/configs.ibd
Copying ./game/answer_count.ibd to /apps/backup/3340/2010-03-19_16-51-33/game/answer_count.ibd ...
....
innobackupex-1.5.1: completed OK!
此时在备份的目录下会有一个xtrabackup_slave_info文件，是设置Slave的命令：
[root@slave150 2010-03-19_16-51-33]# cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=690623257
恢复备份文件：
[root@slave150 2010-03-19_16-51-33]# innobackupex-1.5.1 --apply-log --defaults-file=/apps/backup/3340/2010-03-19_16-51-33/backup-my.cnf /apps/backup/3340/2010-03-19_16-51-33/
100319 16:57:48 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --defaults-file="/apps/backup/3340/2010-03-19_16-51-33/backup-my.cnf" --prepare --target-dir=/apps/backup/3340/2010-03-19_16-51-33
...
下面两行是说，B数据库作为Slave时，所同步的A库日志的位置。 如果新的C库作为A的Slave，那么应该配置这个日志位置。 这个位置也是和上面的xtrabackup_slave_info是一致的。
InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 690623257, file name mysql-bin.000006
下面两行是说，B数据库自己的binlog位置。如果新的C库作为B的Slave，那么应该配置这个日志位置。
InnoDB: Last MySQL binlog file position 0 989572152, file name ./mysql-bin.000005 100319 16:57:52
InnoDB: Started; log sequence number 23 3602458752
[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:
InnoDB: Last MySQL 100319 16:57:55 InnoDB: Shutdown completed; log sequence number 23 3602459148 100319 16:57:55 innobackupex-1.5.1: completed OK!
至此，数据文件已经可用。 将准备好的数据移动到合适的位置，做好新的配置文件，即可启动数据库。
新的配置文件与原有Slave配置 文件基本一致，需要注意修改几个参数： server-id必须修改，否则会与原有主库产生冲突。 如果在同一台机器上进行测试，端口和socket文件必须修改。 启动后，通过mysql客户端连接新的数据库，执行xtrabackup_slave_info中的命令，执行时应该加上master_host等参数， 然后执行start slave即可。
---------------------------------------------------------------------
使用 XtraBackup 实现对 MySQL 的备份
下载 XtraBackup 最新版本 percona-xtrabackup-2.0.0-417.rhel5.x86_64.rpm，我使用的是 RPM 包，因为之前下载的二进制版本似乎有点问题，在恢复后可能无法看到数据表。
在数据库中先要建好用于备份的帐号 backup，并给予数据库的 reload, lock tables, replication client 权限。
在实施备份时，需要在数据库运行的情况下在线执行，并先提前建立用于备份的路径。
这样，将在 /var/backup 目录下产生一个按当前日期时间命令的目录，如 2012-06-01_17-13-10，在恢复时，注意要先关闭服务器，并且将 datadir 的目录先清空，在恢复数据后一定需要重置该目录的权限。具体操作如下：
提示，可以在备份时直接压缩以节约磁盘空间：
不过注意解压需要手动进行，并加入 -i 的参数，否则无法解压出所有文件。
参考资料：
The innobackupex Script
xtrabackup-guide
------------------------------------------------------------------
一、 环境
测试主机OS：Linux RHEL AS 3
MySQL InnoDB环境：innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G
初始化数据：
http://www.percona.com/mysql/xtrabackup/0.8/source/
这里下载的是0.8版本的源码包
下载MySQL源码包:
http://dev.mysql.com/downloads/mysql/5.0.html#source
因为是编译安装XtraBackup，需要使用MySQL的源代码
二、 编译安装
你也可以下载对应平台的二进制版本XtraBackup，免去编译安装的步骤，不过目前二进制版本支持的平台并不多，所以这里列出编译的步骤。
编译XtraBackup；MySQL5.1必须要使用XtraBackup0.9。
另外，使用MySQL5.1编译，需要简单的修改一下XtraBackup的Makefile:
三、 备份
XtraBackup使用脚本innobackupx-1.5.1来备份你的数据库。关于innobackupx-1.5.1的详细参数，请参考http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual。这里列出比较常用的备份命令：普通备份、打包（Tar）备份、压缩（Tar gzip）备份。
需要注意的是，innobackupex-1.5.1会根据你的/etc/my.cnf配置，来确定你的MySQL数据位置(datadir)，所以你并不需要告诉innobackupex-1.5.1你的MySQL数据在哪儿。
1. 普通备份
这里我们使用mysql的root用户备份，密码为123。备份的目标目录（target dir）是/u01/backup/1/innobackup/，innobackupex-1.5.1将在该目录下生成备份。例如/u01 /backup/1/innobackup/2009-09-01_11-59-29就是我执行脚本后生成的备份，所有的备份文件都在该. /2009-09-01_11-59-29目录下。
这里的2>/u01/backup/1/1.log，是将备份过程中的输出信息重定向到1.log。另外innobackupex-1.5.1还支持增量备份，这里并没有测试使用。
那如何恢复呢？为了模拟恢复过程，我先将MySQL停止，并删除MySQL的data目录下所有数据文件。
值得一提的是，默认innobackupex-1.5.1会将二进制日志信息存放在文件xtrabackup_binlog_info中发（方便做Slave）。
innobackupex-1.5.1会根据/et/my.cnf来确定MySQL的数据位置。
2. 打包(Tar)备份
这里基本和前面的普通备份类似，就不做详细说明了，之列出对应的命令。值得一提的是，恢复解压缩时，必须使用-i参数。
恢复：
四、 关于XtraBackup
XtraBackup还在快速的成长阶段，由http://www.percona.com/维护。这里列出一些相关资源:
一、 使用说明
1. 备份
InnoDB配置说明：
需要执行两次xtrabackup –prepare
二、 增量备份
对比innobackupex和xtrabackup我们可以看到，innobackupex操作起来更方便，但是innobackupex会有短 暂的锁表（时间依赖于MyISAM大小）。xtrabackup备份还有另一个重要特性：增量备份。（未来的版本innobackupex也可能增长该功 能）
1. 全量备份
我们需要分别对全量、增量备份各做一次prepare操作。
再次提醒，xtrabackup只备份InnoDB数据文件，表结构是不备份的，所以恢复的时候，你必须有对应表结构文件(.frm)。
参考文献
[1]. http://www.percona.com/docs/wiki/percona-xtrabackup:start
from http://www.w3ccollege.org/mysql/the-mysql-full-backup-incremental-backup-to-restore.html
-------------------------
MySQL数据库的备份，一直是个比较头痛的问题。各种工具虽然不少，但一个真正好用易用的却又非常难找。Mysqldump做为数据的逻辑备份 工具还行，但是无法进行在线热备，而没有物理备份工具，在数据量比较大的时候，恢复的时间也会长得无法接受。InnoDB倒是有个商业的InnoDB Hotbackup，可以对InnoDB引擎的表实现在线热备。最近发现了一个工具，percona出品的Xtrabackup，是InnoDB Hotbackup的一个开源替代品，可以在线对InnoDB/XtraDB引擎的表进行物理备份，试用了一下，非常的不错，值得向MySQL DBA们推荐。
下面是一个实际备份的例子，采用了gzip将备份流进行压缩，约20GB的数据库，压缩后大小为340MB，当然，压缩后的大小跟数据库中实际使用的空间是相关的。备份时间约6分44秒。
innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的，主要是为了方便的同时备份InnoDB和MyISAM引擎的表，并且加入了一些使用的选项。如 –slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。
注意，从备份后的tar包解包的时候，需要使用-i参数。最新发布的是0.7版，猛击这里下载
。
首先将备份好的tar文件解开到目标数据库的数据路径下，这一步类似oracle的restore database：
tar -ixvf mysqlbak.tar 注意解出来的文件和目录的属主以及权限是否正确。如果是将备份恢复到一台全新的环境，则需要修改/etc/my.cnf，将innodb_data_file_path等参数设置和原备份的库一致。然后执行：
$innobackupex-1.5.1 --apply-log /opt/mysqldata 这一步类似于oracle的recover database，从日志来看，差不多一个小时执行完毕，该InnoDB数据库分配空间600GB，实际使用空间约590GB，并且数据的更新量还是比较大的，大约一个小时apply-log完成。运行的日志简单记录如下：
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackup prints "innobackup completed OK!". 090708 09:50:44 innobackupex: Starting ibbackup with command: xtrabackup --prepare --target-dir=/opt/mysqldata xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64) xtrabackup: cd to /opt/mysqldata xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2882535424, start_lsn=(514 2288109039) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M...;ibdata60:10000M xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2882535424 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: Log scan progressed past the checkpoint lsn 514 2288109039 090708 9:50:45 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Doing recovery: scanned up to log sequence number 514 2293351424 (0 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2298594304 (0 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2303837184 (0 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2309080064 (0 %) 090708 9:50:47 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 514 2314322944 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2319565824 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2324808704 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2330051584 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2335294464 (1 %) ...这里省略若干行 InnoDB: Doing recovery: scanned up to log sequence number 514 3881944064 (62 %) InnoDB: Doing recovery: scanned up to log sequence number 514 3887186944 (62 %) InnoDB: Doing recovery: scanned up to log sequence number 514 3887732530 (62 %) 090708 10:52:00 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 350504077, file name mysql-bin.000748 InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 090708 10:52:17 InnoDB: Started; log sequence number 514 3887732530 [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be: InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 090708 10:52:17 InnoDB: Starting shutdown... 090708 10:52:24 InnoDB: Shutdown completed; log sequence number 514 3887732530 090708 10:52:24 innobackupex: Restarting xtrabackup with command: xtrabackup --prepare --target-dir=/opt/mysqldata for creating ib_logfile* xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64) xtrabackup: cd to /opt/mysqldata xtrabackup: This target seems to be already prepared. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M;...;ibdata60:10000M xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 4 xtrabackup: innodb_log_file_size = 104857600 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 090708 10:52:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 090708 10:52:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 090708 10:52:26 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 090708 10:52:26 InnoDB: Log file ./ib_logfile3 did not exist: new to be created InnoDB: Setting log file ./ib_logfile3 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090708 10:52:27 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 350504077, file name mysql-bin.000748 InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 090708 10:52:27 InnoDB: Started; log sequence number 514 3887732748 [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be: InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 090708 10:52:27 InnoDB: Starting shutdown... 090708 10:52:29 InnoDB: Shutdown completed; log sequence number 514 3887732748 090708 10:52:29 innobackupex: innobackup completed OK! 运行完毕后，启动mysql即可。
-------------------------------------------------
从接触MySQL的那天起，就对MySQL的备份相当头大，不同的引擎有不同的备份方式（InnoDB hot backup, mysqlhotcopy），要么需要加读锁（mysqldump），要么需要额外的硬件成本（mysql replication），不像商业数据库都有统一而成熟的在线备份方案。这可能是开源的一个弱点，想法太多，选择太多，结果就是用户不知所措。当然，对 于开源狂热者来说，这也许是开源的魅力和优点所在。
或许是MySQL也越来越商业化的结果，MySQL终于有计划引入新的联机备份机制了。
现有环境：
现有一个数据库环境，信息如下：
Master：代号A
IP地址：192.168.115.149
端口：3340
Slave：代号B
IP地址：192.168.115.150
端口：3340
配置文件：/apps/mysql_3340/my.cnf
socket路径：/tmp/mysql_3340.sock
通过本地socket访问用户名：root
通过本地socket访问密码：无
新搭建数据库：代号C
以下实践都在B数据库所在的机器上操作。
xtrabackup相关程序下载地址：
http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.0/binary/xtrabackup-1.0.tar.gz
下载后解压，将三个可执行文件放到/usr/local/sbin/即可。
当前目录：/apps/backup/
使用innobackupex脚本对数据库进行备份，相关参数可以通过--help参数了解。
以下程序输出经过筛选，略去不必要信息：
[root@slave150 backup]# innobackupex-1.5.1 --defaults-file=/apps/mysql_3340/my.cnf --slave-info --socket=/tmp/mysql_3340.sock 3340 InnoDB Backup Utility v1.5.1-xtrabackup;
Copyright 2003, 2009 Innobase Oy. All Rights Reserved.
...版权信息...
innobackupex-1.5.1: Using mysql Ver 14.14 Distrib 5.1.43, for unknown-linux-gnu (x86_64) using readline 5.1
innobackupex-1.5.1: Using mysql server version Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
innobackupex-1.5.1: Created backup directory /apps/backup/3340/2010-03-19_16-51-33 100319 16:51:33
innobackupex-1.5.1: Starting mysql with options: --unbuffered --socket=/tmp/mysql_3340.sock -- 100319 16:51:33
innobackupex-1.5.1: Connected to database with mysql child process (pid=25246) 100319 16:51:37
innobackupex-1.5.1: Connection to database server closed 100319 16:51:37
innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --defaults-file="/apps/mysql_3340/my.cnf" --backup --suspend-at-end --target-dir=/apps/backup/3340/2010-03-19_16-51-33
innobackupex-1.5.1: Waiting for ibbackup (pid=25252) to suspend
innobackupex-1.5.1: Suspend file '/apps/backup/3340/2010-03-19_16-51-33/xtrabackup_suspended'
xtrabackup Ver 1.0 Rev 113 for 5.0.84 unknown-linux-gnu (x86_64) xtrabackup: uses posix_fadvise().
xtrabackup: cd to /apps/mysql_3340
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: use O_DIRECT >> log scanned up to (23 3600029795)
Copying ./ibdata1 to /apps/backup/3340/2010-03-19_16-51-33/ibdata1 >>
log scanned up to (23 3600060713) >>
log scanned up to (23 3600087337) >>
log scanned up to (23 3600121153) ...
done Copying ./xjqy2/configs.ibd to /apps/backup/3340/2010-03-19_16-51-33/xjqy2/configs.ibd
Copying ./game/answer_count.ibd to /apps/backup/3340/2010-03-19_16-51-33/game/answer_count.ibd ...
....
innobackupex-1.5.1: completed OK!
此时在备份的目录下会有一个xtrabackup_slave_info文件，是设置Slave的命令：
[root@slave150 2010-03-19_16-51-33]# cat xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=690623257
恢复备份文件：
[root@slave150 2010-03-19_16-51-33]# innobackupex-1.5.1 --apply-log --defaults-file=/apps/backup/3340/2010-03-19_16-51-33/backup-my.cnf /apps/backup/3340/2010-03-19_16-51-33/
100319 16:57:48 innobackupex-1.5.1: Starting ibbackup with command: xtrabackup --defaults-file="/apps/backup/3340/2010-03-19_16-51-33/backup-my.cnf" --prepare --target-dir=/apps/backup/3340/2010-03-19_16-51-33
...
下面两行是说，B数据库作为Slave时，所同步的A库日志的位置。 如果新的C库作为A的Slave，那么应该配置这个日志位置。 这个位置也是和上面的xtrabackup_slave_info是一致的。
InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 690623257, file name mysql-bin.000006
下面两行是说，B数据库自己的binlog位置。如果新的C库作为B的Slave，那么应该配置这个日志位置。
InnoDB: Last MySQL binlog file position 0 989572152, file name ./mysql-bin.000005 100319 16:57:52
InnoDB: Started; log sequence number 23 3602458752
[notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be:
InnoDB: Last MySQL 100319 16:57:55 InnoDB: Shutdown completed; log sequence number 23 3602459148 100319 16:57:55 innobackupex-1.5.1: completed OK!
至此，数据文件已经可用。 将准备好的数据移动到合适的位置，做好新的配置文件，即可启动数据库。
新的配置文件与原有Slave配置 文件基本一致，需要注意修改几个参数： server-id必须修改，否则会与原有主库产生冲突。 如果在同一台机器上进行测试，端口和socket文件必须修改。 启动后，通过mysql客户端连接新的数据库，执行xtrabackup_slave_info中的命令，执行时应该加上master_host等参数， 然后执行start slave即可。
---------------------------------------------------------------------
使用 XtraBackup 实现对 MySQL 的备份
下载 XtraBackup 最新版本 percona-xtrabackup-2.0.0-417.rhel5.x86_64.rpm，我使用的是 RPM 包，因为之前下载的二进制版本似乎有点问题，在恢复后可能无法看到数据表。
1 | rpm -ivh percona-xtrabackup-2.0.0-417.rhel5.x86_64.rpm |
1 | mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 'password'; |
2 | mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'backup'; |
3 | mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost'; |
4 | mysql> FLUSH PRIVILEGES; |
1 | mkdir -p /var/backup |
2 | innobackupex --user=backup --password=password /var/backup |
1 | service mysqld stop |
2 | mv /usr/local/mysql/data /tmp |
3 | mkdir /usr/local/mysql/data |
4 | innobackupex --apply-log /var/backup/2012-06-01_17-13-10 |
5 | innobackupex --copy-back /var/backup/2012-06-01_17-13-10 |
6 | chown -R mysql.mysql /usr/local/mysql/data |
7 | service mysqld start |
1 | innobackupex --user=backup --password=password --stream=tar /var/backup/ ¦ gzip>/var/backup/compress/mysql_backup.tar.gz |
1 | tar zxfi /var/backup/compress/mysql_backup.tar.gz |
2 | innobackupex --apply-log /var/backup/compress |
3 | innobackupex --copy-back /var/backup/compress |
The innobackupex Script
xtrabackup-guide
------------------------------------------------------------------
mysql 全量备份(增量备份) 恢复
摘要：本文介绍了XtraBackup的安装（编译安装）和使用。Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。本文测试使用的是0.8版本，与当前最新版本是0.9区别并不大。
Xtrabackup有两个主要的工具：xtrabackup、innobackupex，其中xtrabackup只能备份InnoDB和XtraDB
两种数据表，innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。本文仅仅介绍了innobackupex的备份和
恢复。
一、 环境
测试主机OS：Linux RHEL AS 3
MySQL InnoDB环境：innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G
初始化数据：
use test;下载XtraBackup：
CREATE TABLE items(id INT,var1 VARCHAR(10));
#使用脚本随机插入约1000万数据
http://www.percona.com/mysql/xtrabackup/0.8/source/
这里下载的是0.8版本的源码包
下载MySQL源码包:
http://dev.mysql.com/downloads/mysql/5.0.html#source
因为是编译安装XtraBackup，需要使用MySQL的源代码
二、 编译安装你也可以下载对应平台的二进制版本XtraBackup，免去编译安装的步骤，不过目前二进制版本支持的平台并不多，所以这里列出编译的步骤。
tar zxvf mysql-5.0.85.tar.gz #解压源码包编译成功后，我们会获得可执行文件xtrabackup、innobackupex。为了方便使用，分别将其拷贝到某个$PATH路径下。（我这里/u01/mysql/bin/是$PATH路径的一部分）
tar zxvf xtrabackup-0.8-src.tar.gz #解压源码包
mv xtrabackup-0.8-src ./mysql-5.0.85/ innobase/ #将XtraBackpu移到InnoDB的源码目录
#如果使用的是MySQL5.1，目录改为/mysql-5.1.*/ storage/innobase/
cd mysql-5.0.85/ innobase #进入源代码目录
patch –p2 < . /xtrabackup-0.8-src/fix_innodb_for_backup.patch #给InnoDB源码打上补丁
#如果使用的是MySQL5.1，使用fix_innodb_for_backup51.patch
cd .. #进入目录mysql-5.0.85
./configure
make #我们只需要Make就可以了，不需要Make install
cd innobase/xtrabackup-0.8-src
make #编译XtraBackup；MySQL5.1必须要使用XtraBackup0.9
cp xtrabackup /u01/mysql/bin/安装中要注意的问题：
cp innobackupex-1.5.1 /u01/mysql/bin/
编译XtraBackup；MySQL5.1必须要使用XtraBackup0.9。
另外，使用MySQL5.1编译，需要简单的修改一下XtraBackup的Makefile:
#MySQL 5.0安装完成后，我们就可以使用命令innobackupex来备份我们的数据库了。
#INC= -I. -I.. -I./../include -I./../../include
#INNODBOBJS=
#MySQL 5.1
INC= ……
MYSQLOBJS=
三、 备份
XtraBackup使用脚本innobackupx-1.5.1来备份你的数据库。关于innobackupx-1.5.1的详细参数，请参考http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual。这里列出比较常用的备份命令：普通备份、打包（Tar）备份、压缩（Tar gzip）备份。
需要注意的是，innobackupex-1.5.1会根据你的/etc/my.cnf配置，来确定你的MySQL数据位置(datadir)，所以你并不需要告诉innobackupex-1.5.1你的MySQL数据在哪儿。
1. 普通备份
innobackupex-1.5.1 –user=root –password=123 /u01/backup/1/innobackup/ 2>/u01/backup/1/1.log备份时间3分38秒；3G的表空间,InnoDB Free 2595840 Kb。
这里我们使用mysql的root用户备份，密码为123。备份的目标目录（target dir）是/u01/backup/1/innobackup/，innobackupex-1.5.1将在该目录下生成备份。例如/u01 /backup/1/innobackup/2009-09-01_11-59-29就是我执行脚本后生成的备份，所有的备份文件都在该. /2009-09-01_11-59-29目录下。
这里的2>/u01/backup/1/1.log，是将备份过程中的输出信息重定向到1.log。另外innobackupex-1.5.1还支持增量备份，这里并没有测试使用。
那如何恢复呢？为了模拟恢复过程，我先将MySQL停止，并删除MySQL的data目录下所有数据文件。
cd mysql/data/ ;rm –rf *;命令innobackupex-1.5.1需要指定具体哪个备份目录，先应用备份log，然后copy back。这里的备份目录是/u01/backup/1/innobackup/2009-09-01_11-59-29。恢复时间约3分钟。
innobackupex-1.5.1 –apply-log /u01/backup/1/innobackup/2009-09-01_11-59-29
innobackupex-1.5.1 –apply-log /u01/backup/1/innobackup/2009-09-01_11-59-29
innobackupex-1.5.1 –copy-back /u01/backup/1/innobackup/2009-09-01_11-59-29
cd /u01/mysql/data/
chown -R mysql:dba . #(这时候可能还需要修改datadir修改文件的所有者)
值得一提的是，默认innobackupex-1.5.1会将二进制日志信息存放在文件xtrabackup_binlog_info中发（方便做Slave）。
innobackupex-1.5.1会根据/et/my.cnf来确定MySQL的数据位置。
2. 打包(Tar)备份
这里基本和前面的普通备份类似，就不做详细说明了，之列出对应的命令。值得一提的是，恢复解压缩时，必须使用-i参数。
innobackupex-1.5.1 –user=root –password=123 –stream=tar /u01/backup/2/ 2>/u01/backup/2.log 1>/u01/backup/2/2.tar大约2分55秒。这里使用1>做标准输出重定向。
恢复：
tar –ixvf 2.tar #解压压缩 这里-i参数是必须的。3. 压缩(tar gzip)备份
innobackupex-1.5.1 –apply-log /u01/backup/2/innobackup
innobackupex-1.5.1 –copy-back /u01/backup/2/innobackup
cd /u01/mysql/data/
chown -R mysql:dba . #(这时候可能还需要修改datadir修改文件的所有者)
mysqld_safe –user=mysql &
innobackupex-1.5.1 –user=root –password=123 –stream=tar /u01/backup/1/ 2>/u01/backup/2.log | gzip > /u01/backup/1/1.tar.gz大约3分钟。这里使用了管道|将innobackupex-1.5.1作为gzip的标准输入。它的恢复过程，只需要使用tar –izxvf 解压对应的文件后，操作完全同普通备份。
四、 关于XtraBackup
XtraBackup还在快速的成长阶段，由http://www.percona.com/维护。这里列出一些相关资源:
- 主页： http://www.percona.com/docs/wiki/percona-xtrabackup:start
- Google Group: http://groups.google.com/group/percona-discussion?hl=en
- Launchpad: https://launchpad.net/percona-xtrabackup
- Manual http://www.percona.com/docs/wiki/percona-xtrabackup:xtrabackup_manual
-
- http://www.percona.com/docs/wiki/percona-xtrabackup:start
- http://www.ningoo.net/html/2009/using_xtrabackup_backup_mysql_database.html
- http://www.innodb.com/doc/hot_backup/manual.html#innobackup
- http://www.ibm.com/developerworks/cn/linux/l-diffp/index.htm
一、 使用说明
1. 备份
InnoDB配置说明：
innodb_data_home_dir = ./备份命令：
innodb_data_file_path = ibdata1:50M:autoextend
innodb_log_group_home_dir = ./
innodb_log_files_in_group = 4
innodb_log_file_size = 104857600
$xtrabackup –defaults-file=/etc/my.cnf –backup –target-dir=/u01/xtrabackup/1/2. 恢复
……
$cd /u01/xtrabackup/1/ && ls
ibdata1 test xtrabackup_checkpoints xtrabackup_logfile
需要执行两次xtrabackup –prepare
$xtrabackup –defaults-file=/etc/my.cnf –prepare –target-dir=/u01/xtrabackup/1/这时候，InnoDB的全部数据文件就已经恢复了。将这些数据文件，拷贝回你的MySQL数据文件的位置就可以了。需要注意的 是，xtrabackup只备份数据文件，并不备份数据表结构（.frm），所以使用xtrabackup恢复的时候，你必须有对应表结构文件 （.frm）。
……
$xtrabackup –defaults-file=/etc/my.cnf –prepare –target-dir=/u01/xtrabackup/1/
……
$cd /u01/xtrabackup/1/ && ls
ibdata1 ib_logfile0 ib_logfile1 ib_logfile2 ib_logfile3 test xtrabackup_checkpoints xtrabackup_logfile
二、 增量备份
对比innobackupex和xtrabackup我们可以看到，innobackupex操作起来更方便，但是innobackupex会有短 暂的锁表（时间依赖于MyISAM大小）。xtrabackup备份还有另一个重要特性：增量备份。（未来的版本innobackupex也可能增长该功 能）
1. 全量备份
$xtrabackup –defaults-file=/etc/my.cnf –backup –target-dir=/u01/xtrabackup/2/2. 增量备份
……
$ls /u01/xtrabackup/2/
ibdata1 test xtrabackup_checkpoints xtrabackup_logfile
$xtrabackup –defaults-file=/etc/my.cnf –backup –target-dir=/u01/xtrabackup/2.1/ –incremental-basedir=/u01/xtrabackup/2/在增量备份的目录下，数据文件都是以.delta结尾的。增量备份只备份上一次全量备份之后被修改过的page，所以增量备份一般只暂用较少的空间。
……
$ls /u01/xtrabackup/2.1/
ibdata1.delta test xtrabackup_checkpoints xtrabackup_logfile
$cd /u01/xtrabackup/ && du –max-depth=1 -h3. 增量备份恢复
428K ./2.1
935M ./2
我们需要分别对全量、增量备份各做一次prepare操作。
$xtrabackup –defaults-file=/etc/my.cnf –prepare –target-dir=/u01/xtrabackup/2/这样，/u01/xtrabackup/2/下的数据文件就可以直接放到你的MySQL数据目录下，恢复数据了。
……
$xtrabackup –prepare –target-dir=/u01/xtrabackup/2/ –incremental-dir=/u01/xtrabackup/2.1/
……
$xtrabackup –prepare –target-dir=/u01/xtrabackup/2/ #这一步不是必须的
再次提醒，xtrabackup只备份InnoDB数据文件，表结构是不备份的，所以恢复的时候，你必须有对应表结构文件(.frm)。
参考文献
[1]. http://www.percona.com/docs/wiki/percona-xtrabackup:start
from http://www.w3ccollege.org/mysql/the-mysql-full-backup-incremental-backup-to-restore.html
-------------------------
使用Xtrabackup备份MySQL数据库
MySQL数据库的备份，一直是个比较头痛的问题。各种工具虽然不少，但一个真正好用易用的却又非常难找。Mysqldump做为数据的逻辑备份 工具还行，但是无法进行在线热备，而没有物理备份工具，在数据量比较大的时候，恢复的时间也会长得无法接受。InnoDB倒是有个商业的InnoDB Hotbackup，可以对InnoDB引擎的表实现在线热备。最近发现了一个工具，percona出品的Xtrabackup，是InnoDB Hotbackup的一个开源替代品，可以在线对InnoDB/XtraDB引擎的表进行物理备份，试用了一下，非常的不错，值得向MySQL DBA们推荐。
下面是一个实际备份的例子，采用了gzip将备份流进行压缩，约20GB的数据库，压缩后大小为340MB，当然，压缩后的大小跟数据库中实际使用的空间是相关的。备份时间约6分44秒。
innobackupex是参考了InnoDB Hotbackup的innoback脚本修改而来的，主要是为了方便的同时备份InnoDB和MyISAM引擎的表，并且加入了一些使用的选项。如 –slave-info可以记录备份恢复后，作为slave需要的一些信息，根据这些信息，可以很方便的利用备份来重做slave。
注意，从备份后的tar包解包的时候，需要使用-i参数。最新发布的是0.7版，猛击这里下载
。
$innobackupex-1.5.1 --user=root --stream=tar /bak/ --slave-info | gzip > /bak/bak_mysql.tar.gz
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy.
All Rights Reserved.
This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackup
prints "innobackup completed OK!".
innobackupex: Using mysql Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (x86_64)
using EditLine wrapper
innobackupex: Using mysql server version 5.0.67-log
innobackupex: Created backup directory /bak
090625 15:23:00 innobackupex: Starting mysql with options: --unbuffered --user=root
090625 15:23:00 innobackupex: Connected to database with mysql child process (pid=3431)
090625 15:23:04 innobackupex: Connection to database server closed
090625 15:23:04 innobackupex: Starting ibbackup with command:
xtrabackup --backup --suspend-at-end --log-stream --target-dir=./
innobackupex: Waiting for ibbackup (pid=3565) to suspend
innobackupex: Suspend file '/opt/mysqldata/xtrabackup_suspended'
xtrabackup: suspend-at-end is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /opt/mysqldata
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = /opt/mysqldata
xtrabackup: innodb_data_file_path = ibdata1:10G;ibdata2:10G
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 4
xtrabackup: innodb_log_file_size = 104857600
xtrabackup: use O_DIRECT
xtrabackup: Stream mode.
>> log scanned up to (0 3053406941)
090625 15:23:06 innobackupex: Continuing after ibbackup has suspended
innobackupex: Starting to backup InnoDB tables and indexes
innobackupex: from original InnoDB data directory '/opt/mysqldata'
innobackupex: Backing up as tar stream 'ibdata1'
>> log scanned up to (0 3053416714)
...这里省略若干行
>> log scanned up to (0 3054123851)
tar: ibdata1: file changed as we read it
innobackupex: Backing up as tar stream 'ibdata2'
>> log scanned up to (0 3054142116)
...这里省略若干行
>> log scanned up to (0 3054618483)
innobackupex: Backing up file '/opt/mysqldata/test/sp.ibd'
innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.ibd'
innobackupex: Backing up files '/opt/mysqldata/testdb/*.ibd' (206 files)
>> log scanned up to (0 3054638401)
>> log scanned up to (0 3054668860)
tar: testdb/group_group_thread_0027.ibd: file changed as we read it
>> log scanned up to (0 3054695015)
>> log scanned up to (0 3054928216)
tar: testdb/group_thread_reply_0007.ibd: file changed as we read it
>> log scanned up to (0 3054952588)
>> log scanned up to (0 3055005439)
tar: testdb/group_user_0001.ibd: file changed as we read it
>> log scanned up to (0 3055028610)
>> log scanned up to (0 3055044650)
tar: testdb/group_user_0006.ibd: file changed as we read it
>> log scanned up to (0 3055060461)
innobackupex: Backing up file '/opt/mysqldata/testdb/comments.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.ibd'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.ibd'
090625 15:29:17 innobackupex: Starting mysql with options: --unbuffered --user=root
090625 15:29:17 innobackupex: Connected to database with mysql child process (pid=5458)
>> log scanned up to (0 3055072495)
090625 15:29:21 innobackupex: Starting to lock all tables...
>> log scanned up to (0 3055087148)
>> log scanned up to (0 3055119993)
090625 15:29:39 innobackupex: All tables locked and flushed to disk
090625 15:29:39 innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, and .opt files in
innobackupex: subdirectories of '/opt/mysqldata'
innobackupex: Backing up file '/opt/mysqldata/test/sp.frm'
innobackupex: Backing up file '/opt/mysqldata/test/tmp_dy.frm'
innobackupex: Backing up files '/opt/mysqldata/testdb/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (207 files)
innobackupex: Backing up file '/opt/mysqldata/testdb/comments.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_content.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/testdb_ids.frm'
innobackupex: Backing up file '/opt/mysqldata/testdb/db.opt'
innobackupex: Backing up files '/opt/mysqldata/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,opt}' (52 files)
090625 15:29:40 innobackupex: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, and .opt files
innobackupex: Resuming ibbackup
xtrabackup: The latest check point (for incremental): '0:3054881736'
>> log scanned up to (0 3055120013)
xtrabackup: Transaction log of lsn (0 3053102766) to (0 3055120013) was copied.
090625 15:29:44 innobackupex: All tables unlocked
090625 15:29:44 innobackupex: Connection to database server closed
innobackupex: Backup created in directory '/bak/'
innobackupex: MySQL binlog position: filename '', position
innobackupex: MySQL slave binlog position: master host '127.0.0.1',
filename 'mysql-bin.000006', position 227207755
090625 15:29:44 innobackupex: innobackup completed OK!
innobackupex: You must use -i (--ignore-zeros) option for extraction of the tar stream
上面演示了使用xtrabackup对InnoDB进行在线物理热备，使用innobackupex脚本，可以很简单的一条命令行搞定InnoDB和MyISAM的物理备份。本文则演示如何从xtrabackup的备份中进行恢复。本次恢复的是一个600GB大小的InnoDB数据库，备份的时候没有使用gzip压缩。
首先将备份好的tar文件解开到目标数据库的数据路径下，这一步类似oracle的restore database：
tar -ixvf mysqlbak.tar 注意解出来的文件和目录的属主以及权限是否正确。如果是将备份恢复到一台全新的环境，则需要修改/etc/my.cnf，将innodb_data_file_path等参数设置和原备份的库一致。然后执行：
$innobackupex-1.5.1 --apply-log /opt/mysqldata 这一步类似于oracle的recover database，从日志来看，差不多一个小时执行完毕，该InnoDB数据库分配空间600GB，实际使用空间约590GB，并且数据的更新量还是比较大的，大约一个小时apply-log完成。运行的日志简单记录如下：
InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy. All Rights Reserved. This software is published under the GNU GENERAL PUBLIC LICENSE Version 2, June 1991. IMPORTANT: Please check that the apply-log run completes successfully. At the end of a successful apply-log run innobackup prints "innobackup completed OK!". 090708 09:50:44 innobackupex: Starting ibbackup with command: xtrabackup --prepare --target-dir=/opt/mysqldata xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64) xtrabackup: cd to /opt/mysqldata xtrabackup: This target seems to be not prepared yet. xtrabackup: xtrabackup_logfile detected: size=2882535424, start_lsn=(514 2288109039) xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M...;ibdata60:10000M xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 1 xtrabackup: innodb_log_file_size = 2882535424 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) InnoDB: Log scan progressed past the checkpoint lsn 514 2288109039 090708 9:50:45 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Doing recovery: scanned up to log sequence number 514 2293351424 (0 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2298594304 (0 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2303837184 (0 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2309080064 (0 %) 090708 9:50:47 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Doing recovery: scanned up to log sequence number 514 2314322944 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2319565824 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2324808704 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2330051584 (1 %) InnoDB: Doing recovery: scanned up to log sequence number 514 2335294464 (1 %) ...这里省略若干行 InnoDB: Doing recovery: scanned up to log sequence number 514 3881944064 (62 %) InnoDB: Doing recovery: scanned up to log sequence number 514 3887186944 (62 %) InnoDB: Doing recovery: scanned up to log sequence number 514 3887732530 (62 %) 090708 10:52:00 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 350504077, file name mysql-bin.000748 InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 090708 10:52:17 InnoDB: Started; log sequence number 514 3887732530 [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be: InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 090708 10:52:17 InnoDB: Starting shutdown... 090708 10:52:24 InnoDB: Shutdown completed; log sequence number 514 3887732530 090708 10:52:24 innobackupex: Restarting xtrabackup with command: xtrabackup --prepare --target-dir=/opt/mysqldata for creating ib_logfile* xtrabackup Ver rc-0.7 for 5.0.77 unknown-linux-gnu (x86_64) xtrabackup: cd to /opt/mysqldata xtrabackup: This target seems to be already prepared. xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'. xtrabackup: Temporary instance for recovery is set as followings. xtrabackup: innodb_data_home_dir = ./ xtrabackup: innodb_data_file_path = ibdata1:10000M;ibdata2:10000M;ibdata3:10000M;...;ibdata60:10000M xtrabackup: innodb_log_group_home_dir = ./ xtrabackup: innodb_log_files_in_group = 4 xtrabackup: innodb_log_file_size = 104857600 xtrabackup: Starting InnoDB instance for recovery. xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter) 090708 10:52:25 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 090708 10:52:25 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 090708 10:52:26 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 090708 10:52:26 InnoDB: Log file ./ib_logfile3 did not exist: new to be created InnoDB: Setting log file ./ib_logfile3 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 090708 10:52:27 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: In a MySQL replication slave the last master binlog file InnoDB: position 0 350504077, file name mysql-bin.000748 InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 090708 10:52:27 InnoDB: Started; log sequence number 514 3887732748 [notice (again)] If you use binary log and don't use any hack of group commit, the binary log position seems to be: InnoDB: Last MySQL binlog file position 0 36434864, file name /opt/mysqllog/mysql-bin.003015 xtrabackup: starting shutdown with innodb_fast_shutdown = 1 090708 10:52:27 InnoDB: Starting shutdown... 090708 10:52:29 InnoDB: Shutdown completed; log sequence number 514 3887732748 090708 10:52:29 innobackupex: innobackup completed OK! 运行完毕后，启动mysql即可。
-------------------------------------------------
说说MySQL的在线备份
从接触MySQL的那天起，就对MySQL的备份相当头大，不同的引擎有不同的备份方式（InnoDB hot backup, mysqlhotcopy），要么需要加读锁（mysqldump），要么需要额外的硬件成本（mysql replication），不像商业数据库都有统一而成熟的在线备份方案。这可能是开源的一个弱点，想法太多，选择太多，结果就是用户不知所措。当然，对 于开源狂热者来说，这也许是开源的魅力和优点所在。
或许是MySQL也越来越商业化的结果，MySQL终于有计划引入新的联机备份机制了。
