一、上节回顾

1、mysqldump

-u -p -S -h -P -A -B 库 表
-R --triggers -E 
--master-data=2
--single-transaction
--set-gtid-purged=OFF 构建主从的备份千万不能加OFF，可以是ON或者AUTO
--max-allowed-packet

• 2、xtrabackup

innobackupex
全备：
     innobackupex --user --password --no-timestamp /data/backup/full
增量：
     innobackupex --user --password --no-timestamp --incremental --incremental-basedir=/data/backup/full/ /data/backup/incl
整理备份：
    全备：
         innobackupex --apply-log /data/backup/full
    增量:
         只有最后一次合并的增量不需要--redo-only，过程中所有备份整理都要加--redo-only。
         innobackupex --apply-log --redo-only --incremental-dir=/data/backup/incl /data/backup/full
恢复备份：
         innobackupex --copy-back /data/backup/full

• 3、备份工具如何配合binlog应用

binlog截取最重要的是找 起点和终点
周三下午两点，数据损坏，截取日志的思路

mysqldump：
          起点：找到周二晚上全备脚本，找到change master to
          终点：通过看events 或者 文件内容 找到故障点的位置

• 4、异构

操作系统版本不一样
逻辑备份
数据库软件不一样
MySQL    ---CSV-> MongoDB ,ES

MySQL    ---JSON-> MongoDB ,ES

二、主从复制（重点）

1、介绍

依赖于二进制日志的，“实时”备份的一个多节点架构

2、搭建主从复制的前提（搭建主从复制）

2.1 至少2个实例
2.2 不同server_id
2.3 主库需要开启二进制日志
2.4 主库需要授权一个专用复制用户
2.5 主库数据备份
2.6 开专用复制线程

3、搭建主从复制

多实例搭建文档参考文档

3.1 准备多实例
启动多实例
[root@mysql ~]# systemctl start mysqld3307.service 
[root@mysql ~]# systemctl start mysqld3308.service    
[root@mysql ~]# systemctl start mysqld3309.service 
[root@mysql ~]# netstat -luntp |egrep  330*

3.2、检查server ID
[root@mysql ~]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
[root@mysql ~]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
[root@mysql ~]# mysql -S /data/3309/mysql.sock -e "select @@server_id"

3.3、检查3307（主库）的二进制日志情况
[root@mysql /data/3307]# mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"

3.4、创建主库复制用户
[root@mysql /data/3307]# mysql -S  /data/3307/mysql.sock
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

3.5、进行主库数据库备份
[root@mysql /data/3307]# mysqldump -S /data/3307/mysql.sock  -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql

3.6、恢复数据到从库
[root@mysql /data/3308]# mysql -S  /data/3308/mysql.sock
mysql> set sql_log_bin=0;
mysql> source /tmp/full.sql;

3.7、告诉从库复制的信息
[root@mysql /data/3307]# mysql -S  /data/3307/mysql.sock
mysql> help change master to

[root@mysql /data/3307]# vim /tmp/full.sql        #查看备份日志获得二进制号，pos号
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=444;

[root@mysql /data/3308]# mysql -S  /data/3308/mysql.sock
mysql> CHANGE MASTER TO 
  MASTER_HOST='10.0.0.51',              #主库IP
  MASTER_USER='repl',                   #授权的用户
  MASTER_PASSWORD='123456',             #授权的密码
  MASTER_PORT=3307,                     #主库端口
  MASTER_LOG_FILE='mysql-bin.000004',   #备份日志的二进制号
  MASTER_LOG_POS=444,                   #pos号
  MASTER_CONNECT_RETRY=10;

###如果 change master to 信息输入错误，咋办？
[root@mysql /data/3308]# mysql -S  /data/3308/mysql.sock
mysql> stop slave;
mysql> reset slave all;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;
mysql> start slave;

3.8、启动复制线程（从库操作）
[root@mysql /data/3308]# mysql -S  /data/3308/mysql.sock
mysql> start slave;

3.9、查看主从是否连接成功
mysql -S  /data/3308/mysql.sock
mysql> show slave status\G
#查看这两行
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

3.10、测试（主库创建一个库，从库查看）
#主库创建库
mysql> create database zhucong;

#从库查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
| zhucong            |
+--------------------+
6 rows in set (0.00 sec)

4、主从复制工作过程

• 4.1、名词认识

文件：
    主库：binlog

    从库：
         relay-log          中继日志
         master.info        主库信息文件
         relay-log.info     中继日志应用信息

线程：
     主库：Binlog_Dump_thread 二进制日志投递线程
          查看二进制日志投递线程
          [root@db01 /data/3307/data]# mysql -S /data/3307/mysql.sock -e "show processlist"

     从库: 
          IO_Thread :  从库IO线程 ：    请求和接收binlog
          SQL_Thread:  从库的SQL线程 ： 回放日志

• 4.2、主从复制工作原理

主从原理图

