MySQL主从复制架构实践：主从不同步的解决方案

前言

大家好，我是沐风晓月，本文收录于《MySQL入门到精通》专栏，希望对你有用；

之前在做MySQL主从架构的时候，遇到了形形色色的问题，比如：

• 主从配置好之后，双yes的情况下，居然不能同步；
• 切换主从之后，数据丢失了；
• 主从只更新了一条数据，从库却出来多条数据；
• 模拟从宕机，修复后，无法同步停机时间内，主服务器更新的数据；
  

  文章目录

  • 前言
  • 一. MySQL主从架构思想
  • • 1.1 什么是MySQL主从复制
    • 1.2 MySQL主从同步的作用
    • 1.3 MySQL主从复制的形式
    • 1.4 MySQL主从复制模式
  • 二. 主从不同步的解决方案
  • 总结

一. MySQL主从架构思想

1.1 什么是MySQL主从复制

主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。

主从同步的原理：

默认情况下，MySQL 采用异步复制的方式，执行事务操作的线程不会等复制 Binlog 的线程.

MySQL 主库在收到客户端提交事务的请求之后，会先写入 Binlog，然后再提交事务，更新存储引擎中的数据，事务提交完成后，给客户端返回操作成功的响应。

同时，从库会有一个专门的复制线程，从主库接收 Binlog，然后把 Binlog 写到一个中继日志里面，再给主库返回复制成功的响应。

从库还有另外一个回放 Binlog 的线程，去读中继日志，然后回放 Binlog 更新存储引擎中的数据，提交事务和复制这两个流程在不同的线程中执行，互相不会等待，这是异步复制。

需要注意的是：异步复制它没有办法保证数据能第一时间复制到从库上。

与异步复制相对的就是同步复制。同步复制和异步复制唯一的区别是，什么时候给客户端返回响应。

异步复制时，主库提交事务之后，就会给客户端返回响应；而同步复制时，主库在提交事务的时候，会等待数据复制到所有从库之后，再给客户端返回响应。

同步复制这种方式在实际项目中，基本上没法用，原因有两个：

• 一是性能很差，因为要复制到所有节点才返回响应；
• 二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

为了解决这个问题，MySQL 从 5.7 版本开始，增加一种半同步复制（Semisynchronous Replication）的方式。

异步复制是，事务线程完全不等复制响应；同步复制是，事务线程要等待所有的复制响应；半同步复制介于二者之间，事务线程不用等着所有的复制成功响应，只要一部分复制响应回来之后，就可以给客户端返回了。

1.2 MySQL主从同步的作用

• 一份数据被放在了多个数据库中，其中一个是master，其余的是slave从库。 当主库进行更新的时候，会自动将数据复制到从库中，而我们在客户端读取数据的时候，会从从库中进行读取，也就是采用读写分离的方式，分担了服务器的压力
• 数据备份。我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。
• 具有高可用性。数据备份实际上是一种冗余的机制，通过这种冗余的方式可以换取数据库的高可用性，也就是当服务器出现故障或宕机的情况下，可以切换到从服务器上，保证服务的正常运行。

1.3 MySQL主从复制的形式

• 一主一从

• 一主多从

• 双主

• 多主一从

• 级联复制

当架构中slave节点较多时，master节点就会损耗一部分性能用于replication，我们可以将少量slave节点连接到master节点，其他slave节点连接到二级节点，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

1.4 MySQL主从复制模式

• STATEMENT模式（SBR）
  记录每一条SQL修改：
  每一条会修改数据的SQL语句会记录到binlog中。优点是并需要记录每一条SQL语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
  缺点是在某些情况下会导致master-slave中的数据不一致（如sleep()函数，last_insert_id()，以及user-defined functions(udf)等会出现问题）。
• ROW模式（RBR）
  仅记录修改的内容，不记录具体的SQL：
  不记录每条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样子。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法正确复制的问题。
  缺点是会产生大量的日志，尤其是altertable的时候会让日志暴涨。
• MIXED模式（MBR）
  以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

在 MySQL 中，无论是复制还是备份恢复，依赖的都是全量备份和 Binlog，全量备份相当于备份那一时刻的一个数据快照，Binlog 则记录了每次数据更新的变化，也就是操作日志。

