技术分享|容灾备份|Xtrabackup备份恢复mysql数据库详解|虚拟机备份专家云祺科技

关于我们

技术分享

技术分享 Xtrabackup备份恢复mysql数据库详解

Xtrabackup备份恢复mysql数据库详解

2018-01-30

经过几天的测试，文档终于可以与大家分享了。使用Xtrabackup能够非常快速地备份与恢复mysql数据库，是mysql dba的首选。Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。 Xtrabackup有两个主要的工具：xtrabackup、innobackupex xtrabackup只能备份InnoDB和XtraDB两种数据表，支持在线热备份，不会锁表 innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表

===================================================

一、 centos 5.5 下安装配置Xtrabackup

先看看如何安装Xtrabackup,最简单的安装方式是使用RPM包，不过想使用源代码方式安装的话，其安装方式有点古怪，因为它采用的在MySQL源代码上打补丁构建的方式安装的。这里使用二进制包的安装方式，相对比较灵活。
Shell> mkdir /usr/local/xtrabackup
Shell> tar -zxvf xtrabackup-1.6.tar.gz –C /usr/local/xtrabackup
Shell>cd /usr/local/xtrabackup/bin
Shell>ln –s innobackupex-1.5.1 innobackupex

配置环境变量：
Shell>export PATH=$PATH:/usr/local/xtrabackup/bin
希望永久生效的话，可以加到
echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile
source /etc/profile

