前言

大家好，我是沐风晓月，本文收录于《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 intostudent 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>
  1. slave节点关闭slave同步

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

mysql> stop slave;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
  1. 在master插入数据测试是否还能够同步
  • 在mufeng41 master服务器中插入数据
mysql> insert intostudent 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

  1. 重新配置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，表示已经同步成功

  1. 在主服务器插入数据在从服务器查看
  • 主服务器插入数据
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 intostudent 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)

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

1、先停掉slave

mysql> stop slave;

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

mysql> set global sql_slave_skip_counter=1;

3、再启动slave

mysql> start slave;

4、查看同步状态

mysql> show slave status\G;

总结

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

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