xtrabackup安装及使用

作/译者：陶会祥 来源：http://blog.chinaunix.net/u3/107145/ 欢迎转载,请注明作者及出处~

 

xtrabakup

一、下载&安装

Wget wget (更高版本会要求mysql-client依赖~)

Install
rpm -ivh xtrabackup-1.1-59.rhel5.x86_64.rpm

二、组成
xtrabackup - a compiled C binary, which copies only InnoDB and XtraDB data
innobackupex - a wrapper script that provides functionality to backup a whole MySQL database instance with MyISAM, InnoDB, and XtraDB tables.
tar4ibd - tars InnoDB data safely.

三、实验步骤
任务:模拟线上环境定时向DB中插入数据,在制作结束后备份的数据要完整,和线上完全一致~
1. 输入数据
##cat input_data.sh
#!/bin/sh
for i in {1..10000};
do
sql="insert ignore into t_id_name(id,name) values(null,'$i')";
echo $sql
echo $sql | mysql -h 127.0.0.1 -P3306 fb -pxxxx
sleep 1
done
echo "done"
2.Backup
使用xtrabackup给DB 3306做一备份从库3308~

3. 检测结果
mysql -h 127.0.0.1 -P3306 fb -e "select count(1) from t_id_name"
mysql -h 127.0.0.1 -P3308 fb -e "select count(1) from t_id_name"

四、xtrabackup使用

Usage: [./xtrabackup [DEFAULTS OPTION] —backup | ./xtrabackup [DEFAULTS OPTION] —prepare] [OPTIONS]
Default options are read from the following files in this order: /etc/my.cnf, /opt/mysql-5.0.xx/etc/my.cnf, ~/.my.cnf

参数说明：

--defaults-file=   读my.cnf的位置,在DB服务器上有多实例时很有用,未指定将读/etc/my.cnf~(如要备份DB 3307的数据库,要单独指定3307的 my.cnf文件)
--datadir= 数据存放目录,和my.cnf中要相一致~ (必须,但实际上一般不用特别指定,因为会从my.cnf中去读取~)
--target-dir= 存放备份文件的目录,要使用绝对路径~ 若此目录开始不存在,则--backup选项会建立此目录~
--backup   备份InnoDB/XTraDB表空间,存放*.ibd及备份的log文件(xtrabackup_logfile)到 --target-dir目录~
--stats   统计分析innodb表存储信息,eg. xtrabackup --stats --defaults-file=/data/scrips/mysql_3306.cnf
--prepare 返原innodb前要执行,prepare命令~ eg. xtrabackup --prepare --defaults-file=`pwd`/mysql_3306.cnf --throttle=2000 --target-dir=`pwd`/dbbak  
--print-param 主要用于innobackupex脚本copyback操作时, 打印出目标mysqld的参数~
--use-memory= 用于--prepare 或 --stats,在prepare相当于innodb_buffer_pool_size 作用~ eg.xtrabackup --use-memory=1G --stat
--export       --prepare操作时,导入其它数据库使用。在使用'ALTER TABLE … IMPORT TABLESPACE' 命令时,输出干净的.ibd files 或 .exp 文件.
--suspend-at-end 在backup操作时用于同步(synchronizing)
--log-stream --backup操作时,输出xtrabackup_logfile内容,innobackupex --stream 就使用此选项~
--incremental-lsn= 用于--backup. Copy only .ibd pages newer than specified LSN 'high:low'
--incremental-basedir= 用于增量备份backup~只拷贝比目标目录中更新的.ibd 文件~
--incremental-dir=name= 用于增量prepare,
--tables= innodb数据库使用file-per-table模式时,可用此选项备份部分表~也可用于stat操作时~
--throttle= 用于限制每秒中磁盘的读写操作~ 在线备份时非常有用~
--create-ib-logfile 用于--prepare时生成ib-logfile~ 注意：当前版本未实现,还是要re–prepare^_^
--tmpdir= 存放temporary files的路径~

Making a backup:
The --backup command makes a backup of InnoDB/XTraDB tablespaces~It stores a backup of data files and a backup log file (named xtrabackup_logfile) in the directory specified using --target-dir=.
Restoring from a backup:
--prepare 根据'xtrabackup_logfile'中的记录生成mysql可以使用的数据空间及新的log files~,当前版本你要再执行prepare,以生成 ib_logfile*
返原：
返原到原始的位置：使用innobackupex --copy-back或 cp -r命令~

xtrabackup --backup --target-dir=./dbbk (要使用绝对路径,否则存在datadir下面了)
# xtrabackup --table=sakila.actor --backup --target-dir=`pwd`/actor/
xtrabackup --stats --tables=fb.* (分析统计innodb信息)

