Xtrabackup：MySQL DBA的必备工具

Xtrabackup是percona开发的产品，可以看做是InnoDB Hotbackup的免费替代品。

Percona XtraBackup is the world's only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB, XtraDB, and MyISAM databases⁰. It makes MySQL hot backups for all versions of Percona Server, MySQL, and MariaDB. It performs streaming, compressed, and incremental MySQL backups.

What are the features of Percona XtraBackup?

Here is a short list of XtraBackup features. See the documentation for more.

• Create hot InnoDB backups without pausing your database
• Make incremental backups of MySQL
• Stream compressed MySQL backups to another server
• Move tables between MySQL servers online
• Create new MySQL replication slaves easily
• Backup MySQL without adding load to the server

先看看如何安装Xtrabackup，下载最新的版本，最简单的安装方式无疑是使用RPM包，不过如果你想使用源代码方式安装的话，则会发现其安装方式有点古怪，这是因为它采用的在MySQL源代码上打补丁构建的方式。

wget http://www.percona.com/mysql/xtrabackup/xtrabackup-0.8.tar.gz
tar zxf xtrabackup-0.8.tar.gz
cd xtrabackup-0.8
./configure
make

进行到这里时，千万别惯性使然接着make install，那样就会接着安装MySQL了，正确方法是接着：

cd innobase/xtrabackup/
make
make install

如此一来，就会在你的/usr/bin目录里安装上两个有用的工具：xtrabackup，innobackupex-1.5.1：

xtrabackup可以在不加锁的情况下备份innodb数据表，不过此工具不能操作myisam。
innobackupex-1.5.1是一个脚本封装，能同时处理innodb和myisam，但在处理myisam时需要加一个读锁。

按如上的介绍，由于操作myisam时需要加读锁，这会堵塞线上服务的写操作，而innodb没有这样的限制，所以数据库中innodb表类型所占的比例越大，则越有利。实际应用中一般是直接使用innobackupex-1.5.1方法，它主要有三种操作方式，按手册中的介绍：

Usage:
innobackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP] [--user=NAME]
           [--password=WORD] [--port=PORT] [--socket=SOCKET] [--no-timestamp]
           [--ibbackup=IBBACKUP-BINARY] [--slave-info] [--stream=tar]
           [--defaults-file=MY.CNF]
           [--databases=LIST] [--remote-host=HOSTNAME] BACKUP-ROOT-DIR
innobackup --apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]
           [--ibbackup=IBBACKUP-BINARY] BACKUP-DIR
innobackup --copy-back [--defaults-file=MY.CNF] BACKUP-DIR

第一个命令行是热备份mysql数据库。

The first command line above makes a hot backup of a MySQL database.
By default it creates a backup directory (named by the current date
and time) in the given backup root directory. With the --no-timestamp
option it does not create a time-stamped backup directory, but it puts
the backup in the given directory (which must not exist). This
command makes a complete backup of all MyISAM and InnoDB tables and
indexes in all databases or in all of the databases specified with the
--databases option. The created backup contains .frm, .MRG, .MYD,
.MYI., .TRG, .TRN, .opt, and InnoDB data and log files. The MY.CNF
options file defines the location of the database. This command
connects to the MySQL server using mysql client program, and runs
ibbackup (InnoDB Hot Backup program) as a child process.

带有--apply-log选项的命令是准备在一个备份上启动mysql服务。

The command with --apply-log option prepares a backup for starting a MySQL
server on the backup. This command expands InnoDB data files as specified
in BACKUP-DIR/backup-my.cnf using BACKUP-DIR/ibbackup_logfile,
and creates new InnoDB log files as specified in BACKUP-DIR/backup-my.cnf.
The BACKUP-DIR should be a path name of a backup directory created by
innobackup. This command runs ibbackup as a child process, but it does not
connect to the database server.

带有--copy-back选项的命令从备份目录拷贝数据，索引，日志到my.cnf文件里规定的初始位置。

The command with --copy-back option copies data, index, and log files
from backup directory back to their original locations.
The MY.CNF options file defines the original location of the database.
The BACKUP-DIR is a path name of a backup directory created by innobackup.

Xtrabackup还可以用来moving InnoDB tables between servers，更多的内容可以参考官方文档及例子。

参考链接：Xtrabackup online backup for InnoDB/XTraDB（pdf）

 

 

下面是一个实际备份的例子，采用了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.

 

其它资源：

How to Compile XtraBackup

Impossible – possible, moving InnoDB tables between servers

Copying InnoDB tables between servers

XtraBackup: Open Source Alternative for Innodb Hot Backup call for ideas