MySQL-24：MySQL主从同步

Bertha 。 2022-12-28 01:51 143阅读 0赞

binlog是归档日志，可以用主从同步， 但它的内容是什么样的呢？为什么备库执行了 binlog 就可以跟主库保持一致了呢？

#### 24.MySQL主备的基本原理 ####

如图就是主备切换流程：

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

在状态1时，客户端访问节点A，节点B为备库，将节点A的binlog同步到节点B上执行。当需要切换的时候，就切换状态2。

备库建议设置为只读模式(readonly)，理由有三：

1.  根据readonly判断角色
2.  查询类的语句可以去备库上查询
3.  防止切换过程中出现双写问题

readonly对超级用户是无效的，负责主备库之间的同步线程就拥有超级权限。

**节点A同步到节点B的内部流程：**

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

主库A和备库B之间会维护一个长连接，流程如下：

1.  备库B通过 change master 命令来指定主库A的IP，端口，用户名，密码以及binlog同步的开始位置
2.  备库B执行start slave命令，会启动两个线程`io-thread`和`sql-thread`，`io-thread`负责与主库建立链接，`sql-thread`负责解析relay log。
3.  主库校验完用户名和密码，从指定的binlog发送给备库B，备库B写入本地文件，称为中转文件(relay log)。
4.  sql-thread解析relay log并执行。

#### 24.2binlog的三种格式 ####

binlog有三种格式：statement，row和mixed。

现在有表如下：

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

不过在这之前，我们需要做个设置：

1.  开启binlog，[开启binlog][binlog]

# 通过以下指令看查看是否开启
    show VARIABLES like '%log_bin%';
    # 如果没有开启，则在my.ini中进行配置
    # 启动MySQL的二进制文件，指定binlog文件路径，它会在data目录下生成binlog文件，也可以使用绝对路径
    log_bin = mysql_bin
    # 设置binlog的模式
    binlog_format="STATEMENT"  
    # binlog_format="ROW" 
    # binlog_format="MIXED"

1.  设置binlog的记录模式

# 参数binlog_format：表示binlog的内容格式
    # 通过以下语句查询参数设置
    show variables like '%binlog_format%'
    # 通过以下参数进行设置
    set global binlog_format='STATEMENT';

我们先来看下在statement模式下的binlog内容：

# 我们先执行一条修改语句
    update t set a = 10 where id =1;

[binlog查询指令][binlog 1]

# 用下面指令查看binlog的总文件 因为binlog值追加写的，所以它必定写在号数最大的binlog文件下。
    show BINARY logs ;
    # 用下面指令来查看binlog
    show binlog EVENTS in 'mysql_bin.000012';

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

我们来看下上面binlog的日志内容，从Pos154~514，共4行：

1.  第一行：SET @@SESSION.GTID\_NEXT= 'ANONYMOUS’先过
2.  第二行：begin对应第四行的commit，表示这是一个事务
3.  第三行：有着use 库名，后面跟着我们执行的语句，真实记录
4.  第四行：commit后面跟着一个xid，这个xid是用来关联binlog和redolog，用来确认binlog是否已经写入，存在则提交，不存在则回滚。

为了看出statement和row的区别，我们在执行 如下语句：

delete from t where a = 10 limit 1;

我们就可以发现，MySQL报了一个警告，如果我们用了limit，binlog\_format = statement的话，这是不安全的：

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

现在我们将binlog\_format = row，情况如下所示，

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

和statement模式下的binlog记录进行对比，发现前后是一样的，中间记录的sql语句换成了event：Table\_map和Update\_rows。

1.  Table\_map：表示操作的为blog库的表t
2.  Update\_rows：表示update操作行为，如果是delete，那它就是Delete\_rows

不过从上面看不出什么，我们需要借助mysqlbinlog工具，[mysqlbinlog工具的使用][mysqlbinlog]，查看的结果如下：

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

从上面的结果，我们可以发现几个信息：

*  server id 1：表示这个事务是在 server\_id=1 的这个库上执行的。
 *  Table\_map：依旧还是显示打开的库和表
 *  下面有记录着哪个字段从什么数值改变位什么数值
 *  最后的Xid=32表示已提交的事务

你可以看到，当 binlog\_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，，不会有主备删除不同行的问题。

#### 24.3 binlog的mixed ####

statement格式因为会出现主备不一致的问题，row格式记录比较占空间，同步的时候浪费IO资源，所以出现了mixed格式，MySQL根据自己的判断来决定已什么格式写入binlog。

也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

\*\*恢复数据：\*\*因为row模式的binlog记录的内容详细，无论是执行insert，update，detele语句，我们都能很容易地恢复。

MariaDB 的Flashback工具就是基于上面介绍的原理来回滚数据的。

#### 24.4 循环复制问题 ####

一开始的图模式为M-S模式，下面的M-M模式，两个服务器互为主备库。

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

不过，有一个问题，如果业务逻辑在节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog， 然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了 ，怎么解决？

满足两点即可，每台服务器的binlog必须有自己的id；备库在执行binlog前需要对id进行验证，查看是不是自己的，具体如下：

1.  规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2.  一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3.  每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/bfd8cd604c12457c80e9a18246f7fe4e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/d6010100d3b243fd846629f123783a85.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201214001120798.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[binlog]: https://blog.csdn.net/weixin_40896800/article/details/82143078
[binlog 1]: https://www.cnblogs.com/kiko2014551511/p/11649336.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201214001132173.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201214001143834.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/20201214001157223.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[mysqlbinlog]: https://www.cnblogs.com/softidea/p/12624778.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 6]: https://img-blog.csdnimg.cn/2020121400122018.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 7]: https://img-blog.csdnimg.cn/20201214001244635.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1,size_16,color_FFFFFF,t_70#pic_center