MySQL主从复制延时

目录

主从复制原理

主从复制延时问题

I/O线程存在延迟

SQL线程存在延迟

解决主从复制延时的方案

网络优化

使用高速网络连接

优化网络带宽

减少跨数据中心的复制

事务优化

将大事务拆分成多个小事务

减少并发事务的数量

使用InnoDB存储引擎

数据库性能优化

提升硬件性能

调整服务器负载

使用缓存技术

MySQL配置优化

调整主从服务器时间

调整binlog缓存大小

调整复制线程数

使用半同步复制

---

主从复制原理

MySQL主从复制是MySQL数据库中一种数据备份和数据同步的方式。它将一台MySQL服务器（主服务器）的数据同步到另外一台MySQL服务器（从服务器）。主服务器是数据的提供者，而从服务器是数据的接收者。主从复制可以用于数据备份和读写分离等场景。

MySQL主从复制的原理如下：

• 主服务器将更新的日志写入二进制日志文件（binary log）。
• 从服务器连接主服务器，通过读取主服务器的二进制日志文件并解析其中的更新记录，将这些记录应用到自己的数据库中，从而实现数据的同步。
• 从服务器会定期向主服务器发送心跳包，以确保主服务器的可用性。当主服务器宕机或网络不通时，从服务器会自动切换为主服务器，继续提供服务。

• 

详细的主从同步主要有三种形式（binlog格式）：statement、row、mixed

• 语句复制（Statement-based replication，SBR）: 会将对数据库操作的sql语句写道binlog中
• 行复制（Row-based replication，RBR）: 会将每一条数据的变化写道binlog中。
• 混合复制（Mixed-based replication，MBR）: statement与row的混合。Mysql决定何时写statement格式的binlog, 何时写row格式的binlog。

在 MySQL 中，可以通过配置参数 binlog_format 来选择使用的复制格式，包括 STATEMENT、ROW 和 MIXED。

主从复制延时问题

I/O线程存在延迟

• 网络延迟
• 磁盘I/O存在瓶颈
• 网卡存在问题

SQL线程存在延迟

• 高并发：主库写入量过大，sql线程单线程重放
• 大事务：如果从库执行的二进制日志中包含一个较长时间的事务，而且该事务尚未完成，则SQL线程会一直等待这个事务完成。这会导致SQL线程在处理其他的更新操作时被阻塞，表现为卡顿。

解决主从复制延时的方案

• 网络优化
• 事务优化
• 数据库性能优化
• MySQL配置优化

网络优化

使用高速网络连接

尽可能使用高速网络连接，例如千兆以太网连接或更高速的连接，以确保快速传输数据。

优化网络带宽

通过调整网络带宽分配，可以确保主从数据库之间的网络连接稳定并且带宽充足。

减少跨数据中心的复制

如果数据中心之间的网络延迟比较大，则可以考虑将主从数据库部署在同一数据中心或就近的数据中心。

事务优化

将大事务拆分成多个小事务

将大事务拆分成多个小事务，可以减少主从复制延迟，同时减少锁定资源的时间。

减少并发事务的数量

减少并发事务的数量，可以避免过多的锁定资源和占用资源，从而减少主从复制延迟。

使用InnoDB存储引擎

在使用InnoDB存储引擎时，可以通过设置事务隔离级别，来避免锁定资源的时间。

数据库性能优化

提升硬件性能

通过升级硬件配置，例如增加内存、硬盘容量等，可以提升从数据库性能。

调整服务器负载

通过调整服务器负载，例如减少并发连接数、优化查询语句等，可以减轻从数据库的负载，从而提升性能。

使用缓存技术

在从数据库中使用缓存技术，例如Memcached或Redis等，可以减少从数据库的查询次数，从而提升性能。

MySQL配置优化

调整主从服务器时间

如果主从服务器之间的时间存在偏差，则可以通过调整时间来减少主从复制延迟。可以使用ntpdate命令来同步时间。

修改binlog格式

在MySQL中，有三种binlog格式：STATEMENT、ROW和MIXED。可以通过修改binlog格式来减少主从复制延迟。

STATEMENT格式适用于执行简单的SQL语句，但可能会出现一些不一致的情况。ROW格式记录了每一行数据的修改，但是可能会产生大量的日志，增加复制的负担。MIXED格式将根据具体情况自动选择STATEMENT或ROW格式。

如果使用的是STATEMENT格式，则可以考虑切换到ROW格式，以减少主从复制延迟。如果使用的是ROW格式，则可以考虑切换到MIXED格式，以避免大量的日志。

调整binlog缓存大小

binlog缓存大小是指MySQL服务器用来缓存二进制日志的内存大小。如果binlog缓存大小过小，则可能会导致主从复制延迟。可以通过修改my.cnf文件来调整binlog缓存大小。

调整复制线程数

slave_parallel_workers=n（开启多线程复制）

复制线程是指用于复制二进制日志的MySQL线程。如果复制线程数量过少，则可能会导致主从复制延迟。可以通过修改my.cnf文件来调整复制线程数。

使用半同步复制

半同步复制是一种MySQL复制模式，它可以提高主从复制的可靠性，并减少主从复制延迟。在半同步复制模式下，当从服务器收到主服务器发送的日志时，它会向主服务器发送一个确认消息，以确保数据已经正确地复制到从服务器上。

可以通过在my.cnf文件中启用半同步复制来减少主从复制延迟。