mysql xtrabackup安装_xtrabackup安装及使用

xtrabakup

一、下载&安装

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 018719, 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: MySQLbinlog 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 root@photo26.db "( nc -d -l 80 > /data/photo_10_bak/dbback.tar 2>/dev/null

innobackupex-1.5.1 --stream=tar ./ | pv -q -L10m | ssh vadim@desthost "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 user@host cat ">" backup.tar

Copy over network without ssh encryption (fast copying)

ssh user@host "( 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;