主从复制查询状态+故障及延时

1. 查看配置文件

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
             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: 154
              Relay_Log_Space: 739
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 7
                  Master_UUID: e44991f2-b834-11e9-8c01-000c2992bac2
             Master_Info_File: /data/3308/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

1.1 查看线程状态

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Running"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

1.2 线程报错具体信息

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Last"
                   Last_Errno: 0
                   Last_Error: 
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 

1.3 主库连接信息以及二进制信息(master)

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Master" | grep -v 'SSL'
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3307
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 154
        Relay_Master_Log_File: mysql-bin.000007
          Exec_Master_Log_Pos: 154
        Seconds_Behind_Master: 0
             Master_Server_Id: 7
                  Master_UUID: e44991f2-b834-11e9-8c01-000c2992bac2
             Master_Info_File: /data/3308/data/master.info
           Master_Retry_Count: 86400
                  Master_Bind: 
           Master_TLS_Version:

1.4 从库和主库延时的时间

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep Behind"_Master"
Seconds_Behind_Master: 0

1.5 过滤复制相关状态

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Replicate"
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB:

1.6 延时从库的状态信息

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Delay"
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL

1.7 查看监控GTID状态

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Gtid"
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 

1.8 中继日志监控

[root@db01 ~]$ mysql -S /data/3308/mysql.sock -e 'show slave status\G;' | grep "Relay"
               Relay_Log_File: db01-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000007
              Relay_Log_Space: 739

2. 主从复制故障

2.1 IO线程故障

• 读取master.info

损坏
信息错误change master to信息错误

• 连接主库

网络
防火墙
主机(主库)没有运行
连接数上线

>> 以上问题报错:
Slave_IO_running: Connecting
Last_IO_Error: xxxxxxx

>> 排查方法
通过复制用户，手工连接主库，查看报错现象

修复：
stop slave
reset slave all
change master to
start slave

• 请求日志

master.info 复制起点
主库：损害，误删除等操作

• 接收日志

relaylog损坏
>>修复
stop slave
reset slave all
change master to
start slave

• 更新mater.info(拿到的信息存放在relay.info中)

2.2 SQL线程故障

(1) relay.info
(2) 回放relaylog中的日志

SQL语句为什么会失败？
(1) 语法，SQL_Mode版

• 版本差异，SQL_mode不一致

(2) DDL DCL DML为什么会失败

• create database /table 创建的对象已经存在。
  从库被提前写入了
• drop database errdb; 要删除和修改的对象不存在
• alter.insert update,delete

处理方法(以从库为核心的处理方案)：

方法一：
stop slave; 
set global sql_slave_skip_counter = 1;
#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;
方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突

但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.

2.3 防止从库写入

>> (1) 可以设置从库只读.
mysql[(none)]>show variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |
| super_read_only       | OFF   |
| transaction_read_only | OFF   |
| tx_read_only          | OFF   |
+-----------------------+-------+
5 rows in set (0.01 sec)
注意：
只会影响到普通用户，对管理员用户无效。
>> (2)加中间件
读写分离。

2.4 其他扩展

pt-xxx 关于主从方面的工具
检查主从数据一致性
实现主从数据同步

3. 主从延时

3.1 延时

主库做的事，从库很久才执行。

3.2 主从延时的现象

（1）最直观：主库做变更，从库看状态
（2）Seconds_Behind_Master:0(只能证明，有或没有)
（3）计算日志的差异

3.3 主从延时的原因

（1）外部因素

• 网络
• 硬件
• 版本差异
• 参数差异

（2）主从复制的内部原因
主库：

• 二进制日志的方面

二进制日志落地不及时

解决方案：
sync_binlog=1
可以将binlog单独存放在高性能存储中

• Dump_T(默认是串行工作模式)

主库的事务量大
主库发生大事务

解决方案：

1. GTID模式
2. 双一的保证

如何监控：

主库：
mysql[(none)]>show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                    |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000017 |      234 |              |                  | 06073cdc-bfd1-11e9-9a47-000c2992bac2:1-6,
c4e0cd26-b75c-11e9-b4d8-000c2992bac2:1-242 |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从库： 
show slave status\G;
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154

从库：
（1）IO线程方面
relaylog写入
解决方案：
可以将relaylog单独存放在高性能存储中
（2）SQL线程方面(只有一个,串行回放)
默认SQL线程，只能逐条的回放SQL
事务并发度高或者大事务

5.6 版本加入了多SQL复制

按照库(database)级别，进行并发回放SQL
slave_parallel_workers=16
slave_parallel_type=DATABASE

5.7 版本进行了多SQL复制加强(MTS)

真正按照事务级别，实现了多SQL线程回放。
slave_parallel_workers=16
slave_parallel_type=logical_clock

注意：必须依赖于GTID复制

如何监控：

（1）监控取了多少日志
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
（2）监控回放了多少日志
[root@db01 data]$ cat relay-log.info 
7
./db01-relay-bin.000002
320
mysql-bin.000001
154

4. 主从基础小结

4.1 主从前提
4.2 主从原理 *****
4.3 主从监控
    show master status;
    show processlist;
    show slave status\G
    master.info 
    relay.info
4.4 主从故障
    IO 
        连接 
        binlog
    SQL 
        从库写入
        DML,insert ,update ,delete

4.5 主从延时 
    主
        dump 串行 : GTID,双一 并行
        show slave status\G 
        Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 1084       
        
        show master status \G
        
    从
        SQL 串行  : MTS 
        已经拿过来的日志: 
        show slave status\G 
        Master_Log_File: mysql-bin.000001
        Read_Master_Log_Pos: 1084
        已经执行过的:
        ./db01-relay-bin.000003
        920
        mysql-bin.000001
        800