MySql 备份+ 高可用（二）

• 一、Xtrabackup介绍

　　MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。

　　Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

• 二、Xtrabackup优点

（1）备份速度快，物理备份可靠

（2）备份过程不会打断正在执行的事务（无需锁表）

（3）能够基于压缩等功能节约磁盘空间和流量

（4）自动备份校验

（5）还原速度快

（6）可以流传将备份传输到另外一台机器上

（7）在不增加服务器负载的情况备份数据

• 三、Xtrabackup备份原理

Xtrabackup备份流程图：

（1）innobackupex启动后，会先fork一个进程，用于启动xtrabackup，然后等待xtrabackup备份ibd数据文件；

（2）xtrabackup在备份innoDB数据是，有2种线程：redo拷贝线程和ibd数据拷贝线程。xtrabackup进程开始执行后，会启动一个redo拷贝的线程，用于从最新的checkpoint点开始顺序拷贝redo.log；再启动ibd数据拷贝线程，进行拷贝ibd数据。这里是先启动redo拷贝线程的。在此阶段，innobackupex进行处于等待状态（等待文件被创建）

（4）xtrabackup拷贝完成ibd数据文件后，会通知innobackupex（通过创建文件），同时xtrabackup进入等待状态（redo线程依旧在拷贝redo.log）

（5）innobackupex收到xtrabackup通知后哦，执行FLUSH TABLES WITH READ LOCK（FTWRL），取得一致性位点，然后开始备份非InnoDB文件（如frm、MYD、MYI、CSV、opt、par等格式的文件），在拷贝非InnoDB文件的过程当中，数据库处于全局只读状态。

（6）当innobackup拷贝完所有的非InnoDB文件后，会通知xtrabackup，通知完成后，进入等待状态；

（7）xtrabackup收到innobackupex备份完成的通知后，会停止redo拷贝线程，然后通知innobackupex，redo.log文件拷贝完成；

（8）innobackupex收到redo.log备份完成后，就进行解锁操作，执行：UNLOCK TABLES；

（9）最后innbackupex和xtrabackup进程各自释放资源，写备份元数据信息等，innobackupex等xtrabackup子进程结束后退出。

• 四、xtrabackup的安装部署以及备份恢复实现

• 1、xtrabackup的安装

下载地址：https://www.percona.com/downloads/XtraBackup/LATEST/

可以选择rpm包方式安装，也可以下载源码包编译安装，这里直接采用rpm包的方式进行安装

1[root@master tools]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
2[root@master tools]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 
3[root@master ~]# rpm -qa |grep xtrabackup
4percona-xtrabackup-24-2.4.9-1.el7.x86_64
5
6Xtrabackup中主要包含两个工具：
7xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
8innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
9常用选项:  
10   --host     指定主机
11   --user     指定用户名
12   --password    指定密码
13   --port     指定端口
14   --databases     指定数据库
15   --incremental    创建增量备份
16   --incremental-basedir   指定包含完全备份的目录
17   --incremental-dir      指定包含增量备份的目录   
18   --apply-log        对备份进行预处理操作             
19     一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
20   --redo-only      不回滚未提交事务
21   --copy-back     恢复备份目录

使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件，这些文件会被保存到一个以时间命名的目录当中。在备份的同时，innobackupex还会在备份目录中创建如下文件：

1(1)xtrabackup_checkpoints -- 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息：
2每个InnoDB页(通常为16k大小)
3都会包含一个日志序列号，即LSN，LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
4(2)xtrabackup_binlog_info  --  mysql服务器当前正在使用的二进制日志文件及备份这一刻位置二进制日志时间的位置。
5(3)xtrabackup_binlog_pos_innodb  --  二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
6(4)xtrabackup_binary  --  备份中用到的xtrabackup的可执行文件；
7(5)backup-my.cnf  --  备份命令用到的配置选项信息：
8
9在使用innobackupex进行备份时，还可以使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录：如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户：

1mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';　　#创建用户
2mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　       #回收此用户所有权限
3mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost';　　#授权刷新、锁定表、用户查看服务器状态
4mysql> FLUSH PRIVILEGES;　　                                        #刷新授权表

注意：备份时需启动MySQL，恢复时需关闭MySQL，清空mysql数据目录，且不能重新初始化

• 2、xtrabackup全量备份与恢复

  备份： innobackupex --user=bkpuser --password=123456 --defaults-file=/etc/my.cnf /BACKUP-DIR/

  恢复： innobackupex --apply-log /backups/2018-07-30_11-04-55/innobackupex --copy-back --defaults-file=/etc/my.cnf  /backups/2018-07-30_11-04-55/  innobackupex --move-back --defaults-file=/etc/my.cnf /backups/2018-07-30_11-04-55/

实战练习

1（1）全量备份
2[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/　　#--user=root 指定备份用户#--password=123456  指定备份用户密码#--host　　指定主机#/backups　　指定备份目录
3
4[root@master backups]# ll 2018-07-30_11-01-37/　　                     #查看备份数据
5-rw-r----- 1 root root      418 Jul 30 11:01 backup-my.cnf　　         #备份用到的配置选项信息文件
6-rw-r----- 1 root root 79691776 Jul 30 11:01 ibdata1　　               #数据文件-rw-r----- 1 root root       21 Jul 30 11:01 xtrabackup_binlog_info　　#mysql服务器当前正在使用的二进制日志文件和此时二进制日志时间的位置信息文件
7-rw-r----- 1 root root      113 Jul 30 11:01 xtrabackup_checkpoints　　#备份的类型、状态和LSN状态信息文件
8-rw-r----- 1 root root      482 Jul 30 11:01 xtrabackup_info
9-rw-r----- 1 root root     2560 Jul 30 11:01 xtrabackup_logfile　　　　 #备份的日志文件
10
11（2）恢复
12[root@slave ~]# /etc/init.d/mysqld stop　　 
13[root@slave tools]# yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm 　　
14[root@master backups]# scp -r 2018-07-30_11-01-37/ root@192.168.56.12:/backups/　　 #从master上拷贝备份数据
15[root@slave tools]# innobackupex --apply-log /backups/2018-07-30_11-01-37/　　　　　 #合并数据，使数据文件处于一致性的状态
16[root@slave ~]# rm -rf /usr/local/mysql/data/　　#在slave上删除原有的数据
17[root@slave ~]# vim /etc/my.cnf　　              #配置my.cnf的数据目录路径，否则会报错，要和master一致
18datadir=/usr/local/mysql/data
19[root@slave ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/　　#在slave上数据恢复180729 23:32:08 completed OK!　　                #看到completed OK就是恢复正常了
20[root@slave ~]# ll /usr/local/mysql/data/　　    #slave上可以看到数据已经恢复，但是属主会有问题，需要进行修改，所以一般使用mysql的运行用户进行恢复
21[root@slave ~]# chown -R mysql.mysql /usr/local/mysql/data/　　#修改属主属组
22[root@slave ~]# /etc/init.d/mysqld start　　 
23[root@slave ~]# mysql -uroot -p -e "show databases;"　　#查看数据，是否恢复

总结全库备份与恢复三步曲：

a.　innobackupex全量备份，并指定备份目录路径；

b.　在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求；

c.　恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。

• 3、xtrabackup增量备份与恢复

　　使用innobackupex进行增量备份，每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。这正是InnoDB表可以进行增量备份的基础，即innobackupex通过备份上次完全备份之后发生改变的页面来实现。在进行增量备份时，首先要进行一次全量备份，第一次增量备份是基于全备的，之后的增量备份都是基于上一次的增量备份的，以此类推。

要实现第一次增量备份，可以使用下面的命令进行：

1基于全量备份的增量备份与恢复
2增量备份（基于全量备份）
3innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37
41. 增量
5innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37
62.合并数据
7innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37 --incremental-dir=/backups/2018-07-30_13-51-47/3. 恢复
8innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2017-01-05_11-04-55/
9解释：
101. 2018-07-30_11-01-37指的是完全备份所在的目录。2. 2018-07-30_13-51-47指定是第一次基于2018-07-30_11-01-37增量备份的目录，其他类似以此类推，即如果有多次增量备份。每一次都要执行如上操作。

需要注意的是，增量备份仅能应用于InnoDB或XtraDB表，对于MyISAM表而言，执行增量备份时其实进行的是完全备份。

"准备"(prepare)增量备份与整理完全备份有着一些不同，尤其要注意的是：
①需要在每个备份 (包括完全和各个增量备份)上，将已经提交的事务进行"重放"。"重放"之后，所有的备份数据将合并到完全备份上。
②基于所有的备份将未提交的事务进行"回滚"

（1）增量备份演示

1[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 /backups/   #全备数据#使用innobackupex进行增量备份[root@master backups]# innobackupex --user=root --password=123456 --host=127.0.0.1 --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37/
2[root@master backups]# ll　　    #查看备份数据
3total 0
4drwxr-x--- 7 root root 232 Jul 30 11:01 2018-07-30_11-01-37　　#全量备份数据目录
5drwxr-x--- 8 root root 273 Jul 30 13:51 2018-07-30_13-51-47　　#增量备份数据目录
6[root@master 2018-07-30_11-01-37]# cat xtrabackup_checkpoints #查看全量备份的xtrabackup_checkpoints
7backup_type = full-backuped　　  #备份类型为全量备份
8from_lsn = 0　　                 #lsn从0开始
9to_lsn = 3127097　　             #lsn到3127097结束
10last_lsn = 3127097
11compact = 0
12recover_binlog_info = 0
13
14[root@master 2018-07-30_13-51-47]# cat xtrabackup_checkpoints 　　#查看增量备份的xtrabackup_checkpoints
15backup_type = incremental　　    #备份类型为增量备份
16from_lsn = 3127097　　           #lsn从3127097开始
17to_lsn = 3158741　　             #lsn到啊3158741结束
18last_lsn = 3158741　　
19compact = 0
20recover_binlog_info = 0

（2）增量备份后数据恢复演示

1（1）模拟mysql故障，删除数据目录所有数据[root@master ~]# /etc/init.d/mysqld stop　　#模拟mysql故障，停止mysql
2[root@master ~]# rm -rf /usr/local/mysql/data/*　　#删除数据目录中的所有数据（2）合并全备数据目录，确保数据的一致性
3[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/（3）将增量备份数据合并到全备数据目录当中
4[root@master ~]# innobackupex --apply-log --redo-only /backups/2018-07-30_11-01-37/ --incremental-dir=/backups/2018-07-30_13-51-47/
5[root@master ~]# cat /backups/2018-07-30_11-01-37/xtrabackup_checkpoints 
6backup_type = log-applied　　 #查看到数据备份类型是增加
7from_lsn = 0　　              #lsn从0开始
8to_lsn = 3158741　　          #lsn结束号为最新的lsn
9last_lsn = 3158741
10compact = 0
11recover_binlog_info = 0（4）恢复数据
12[root@master ~]# innobackupex --copy-back /backups/2018-07-30_11-01-37/
13[root@master ~]# chown -R mysql.mysql /usr/local/mysql/data　　#更改数据的属主属组
14[root@master ~]# /etc/init.d/mysqld start　　                  #启动mysql
15[root@master ~]# mysql -uroot -p -e "show databases;"　　      #查看数据是否恢复

总结：

（1）增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录；

（2）进行数据备份时，需要使用参数--apply-log redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性；

（3）再将增量备份数据使用参数--incremental-dir合并到全备数据当中；

（4）最后通过最后的全备数据进行恢复数据，注意，如果有多个增量备份，需要逐一合并到全备数据当中，再进行恢复。

##################################################

1.环境准备

1[root@db01 ~]# hostname ;hostname -I
2db01
310.0.0.51 172.16.1.51
4[root@db02 ~]# hostname ;hostname -I
5db02
610.0.0.52 172.16.1.52 
7#两台服务器均安装以下软件包。
8wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
9yum -y localinstall percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm-- 创建测试环境create database oldboy;use oldboydrop table test;CREATE TABLE test (id int(4) NOT NULL AUTO_INCREMENT,name char(20) NOT NULL,PRIMARY KEY (id)) ;insert into test(id,name) values(1,'oldboy');insert into test(name) values('oldgirl');insert into test values(3,'inca');insert into test values(4,'zuma'),(5,'kaka');

这里db01为主库，db02为从库,从库里没有数据。现在要实现的是db01到db02的主从复制。server id不能相同，同时开启binlog功能

2. 备份

先在db01上做一次全备

1[root@db01 ~]# innobackupex --defaults-file="/etc/my.cnf" --user=root --socket=/application/mysql/tmp/mysql.sock --password=123456 /opt/
2#现在数据已经备份到opt下面了
3
4#把备份好的数据复制到db02的opt目录下
5[root@db01 ~]#  scp -r /opt/2017-05-05_11-23-50/ 10.0.0.52:/opt/

3.db01插入数据

在做完备份后，在生产环境中一般都会有数据继续写入数据库，所以这里再插入几条数据模拟环境。

1use oldboy;
2insert into test(id,name) values(6,'bing');
3insert into test(id,name) values(7,'zhao');
4insert into test(id,name) values(8,'ya');
5insert into test(id,name) values(9,'ting');

4.预处理

1[root@db02 ~]# sed -i '16a datadir = /application/mysql/data' /application/mysql/my.cnf
2[root@db02 opt]# innobackupex --defaults-file="/etc/my.cnf" --user=root --socket=/application/mysql/tmp/mysql.sock --password=123456 --apply-log --use-memory=200M /opt/2017-05-05_09-35-07/
3#注：--use-memory参数可以设置适用内存进行处理，如果服务器内存够大，多给一点是没问题的，可以加快处理速度。

5.db02

1#删除原有的数据文件,必须先清空原有数据文件，不然后报错
2[root@db02 mysql]# mv /application/mysql/data /tmp/
3
4#导入db01的数据文件
5[root@db02 mysql]# innobackupex --defaults-file="/etc/my.cnf" --user=root --socket=/application/mysql/tmp/mysql.sock --move-back /opt/2017-05-05_11-23-50/
6
7#可以看到刚才被删除的数据又恢复过来了
8[root@db02 data]# cat xtrabackup_binlog_pos_innodb 
9mysql-bin.000002    1461
10[root@db02 mysql]# chown -R mysql.mysql data
11[root@db02 mysql]# /etc/init.d/mysqld start

6.创建主从同步授权用户

1-- 在db01上
2mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by '123456';

7.做主从同步

1-- db02
2mysql> change master to master_host='10.0.0.51',master_user='rep',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=1461;
3mysql> start slave;
4
5#检查
6[root@db02 mysql]# mysql -uroot -poldboy123 -e "show slave status\G"|egrep -i "Yes|Behind_Master"
7Warning: Using a password on the command line interface can be insecure.
8             Slave_IO_Running: Yes
9            Slave_SQL_Running: Yes
10        Seconds_Behind_Master: 0

8.检查数据

9.延时复制

mysql自带还有延时复制的功能，比如在一主4从的时候，可以将其中一台做延时复制，假如出现意外情况或误操作，延时复制的功能还有一丝恢复数据的机会。

1mysql> change master to master_delay = 3600;    -- 等于你想要延迟的时间
2mysql> start slave;
3
4-- 表示从库延迟1小时后同步数据
5mysql> show slave status\G
6                    SQL_Delay: 3600
7          SQL_Remaining_Delay: 3561

###################################################

1.主从复制

主库：

1）修改配置文件

#编辑mysql配置文件my.cnf

1[mysqld]
2server-id=1         #不能用从库相同
3log_bin=mysql-bin   #开启日志

2）创建主从复制用户

创建rep用户

mysql> grant replication slave on *.* to rep@'10.0.0.%' identified by 'oldboy123';

3）查看主库binlog位置点

show master status;

从库：

1）修改配置文件

#修改db02配置文件my.cnf

1[mysqld]
2server-id=2read_only=1

2）配置主从

mysql> change master to master_host='10.0.0.51',master_user='rep',master_password='oldboy123',master_auto_position=1;master_log_file='mysql-bin.000002',master_log_pos=1558;mysql> start slave;mysql> set global read_only=1;  #从库只读

2、延时同步

企业中一般会延时3-6小时

1）延时从库配置方法

1#停止主从
2#设置延时为180秒    mysql>CHANGE MASTER TO MASTER_DELAY = 180;
3#开启主从
4#查看状态          SQL_Delay: 60

总数据量级500G，正常备份去恢复需要1.5-2小时
2）怎么利用延时从库，恢复数据？

11、停止SQL线程
2mysql> stop slave sql_thread;
3
42、截取relaylog到误删除之前点
5relay-log.info 获取到上次运行到的位置点，作为恢复起点
6分析relay-log的文件内容，获取到误删除之前position

模拟故障

11）从库，关闭SQL线程
2mysql > stop slave sql_thread;
3
42）截取relay-log
5起点：
6cat /data/3308/data/relay-log.info
7 /db01-relay-bin.000002283
8终点：
9mysql > show relaylog events in 'db01-relay-bin.000002'
10db01-relay-bin.000002 | 268047
113）导出sql
12# mysqlbinlog --start-position=283 --stop-position=268047 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
13
144）恢复relay.sql
15mysql> set sql_log_bin=0;
16mysql> source /tmp/relay.sql
17mysql> show tables;

 3、半同步复制

 半同步复制开启方法

1）主库

1#查看是否有动态支持
2mysql> show global variables like 'have_dynamic_loading';
3#安装自带插件
4mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME'semisync_master.so';
5
6#启动插件
7mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
8
9#设置超时（毫秒）
10mysql> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
11
12#修改配置文件 my.cnf
13[mysqld]
14rpl_semi_sync_master_enabled=1
15rpl_semi_sync_master_timeout=1000
16
17检查安装：
18mysql> show variables like'rpl%';
19mysql> show global status like 'rpl_semi%'

2）从库

1#安装slave半同步插件
2mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME'semisync_slave.so';
3
4#启动插件
5mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;
6
7#重启io线程使其生效
8mysql> stop slave io_thread; start slave io_thread;
9
10#编辑配置文件 my.cnf
11[mysqld]
12rpl_semi_sync_slave_enabled =1

注：相关参数说明

1rpl_semi_sync_master_timeout=milliseconds
2设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
3
4rpl_semi_sync_master_wait_no_slave={ON|OFF}
5如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
6可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。

3）测试半同步

1#创建两个数据库，test1和test2
2mysql> create database test1;
3mysql> create database test2;
4
5#查看复制状态
6mysql> show global status like 'rpl_semi%';

1#关闭半同步（1:开启 0:关闭）
2mysql> SET GLOBAL rpl_semi_sync_master_enabled = 0;
3
4#查看半同步状态
5mysql> show global status like 'rpl_semi%';

1#再一次创建两个库
2mysql> create database test3;
3mysql> create database test4;
4
5#再一次查看半同步状态
6mysql> show global status like 'rpl_semi%';

注:不难发现，在查询半同步状态是，开启半同步，查询会有延迟时间，关闭之后则没有

4、过滤复制

主库：

白名单:只记录白名单中列出的库的二进制日志

• binlog-do-db

黑名单：不记录黑名单列出的库的二进制日志

• binlog-ignore-db

从库：

白名单：只执行白名单中列出的库或者表的中继日志

• --replicate-do-db=test
• --replicate-do-table=test.t1
• --replicate-wild-do-table=test.t2

黑名单：不执行黑名单中列出的库或者表的中继日志

• --replicate-ignore-db
• --replicate-ignore-table
• --replicate-wild-ignore-table