mysql 级联 binlog_MySQL主从复制（1）基于Binlog的主从复制

小咪咪 2023-01-11 08:17 142阅读 0赞

一、MySQL主从复制介绍

主从复制是一种MySQL自带的容灾备份方案，可以在一定程度上实现业务读写分离，如主从节点配置不同的域名或者使用中间件来实现)，还可以在一定程度上当做备份使用(配置一台从服务器为延迟从库的方式来实现)。传统的主从复制依赖于MySQL binlog实现。主节点通过Binlog记录了数据库所有DDL与DML操作(SELECT、SHOW等语句不对数据产生变化，不会有记录)，从库将binlog日志复制到自己的中继日志Relaylog中，然后根据Relaylog做相同的操作，实现了数据的同步。

主从结构通常可以分为一主一从、一主多从、级联复制等，如果是一主多从的架构需要注意从服务器的数量不能太多，否则会因为日志的传输给主库带来过多的带宽消耗(曾遇到主库网卡异常导致从库一直卡住的原因，当时一直思考的是大事务原因，结果是硬件导致)

二、Binlog日志介绍

1、binlog写入机制

事务执行过程中先把日志写到binlog cache中，等事务提交的时候再把cache写到binlog文件fsync落盘并清空cache，这一点和redolog很像。一个事务的 binlog不能被拆开，不论事务多大也要确保一次性写入binlog。每个线程都会申请一块binlog cache，内存空间大小由参数 binlog\_cache\_size控制。如果超过参数设置的大小就要暂存到磁盘。而write和fsync 的时机由参数sync\_binlog决定(双1参数之1)

2、binlog格式介绍

· statement(标准模式，简称SBR)：保存的是每一条修改数据的SQL语句，然后在从服务器上会执行相同的语句来同步数据。优点是日志记录量比较少，缺点是数据一致性不是最高级别

· row(行模式，简称RBR)：推荐模式，将数据的具体改变记录在日志中，日志量大，安全性最高，不会因为某些产生随机数的SQL而让主从数据不一致，比如包含了时间函数的语句。row格式的二进制日志无法直接阅读，需要转码

· mixed(混合模式，简称MBR)：statement和row的结合模式，当基于语句无法精确复制时，就会采用行模式进行复制。实际上比较鸡肋，不考虑使用

delete \* from test; \#假设这条SQL语句删除了100万条数据

\# STATEMENT语句模式仅记录这一条SQL语句

\# ROW行模式会记录执行删除时的每一个语句，会记录100万次

三、MySQL主从复制配置

在部署主从架构前先确保主从节点的MySQL版本一致、时间一致

![4fa1559e93557761b4ed9ade0022f33a.png][]

1、Master主库配置

· 主库开启binlog以及先关配置

\[mysqld\]

basedir = /usr/local/mysql

datadir = /mysqldata

port = 3306

socket = /tmp/mysql.sock

server\_id = 1 \#开启binlog的话必须配置它，建议设置为IP最后一段+端口的形式，如103306，该ID最大值为4294967295，主从不能一样。

innodb\_file\_per\_table = ON

\#\#\#\# log set \#\#\#

log\_bin = /data/mysql/log/master-bin \#binlog日志路径与日志文件前缀

log\_bin\_index = /data/mysql/log/master-bin.index \#binlog索引文件

log\_bin\_trust\_function\_creators = 1 \#默认为0，开启后可以同步函数和存储过程

binlog\_format = row \#推荐用row模式

binlog\_rows\_query\_log\_events = on \#将原始sql语句加上注释记录到日志中便于分析

expire\_logs\_days = 10 \#二进制日志过期天数，通常设置为一个全备周期+1天，比如每周全备一次，这个时间就设置为8

max\_binlog\_size = 1024M \#二进制日志大小，如果一个SQL事务产生的数据超过该限制，则突破该限制

binlog\_cache\_size = 2M \#二进制日志缓存区大小，默认32K，建议1-4M

sync\_binlog = 1 \#binlog buffer刷盘策略。1代表每次提交事务之前将二进制日志同步到硬盘；N为有N次commit的时候才写入磁盘；0为关闭

innodb\_flush\_log\_at\_trx\_commit=1 \#默认为1，每次事务提交时会把buffer数据写入磁盘

master\_info\_repository = TABLE \#将master-info信息保存到表中，减少开销的同时可以提升可靠性。默认是file

relay\_log\_info\_repository = TABLE

log\_slave\_updates = 1

relay\_log\_recovery = 1 \#如果从库IO线程崩溃，并且relaylog损坏，则放弃所有未执行的relaylog，重新从master获取日志保持完整

· 主库建立一个账号并给与数据复制权限

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON \*.\* TO 'slave'@'192.168.36.11' IDENTIFIED BY '123456789';

· 查看主库当前的position信息，从库会从该位置点进行复制

mysql > show master status \\G

2、Slave从库配置(业务从库)

· 从库配置relaylog中继日志相关信息，默认情况下relaylog会在SQL线程执行完毕后自动删除

\[mysqld\]

