mysql主从复制过程和监控

1. 主库与从库的进程

主库：

Binglog_DUMP Thread  dump

从库：

SLAVE_IO_THREAD    io
SLAVE_SQL_THREAD    sql

图片

过程：

1.从库执行change master to命令（主库的连接信息+复制的起点）
2.从库会将以上的信息，记录到master.info文件
3.从库执行start slave，立即开启io sql线程
4.从库io_t,读取master.info的信息，获取到ip port user pass bninlog 的位置信息
5.从库io请求连接主库，主库专门提供一个dump,负责和io交互
6.io根据binlog的位置信息，请求主库的新的binlog的
7.主库通过dump将最新的binlog的，通过网络tp诶小从库的io
8.io接收到新的binlog的信息，存储到缓存tcp/ip，立即返回ack给主库，并更新master.info 
9.io将tcp/ip中缓存的数据，转储到磁盘relaylog
10.sql读取relay.info的信息，获取上次已经用过的relaylog的位置信息
11.sql会按照上次的位置回放最新的reloylog的，再次更新relay.info
12.从库会自动的purge应用过得relay进行定期的清理

msyql>help change master to 
CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

补充：

一旦主从复制构建完成，主库中发生了新的变化，都会通过dump发送信号给io,增强了主从复制的实时性

2. 主从复制监控

命令

show slave status \G

信息：

                 主库有关的信息
                  Master_Host: 192.168.80.90
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10


              
              Master_Log_File: mysql-bin.000014
          Read_Master_Log_Pos: 194
                     从库的relay应用信息
               Relay_Log_File: sjk1-relay-bin.000011
                Relay_Log_Pos: 407
        Relay_Master_Log_File: mysql-bin.000014
                      从库线程的运行状态
             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: 194
              Relay_Log_Space: 866
              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: 6
                  Master_UUID: dcb138da-4360-11ec-985d-000c29d2f486
             Master_Info_File: /data/mysql/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:
           
       GTID复制有关的状态信息
           Retrieved_Gtid_Set: 61deb43b-3b02-11ec-a22a-000c29d2f486:17-56
            Executed_Gtid_Set: 61deb43b-3b02-11ec-a22a-000c29d2f486:1-56
                Auto_Position: 0


         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:

3. 故障

3.1 io线程故障

1.连接主库
网络 连接信息变更过 防火墙 连接数上限
排错：

手动登录
mysql -urepl -p123 -h 192.168.80.90 -P 3306

解决：

stop slave
reset slave all;
change master to 
start slave

2. binlog没有打开
   主库没有开启binlog
   binlog文件不完整 损坏

主库的reset master 处理
从库

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;

3.存储binlog到relaylog

3.2 sql 线程故障

relay-log损坏
回放relaylog
研究sql语句失败的原因
库 或 表已经存在
约束冲突，主键，唯一键，非空

处理：
原则：一切以主库为准
反操作，删除从库已存在的库或表

暴力的解决：不建议

方法一：

stop slave; 
set global sql_slave_skip_counter = 1;
start slave;

#将同步指针向下移动一个，如果多次不同步，可以重复操作。
start slave;

方法二：
/etc/my.cnf
slave-skip-errors = 1032,1062,1007

常见错误代码:
1007:对象已存在
1032:无法执行DML
1062:主键冲突,或约束冲突
为了很程度的避免SQL线程故障

为了很程度的避免SQL线程故障
(1) 从库只读
read_only
super_read_only
(2) 使用读写分离中间件
atlas
mycat
ProxySQL
MaxScale

4. 主从延时的原因

主库

1.binlog写入的bujishi
2.默认情况下dump是串行传输的，一步一步按照顺序，到时慢
解决：必须gtid使用gtid commit的方式，支持dump并行
3.主库很繁忙
慢语句
锁等待
从库的个数
网络延时

从库：

1.如果主库的并发事务量很大，由于从库的单sql的线程，导致你不管传多少日志，一次只能执行一个事务
5.6 版本,有了GTID,可以实现多SQL线程,但是只能基于不同库的事务进行并发回放.(database) 
5.7 版本中,有了增强的GTID,增加了seq_no,增加了新型的并发SQL线程模式(logical_clock),MTS技术
2.主从硬件差别大
3.主从的参数配置
4.索引不一样
5.版本有差异

主从延时的监控

show slave  status\G
Seconds_Behind_Master: 0

主库方面原因的监控

主库:
mysql> show master status ;
File: mysql-bin.000001
Position: 1373

从库
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1373

从库方面原因监控:

拿了多少:
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 691688
执行了多少:
Relay_Log_File: db01-relay-bin.000004
Relay_Log_Pos: 690635
Exec_Master_Log_Pos: 691000
Relay_Log_Space: 690635