主从

﹏ヽ暗。殇╰゛Y 2022-04-13 14:25 124阅读 0赞

# mysql复制的三种方式 #

1.  基于SQL语句的复制(statement-based replication, SBR) 对应binlog STATEMENT格式
2.  基于行的复制(row-based replication, RBR) 对应binlog row格式
3.  混合模式复制(mixed-based replication, MBR) 对应binlog mixed格式

# show slave status\\G 查看 #

*  slave I/O线程的状态  
    1.waiting for master update 等待连接到主库进行同步，是连接之前的状态  
    2.connecting to master 正在尝试连接到主库  
    3.waiting to reconnect after a failed binlog dump request 因为断开连接，导致binlog请求失败，I/O线程进入休眠状态，定期尝试重连  
    4.waiting for master to send event 连接正常，等待binlog
 *  Master\_Log\_File 当前I/O线程正在读取的主服务器二进制日志文件的名称。
 *  Read\_Master\_Log\_Pos 当前I/O线程正在读取的二进制日志的位置。
 *  Relay\_Log\_File 当前slave SQL线程正在读取并执行的relay log的文件名。
 *  Relay\_Log\_Pos 当前slave SQL线程正在读取并执行的relay log文件中的位置
 *  Relay\_Master\_Log\_File SQL线程从relay日志中读取的正在执行的sql语句，对应主库的sql语句记录在主库的binlog日志信息
 *  Slave\_IO\_Running I/O线程是否被启动并成功地连接到主服务器上。若为yes则说明成功连接8.Slave\_SQL\_Running SQL线程是否被启动，若为yes则说明正常连接
 *  Relay\_Log\_Space 中继日志的总大小。
 *  seconds\_Behind\_Master: 是slave当前的时间戳和master记录该事件时的时间戳的差值。
 *  SQL\_Delay 主从延迟
 *  Slave\_SQL\_Running\_State SQL线程运行状态

1.  Reading event from the relay log 线程已经从中继日志读取一个事件，可以对事件进行处理了。
2.  Has read all relay log / waiting for the slave I/O thread to update it 线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

*  Retrieved\_Gtid\_Set 获取到的GTID<IO线程>
 *  Executed\_Gtid\_Set 执行过的GTID<SQL线程>

# 参数 #

*  主库参数  
    binlog-do-db = \# 需要复制的库 只记录某个数据库的binlog  
    binlog-ignore-db = \# 需要被忽略的库 不记录某个数据库的binlog
 *  从库参数  
    log\_slave\_updates 是否记录主从的数据的binlog，默认是开着的  
    replicate-do-db = \# 需要复制的库  
    replicate-ignore-db = \# 需要忽略的库  
    replicate-do-table = \# 需要复制的表  
    replicate-ignore-table = 需要忽略的

# mysql replication主从的三个线程 #

*  Master Binlog dump thread：Master端创建该线程来响应Slave端I/O线程的请求，向Slave端发送二进制内容。Binlog dump线程读取主服务器二进制内容前会对其加锁，读取结束后无论内容是否被发送到Slave端，锁都会被即刻释放。在一主多从架构中，Master端会为每个连接的I/O线程创建一个Binlog dump线程。
 *  Slave I/O thread：当Slave端执行START SLAVE命令时，Slave端随即创建一个I/O线程，并连接Master端请求更新。Slave端的I/O线程从Master端的Binlog dump线程读取更新并拷贝到本地Relay log（中继日志）中。
 *  Slave SQL thread：Slave创建的第二个线程是SQL线程，该线程从Relay log中读取和执行语句。

# 异步复制 #

1.  Slave创建I/O线程并连接Master，请求日志文件的指定位置（或者从最开始的日志）之后的日志内容。
2.  Master服务器接收到来自Slave服务器的I/O线程请求后，会随之创建Binlog Dump线程来响应这个线程。然后，Binlog Dump线程根据请求信息读取指定日志位置之后的日志信息，返回给Slave端的I/O线程。
3.  Slave的I/O线程接收到信息后，将日志内容依次写入Slave端的Relay Log（中继日志）文件中，并将读取到的Master端的二进制文件名和位置记录到master-info文件中。
4.  Slave的SQL线程检测到Relay Log中新内容后，会马上解析并执行这些语句。

# 半同步复制 #

*  前提：master和至少一个slave都要启用semi-sync replication模式；
 *  与普通异步复制的区别

1.  某个slave连接到master时，会主动告知当前自己是否处于semi-sync模式；
2.  在master上提交事务，写入binlog后，还需要通知至少一个slave收到该事务，等待写入relay log并成功刷新到磁盘后，向master发送“slave节点已完成该事务”确认通知；
3.  master收到上述通知后，才可以真正完成该事务提交，返回事务成功标记；

*  当slave向master发送通知时间超过rpl\_semi\_sync\_master\_timeout设定值时，主从关系会从semi-sync模式自动调整成为传统的异步复制模式。

# GTID复制 #

*  GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID\_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID\_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找点儿，而无需像之前那样通过File\_name和File\_position找点了。
 *  server\_uuid和transaction\_id两个共同组成一个GTID。即：GTID = server\_uuid:transaction\_id

mysql> show global variables like 'server_uuid';

### 注意 ###

*  AUTOMATIC以后，再有事务执行时，MySQL生成transaction\_id时，不是按当前最大的transaction\_id继续增长，而是补缺口（使用最小的缺失的那个transaction\_id作为下一个gtid）。这样的话，即使是同一个server\_uuid，也不能通过transaction\_id的大小来判断事务的顺序。
 *  gtid\_mode=ON/OFF，不能设置为0或1

### 相关变量 ###