mysqld配置文件：(多实例时备份必须要)
[mysqld]
datadir                         = /data/mysql/mysql_3306/var/
innodb_data_home_dir            = /data/mysql/mysql_3306/ibdata
innodb_log_group_home_dir       = /data/mysql/mysql_3306/iblogs
innodb_data_file_path=ibdata1:10M;ibdata2:10M:autoextend
innodb_log_files_in_group = 2
innodb_log_file_size = 1G

步骤：

xtrabackup --defaults-file=`pwd`/mysql_3306.cnf --backup   --target-dir=`pwd`/dbbak
xtrabackup defaults-file=`pwd`/mysql_3306.cnf --prepare -- --target-dir=`pwd`/dbbak
xtrabackup --defaults-file=`pwd`/mysql_3306.cnf --prepare --target-dir=`pwd`/dbbak

好象--defaults-file= 做为第一个参数，不易出现备份binlog位置错误 ？~

并路径一定要写绝对路径,否则易出错!

[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 902367, file name ./fb-bin.000002

指定位置：
change master to master_host=‘xxx',master_port=3306,master_log_file='fb-bin.000002',master_log_pos=902367;

xtrabackup缺点：不能备份myisam，要手动拷贝frm文件~

一个shell备份脚本：

限速备份fb数据库中表~

#!/bin/sh
file="/data/dbbak/xtrabackup/backup.log"
datadir="/data/var"
target_dir="/data/dbbak/xtrabackup"
[ ! -d $target_dir ] && { mkdir -p $target_dir ; }
echo `date`
echo `date`>>$file
#xtrabackup --backup --throttle=1000 --tables=fb.* --target-dir=$target_dir 1>>$file 2>&1
xtrabackup --backup --tables=fb.* --target-dir=$target_dir 1>>$file 2>&1
xtrabackup --prepare --tables=fb.* --target-dir=$target_dir 1>>$file 2>>$file
xtrabackup --prepare --tables=fb.* --target-dir=$target_dir 1>>$file 2>>$file
cp -r $datadir/mysql $target_dir
cp "$datadir"/fb/*.frm $target_dir/fb/
cp $datadir/fb/*.MYD $target_dir/fb/
cp $datadir/fb/*.MYI $target_dir/fb/
echo `date`
echo `date`>>$file
echo "done"

五、innobackupex使用

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

1. Perform a full backup
innobackupex-1.5.1 /data/backups

2. Perform an incremental backup
xtrabackup --backup --target-dir=/data/backups/incremental --incremental-basedir=/data/backups/2010-04-11_17-55-54
Restore:
3. Execute —apply-log for the full backup
innobackupex-1.5.1 --apply-log /data/backups/2010-04-11_17-55-54/
4. Apply the incremental changes
xtrabackup --prepare --target-dir=/data/backups/2010-04-11_17-55-54 --incremental-dir=/data/backups/incremental
5. Copy the prepared data back to the datadir
innobackupex-1.5.1 --copy-back /data/backups

操作步骤：

innobackupex需要先建立备份目录,而xtrabackup不需要目录存在~

5.1 测试对3306实例备份：
innobackupex-1.5.1 --defaults-file=`pwd`/mysql_3306.cnf --socket=/data/mysql/mysql_3306/mysql.sock --user=root --password=xxx `pwd`/dbak_inbak
innobackupex-1.5.1 --apply-log dbak_inbak/2010-10-27_19-44-34/

[notice (again)] (很重要，用来标识备份时间的log位置)
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 18719, file name ./fb-bin.000001

cat dbak_inbak/2010-10-29_00-41-28/xtrabackup_binlog_info  
fb-bin.000001   18719

cat dbak_inbak/2010-10-29_00-41-28/xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='', MASTER_LOG_POS=

change master to master_host='xx.db', master_port=3306,master_log_file='fbfb-bin.000001,master_log_pos=18719

5.2 测试对slave实例备份：

现实线上环境，可能maser已经其有Slave了，这时我们也可以对Slave进行在线备份~

此实验中3307是3306的Slave，我们对3307进行备份~
--slave-info 在对从库进行备份有非常有用,文件ibbackup_slave_info指出了master的binlog文件及位置,新的master的从库可以使用此位置~

步骤：

innobackupex-1.5.1 --slave-info --defaults-file=`pwd`/mysql_3307.cnf --socket=/data/mysql/mysql_3307/mysql.sock --user=root --password=xxx `pwd`/dbak
innobackupex-1.5.1 --apply-log dbak/2010-10-29_00-49-03

innobackupex-1.5.1: Backup created in directory '/data/scrips/dbak/2010-10-29_00-49-03'
innobackupex-1.5.1: MySQL binlog position: filename 'fb-bin.000002', position 92788
innobackupex-1.5.1: MySQL slave binlog position: master host 'xx.db', filename 'fb-bin.000001', position 92676
101029 00:49:28 innobackupex-1.5.1: completed OK!

观察到备份的目录中,有xtrabackup_binlog_info ，xtrabackup_slave_info 两个文件：

# cat dbak/2010-10-29_00-49-03/xtrabackup_binlog_info  
fb-bin.000002   92788
total 0
# cat dbak/2010-10-29_00-49-03/xtrabackup_slave_info
CHANGE MASTER TO MASTER_LOG_FILE='fb-bin.000001', MASTER_LOG_POS=92676

可用来指定binlog位置：

我们建立3308，3309实例，启动正常后分别执行下面的命令：

对3308： change master to master_host='xx.db', master_port=3307,master_log_file='fb-bin.000002',master_log_pos=92788; slave start;
对3309：

change master to master_host='xx.db', master_port=3306,master_log_file='fb-bin.000001',master_log_pos=92676;slave start;

检查结果：

由图可知，备份的结果是正确的~

5.3 测试对远程备份：

远程Host: nc -l 1234 | tar -ix 或者 nc -l 1234 | cat - > bak.tar  
被备份DB: innobackupex-1.5.1 --slave-info --defaults-file=`pwd`/mysql_3306.cnf --socket=/data/mysql/photo_10/mysql.sock   --throttle=1000 --include=fb.* --user=root --password=xxx --stream=tar   ./ | nc photo26.db 80

change master to master_host='xxx', master_port=3306,master_log_file='fb-bin.000001',master_log_pos=188867;
[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 188867, file name ./fb-bin.000001

补充：

ssh "( nc -d -l 80 > /data/photo_10_bak/dbback.tar 2>/dev/null
innobackupex-1.5.1 --stream=tar ./ | pv -q -L10m | ssh "cat - > /data/vol1/mysqluc/backup.tar"
Compress single file:
innobackupex --stream=tar ./ | gzip - > backup.tar.gz
Copy uncompressed stream over network to file
innobackupex --stream=tar ./ | ssh cat ">" backup.tar

Copy over network without ssh encryption (fast copying)
ssh "( nc -l -p 9210 > backup.tar & )" && innobackupex --stream=tar ./ | nc host 9210

5.4 增量备份：

backup：

xtrabackup --defaults-file=`pwd`/mysql_3306.cnf --backup   --user=root --password=xxxx--target-dir=`pwd`/dbak_inbak/incremental --incremental-basedir=`pwd`/dbak_inbak/2010-10-31_00-11-16/

prepare：
xtrabackup --defaults-file=`pwd`/mysql_3306.cnf --prepare --user=root --password=xxxx- --target-dir=`pwd`/dbak_inbak/2010-10-31_00-11-16/ --incremental-basedir=`pwd`/dbak_inbak/incremental/

增量目录：

binlog位置及设置从库：

cat /data/mysql/mysql_3308/xtrabackup_binlog_info
fb-bin.000001   397143
change master to master_host='xxxx', master_port=3306,master_log_file='fb-bin.000001',master_log_pos=397143;

5.5 导入导出innodb表：（测试未成功）
1. Take a backup of the table t_id_name
xtrabackup --defaults-file=`pwd`/mysql_3306.cnf --backup --table=fb.t_id_name --target-dir=`pwd`/dbbak
: Note: as long as we did the backup of the only table actor, you'll see errors, saying that other tables are not found, ignore them
2. Do export
xtrabackup --defaults-file=`pwd`/mysql_3306.cnf --prepare --export --target-dir=`pwd`/dbbak

目标Host执行
3. Create table actor on the target server
CREATE TABLE `t_id_name` (
      `id` int(11) unsigned NOT NULL auto_increment,
       `name` varchar(255) collate utf8_bin default '',
      PRIMARY KEY (`id`)
     ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

4. Discard a tablespace t_id_name     
alter table t_id_name discard tablespace;
inndb_pertable设置时,discard实际上是删除t_id_name.ibd文件~
set global innodb_expand_import=1; (出错了)
ERROR 1193 (HY000): Unknown system variable 'innodb_expand_import'
5. Copy t_id_name.ibd, t_id_name.exp to /data/mysql/var
# cp t_id_name.ibd t_id_name.exp fb/
# chown -R mysql /data/mysql

6. Import a table space ''t_id_name''
mysql> use fb
mysql> alter table t_id_name import tablespace;

参考：

~