文章目录
MySQL 主从复制用于多个数据库服务器之间的数据同步，它可以提供高可用性、提高数据库整体性能和吞吐量，以及可以进行数据备份和数据库恢复。MySQL 主从复制是通过 binlog 实现的，主服务写入操作会同时添加到 binlog 中，而从数据库定期拉取主数据库的 binlog，然后将拉取的数据存放到自己的 relaylog 中，之后再由单独 SQL 线程将数据写入到从数据库中，完成数据的同步
基本过程
MySQL一主一从的复制过程如下图所示
复制模式
同步复制
同步复制：在事务提交之前，必须等待从服务器确认已经接收到更新。这种方式能够保证数据的一致性，但是会增加主服务器的延迟。
异步复制
异步复制：MySQL 主从复制中最常见和默认的模式。在异步复制模式中，主服务器将数据修改操作记录到binlog中，并将日志传输给从服务器。从服务器接收到binlog后，会异步地应用这些日志进行数据复制。
优点：它的优点是及时响应给使用者，主服务器不会受到从服务器的影响而等待确认，可以提高主服务器的性能。
缺点：由于是异步复制，可能存在数据传输的延迟，且从服务器上的复制过程是不可靠的。如果主服务器故障，尚未应用到从服务器的数据可能会丢失。
半同步复制
半同步复制：半同步复制是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用了这些日志后才继续执行后续操作，这种方式提供了一定程度的数据一致性保证，同时减少了主服务器的延迟。从 MySQL 5.5 开始，MySQL 以插件的形式支持 semi-sync 半同步复制。
优点：可以提供更高的数据一致性和可靠性，确保至少一个从服务器与主服务器保持同步。如果主服务器故障，已经确认接收并应用到从服务器的数据不会丢失。
缺点：由于半同步复制需要等待从服务器的确认，因此相对于异步复制，会增加一定的延迟，可能会影响主服务器的性能。
如果对数据一致性和可靠性要求较高，可以考虑使用半同步复制；如果对延迟和主服务器性能要求较高，可以继续使用异步复制，根据实际需求调整复制模式。
需要注意的是：
- 主库和从库都要启用半同步复制才会进行半同步复制功能，否则主库会还原为默认的异步复制。
- 如果在等待过程中，等待时间已经超过了配置的超时时间，没有收到任何一个从库的 ACK，那么此时主库会自动转换为异步复制。当至少一个半同步从节点赶上来时，主库便会自动转换为半同步复制。
半同步复制的潜在问题
在传统的半同步复制中（MySQL 5.5 引入），主库写数据到 binlog，并且执行 commit 提交事务后，会一直等待一个从库的 ACK，即从库写入 Relay Log 后，并将数据落盘，再返回给主库 ACK，主库收到这个 ACK 以后，才能给客户端返回事务完成的确认。
这样会出现一个问题，就是实际上主库已经将该事务 commit 到了存储引擎层，应用已经可以看到数据发生了变化，只是在等待返回而已。如果此时主库宕机，可能从库还没写入 Relay Log，就会发生主从库数据不一致。
为了解决上述问题，MySQL 5.7 引入了增强半同步复制。针对上面这个图，“Waiting Slave dump” 被调整到了 “Storage Commit” 之前，即主库写数据到 binlog 后，就开始等待从库的应答 ACK，直到至少一个从库写入 Relay Log 后，并将数据落盘，然后返回给主库 ACK，通知主库可以执行 commit 操作，然后主库再将事务提交到事务引擎层，应用此时才可以看到数据发生了变化。
rpl_semi_sync_master_wait_point 参数
MySQL仍然支持之前的半同步方案，并在 5.7.2 版本引入了一个新的参数 rpl_semi_sync_master_wait_point 进行控制。这个参数有两种取值：参考https://dev.mysql.com/doc/refman/8.4/en/replication-options-source.html#sysvar_rpl_semi_sync_master_wait_point
- AFTER_SYNC：这个是新的半同步方案，Waiting Slave dump 在 Storage Commit 之前。
- AFTER_COMMIT：这个是老的半同步方案。
在 MySQL 5.5 – 5.6 使用 after_commit 的模式下，客户端事务在存储引擎层提交后，在主库等待从库确认的过程中，主库宕机了。此时，结果虽然没有返回给当前客户端，但事务已经提交了，其他客户端会读取到该已提交的事务。如果从库没有接收到该事务或者未写入 relay log，同时主库宕机了，之后切换到备库，那么之前读到的事务就不见了，出现了幻读，也就是数据丢失了。
MySQL 5.7 默认值则是 after_sync，主库将每个事务写入 binlog，传给从库并刷新到磁盘 (relay log)。主库等到从库返回 ack 之后，再提交事务并且返回 commit OK 结果给客户端。 即使主库宕机，所有在主库上已经提交的事务都能保证已经同步到从库的 relay log 中，解决了 after_commit 模式带来的幻读和数据丢失问题，故障切换时数据一致性将得到提升。因为从库没有写入成功的话主库也不会提交事务。并且在 commit 之前等待从库 ACK，还可以堆积事务，有利于 group commit 组提交，有利于提升性能。
但这样也会有个问题，假设主库在存储引擎提交之前挂了，那么很明显这个事务是不成功的，但由于对应的 Binlog 已经做了 Sync 操作，从库已经收到了这些 Binlog，并且执行成功，相当于在从库上多了数据（从库上有该数据而主库没有），也算是有问题的，但多了数据一般不算严重的问题。它能保证的是不丢数据，多了数据总比丢数据要好。
数据复制方式
根据复制的数据类型不同，MySQL 提供了两种主要的数据复制方式：
- 基于行的复制（Row-based Replication, RBR）：记录每行数据的变化，并将这些变化发送到从服务器。这种方式的优点是它能很好地处理事务性操作，并且容易理解和调试。
- 基于语句的复制（Statement-based Replication, SBR）：记录执行的 SQL 语句，并将这些语句发送到从服务器。这种方式的优点是通常需要较少的二进制日志空间，但它可能在某些情况下产生不同的结果。
- 混合模式复制（Mixed-mode Replication）：MySQL 默认使用混合模式，它根据特定情况选择基于行或基于语句的复制。
上面的3种模式对应了binlog的3种格式，不同的日志格式决定了不同的主从同步效果：
- STATEMENT ：出现在 MySQL 5.1 之前，在这种格式下，binlog 记录的是执行的 SQL 语句的文本。
优点：日志文件通常较小，复制效率较高。
缺点：在某些情况下，由于数据库环境如表结构、字符集等的差异，在从服务器上重放这些 SQL 语句可能会导致不一致的结果。例如，获取当前时间的函数或存储过程等，可能会导致数据不一致。 - ROW 格式
行模式，诞生于 MySQL 5.1，在这种格式下，binlog 记录的是每一行数据更改的具体内容。
优点：能够精确地记录数据的变化，避免了 STATEMENT 格式中的环境依赖问题，提供了更强的一致性保证。
缺点：日志文件可能会比 STATEMENT 格式大，因为记录了每一行的详细变化。此外，ROW 格式的日志在进行大量数据更新时可能会导致更高的 I/O 开销。 - MIXED 格式
混合模式，在这种格式下，binlog 可以根据具体的 SQL 语句和操作自动选择使用 STATEMENT 或 ROW 格式。
优点：结合了 STATEMENT 和 ROW 格式的优点，能够在保证一致性的同时尽可能地优化日志大小和复制性能。
缺点：由于混合使用了两种格式，可能需要更复杂的管理和监控。在某些特定情况下，MIXED 格式可能无法达到最优的性能或一致性。
读写分离
主从架构能实现读写分离的效果，主库用于数据更新等写操作，从库用于查询等读操作
应用程序需要知道哪些请求是写请求，哪些是读请求。可以通过以下几种方法来实现读写分离：
1.手动路由
应用程序代码中直接根据逻辑来决定是访问主数据库还是从数据库。例如，所有写操作直接连接主数据库，读操作则连接从数据库。这需要配置多数据源，且代码侵入性强，耦合严重
2.使用连接池：有些连接池组件支持读写分离，可以配置主从关系，自动分配连接。
3.使用代理层：如 ProxySQL 或者 MaxScale 可以根据 SQL 请求类型自动将请求转发到合适的节点。
主从延迟
为了完成主从复制，从库需要通过 I/O 线程获取主库中 dump 线程读取的 binlog 内容并写入到自己的中继日志 relay log 中，从库的 SQL 线程再读取中继日志，重做中继日志中的日志，相当于再执行一遍 SQL，更新自己的数据库，以达到数据的一致性。
- 主库执行完一个事务，写入 binlog，将这个时刻记为 T1；
- 从库接收完这个 binlog 的时刻记为 T2；
- 从库执行完成这个事务，将这个时刻记为 T3。
主从延迟就是同一个事务在从库执行完成的时间与主库执行完成的时间之差，也就是 T3 - T1。
可以在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒。
seconds_behind_master 的计算方法是这样的：
每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master。
在网络正常的时候，日志从主库传给从库所需的时间是很短的，即 T2 - T1 的值是非常小的。也就是说，网络正常情况下，主从延迟的主要来源是从库接收完 binlog 和执行完这个事务之间的时间差。
由于主从延迟的存在，我们可能会发现，数据刚写入主库，结果却查不到，因为可能还未同步到从库。主从延迟越严重，该问题也愈加明显。
如何解决
参考：https://worktile.com/kb/p/26544
主库和从库在执行同一个事务的时候出现时间差的问题，主要原因包括但不限于以下几种情况：
- 从库所在机器的性能要比主库性能差，从库同步不过来
- 从库的压力较大，即从库承受了大量的请求。
- 执行大事务。因为主库上必须等事务执行完成才会写入 binlog，再传给备库。如果一个主库上语句执行 10 分钟，那么这个事务可能会导致从库延迟 10 分钟。
- 从库的并行复制能力差，是否支持并行
解决主从延迟主要有以下方案：
使用 semi-sync 半同步复制
MySQL 默认的复制是异步的，所以主库和从库的数据会有一定的延迟，更重要的是异步复制可能会引起数据的丢失。但是全同步复制又会使得完成一个事务的时间被拉长，带来性能的降低。因此使用半同步复制。
相对于异步复制，半同步复制提高了数据的安全性，减少了主从延迟，当然它也还是有一定程度的延迟，这个延迟最少是一个 TCP/IP 往返的时间。所以，半同步复制较好在低延时的网络中使用。
一主多从分摊从库压力
如果从库承担了大量查询请求，那么从库上的查询操作将耗费大量的 CPU 资源，从而影响了同步速度，然后加重主从延迟。那么可以多接几个从库，让这些从库来共同分担读的压力。简而言之，就是加机器，方法简单粗暴，但也会带来一定成本。
强制走主库，保证强一致性
如果某些操作对数据的实时性要求比较苛刻，需要反映实时最新的数据，比如说涉及金钱的金融类系统、在线实时系统、又或者是写入之后马上又读的业务，这时我们就得放弃读写分离，让此类的读请求也走主库，这就不存延迟问题了。当然这也失去了读写分离带给我们的性能提升，需要适当取舍。
主库更新后，读从库之前先 sleep 一下；
判断主备无延迟方案（例如判断 seconds_behind_master 参数是否已经等于 0、对比位点）；
从库并行复制
一般 MySQL 主从复制有三个线程参与，都是单线程：Binlog Dump 线程、IO 线程、SQL 线程。复制出现延迟一般出在两个地方：
SQL 线程忙不过来（主要原因）；
网络抖动导致 IO 线程复制延迟（次要原因）。
日志在备库上的执行，就是备库上 SQL 线程执行relay log更新数据的逻辑。
在 MySQL 5.6 版本之前，MySQL 只支持单线程复制，由此在主库并发高、TPS 高时就会出现严重的主备延迟问题。从 MySQL 5.6 开始有了多个 SQL 线程的概念，可以并发还原数据，即并行复制技术。这可以很好的解决 MySQL 主从延迟问题。从单线程复制到最新版本的多线程复制，中间的演化经历了好几个版本。其实说到底，所有的多线程复制机制，都是要把原来的单线程拆成多个线程，也就是都符合下面的这个多线程模型：
slave_parallel_workers
参考：https://dev.mysql.com/doc/refman/8.4/en/replication-options-replica.html#sysvar_slave_parallel_workers
就是原来的SQL线程不再直接更新数据了，只负责读取relay log和分发事务。真正更新日志的，变成了 worker 线程。而 worker 线程的个数，就是由参数 slave_parallel_workers 决定的。由于 worker 线程是并发运行的，为了保证事务的隔离性以及不会出现更新覆盖问题，coordinator 在分发的时候，需要满足以下这两个基本要求：
- 更新同一行的两个事务，必须被分发到同一个 worker 中（避免更新覆盖）。
- 同一个事务不能被拆开，必须放到同一个 worker 中（保证事务隔离性）。
各个版本的多线程复制，都遵循了这两条基本原则。
以下是按表分发策略和按行分发策略，可以帮助理解 MySQL 官方版本并行复制策略的迭代：
-
按表分发策略：如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个 worker 不会更新同一行。按表分发的方案，在多个表负载均匀的场景里应用效果很好，但缺点是：如果碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
-
按行分发策略：如果两个事务没有更新相同的行，则它们在备库上可以并行。显然，这个模式要求 binlog 格式必须是 row。按行并行复制的方案解决了热点表的问题，并行度更高，但缺点是：相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源。
MySQL 5.6 版本的并行复制策略
MySQL 5.6 版本，支持了并行复制，只是支持的粒度是基于 Schema按库并行。其核心思想是：不同 schema 下的表并发提交时的数据不会相互影响，即从库可以对 relay log 中不同的 schema各分配一个类似 SQL 线程功能的线程，来重放 relay log 中主库已经提交的事务，保持数据与主库一致。
如果在主库上有多个 DB，使用这个策略对于从库复制的速度可以有比较大的提升。但通常情况下都是单库多表，那基于库的并发也就没有什么作用，根本无法并行重放，所以这个策略用得并不多。
MySQL 5.7 的并行复制策略
MySQL 5.7 引入了基于组提交的并行复制，参数 slave_parallel_workers 设置并行线程数，由参数 slave-parallel-type 来控制并行复制策略：
- 配置为 DATABASE，表示使用 MySQL 5.6 版本的按库并行策略；
- 配置为 LOGICAL_CLOCK，表示使用基于组提交的并行复制策略；
利用 binlog 的group commit机制，可以得出一个组提交的事务都是可以并行执行的，原因是：能够在同一组里提交的事务，由于存在行锁，一定不会修改同一行
基于组提交的并行复制具体流程如下：
- 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；commit_id 直接写到 binlog 里面；
- 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
- 这一组全部执行完成后，coordinator 再去取下一批执行。
所有处于 prepare 和 commit 状态的事务都是可以在备库上并行执行的。
binlog 的组提交的两个有关参数：
- binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync 刷盘；
- binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。
这两个参数是用于故意拉长 binlog 从 write 到 fsync 的时间，以此减少 binlog 的写盘次数。在 MySQL 5.7 的并行复制策略里，它们可以用来制造更多的“同时处于 prepare 阶段的事务”。可以考虑调整这两个参数值，来达到提升备库复制并发度的目的。
实战模拟
设置 MySQL 主从复制通常包括以下几个步骤：
- 启用二进制日志：在主服务器上启动二进制日志记录功能，这个一般是默认开启的
- 配置全局唯一标识符：设置 server-id 以区分不同的服务器。
- 创建复制用户：在主服务器上创建一个用户，用于从服务器连接。
- 设置从服务器：在从服务器上配置要复制的主服务器的信息，并启动复制进程。
- 检查和监控：确保复制正常工作，并定期检查复制状态。
注意事项
在设置主从复制前，请确保主服务器上的所有表都支持复制。
如果存在数据不一致的情况，请检查是否有未完成的事务或者是否使用了不支持复制的功能。
定期检查从服务器的错误日志来排查任何可能的问题。
Docker + Docker Compose
使用docker安装MySQL来进行模拟
环境要求
1.安装docker，并设置国内镜像站点
[root@localhost ~]# docker -v
Docker version 26.1.3, build b72abbb
2.安装docker-compose
[root@localhost ~]# docker-compose -v
/usr/local/lib/python3.6/site-packages/paramiko/transport.py:32: CryptographyDeprecationWarning: Python 3.6 is no longer supported by the Python core team. Therefore, support for it is deprecated in cryptography. The next release of cryptography will remove support for Python 3.6.
from cryptography.hazmat.backends import default_backend
docker-compose version 1.29.2, build unknown
docker-compose.yml
MySQL8.0.187版本，定义master和slave两个服务
# 指定本docker-compose.yml文件使用的 Docker Compose 文件格式版本
version: "3"
# 定义mysql-master和mysql-slave服务
services:
# mysql-master服务
mysql-master:
# 使用的镜像
image: mysql:8.0.18
# 容器名称
container_name: mysql-master
# 额外配置 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID
command: --server-id=1 --log-bin=mysql-bin --binlog-format=row
# 设置环境变量
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_DATABASE: testdb
MYSQL_USER: replication_user
MYSQL_PASSWORD: 123456
# 端口映射，容器的3306端口映射到本机的3306端口，使本机能够访问该容器
ports:
- "3306:3306"
# mysql-slave服务
mysql-slave:
image: mysql:8.0.18
container_name: mysql-slave
# 从节点需要主节点先启动后才启动，具有依赖关系
depends_on:
- mysql-master
# 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID
command: --server-id=2 --log-bin=mysql-bin --binlog-format=row
environment:
MYSQL_ROOT_PASSWORD: 123456
MYSQL_DATABASE: testdb
MYSQL_USER: replication_user
MYSQL_PASSWORD: 123456
# 端口映射，占用另一个端口
ports:
- "3307:3306"
运行容器
docker-compose up -d(-d 参数表示以后台模式detached mode运行容器)
这个是因为上面docker-compose.yml文件中包含有中文注释
ERROR: .UnicodeDecodeError: ‘utf-8’ codec can’t decode byte 0xb6 in position 4: invalid start byte
可以把所有中文注释去掉，重新运行容器
通过docker container ls查看是否运行成功
配置MySQL
访问主节点
docker exec -it mysql-master bash
mysql -uroot -p123456
创建用于主从复制的用户，登录Master节点，执行下面的SQL
mysql> ALTER USER 'replication_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'replication_password';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
# 记录下 File 和 Position 的值，后续用于配置从服务器
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 848 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
主节点的表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
5 rows in set (0.00 sec)
配置从节点
从节点需要跟踪主节点的log file和position，将 MASTER_HOST、MASTER_USER、MASTER_PASSWORD、MASTER_LOG_FILE 和 MASTER_LOG_POS 替换为对应的值
mysql> CHANGE MASTER TO
-> MASTER_HOST='mysql-master',
-> MASTER_USER='replication_user',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=848;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
出现警告信息
MySQL 8.4版本后执行ALTER USER 'replication_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'replication_password';会报错ERROR 1524 (HY000): Plugin ‘mysql_native_password’ is not loaded
在主节点的testdb创建一个表
mysql> use testdb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> CREATE TABLE test_table (
-> id INT PRIMARY KEY,
-> name VARCHAR(100)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_table |
+------------------+
1 row in set (0.00 sec)
然后启动从节点的复制进程
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
# 等上一会儿，可以看到表已经同步过来了
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test_table |
+------------------+
1 row in set (0.00 sec)
此时检查从服务器的复制状态
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: mysql-master
Master_User: replication_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1090
Relay_Log_File: ee3819086f20-relay-bin.000002
Relay_Log_Pos: 564
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1090
Relay_Log_Space: 779
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 943e9a56-75a7-11ef-9473-0242ac120002
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
测试完成后：执行docker-compose down
扫一扫
859
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
