MySQL 技术内幕——主从同步原理

如何避免 MySQL 单点故障？

• 利用MySQL的主从复制来解决MySQL的单点问题；

MySQL 主从同步，Master 必须启用 binlog，即二进制日志，binlog 记录了所有写操作的 SQL 语句，不记录任读操作。如果不开启 binlog 功能，则无法实现主从同步。

binlog 存在三种日志格式：

• Statement：binlog 中存储 SQL 语句，存储日志量是最小的；
• Row：存储 event 数据，存储日志量大，但是不能很直接的进行读取；
• Mixed：介于 Row 和 Statement 之间，对于不确定的操作使用 Row 记录，如果每天数据操作量很大，产生的日志比较多，可以考虑选择使用 Mixed 格式。

1.复制功能

复制功能解决来什么问题？

• 实现了在不同服务器上的数据分布；
  • MySQL 的复制是利用二进制日志的增量进行的，不需要太多的带宽；
  • 如果是使用基于行的复制在进行大批量的更改时，会对带宽带来一定的压力；
• 实现数据读取的负载均衡；
  • 需要其他组件配合完成，例如利用DNS轮询的方式把程序的读连接到不同的备份数据库；
• 增加了数据的安全性；
  • 利用备库的备份来减少主库负载；
  • 复制并不能代替备份；
  • 备份的另一个作用是方便进行数据库高可用架构的部署，避免 MySQL 单点失败；
• 实现数据库高可用和故障切换；
• 实现数据库在线升级。

MySQL 复制功能提供分担读负载；

我们可以使用复制功能对数据库服务器进行水平扩展，为数据库增加一个或多个备库，用于分担主数据库的读负载，复制功能也为高可用、灾难恢复、备份提供来更多的选择。

MySQL 的复制是基于主库上的二进制日志，然后在备库上存放这些日志的方式来完成的。所以 MySQL 的复制是异步的。这也表明同一时间点，备库上的数据可能与主库存在不一致的问题。且无法保证主备之间的延迟。

附一：MySQL二进制日志

MySQL 复制功能根据主库二进制数据格式可以分为：

复制	优点	缺点
基于SQL语句的复制	1、生成的日志量少，节约网络传输IO； 2、并不强制要求主从数据库的表定义完全相同； 3、相比于基于行的复制方式更为灵活；	1、对于非确定性事件，无法保证主从复制数据的一致性； 2、对于存储过程、触发器、自定义函数进行的修改也能造成数据不一致； 3、相比于基于行的复制方式在从库上执行时需要更多的行锁；
基于行的复制	1、可以应用于任何SQL的复制，包括非确定性函数、存储过程等； 2、可以减少从库上锁的使用；	1、要求主从数据库的表结构相同，否则可能会中断复制； 2、无法在从库上单独执行触发器；

综合考虑，建议大家使用基于行的复制，这种复制对主从数据库的一致性更加有保证。

MySQL 复制最容易出现性能的问题就是主从延迟的问题。影响主从延迟的因素有：

因素	解决方案
主库写入二进制日志的时间	控制主库的事务大小，分割大事务
二进制日志传输时间 (主->从)	可以使用MiXED日志格式
默认情况下从库只有一个SQL线程，主库上并发的修改在从库上变成了串行，最常见的因素	使用多线程复制 (MySQL 5.6及以上) ，在MySQL 5.7中可以按照逻辑时钟的方式来分配SQL线程

1.主从同步过程

主从同步过程是异步的：

1. Master 创建一个 I/O 线程，Slave 创建一个 I/O 线程和一个 SQL 线程；
2. Slave上的 I/O 线程连接上 Master，并告诉 Master 日志读取偏移位；
3. Master I/O 线程检查该值是否小于当前二进制日志偏移位，如果小于则读取偏移位之后的数据传给 Slave 的 I/O 线程；
4. Slave I/O 线程将读取到的数据依次写入到 relay log (中继日志) 中，并记录当前偏移位 (下次使用)；
5. Slave 的 SQL 线程发现 relay log 中新增了内容，则立即解析日志新增的内容，并依次执行，Slave 和 Master 执行的都是相同的 SQL，保证了数据的一致性；
6. 完成上次同步后，Slave I/O 线程会不断的向 Master I/O 线程要 binlog 信息。

注意：

