上节回顾

1、mysqldump

-u  -p   -S  -h  -P

-A   -B   库  表

--master-data=2

--single-transaction

--set-gtid-purged=OFF  构建主从复制的时候不能加OFF,可以是ON 或者AUTO

--max-allowed-packet=

2、xtranbackup

innobackupex

全备：
        innobackupex --user=root --password=123 --no-timestamp /data/backup/full

增量:
       innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1

整理备份：
       全备：innobackupex --apply-log /data/backup/full

       增量：只有最后一次合并的增量不需要--redo-only,过程中所有备份整理都要加--redo-only
        innobackupex --apply-log --redo-only  --incremental-dir=/data/backup/inc1 /data/backup/full

恢复备份：
            innobackupex --copy-back /data/backup/full

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

binlog截取最重要的是找到起点和终点

周三下午两点，数据损坏，截取日志的思路：
     mysqldump：
           起点：找到周二晚上全备脚本，找到change master to
           终点：通过看events 或者文件内容找到故障点
      xtrabackup：
            起点：找到周二晚上增量备份，备份路径下xtrabackup_binlog_info
            终点：通过看events 或者文件内容 找到故障点的位置
 

4、异构

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

主从复制

1、介绍

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

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

2.1、至少两个实例

2.2、不同的server_id

2.3、主库需要开启二进制日志

2.4、主库需要授权一个专用复制用户

2.5、主从数据备份

2.6、开启专用复制线程

3、搭建主从复制

3.1、准备多实例

3.2、检查server_id

 mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
 mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+

3.3、检查3307主库的二进制日志情况

mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"
+---------------------------------+-----------------------+
| Variable_name                   | Value                 |
+---------------------------------+-----------------------+
| log_bin                         | ON                    |
| log_bin_basename                | /data/mysql-bin       |
| log_bin_index                   | /data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                   |
| log_bin_use_v1_row_events       | OFF                   |
| sql_log_bin                     | ON                    |
+---------------------------------+-----------------------+
6 rows in set (0.00 sec)

3.4、主库创建复制用户

mysql -uroot -p -S /data/3307/mysql.sock
grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

3.5、进行主库数据备份（贴近生产）

mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql

3.6、恢复数据到从库

mysql  -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/full.sql;

3.7、告诉从库复制的信息

mysql>help change master to;

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


vim /tmp/full.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=444;

3.8、启动复制线程

mysql>start slave;

3.9、如果change master to 信息输入错误，怎么办？

mysql>stop slave;
mysql>reset slave all;
CHANGE MASTER TO
  MASTER_HOST='10.0.0.51',
  MASTER_USER='repl',
  MASTER_PASSWORD='123',
  MASTER_PORT=3307,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=444,
  MASTER_CONNECT_RETRY=10;
mysql>start slave;

3.10、检查主从有没有连接上

mysql> show slave status\G

 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

4、主从复制工作过程

4.1、名词认识

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


线程：
        主库：binlog_dump_thread     二进制日志投递线程
               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经过查询如果发现有新的binlog，截取并返回给从库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）relay-log定期自动清理的功能。


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

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

5.1、主从监控

主库：
show processlist;

从库：
show slave status\G



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


从库的relaylog的信息
Relay_Log_File: db01-relay-bin.000002   -------->从库已经运行过的relaylog文件名
Relay_Log_Pos: 639    -------->从库已经运行过的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       ------->IO报错的号码
Last_IO_Error:         ------->IO报错的具体信息
Last_SQL_Errno: 0      ------->SQL报错的号码
Last_SQL_Error:        ------->SQL线程报错的具体原因

5,2,1、IO线程故障

（1）连接主库连接不上
        connecting状态：
               原因：
                      网络不通
                      防火墙
                      IP 不对
                      port 不对
                      用户，密码不对
                      skip_name_resolve
                      连接数上限（默认151个）
        如何解决？
            stop slave;
            reset  slave all;
            CHANGE MASTER TO
            MASTER_HOST='10.0.0.51',
            MASTER_USER='repl',
            MASTER_PASSWORD='123',
            MASTER_PORT=3307,
            MASTER_LOG_FILE='mysql-bin.000003',
            MASTER_LOG_POS=444,
            MASTER_CONNECT_RETRY=10;
            start slave;