修改mysql配置文件:
Shell> vi /etc/my.cnf
添加或修改：datadir =/usr/local/mysql/var（数据库目录）
特别注意：default_table_type = InnoDB（必须改，否则进行增量备份的时候不成功） 
================================================= 
二、建立测试数据
1.调整my.cnf参数
Shell> vi /etc/my.cnf
添加或修改：datadir =/usr/local/mysql/var（数据库目录）
特别注意：default_table_type = InnoDB（必须改，否则进行增量备份的时候不成功）
2.建立数据库
mysql -uroot -p123456
create database test_myisam CHARACTER SET=gbk;
create database test_innodb CHARACTER SET=gbk; 
3.建立数据表
use test_myisam;
CREATE TABLE `t_myisam` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`password` varchar(150) default NULL,
`userstatus` int(2) default NULL,
PRIMARY KEY (`id`)
); 
show create table t_myisam\G; 
use test_innodb;
CREATE TABLE `t_innodb` (
`id` bigint(20) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`password` varchar(150) default NULL,
`userstatus` int(2) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET=gbk; 
show create table t_innodb\G; 
4.建立存储过程
vi addTest.sql
DROP procedure IF EXISTS addTest;
delimiter //
create procedure addTest(i int)
begin
declare name varchar(64);
delete from test_myisam.t_myisam where id != '0';
delete from test_innodb.t_innodb where id != '0';
While i>0 do
Set name=concat('test',i);
insert into test_myisam.t_myisam(id,name,password,userstatus) values (i,name,password(name),1);
insert into test_innodb.t_innodb(id,name,password,userstatus) values (i,name,password(name),1);
Set i=i-1;
END while;
end
//
delimiter ; 
说明：
addTest(i int),i为插入数据的行数，先删除以前的数据，然后再写入到2个库的2个表中，数据内容一样。 
5.写入数据
mysql -uroot -p123456 test_myisam /data/backup/db/backup1.log 
xtrabackup_50  Ver 1.3 Rev 148 for 5.0.91 unknown-linux-gnu (x86_64)
Copying ./ibdata1
     to /www/backup/db/innobackup/2010-08-23_14-38-05/ibdata1
        ...done
xtrabackup: The latest check point (for incremental): '0:1720034804'
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (0 1720034804) to (0 1720034804) was copied. 
说明：
使用mysql的root用户备份，密码为123456
备份的目标目录是/usr/local/mysql/var，--databases="test_myisam test_innodb"用于指定要备份的数据库
这里的2>/u01/backup/1/1.log，是将备份过程中的输出信息重定向到/www/backup/db/backup1.log 
三、测试
1.innobackupex备份

innobackupex-1.5.1选项注释如下：

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

对数据库进行热备。默认情况下以当前时间戳创建备份根目录。上述命令完整备份所有MyISAM和InnoDB引擎表以及索引。包含.frm,.MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .opt, .par和InnoDB数据和日志文件。使用mysql客户端程序连接mysql服务器，运行ibbackup(InnoDB热备程序)作为一个子进程。

innobackupex-1.5.1--apply-log [--use-memory=MB] [--uncompress] [--defaults-file=MY.CNF]

[--export] [--redo-only][--ibbackup=IBBACKUP-BINARY] BACKUP-DIR

从备份恢复，运行ibbackup子进程，但是不能连接到数据库。按照BACKUP-DIR/backup-my.cnf定义，使用BACKUP-DIR/ibbackup_logfile恢复InnoDB数据文件和创建新的InnoDB日志文件。

innobackupex-1.5.1--copy-back [--defaults-file=MY.CNF] BACKUP-DIR

从备份目录拷贝数据，索引和日志文件到原datadir目录。

--defaults-file=[MY.CNF]该选项传递给xtrabackup子进程，从指定文件读取缺省选项

--apply-log 从备份恢复。

--redo-only 该选项强制跳过rollback阶段，只进行redo。这是有必要使用的，如果备份后，要使用增量改变的。

--copy-back 从备份目录拷贝数据和索引文件到datadir目录

--remote-host=HOSTNAME备份到远程主机上，使用ssh

--stream=[tar|cpio(notimplemented)] 指定备份标准输出格式

--tmpdir=DIRECTORY默认与tmpdir相同。使用—remote-host或—stream参数后，传输日志文件将存放在临时目录下

--use-memory=MB选项传递给xtrabackup子进程。恢复使用内存大小

--parallel=NUMBER-OF-THREADS选项传递给xtrabackup子进程，指定数据传输线程总数。默认为1

--throttle=IOS选项传递给xtrabackup子进程，限制IO线程数量

--sleep=MS 选项传递给xtrabackup子进程。每拷贝1MB数据暂停多少MS时间

--compress[=LEVEL]选项传递给xtrabackup子进程。压缩级别在0-9.1快速压缩，9最佳压缩，0不压缩。默认为1.

--include=REGEXP选项传递给xtrabackup子进程。使用正则进行匹配

--databases=LIST指定备份数据库

--tables-file=FILE

--uncompress选项传递给xtrabackup子进程。对压缩过的InnoDB数据文件不进行压缩

--export 仅使用于prepare选项。选项传递给xtrabackup子进程。

--user=NAME

--password=WORD

--host=HOST

--port=PORT

--slave-info 备份复制从服务端，主从信息记录在ibbackup_slave_info文件中

--socket=SOCKET

--no-timestamp不在备份根目录下创建以当前时间戳为名称的新的备份目录

--ibbackup=IBBACKUP-BINARYibbackup二进制路径

--no-lock 禁止表级锁。全部是InnoDB引擎表和不关系二进制日志位置下使用

--scpopt=SCP-OPTIONS指定scp参数

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --databases="test_myisam test_innodb" /root/backups>>/root/backups/backup.log 2>&1

压缩(tar gzip)备份
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --no-lock --user=root --password=gaojinbo --databases="test_myisam test_innodb" --stream=tar /www/backup/db/innobackup/ 2>/www/backup/db/backup2.log | gzip > /www/backup/db/innobackup/2.tar.gz 
这种备份时间会比较长，主要是执行压缩。
恢复过程，只需要使用tar izxvf 解压对应的文件后，操作完全同普通备份。 
2.模拟恢复过程
/etc/init.d/mysql  stop
cd /usr/local/mysql/var
rm -rf test_myisam;
rm -rf test_innodb;
rm -f ibdata1
rm -f ib_logfile*
innobackupex --apply-log --defaults-file=/etc/my.cnf --user=root --password=123456 /data/backup/2012-04-24_01-03-36
cp -r /data/backup/2012-04-24_01-03-36/ib* ./
cp -r /data/backup/2012-04-24_01-03-36/test_myisam ./
cp -r /data/backup/2012-04-24_01-03-36/test_innodb ./
chown -R mysql:mysql test_myisam/
chown -R mysql:mysql test_innodb/
chown mysql:mysql ib*
/etc/init.d/mysql start 
通过以上步骤就能完全恢复mysql数据了，恢复过程中必须要启动mysql 
3.检验数据后的数据
select * from test_myisam.t_myisam where id<300;
select * from test_innodb.t_innodb where id<300;
show create table test_myisam.t_myisam;
show create table test_innodb.t_innodb; 
mysql> select count(*) from test_myisam.t_myisam;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec) 
mysql> select count(*) from test_innodb.t_innodb;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (17.78 sec) 
为何查询时间相差这么大？
myisam 的表rows 记录在表信息中，innodb 的要扫描表 
============================================== 
四、xtrabackup备份与恢复
xtrabackup只备份InnoDB数据文件，表结构是不备份的，所以恢复的时候，你必须有对应表结构文件(.frm)

