乐趣区

关于mysql:MySQL-主从复制解决了什么问题出现同步延迟如何解决

jiezi

主从复制解决的问题

  • 数据分布：通过复制将数据分布到不同地理位置
  • 负载平衡：读写拆散以及将读负载到多台从库
  • 备份：可作为实时备份
  • 高可用性：利用主主复制实现高可用

复制原理

复制的原理其实很简略，仅分为以下三步：

  • 在主库上把数据更改记录到二进制日志 binary log 中，具体是在每次筹备提交事务实现数据更新前，主库将数据更新的事件记录到二进制日志中去，Mysql 会依照事务提交的程序来记录二进制日志的。日志记录好之后，主库告诉存储引擎提交事务。
  • 从库会启动一个 IO 线程，该线程会连贯到主库。而主库上的 binlog dump 线程会去读取主库本地的 binlog 日志文件中的更新事件。发往从库，从库接管到日志之后会将其记录到本地的中继日志 relay-log 当中。
  • 从库中的 SQL 线程读取中继日志 relay-log 中的事件，将其重放到从库中。（在 5.6 版本之前 SQL 线程是单线程的，使得主从之间提早更大）

两种复制形式

日志文件中记录的到底是什么呢？mysql 反对了两种日志格局，这两种日志格局也体现了各自的复制形式

基于语句复制

基于语句的复制相当于逻辑复制，即二进制日志记录了操作的语句，通过这些语句在从库进行重放来实现复制。

这种形式简略，二进制日志占用空间少，使得带宽小传输效率较高。然而基于语句的更新依赖于其余因素，比方插入数据时利用工夫戳函数调用以后工夫作为工夫值也会呈现问题，因为因为主从之间的提早导致工夫值不统一。存储过程和触发器也可能呈现问题。

所以在开发当中咱们应该将逻辑尽量放在代码层，而不应放到 mysql 中，不易扩大。

基于行复制

基于行的复制相当于物理复制，即二进制日志记录了理论更新数据的每一行。这样导致行复制的压力比拟大，因为日志占用空间较大，传输占用带宽也较高。然而比基于语句复制更加准确，能够屏蔽一些因为主库从库之间的差别导致的不统一。如方才提到的工夫戳函数。

二者比照：
  • 语句复制
  • 传输效率高，缩小提早。
  • 在从库更新不存在的记录时，语句赋值不会失败。而行复制会导致失败，从而更早发现主从之间的不统一。
  • 设表里有一百万条数据，一条 sql 更新了所有表，基于语句的复制仅须要发送一条 sql，而基于行的复制须要发送一百万条更新记录
  • 行复制
  • 不须要执行查问打算。
  • 不晓得执行的到底是什么语句。

例如一条更新用户总积分的语句，须要统计用户的所有积分再写入用户表。如果是基于语句复制的话，从库须要再一次统计用户的积分，而基于行复制就间接更新记录，无需再统计用户积分。

因为两种形式各有优缺点，所以 mysql 在这两种复制模式进行动静的切换。默认是语句。

配置要点 

# 如果在双主复制构造中没有设置 ID 的话就会导致循环同步问题
server_id=1

# 即日志中记录的是语句还是行更新或者是混合
binlog_format=mixed

# 在进行 n 次事务提交当前，Mysql 将执行一次 fsync 的磁盘同步指令。将缓冲区数据刷新到磁盘。# 为 0 的话由 Mysql 本人管制频率。sync_binlog=n

# 为 0 的话，log buffer 将每秒一次地写入 log file 中并且刷新到磁盘。# mysqld 过程解体会失落一秒内的所有事务。# 为 1 的话，每次事务 log buffer 会写入 log file 并刷新到磁盘。（较为平安）# 在解体的时候, 仅会失落一个事务。# 为 2 的话，每次事务 log buffer 会写入 log file，但一秒一次刷新到磁盘
innodb_flush_logs_at_trx_commit=0


# 阻止从库解体后主动启动复制，给一些工夫来修复可能的问题，# 解体后再主动复制可能会导致更多的问题。并且自身就是不统一的
skip_slave_start=1 


# 是否将从库同步的事件也记录到从库本身的 bin-log 中
# 容许备库将重放的事件也记录到本身的二进制日志中去，能够将备库当做另外一台主库的从库
log_slave_update 

# 日志过期删除工夫，提早重大的话会导致日志文件占用磁盘
expire_logs_days=7

innodb_flush_logs_at_trx_commit 的三个参数很容易弄混。以下是具体的解析：

mysql 先将日志写到 log buffer 缓冲区当中，再将 log buffer 缓冲区的数据写到 log file 日志文件中，此时写入的是内存中的 log file，最终仍需操作系统将内存中的数据刷写到磁盘上。参数 0：mysql 每秒都会将 log buffer 的数据写入到 log file 中并且刷新到磁盘。意味着 mysql 解体的时候将会失落一秒内的所有事务。参数 1：每次事务提交都会将 log buffer 写入到 log file 并刷新到磁盘。意味着在 mysql 解体的时候, 仅会失落一个事务。参数 2：每次事务提交都会将 log buffer 写入到 log file 但不同时写入到磁盘，由 mysql 自行管制每秒将 log file 刷写到磁盘上，当 mysql 解体的时候操作系统没解体的时候，log_file 中仅会失落一个事务，操作系统仍会将 log file 刷写到磁盘，而如果操作系统也解体或断电的话，则会失落一秒内的事务。

举荐应用：

innodb_flush_logs_at_trx_commit=2
sync_binlog=500

性能会较快 

innodb_flush_logs_at_trx_commit=1
sync_binlog=1

较为平安

提早问题

提早的产生

当主库的 TPS 并发较高时，因为主库下面是多线程写入的，而从库的 SQL 线程是单线程的，导致从库 SQL 可能会跟不上主库的处理速度（生产者比消费者快，导致商品沉积）。

提早的解决
  • 网络方面：将从库散布在雷同局域网内或网络提早较小的环境中。
  • 硬件方面：从库配置更好的硬件，晋升随机写的性能。
  • 配置方面：

从库配置 

sync_binlog=0
innodb_flush_log_at_trx_commit=2
logs-slave-updates=0
增大 innodb_buffer_pool_size

让更多操作在 Mysql 内存中实现，缩小磁盘操作。或者降级 Mysql5.7 版本应用并行复制。

  • 架构方面：比方在事务当中尽量对主库读写，其余非事务中的读在从库。打消一部分提早带来的数据库不统一。减少缓存升高一些从库的负载。

笔者集体心得，如有谬误恳请网友评论斧正。

起源：juejin.cn/post/6844903968259178504

退出移动版