主从同步，也是数据复制，全量快照+增量操作日志的备份恢复和数据复制，是几乎所有存储系统使用的方案。

二. 主从不同步的解决方案

在模拟主从不同步之前，要提前安装好MySQL主从同步，可以参考沐风晓月的上篇文章：提高MySQL数据可靠性的必备技能：基于MySQL8实现主从同步。

接下来我们来模拟从库宕机的情况，然后将其恢复。

1. 插入数据测试

• 在master中插入数据

mysql> use aaDatabase changedmysql> show tables;Empty set (0.00 sec)mysql> create table student(name varchar(20),age int(11));Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> insert into  student values("mufeng",19);Query OK, 1 row affected (0.01 sec)mysql> select * from student;+--------+------+| name   | age  |+--------+------+| mufeng |   19 |+--------+------+1 row in set (0.00 sec)

• 在从数据库中查看

mysql> use aaReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from student;+--------+------+| name   | age  |+--------+------+| mufeng |   19 |+--------+------+1 row in set (0.00 sec)mysql> 

2. slave节点关闭slave同步

在mufeng42服务器中关闭从服务器：

mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> 

3. 在master插入数据测试是否还能够同步

• 在mufeng41 master服务器中插入数据

mysql> insert into  student values("mufeng1",20);Query OK, 1 row affected (0.00 sec)mysql> select * from student;+---------+------+| name    | age  |+---------+------+| mufeng  |   19 || mufeng1 |   20 |+---------+------+2 rows in set (0.00 sec)mysql> 

• 在mufeng42 从服务器中测试：

mysql> use aaDatabase changedmysql> select * from student;+--------+------+| name   | age  |+--------+------+| mufeng |   19 |+--------+------+1 row in set (0.00 sec)mysql> 

此时我们发现无法同步，如果想继续同步怎么办？

需要重新配置下同步，先设置master

4. 重新配置MySQL主从同步

• master节点查看状态
  这一步之前最好先重启下MySQL服务

mysql> show master status;+---------------+----------+--------------+------------------+-------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------+| binlog.000003 |      157 |              |                  |                   |+---------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

• slave 节点重新设置同步

这里我们需要在mufeng42从服务器上来设置：

mysql> change master to master_host='192.168.1.41',master_user='slave21',master_password='Root!2#mufeng',master_log_file='binlog.000003',master_log_pos=157;Query OK, 0 rows affected, 8 warnings (0.01 sec)## 最好重启一下MySQLd服务mysql> start slave ;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show slave status \G 

发现两个都为yes，表示已经同步成功

5. 在主服务器插入数据在从服务器查看

• 主服务器插入数据

mysql> use aaReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> insert into  student values("mufeng3",23);Query OK, 1 row affected (0.00 sec)

• 从服务器查看

mysql> select * from student;+---------+------+| name    | age  |+---------+------+| mufeng  |   19 || mufeng2 |   21 || mufeng3 |   23 |+---------+------+3 rows in set (0.00 sec)

可以看到，主从数据库又重新同步了， 有时候主库的数据无法同步过来，可以跳过错误：

先停掉slave

mysql> stop slave;

跳过错误步数，后面步数可变

mysql> set global sql_slave_skip_counter=1;

再启动slave

mysql> start slave;

查看同步状态

mysql> show slave status\G;

总结

本文的理论部分参考了众多网上的博客和权威文献，无法一 一列出，若侵权，可以联系我删除。

💕 好啦，这就是今天要分享给大家的全部内容了，我们下期再见！
💕 本文由沐风晓月原创，首发于CSDN博客， 博客主页：mufeng.blog.csdn.net
💕 每一次学习都很枯燥，单调，孤独，甚至看不到未来，每一次遇到问题都让人疑惑，焦虑，怀疑，甚至想要放弃。 但坚定的走下来，会收获很多。收获的不单单是技术的成长还有一颗强大的心。
💕 喜欢的话记得点赞收藏哦

来源地址：https://blog.csdn.net/wisdom_futrue/article/details/129578338