*  gtid\_executed：这既是一个Global级别的变量，又是一个Session级别的变量，是只读变量。Global级别的gtid\_executed表示当前实例已经执行过的GTID集合。Session级别的gtid\_executed一般情况下是空的。
 *  gtid\_owned：这既是一个Global级别的变量，又是一个Session级别的变量，是只读变量。Global级别的gtid\_owned表示当前实例正在执行中的GTID，以及对应的线程id。Session级别的gtid\_owned一般情况下是空的。
 *  gtid\_purged：这是一个Global级别的变量，可动态修改。我们知道binlog可以被purge掉，gtid\_purged表示当前实例中已经被purge掉的GTID集合，很明显gtid\_purged是gtid\_executed的子集。但是gtid\_purged也不是可以随意修改的，必须在@@global.gtid\_executed是空的情况下，才可以动态设置gtid\_purged。

### gtid在binlog中的位置 ###

# at 120
    #151222  9:07:58 server id 1026872634  end_log_pos 247 CRC32 0xedf993a8     Previous-GTIDs
    # b3485508-883f-11e5-85fb-e41f136aba3e:1-14,
    # b694c8b2-883f-11e5-85fb-e41f136aba3e:1-10115960:12000000-12000005
    # at 247
    #151222  9:08:03 server id 1026872625  end_log_pos 295 CRC32 0xc3d3d8ee     GTID [commit=yes]
    SET @@SESSION.GTID_NEXT= 'b694c8b2-883f-11e5-85fb-e41f136aba3e:10115961'/*!*/;
    # at 295
    #151222  9:08:03 server id 1026872625  end_log_pos 370 CRC32 0x0a32d229     Query   thread_id=18    exec_time=1 error_code=0
    BEGIN
    /*!*/;
    # at 370
    #151222  9:08:03 server id 1026872625  end_log_pos 480 CRC32 0x3c0e094f     Query   thread_id=18    exec_time=1 error_code=0
    use `db`/*!*/;
    SET TIMESTAMP=1450746483/*!*/;
    update tb set val = val + 1 where id = 1
    /*!*/;
    # at 480
    #151222  9:08:03 server id 1026872625  end_log_pos 511 CRC32 0x5772f16b     Xid = 6813913
    COMMIT/*!*/;
    # at 511
    #151222  9:10:19 server id 1026872625  end_log_pos 559 CRC32 0x3ac30191     GTID [commit=yes]
    SET @@SESSION.GTID_NEXT= 'b694c8b2-883f-11e5-85fb-e41f136aba3e:10115962'/*!*/;
    # at 559
    #151222  9:10:19 server id 1026872625  end_log_pos 634 CRC32 0x83a74912     Query   thread_id=18    exec_time=0 error_code=0
    SET TIMESTAMP=1450746619/*!*/;
    BEGIN
    /*!*/;
    # at 634
    #151222  9:10:19 server id 1026872625  end_log_pos 744 CRC32 0x581f6031     Query   thread_id=18    exec_time=0 error_code=0
    SET TIMESTAMP=1450746619/*!*/;
    update tb set val = val + 1 where id = 1
    /*!*/;
    # at 744
    #151222  9:10:19 server id 1026872625  end_log_pos 775 CRC32 0x793f8e34     Xid = 6813916
    COMMIT/*!*/;

### 每个事务之前，都有一个GTID\_log\_event，用来指定GTID的值 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NTUzMjk0_size_16_color_FFFFFF_t_70]

## GTID原理 ##

*  在开启GTID模式的情况下，如果指定MASTER\_AUTO\_POSITION=1。START SLAVE时，从库会计算Retrieved\_Gtid\_Set和Executed\_Gtid\_Set的并集（通过SHOW SLAVE STATUS可以查看），然后把这个GTID并集发送给主库。主库会使用从库请求的GTID集合和自己的gtid\_executed比较，把从库GTID集合里缺失的事务全都发送给从库。如果从库缺失的GTID，已经被主库pruge了呢？从库报1236错误，IO线程中断。
 *  GTID更新的时机

1.  mysql.gtid\_executed表：GTID持久化的介质，MySQL启动阶段会读取这个表来获取gtid\_executed变量的值。
2.  gtid\_executed变量(show global variables)：MySQL数据库已经执行了哪些GTID事务，处于内存中。show slave status中的executed\_gtid\_set也取自这里。
3.  gtid\_purged变量(show global variables)：由于BINLOG文件的删除(如purge binary logfiles或者超过expire\_logs\_days设置)已经丢失的GTID事务，同时在搭建备库的我们使用set global gtid\_purged变量来提示MySQL哪些GTID事务我已经执行过了。

## 跳过事务 ##

*  开启GTID以后，无法使用set global sql\_slave\_skip\_counter ＝ x 跳过事务。
 *  通过set grid\_next= ‘zzzz’（'zzzz’为待跳过的事务），然后执行BIGIN;COMMIT产生一个空事务，占据这个GTID，再START SLAVE，会发现下一条事务的GTID已经执行过，就会跳过这个事务了

# 复制延迟配置 #

mysql>stop slave;
    mysql>CHANGE MASTER TO MASTER_DELAY = 100；         -------单位为秒
    mysql>start slave;
    mysql>show slave status \G;

*  SQL\_Delay：字段变为100，表示此时从库与主库的同步开始有了100秒的固定延迟。
 *  SQL\_Remaining\_Delay：当主库有了新的操作，该字段会进入倒计时，从配置的延迟时间(这里为100)开始。正常情况下为NULL。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0NTUzMjk0_size_16_color_FFFFFF_t_70]: /images/20220413/d0ed04350d304961807bcaa5f38f7ca7.png