linux运维、架构之路-xtrabackup

一、XtraBackup介绍

1、备份工具

xtrabackup：是一款基于InnoDB的在线热备工具，具有开源，免费，支持在线热备，占用磁盘空间小，能够非常快速地

                          备份与恢复mysql数据库，(备份时不影响数据读写)

官方下载地址为http://www.percona.com/software/percona-xtrabackup

mysqldump：备份是逻辑备份，备份出来的文件是sql语句，但是当备份MySQL数据超过10G时，用mysqldump来导出备份就比较慢了

2、备份原理

　

3、xtrabackup包含两个主要的工具

①xtrabackup只能备份innodb和xtradb两种引擎的表，而不能备份myisam引擎的表；
②innobackupex是一个封装了xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁，还有就是myisam不支持增量备份

innobackupex备份和恢复的工作原理

二、XtraBackup安装部署

1、环境

①db

[root@db ~]# cat /etc/redhat-release 
CentOS release 6.9 (Final)
[root@db ~]# uname -r
2.6.32-696.el6.x86_64
[root@db ~]# /etc/init.d/iptables status
iptables: Firewall is not running.
[root@db ~]# getenforce 
Disabled
[root@db ~]# hostname -I
172.19.5.54 172.16.1.54

②db01

[root@db01 ~]# cat /etc/redhat-release 
CentOS release 6.9 (Final)
[root@db01 ~]# uname -r
2.6.32-696.el6.x86_64
[root@db01 ~]# /etc/init.d/iptables status
iptables: Firewall is not running.
[root@db01 ~]# getenforce 
Disabled
[root@db01 ~]# hostname -I
172.19.5.55 172.16.1.55

2、安装XtraBackup

cd /server/tools/
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.9-1.el6.x86_64.rpm -y

3、db服务器模拟插入数据

create database oldboy;#创建oldboy库
use oldboy;
create table test( id int(4) not null,name char(20) not null);#在oldboy库中创建test表
insert into test values(1,"oldboy");#test表中插入数据
insert into test values(2,"oldgirl");
insert into test values(3,"inca");
insert into test values(4,"zuma");
insert into test values(5,"kaka");
insert into test values(6,"老男孩");

查看test表中内容

mysql> select * from test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
+----+-----------+
6 rows in set (0.00 sec)

4、备份数据

①db操作

mkdir /backup -p && cd /backup
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock /backup/
#备份完成会出现如下内容
……
xtrabackup: Transaction log of lsn (1642766) to (1642766) was copied.
180109 11:40:30 completed OK!

②查看备份的内容

[root@db backup]# ll /backup/2018-01-09_11-57-49/
total 131104
-rw-r----- 1 root root      418 2018-01-09 11:57 backup-my.cnf
-rw-r----- 1 root root 12582912 2018-01-09 12:01 ibdata1
-rw-r----- 1 root root 50331648 2018-01-09 12:01 ib_logfile0
-rw-r----- 1 root root 50331648 2018-01-09 12:01 ib_logfile1
-rw-r----- 1 root root 12582912 2018-01-09 12:01 ibtmp1
drwxr-x--- 2 root root     4096 2018-01-09 11:57 mysql
drwxr-x--- 2 root root     4096 2018-01-09 11:57 oldboy
drwxr-x--- 2 root root     4096 2018-01-09 11:57 performance_schema
-rw-r----- 1 root root       22 2018-01-09 11:57 xtrabackup_binlog_info
-rw-r--r-- 1 root root       22 2018-01-09 12:01 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root      113 2018-01-09 12:01 xtrabackup_checkpoints
-rw-r----- 1 root root      536 2018-01-09 11:57 xtrabackup_info
-rw-r----- 1 root root  8388608 2018-01-09 12:01 xtrabackup_logfile

③预处理数据

innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --apply-log --use-memory=1G /backup/2018-01-09_11-57-49/

④模拟删除oldboy库，恢复全备

drop database oldboy;

⑤为了保持数据一致性，停库恢复

[root@db ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
rm -fr /application/mysql/data/*
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --move-back /backup/2018-01-09_11-57-49/

⑥恢复全备成功后，重新授权数据目录，启动MySQL

chown -R mysql.mysql /application/mysql/data/
[root@db mysql]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 
#登录数据库查看恢复的数据
mysql> select * from oldboy.test;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | oldboy    |
|  2 | oldgirl   |
|  3 | inca      |
|  4 | zuma      |
|  5 | kaka      |
|  6 | 老男孩    |
+----+-----------+
6 rows in set (0.00 sec)

三、主从复制部署

 1、授权从库连接用户

grant replication slave on *.* to 'rep'@'172.19.5.%' identified by '123456';

2、分发全备数据到从库db01

scp -rp 2018-01-09_14-58-50/ 172.19.5.55:/backup/

3、从库预处理

innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --apply-log --use-memory=1G /backup/2018-01-09_10-33-44/

4、停从库，恢复数据

[root@db01 data]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS! 
rm -fr /application/mysql/data/*
innobackupex --defaults-file="/etc/my.cnf" --user=root --password='123456' --socket=/application/mysql/tmp/mysql.sock --copy-back /backup/2018-01-09_14-58-50/

5、查看master位置点

[root@db01 2018-01-09_14-58-50]# cat /backup/2018-01-09_14-58-50/xtrabackup_binlog_info 
mysql-bin.000001    120

6、启动从库db01，设置主从复制

chown -R mysql.mysql /application/mysql/
/etc/init.d/mysqld start
#登录数据库
CHANGE MASTER TO  
MASTER_HOST='172.19.5.54',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456', 
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;

开启主从同步功能

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.19.5.54
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1438
               Relay_Log_File: db01-relay-bin.000003
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1438
              Relay_Log_Space: 1936
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0

四、主从复制架构扩展

1、主从延迟复制

stop slave;
change master to master_delay = N(600秒);
start slave;

应用场景

①误删除恢复

②测试

③历史查询

2、半同步复制

①master配置

install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_master_timeout = 1000;

②slave配置

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled = 1;
set global rpl_semi_sync_master_timeout = 1000;

③查看半同步是否开启

stop slave io_thread;
start slave io_thread;#重启IO线程
show global status like 'rpl_sem%';#查看半同步状态