xtrabackup选项注释如下：

--print-defaults显示默认选项。Xtrabackup默认情况会去读my.cnf文件，读取顺序是/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

--no-defaults 忽略任何my.cnf文件选项

--defaults-file=#读取指定的my.cnf文件

--defaults-extra-file=#再读取另外一个文件

--target-dir=name目的目录，默认目录在./xtrabackup_backupfiles/，相对于datadir目录

--backup 备份

--stats 计算datadir目录统计信息

--prepare 从backup恢复

--export 在恢复时，创建文件导入到另一个数据库

--apply-log-only在恢复时，停止恢复进程不进行LSN只使用log

--print-param 打印出参数

--use-memory=#buffer_pool_size值

--suspend-at-end在备份时，创建xtrabackup_suspended文件，直到备份完成后删掉

--throttle=# 限制IO总数

--log-stream 记录标准输出信息xtrabackup_logfile

--extra-lsndir=name仅适用于backup，保存另一份xtrabackup_checkpoints文件

--incremental-lsn=name仅适用于backup，增量备份

--incremental-basedir=name仅适用于backup，增量备份目录

--incremental-dir=name仅适用于prepare，恢复指定目录下的.delta文件和日志文件

--tables=name 过滤某些表

--tables_file=name过滤database.table列表文件

--create-ib-logfile

-h,--datadir=name datadir目录

-t,--tmpdir=name tmpdir目录

--parallel=# 默认为1.传输数据文件的并行线程数。没有任何流模式的影响

--innodb_* 有关innodb参数

1.普通备份
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d`
cp -r /usr/local/mysql/var/test_innodb /data/backup/db/xtrabackup/`date +%Y-%m-%d` 
模拟恢复过程
cd /usr/local/mysql/var
/etc/init.d/mysql  stop
rm -rf test_innodb
rm -f ibdata1
rm -f ib_logfile*
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d`
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d`
cp /data/backup/db/xtrabackup/`date +%Y-%m-%d`/ib* /usr/local/mysql/var
cp -r /data/backup/db/xtrabackup/`date +%Y-%m-%d`/test_innodb /usr/local/mysql/var
chown -R mysql:mysql /usr/local/mysql/var
/etc/init.d/mysql start 
2.增量备份
修改存储过程，新增500万条数据（略）
mysql> select count(*) from t_innodb;
+----------+
| count(*) |
+----------+
| 15000000 |
+----------+
1 row in set (5.23 sec) 
mkdir -p /data/backup/db/xtrabackup/`date +%Y-%m-%d`-incre
xtrabackup --defaults-file=/etc/my.cnf --backup --target-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d`-incre --incremental-basedir=/data/backup/db/xtrabackup/`date +%Y-%m-%d` 
3.模拟恢复过程
cd /usr/local/mysql/var
/etc/init.d/mysql  stop
rm -rf test_innodb
rm -f ibdata1
rm -f ib_logfile*
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d`
xtrabackup --defaults-file=/etc/my.cnf --prepare --target-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d` --incremental-dir=/data/backup/db/xtrabackup/`date +%Y-%m-%d`-incre 
cp /data/backup/db/xtrabackup/`date +%Y-%m-%d`/ib* /usr/local/mysql/var
cp -r /data/backup/db/xtrabackup/`date +%Y-%m-%d`/test_innodb /usr/local/mysql/var
chown -R mysql:mysql /usr/local/mysql/var
chown -R mysql:root /usr/local/mysql/var/mysql
/etc/init.d/mysql start 
4.检验恢复后的数据
mysql -uroot -p123456
use test_innodb;
mysql> select count(*) from t_innodb;
+----------+
| count(*) |
+----------+
| 15000000 |
+----------+
1 row in set (19.03 sec) 
可以看出，数据完全被恢复了。