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。

更多介绍可以参考这里。

在Crontab中调度Perl程序的环境变量问题

在corntab中调度perl程序时，如何获取并设置环境变量是一大问题。在shell脚本中，我一般是通过直接调用~/.profile或者~/.bash_profile（视shell类型而定）来解决。而Perl中则没有一个现成的比较方便的方法。

老楼最近在一篇blog中给出了一种方法，基本上能解决上述问题，但主要是针对ORACLE_HOME，其他的还需要再写一个差不多的函数，并且还需要配置文件，通用性不是太好。

我的办法是，直接利用.profile或者.bash_profile，在perl中循环将所有的环境变量一次性设置好。这个设置过程，我写成了一个函数放在公共模块中，只需要在脚本中包含该模块，然后调用一次该函数即可。因为要兼容Oracle和MySQL，按照我们的安装规范，Oracle安装用户为oracle，而MySQL数据库的安装用户为mysql，相应的脚本使用相应的用户。自动化是需要建立在规范之上的，下面是函数代码：

sub set_env{
  my $user=`whoami`;
  chomp($user);
  my $profile="/home/".$user."/.profile";
  if (! -e $profile ){
    $profile="/home/".$user."/.bash_profile"
  }
  open(NEWENV, ". $profile && env|");
  while (){
    if (/(\w+)=(.*)/){
      $ENV{$1}="$2";
    }
  }
  close NEWENV;
}

使用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.

常用标签： oracle life MySQL Oracle11g blog dba 新特性 oow2009 oow wordpress

最新评论 | Recent comments

订阅更新 | Subscribe

文章分类 | Categories

最近文章 | Recent posts

站点信息 | Statistics