（2）请求新的binlog
         IO线程no的状态分析：
         原因一：日志名不对 
                从库信息：                 
                MASTER_LOG_FILE='mysql-bin.000003',
                MASTER_LOG_POS=444,
      对比备份的位置号 
  
         原因二：日志损坏，日志不连续   
          演示：
          主库：
1、flush logs;
2、reset master;
4、create database dd;
5、create database dd1;
6、create database dd2;

           从库：
3、show slave status\G （报错）
7、stop slave;
   reset  slave all;
   CHANGE MASTER TO
   MASTER_HOST='10.0.0.51',
   MASTER_USER='repl',
   MASTER_PASSWORD='123',
   MASTER_PORT=3307,
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=154,
   MASTER_CONNECT_RETRY=10;
   start slave;
8、show slave status\G
       
（3）写relaylog

（4）更新master.info

5.2.1、SQL线程故障

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


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

mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)

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


方法二：万全的解决
设置从库只读，防止写入   
      read_only  只能管普通用户
      super_read_only  管root用户
使用中间件做成读写分离的架构

6、主从延时原因分析

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

6.1、主库方面：

1)日志写入不及时
mysql>  select @@sync_binlog;
+---------------+
| @@sync_binlog |
+---------------+
|             1 |
+---------------+

2)主库业务并发较高
    “分布式架构”

3）从库太多
     级联主从 
 


对于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、 如何设置

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、思路

主库模拟故障：
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线程
 stop slave sql_thread;
2、找relaylog起点和终点
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
show relaylog events in 'db01-relay-bin.000002'
3、截取日志
mysqlbinlog --start-position=320 --stop-position=927 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql


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

8、过滤复制

主库：（了解）
binlog_do_db          白名单
binlog_ignore_db    黑名单
  

从库：
在 SQL线程进行回放日志时，进行控制过滤
Replicate_Do_DB: 
Replicate_Ignore_DB: 

Replicate_Do_Table: 
Replicate_Ignore_Table: 

Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 


例子： 
只需要复制xyz库的数据到从库

vim  /data/3308/my.cnf
replicate_do_db=xyz

systemctl restart mysqld3308

mysql> show slave status \G

注意：如果有多个库的话，写多行即可

image.png

9、半同步复制（基本不用，性能差）

加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';



和传统复制区别:
    是一个插件形式提供的功能
   主库：ack_recevier
   从库：ACK_send

 主库的ack_  re线程，只有接收到从库发来的ACK确认，主库事务才能commit成功
从库的ACK_send只有等relaylog落地才能发送ACK
主库只会等10秒，   如果ACK还没收到的，会自动替换为异步复制。

10、GTID复制

10.0、作用

性能方面：group commit      MTS

10.1、重点参数

gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1              ----强制刷新从库二进制日志：1. 高可用（MHA） 2. 级联复制的中间库

10.2、清理环境

pkill mysqld
 \rm -rf /data/*
 \rm -rf /data/*
 mkdir -p /data/mysql/data 
 mkdir -p /data/binlog/
 chown -R mysql.mysql /data

10.3、准备配置文件

主库db01：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>
EOF

slave1(db02)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF

slave2(db03)：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF

10.4 初始化数据(所有节点)

mysqld --initialize-insecure --user=mysql --basedir=/application/mysql  --datadir=/data/mysql/data

10.5 启动数据库

systemctl start mysqld

10.6 主库创建用户

grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
 

10.7 两个从库开启主从

mysql -e "change master to master_host='10.0.0.51',master_user='repl',master_password='123' ,MASTER_AUTO_POSITION=1;start slave; "

 mysql -e "show slave status \G"|grep Yes