Xtrabackup 0.9发布
在我之前的文章中，介绍并演示了xtrabackup，从这段时间实际使用的情况来看，这是一个值得向所有MySQL DBA推荐的备份工具，尤其是针对使用InnoDB引擎为主的库。
现在这个开源的备份工具正处于亢奋期，刚刚发布0.8没多久，0.9版又发布了。0.9版没有引入太多的新特性，主要是对0.8版本的一些严重的bug进行修正。实际上，0.8版出来后我就试用了RPM版，但是遇到了问题无法正常备份，所以目前在生产环境中还是使用的0.7版。有同事尝试在MySQL5.1中编译xtrabackup0.8也无法成功。果然，0.9版修复的bug列表中，就有这么一个bug： Bug #394374:Can’t compile with MySQL 5.1
有兴趣尝试的，可以到这里下载。
xtrabackup 0.8版发布
前段时间开始测试xtrabackup 0.7的时候，在下载的地方已经看到有0.8的目录和源代码，只是各个平台的编译版本还没有，当时就猜0.8版本很快就要出来了。不过最近很少有时间看Google Reader，今天才发现其实7月1号0.8版就已经出来了，可以到这里下载。
看了一下新特性，没有特别让人心动的功能出来。innobackupex脚本开始支持增量备份（xtrabackup本身早已经支持）；开始支持archive引擎的备份；stream备份模式支持tar4ibd；增加了–export选项以支持InnoDB表在不同库之间移动（只能导入到XtraDB引擎，详细参考这里）；另外修复了一些bug。
更多介绍可以参考这里。
使用Xtrabackup备份MySQL数据库(续)
在上一篇里，演示了使用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即可。
使用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版，猛击这里下载
MySQL DBA们，你是如何做备份的呢？欢迎到这里探讨。
$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.