basedir = /usr/local/mysql

datadir = /mysqldata

port = 3306

socket = /tmp/mysql.sock

server\_id = 2 \#和Master的ID不一样即可

relay-log = relay-log \#中继日志路径和文件名，需注意主机名一旦更改主从就会出错

relay-log-recover = 1 \#打开中继日志恢复模式，如果从库宕机导致当前中继日志损坏的话会放弃该日志并重新从master上获取

read\_only = 1 \#从库只读(该配置对root用户无效)，避免从库有写操作导致主从故障

super\_read\_only =1 \#配置root用户也为只读

master\_info\_repository = table

relay\_log\_info\_repository = table \#relay-info信息写入数据库表中，默认是file

slave\_parallel-type = LOGICAL\_CLOCK \#MySQL5.7引入的MTS并行复制机制，默认是database基于库进行并行复制，LOGICAL\_CLOCK可以实现“master如何并行执行的语句，slave就如何并行回放语句”，在减少从库延迟的同时解决了并行复制时语句执行顺序问题。

slave\_parallel\_workers = 8 \#从库并行复制的线程数，通常和CPU核数一致，可以一定程度上解决从库延迟问题

expire\_logs\_days = 10

max\_binlog\_size = 1024M

log\_error = error.log

innodb\_file\_per\_table=ON

skip\_name\_resolve=ON

\#\#\#进行级联主从时从库也要配置binlog

log\_slave\_updates = 1 \#从库也记录binlog日志

log\_bin = slave-bin

binlog\_format = row

· 从库使用主库上配置好的复制用户，并且指定好主库的position信息

stop slave;

CHANGE MASTER TO MASTER\_HOST='192.168.145.85',MASTER\_USER='repl',MASTER\_PASSWORD='123456',MASTER\_LOG\_FILE='master-bin.000004',MASTER\_LOG\_POS=0;

\#MASTER\_HOST：MASTER服务器的IP

\#MASTER\_USER：有slave权限的用户，就是GRANT所授权的用户

\#MASTER\_PASSWORD：从库用户的密码

\#MASTER\_LOG\_FILE：在主库上执行show master status语句可以查看日志名

\#MASTER\_LOG\_POS=333：这个位置决定了从库从哪个位置开始复制，实测写0也可以，从头复制

· 启动从库复制线程，需要为两个yes

mysql > start slave; \#启动从库

mysql > show slave status \\G \#查看从库状态

3、延迟从库配置

在需要配置为延迟从库的客户端上执行以下命令

mysql > stop slave;

mysql > change master to master\_delay = 3600; \#SQL线程延迟1小时，IO线程不影响

mysql > start slave;

mysql > show slave status \\G \#查看SQL\_Dely的值

四、主从状态查看

1、查看主库Position信息与从库状态

show master status \\G \#查看主库binlog position信息

show slave status \\G \#查看从库状态

2、从库状态关键指标

· Read\_Master\_Log\_Pos：从库读取到主库二进制日志到哪个位置

·Exec\_Master\_Log\_Pos：从库执行到主库二进制日志到哪个位置。如果和Read\_Master\_Log\_Pos一致代表数据是同步的

·Relay\_Log\_File：从库中继日志记录到的位置，正常情况下中继日志内容的和主库binlog是一样的

·Seconds\_behind\_master：从服务器比主服务器慢了多少秒，为0代表没有延迟

·Slave\_IO\_Running、Slave\_SQL\_Running：如果有一项为No都表示主从异常。IO Thread的作用是从Master端请求二进制日志并存放到Slave端的中继日志中；SQL Thread的作用是将中继日志里的事件导入到SQL语句中，下图是启动slave之前的状态：

![c737091a7218d6e56a02c63ebe948562.png][]

五、使用延迟从库恢复故障的思路

1、如果主库执行了错误的语句，比如drop database，现在需要使用延迟从库来恢复数据。首先就是在延迟从库上停止SQL线程，让它不再继续执行relay中的SQL

stop slave sql\_thread;

2、查看延迟从库relaylog位置点，这个作为恢复的起点，这里假设为482

show slave status \\G \#查看relay\_log\_file和relay\_log\_pos信息

3、查看延迟从库relaylog中的故障点，通常是在最后一个relaylog中，这里需要注意Position信息只需要看左边一列的，它才是relaylog中的位置，而relaylog中的End\_log\_pos是主库中的位置，不需要关注。这里假设DROP操作的起始点是1402

mysql > show relaylog events in 'relay-bin.000005';

4、生成用于恢复数据的SQL，需要注意恢复的数据不仅是data1，在drop database data1后其他库可能还有操作，这部分也是要恢复的

mysqlbinlog --start-position=482 --stop-position=1402 /data/mysqllog/relay-bin.00005 > /tmp/relay.sql

[4fa1559e93557761b4ed9ade0022f33a.png]: /images/20221119/ee9963b353cb45e3a539755bfe3a691e.png
[c737091a7218d6e56a02c63ebe948562.png]: /images/20221119/de654fb5d49242be8944905cbbf07e0e.png