1. 主从复制是异步的 SQL 语句级的复制；
2. 复制时，Master 有一个 I/O 线程，Slave 有两个线程，I/O 和 SQL 线程；
3. 作为复制的所有 MySQL 节点的 server-id 都不能相同；
4. MySQL 可以对整个 MySQL 实例进行同步，也可以对实例中的某个库或是某个表进行同步。

1、主从同步的延迟主要体现在哪里？

只有在 Master 上完成了写操作，Master 才会把日志记录到 binlog 中，写入 binlog 的过程是一个串行的过程，同样 Slave 解析执行 relay log 新增的内容，也是串行的过程。在高并发环境时，写操作是并行的，但是到了 Slave 成了串行的了。这是主从延迟主要的方面。

2、有没有没有主从同步延迟的方案呢？

使用主从同步机制多少有些延迟，可能是纳秒或者微秒级。

MySQL 提供了多线程复制，需要手动配置开启，多线程复制可以减轻主从同步的延迟，MySQL 5.7 的多线程复制是基于 table 的。

如果非要找一种无延迟的架构，可以考虑同时多写的架构。

3、主从同步，Master 宕机后，怎么办？

主从架构无法自动故障转移，可以将其升级为 MHA 架构自动实现故障转移。

Master 宕机后，会进行选举，选举出新的 Master。

2.主从同步监控

如何监控主从复制链路的状态？

对于主从复制的监控，基本都要依赖于 show slave status 命令。

如何监控主从复制延迟？

参与复制的主从服务器之间一定存在着一些延迟，正常的情况下延迟是非常小的，基本在 1 秒之内。所以对于应用来说，影响并不大，特别是对于一些主从延迟不敏感的应用。如果由于某种原因，主从服务器之间出现了很大的延迟，就会影响到应用的正常使用了。所以必须要对主从复制延迟进行一些监控。

如果发现从服务器之间的延迟持续的增大，那么就需要进行一些检查，查找原因并及时解决。一般情况下，可以通过下面的方法对主从复制延迟进行监控：

利用 show slave status 命令返回的信息：

Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0

Seconds_Behind_Master 就是主从复制延迟的秒数。这种获取方法比较简单，但是结果并不准确，因为这个值是根据同步到从服务器上主服务器的 binlog 和已经在从服务器上重新执行过的 binlog 日志之间的时间差，所以会有很多种情况会导致数据不准确，例如当网络出现问题的时候，主服务器上还有大量的 binlog 没有同步到从服务器上，同时已经同步到从服务器上的 binlog 都已经被完全被重用完了，这种情况下，主从之间是存在着很大的延迟的，但是通过 show slave status 命令却不能发现这种延迟。

所以为了更加准确的发现延迟，就需要另外的方法：

这个方法需要使用多线程的程序同时对于主从服务器的状态来进行检查。主服务器上执行 show master status 命令来获取主服务器上的二进制日志文件信息和偏移量：

mysql> show master status \G
***************************** 1. row *****************************
File: mysql-bin.001099
Position: 302055050

从服务器上执行 show slave status 命令获取主服务器发送过来的二进制日志文件信息和偏移量：

Master_Log_File: mysql-bin.001099
Read_Master_Log_Pos: 301855050

以及在从服务器上执行 show slave status 命令获取已经传输完成的主服务器上二进制文件的信息和偏移量：

Exec_Master_Log_Pos: 301855050
Relay_Log_Space: 301855350

通过上面三个信息的对比，就可以知道主从服务器之间是否存在大量的延迟了。如果文件名（File 和 Master_Log_File）和偏移量（Position 和 Read_Master_Log_Pos）都一样，说明当前的主从不存在任何延迟的。

当每次修复完主从复制的时候，都要检查主从复制数据的一致性。那么如何验证主从复制的数据是否一致？

这里就需要使用到 Percona 公司发布的 MySQL 工具集中的 pt-table-checksum：

pt-table-checksum u=db_user,p='db_password' \
	--databases mysql \
	--replicate test.checksums

–databases 参数指定的是数据库的名字，–replicate 参数指定的是要在 test 库下创建 checksums 这张表，并且将数据写入到这张检测表中。需要注意的是，这条命令只需要在主库上运行就可以了，它会自动发现主库下所有从库信息，并对所有从库指定的数据库的数据来进行检测。

建立数据库账户可以使用：

GRANT SELECT,PROCESS,SUPER,REPLICATION SLAVE ON *.* TO 'db_user'@'ip' IDENTIFIED BY 'db_password';