工作原理：
        (1) 从库执行 change master to 语句，会立即将主库信息记录到master.info中
        (2) 从库执行 start slave语句，会立即生成IO_T和SQL_T
        (3) IO_T 读取master.info文件，获取主库信息
        (4) IO_T 连接主库，主库会立即分配一个DUMP_T,进行交互
        (5) IO_T 根据master.info binlog信息，向DUMP_T请求最新的binlog
        (6) 主库DUMP_T,经过查询，如果发现有新的，截取并返回给从库IO_T
        (7) 从库IO_T会收到binlog，存储在TCP/IP缓存中，在网络底层返回ACK
        (8) 从库IO_T会更新master.info，重置binlog位置点信息
        (9) 从库IO_T会将binlog，写入到relay-log中
        (10) 从库SQL_T 读取relay-log.info 文件，获取上次执行过的位置点
        (11) SQL_T按照位置点往下执行relay-log日志
        (12) SQL_T执行完成之后，重新更新relay-log.info
        (13) relaylog定期自动清理的功能

细节：
主库发生了信息的修改，更新二进制日志完成后，会发送一个信号DUMP_T,DIPM_T通知给IO_T线程

5、主从复制监控及分析故障处理

• 5.1 主从监控

主库： 
    show processlist; 
    Master has sent all binlog to slave; waiting for more updates  #正常状态

从库： 
    show slave status \G

主库的信息（master.info）：        
Master_Host: 10.0.0.51                       主库的IP
Master_User: repl                            复制用户名
Master_Port: 3307                            主库的端口
Connect_Retry: 10                            断连之后重试次数
Master_Log_File: mysql-bin.000001            已经获取得到binlog的文件名
Read_Master_Log_Pos: 444                     已经获取得到binlog的位置号

从库的relaylog的信息(relay-log.info)：
Relay_Log_File: db01-relay-bin.000002        从库已经运行过的relaylog的文件名
Relay_Log_Pos: 320                           从库已经运行过的relaylog的位置点

从库复制线程工作状态：
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: 

从库延时主库的时间:
Seconds_Behind_Master: 0                     从库延时主库的时间（秒为单位）

从库线程报错详细信息：
Last_IO_Errno: 0                             IO报错的号码
Last_IO_Error:                               IO报错的具体信息
Last_SQL_Errno: 0                            SQL报错的号码
Last_SQL_Error:                              SQL线程报错的具体原因

延时从库：
SQL_Delay: 0                                 延时从库设定的时间
SQL_Remaining_Delay: NULL                    延时操作剩余时间  

GTID复制信息：
Retrieved_Gtid_Set:                          接收到的GTID的个数
Executed_Gtid_Set:                           执行了的GTID的个数

• 5.2 主从故障的分析及处理

从库复制线程工作状态：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

从库线程报错详细信息：
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:

• 5.2.1 IO线程故障

(1) 连接主库连接不上
connecting
原因：
     网络不通
     防火墙

     IP不对
     port不对
     用户,密码不对
     skip_name_resolve
     连接数上限

处理思路：
[root@mysql /data/3308]# mysql -urepl -p123 -h 10.0.0.51 -P 3307  ----> 密码错误
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'mysql' (using password: YES)

[root@mysql /data/3308]# mysql -ureplo -p123456 -h 10.0.0.51 -P 3307 ----> 用户名错误
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'replo'@'mysql' (using password: YES)

[root@mysql /data/3308]# mysql -urepl -p123 -h 10.0.0.51 -P 3308 ----> 端口错误
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'mysql' is not allowed to connect to this MySQL server

[root@mysql /data/3308]# mysql -urepl -p123 -h 10.0.0.52 -P 3307 ----> IP错误
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110)

[root@mysql /data/3308]# mysql -urepl -123 -h 10.0.0.52 -P 3307 ----> 参数错误
mysql: [ERROR] mysql: unknown option '-1'

如何处理？
[root@mysql /data/3308]# mysql -S /data/3308/mysql.sock
mysql> stop slave;
mysql> reset slave all;
mysql> change master to
mysql> CHANGE MASTER TO              #修改数据
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;
mysql> start slave;

(2) 请求新的binlog
IO线程NO的状态分析：
原因1：日志名不对
从库信息：
         Master_Log_File: mysql-bin.000001            
         Read_Master_Log_Pos: 444         
         对比备份的位置号                   

原因2：日志损坏原、日志不连续
演示损坏步骤：
    主库：
        [root@mysql /data/3307]# mysql -S /data/3307/mysql.sock
        mysql> flush logs;
        mysql> flush logs;
        mysql> flush logs;
        mysql> flush logs;
        mysql> flush logs;
        mysql> reset master;

        mysql> create database dd;
        mysql> create database dd1;
        mysql> create database dd2;

    从库：
        [root@mysql /data/3308]# mysql -S /data/3308/mysql.sock
        mysql> show slave status\G
        Slave_IO_Running: No
        Slave_SQL_Running: Yes
        报错信息：
                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000004' at 444, the last event read from '/data/3307/mysql-bin.000010' at 154, the last byte read from '/data/3307/mysql-bin.000010' at 154.'
        恢复：
            [root@mysql /data/3307]# mysql -S /data/3307/mysql.sock 
            [root@mysql /data/3307]# mysql> show master status\G;       #在主库上查看二进制号
            File: mysql-bin.000001

            [root@mysql /data/3308]# mysql -S /data/3308/mysql.sock 
            mysql> stop slave;
            mysql> reset slave all ;
            mysql> CHANGE MASTER TO
               MASTER_HOST='10.0.0.51',
               MASTER_USER='repl',
               MASTER_PASSWORD='123456',
               MASTER_PORT=3307,
               MASTER_LOG_FILE='mysql-bin.000001',
               MASTER_LOG_POS=154,
               MASTER_CONNECT_RETRY=10;
            mysql> start slave;
        查看结果：   
                 mysql> show slave status\G
                 Slave_IO_Running: yes
                 Slave_SQL_Running: Yes

(3) 写relaylog
(4) 更新master.info
(5) server_id重复

• 5.2.2 SQL线程故障

原因1：
读relay-log.info
读relay-log，并执行日志
更新relay-log.info
以上文件损坏，最好是重新构建主从

原因2：
为什么一条SQL语句执行不成功
1、主从数据库版本差异较大
2、主从数据库配置参数不一致（例如：sql_mode等）
3、想要创建的对象已经存在
4、想要删除或修改对象不存在
5、主键冲突
6、DML语句不符合表定义及约束时
7、归根结底是从库写入了

从库出现通过错误跳过错误：
方法一：
mysql> stop slave;                                   #停止从库
mysql> set global sql_slave_skip_counter = 1;        #从库跳过主库过来错误语句
mysql> start slave;                                  #重新启动从库

方法二：

[root@mysql /data/3308]# vim /etc/my.cnf                                  
slave-skip-errors = 1032,1062,1007                   #只要一遇到1032,1062,1007，直接跳过

方法三：万全的解决
设置从库只读，防止写入
使用中间件做成读写分离的架构

设置只读查看方法，一般不推荐，可以使用中间件来实现：
mysql> show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |  
| read_only             | OFF   |   #设置为0，能对普通用户有效
| super_read_only       | OFF   |   #设置为0，能对root用户有效
| transaction_read_only | OFF   | 
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.02 sec)

6、主从延时原因分析

从库延时主库的时间（以秒为单位）：
Seconds_Behind_Master: 0 

• 6.1 主库方面

日志写入不及时:
               mysql> select @@sync_binlog;
               +---------------+
               | @@sync_binlog |
               +---------------+
               |             1 |  #等于1立刻刷新
               +---------------+
               1 row in set (0.00 sec)
主库并发业务较高:
                采用'分布式架构'
从库太多：
         级联主从

对于Classic Replication：
主库是有能力并发运动事务的，但是在Dump_T再传输日志的数据时候，是以事件为单元传输日志的，
所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。

怎么处理：
group commit
从5.6开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID支持，还需要双一保证。

• 6.2 从库方面

Classic Replication
SQL 线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程
在5.6中出现了database级别多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS，真正实现了事务级别的并发SQL

7、延时从库

• 7.1、数据损坏

数据损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。

• 7.2、延时从库设置理念

对SQL线程进行延时设置

• 7.3、延时从库设置多久合适

一般企业，延时3-6小时

• 7.4、如何设置延时从库（秒为单位）

[root@mysql /data/3308]# mysql -S /data/3308/mysql.sock
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_DELAY = 300;  
mysql> start slave;

#查看设置结果
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL

• 7.5、如何使用延时从库

• 7.5.1 思路

模拟故障：
[root@mysql /data/3307]# mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;

发现问题了：
1、停止SQL线程，停止主库业务
2、模拟SQL线程，手工恢复relaylog到drop之前的位置点
3、截取relaylog日志，找到起点（relay-log.info）和终点（drop 操作）
4、恢复截取日志，验证数据可用性

开始处理：
1、停止从库的SQL线程
[root@mysql /data/3308]# mysql -S /data/3308/mysql.sock
mysql> stop slave sql_thread;

2、找到起始点
show slave status \G
Relay_Log_Pos: 473

3、找到终点，找到drop之前操作即可，只看左边pos号
show relaylog events in 'mysql-relay-bin.000002';
1080

4、备份
mysqlbinlog --start-position=473 --stop-position=1080 /data/3308/data/mysql-relay-bin.000002 >/tmp/relay.sql

5、恢复 
mysql -S /data/3308/mysql.sock 
set sql_log_bin=0;
source /tmp/relay.sql

8、过滤复制

9、半同步复制

CHANGE MASTER TO
MASTER_HOST='10.0.0.51',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;