MySQL的主从复制原理

前言

MySQL 主从复制也称为 MySQL 主从同步，是构建数据库高可用集群架构的基础，我们通过为服务器配置一个或多个备库的方式来进行数据同步，复制功能不仅有利于构建高性能应用，同时也是高可用性、可扩展性、灾难恢复、备份以及数据仓库等工作的基础。

复制的基本问题是解决不同服务器的数据保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置为另外一台服务器的主库，主库和备库之间可以有多种不同的组合方式。

常见主从架构模式单向主从

双向主从

级联主从

多主一从

主从原理

在实战前，先理解主从复制的原理更为重要。主从复制过程中有三个线程，主库有一个工作线程 I/O dump thread，备库有两个工作线程，I/O thread 和 SQL thread

主库接收SQL请求记录到自己的 binlog 中备库的 I/O thread 去请求主库的 binlog主库的 I/O dump thread 给备库 I/O thread 传送 binlog备库将得到的 binlog 写到自己的 relay log 中备库的 SQL thread 读取 relay log 执行 SQL

基于语句复制（STATEMENT）

在 MySQL5.0 以前只支持基于语句的复制。基于语句的复制模式下，主库会记录那些造成数据更改的操作，当备库读取并重放这些操作时，实际上只是把主库上的SQL执行一遍。好处是实现简单，简单的记录并执行这些语句，能让主备保持同步。

但实际上基于语句的复制方式有时会出问题。因为主库上的数据更新除了执行的语句外，可能还依赖于其他因素，例如，同一条 SQL 在主库和备库上的执行时间可能稍有不同，因此在传输带 binlog 中，还包括一些元数据信息，如当前的时间戳，还存在着一些无法被正确复制的 SQL，例如，CURRENT_USER() 函数的语句。存储过程和触发器在使用基于语句的复制模式时也可能存在问题。

基于行复制（ROW）

MySQL5.1开始支持基于行复制，这种方式会将实际的数据记录在 binlog 中，跟其他数据库的实现很像。基于行复制的模式有优点，也有缺陷。好处是可以正确的复制每一行，一些语句可以被更加有效的复制。

也有一些情况，基于行复制的代价会比较大，例如：ｕｐｄａｔｅ tb_user set age=10;

由于这条 SQL 会更新全表，使用基于行的开销会很大，因为每一行的数据都会记录到 binlog 中，这使得 binlog 文件庞大，并且会给主库增加额外的负载。

混合模式（MIXED）

以上两种模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择日志保存方式。

因为两种模式各有优缺点以及使用的场合，所以 MySQL 支持在这两种复制模式中动态切换（MIXED模式），MySQL8.0 默认使用基于行复制的方式，理论上基于行的复制模式在整体上更优，且在实际应用中适用于大多数场景。，当然也可以使用参数 binlog_format 手动指定复制的模式。

主从实战

本文操作实战环境：MySQL 8.0.26 + centos7

一主一从

1、两台服务器分别部署 MySQL，两台服务器 ip 为：

主：192.168.96.95从：192.168.96.82

2、在 my.cnf 中设置 server_id：

主：10000从：10001

注：更改 server_id 后需重启服务

3、开启 GTID 模式

在主库 my.cnf 中配置如下参数：

gitd_mode=onenforce_gtid_consistency=onlog_bin=on复制代码

备库中要配置：

gitd_mode=onenforce_gtid_consistency=onlog_slave_updates=1复制代码

4、检查主库是否开启了 log_bin 参数（MySQL8.0默认开启）

mysql> show variables like ‘%log_bin%‘;+---------------------------------+-----------------------------+| Variable_name| Value|+---------------------------------+-----------------------------+| log_bin | ON|| log_bin_basename| /var/lib/mysql/binlog|| log_bin_index| /var/lib/mysql/binlog.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events| OFF || sql_log_bin | ON|+---------------------------------+-----------------------------+6 rows in set (0.01 sec)复制代码

5、将 binlog 格式设置为基于行复制的格式（MySQL8.0 默认为 ROW）

mysql> show variables like ‘%binlog_format%‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW|+---------------+-------+1 row in set (0.00 sec)复制代码

6、创建主从复制账号

# 账号：bak;密码：123456;在 96 段可用mysql> create user ‘bak‘@‘192.168.96.%‘ identified by ‘123456‘;Query OK, 0 rows affected (0.04 sec)mysql> ｇｒａｎｔ replication slave on *.* to ‘bak‘@‘192.168.96.%‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)复制代码

7、主库上查询状态

mysql> show master status;+---------------+----------+--------------+------------------+-------------------------------------------+| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+---------------+----------+--------------+------------------+-------------------------------------------+| binlog.000003 |908 ||| 62cd056a-e9f1-11eb-9218-0242ac110002:1-16 |+---------------+----------+--------------+------------------+-------------------------------------------+1 row in set (0.00 sec)复制代码

8、在备库上配置主从

mysql>change master to MASTER_HOST=‘192.168.96.95‘,MASTER_USER=‘bak‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘binlog.000003‘,MASTER_LOG_POS=908;Query OK, 0 rows affected, 8 warnings (0.03 sec)复制代码

其中，参数如下：

MASTER_HOST：指定主库IPMASTER_USER：之前创建的复制用户MASTER_PASSWORD：用户密码MASTER_LOG_FILE：从备份文件中获取的当前 binlogMASTER_LOG_POS：从备份文件中获取的 position 号

其中，MASTER_LOG_FILE 和 MASTER_LOG_POS 参数可以指定为当前 主库中的 binlog 文件的 pos，可以先做主库的全量备份，再从主库中指定的 binlog 的 pos 开始同步。

9、备库上开启主从同步

mysql> start slave;Query OK, 0 rows affected, 1 warning (0.00 sec)复制代码

10、查看备库状态

mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.96.95Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000004Read_Master_Log_Pos: 2898Relay_Log_File: 0981bb088bd0-relay-bin.000002Relay_Log_Pos: 1093Relay_Master_Log_File: binlog.000004 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 2898Relay_Log_Space: 1309Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids: Master_Server_Id: 10000Master_UUID: 62cd056a-e9f1-11eb-9218-0242ac110002 Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set: 62cd056a-e9f1-11eb-9218-0242ac110002:32Executed_Gtid_Set: 267c5d14-e9f4-11eb-a424-0242ac110002:1-12,62cd056a-e9f1-11eb-9218-0242ac110002:32Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name:Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace:1 row in set, 1 warning (0.00 sec)复制代码

其中，一些重要的参数：

Slave_IO_Running：I/O thread 状态Slave_SQL_Running：SQL thread 状态Master_Log_File：当前主库的 binlogRead_Master_Log_Pos：正在读取主库当前 binlog 的 pos 位置Exec_Master_Log_Pos：执行到主库 binlog 的 pos 位置

Slave_IO_Running 和 Slave_SQL_Running 两个参数都为 yes 时，代表从节点配置正确。

11、验证主从同步

在主库中执行 ｉｎｓｅｒｔ 语句，可以看到备库中成功完成了同步。

主库中 user 表记录：

备库中 user 表记录：

12、slave 设置为 read-only

mysql> show variables like ‘%read_only%‘;+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_read_only| OFF|| read_only | OFF|| super_read_only| OFF|| transaction_read_only | OFF|+-----------------------+-------+4 rows in set (0.01 sec)mysql> set global read_only=1;Query OK, 0 rows affected (0.00 sec)复制代码

一主一从的架构模式下，一般备库用来读，建议在从服务商启动 read-only 选项，这样保证从服务器上的数据仅与主服务器进行同步，避免其他线程修改数据。在启用 read-only 后，如果操作从服务器的用户没有 super 权限，则对从服务器进行任何的修改会抛出错误（read-only 对拥有 super 权限的账号是不生效的）

双向主从

双向主从架构与单向主从架构的区别是，主库支持写操作，备库去做主库同步；而双向架构，是两台主库，每台都支持写操作，其中一台更新了数据，另外一台去做同步操作，始终保持两台服务器数据一致。

1、保证两台服务器 server_id 不同2、检查两台是否都开启了 log_bin 参数3、检查两台 binlog 是否设置基于行复制的格式4、创建主从复制账号5、在两台库上分别查询当前 binlog 和 pos6、在两台库上分别配置主从（互为主从）7、在两台库上开启主从同步，查看备库状态信息等8、插入数据验证双向主从架构是否实现互相数据同步

双向主从架构模式与单向主从的配置方式相似，不做赘述。注：双向主从架构模式不得设置 read-only 为 ON

级联主从

级联主从在单向主从架构的基础上，在第二个 slave 中设置第一个 slave 为 master 开启主从即可，配置过程不再赘述。

多主一从

多主一从，也称多源复制，就是把多台主库的数据同步到一个备库上，备库会创建通往每个主库的管道。在 MySQL 5.7以前，只能实现 一主一从、一主多从或多主多从的架构模式。

在 slave 上配置多个 master 时指定 channel 名称，同时在 start 时也根据 channel 名称开启同步即可，例如：

# 配置主从mysql>CHANGE MASTER to MASTER_HOST=‘192.168.96.177‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘123456‘,MASTER_LOG_FILE=‘binlog.000020‘,MASTER_LOG_POS=1998 for channel ‘master1‘;Query OK, 0 rows affected, 8 warnings (0.03 sec)# 开启同步mysql>start slave for channel ‘master1‘;Query OK, 0 rows affected, 1 warnings (0.03 sec)复制代码

多主一从架构中，对任意一个 master 做增删改操作时，slave 都会同步此操作。而一般情况下为了保持主从数据一致 slave 只做读操作即可。

多源复制的优点：

可以集中备份，在备库上备份，不会影响线上数据库正常运行数据汇总在一起，方便做数据统计半同步复制

MySQL 复制默认的方式是异步复制，也就是上面实操的，当主库把操作写入 binlog 后，并不知道备库是否已经接受并写入 relay log 了，这种异步复制的方式，如果在高可用集群架构下做主备切换，就会造成新的主库丢失数据的情况。

MySQL5.5后引入了半同步复制功能，主从服务器必须同时安装半同步复制插件，才能开启该复制功能。在半复制功能下，确保备库接收完主库传递的 binlog 并写入自己的 relay log 了，才会通知主库上面的等待线程，该操作完毕。如果等待超时，超过 rpl_semi_sync_master_timeout 参数设置的时间，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台备库通知主库已经接收到 binlog 了为止。

半同步复制原理如图：

半同步复制提升了主从之间数据的一致性，让复制更加安全可靠。

实战

1、在主库中安装半同步复制插件并开启半同步复制功能

# 安装半同步复制插件mysql> install plugin rpl_semi_sync_master soname ‘semisync_master.so‘;Query OK, 0 rows affected, 1 warning (0.07 sec)# 开启半同步复制mysql> set global rpl_semi_sync_master_enabled=on;Query OK, 0 rows affected (0.00 sec)# 查询是否开启半同步复制功能mysql> show variables like ‘%semi%‘;+-------------------------------------------+------------+| Variable_name | Value|+-------------------------------------------+------------+| rpl_semi_sync_master_enabled| ON || rpl_semi_sync_master_timeout| 10000|| rpl_semi_sync_master_trace_level| 32 || rpl_semi_sync_master_wait_for_slave_count | 1|| rpl_semi_sync_master_wait_no_slave| ON || rpl_semi_sync_master_wait_point| AFTER_SYNC |+-------------------------------------------+------------+6 rows in set (0.03 sec)复制代码

2、在备库中安装半同步复制插件并开启半同步复制功能

mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so‘;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> set global rpl_semi_sync_slave_enabled=on;Query OK, 0 rows affected (0.00 sec)mysql> show variables like ‘%semi%‘;+-------------------------------------------+------------+| Variable_name | Value|+-------------------------------------------+------------+| rpl_semi_sync_master_enabled| OFF|| rpl_semi_sync_master_timeout| 10000|| rpl_semi_sync_master_trace_level| 32 || rpl_semi_sync_master_wait_for_slave_count | 1|| rpl_semi_sync_master_wait_no_slave| ON || rpl_semi_sync_master_wait_point| AFTER_SYNC || rpl_semi_sync_slave_enabled| ON || rpl_semi_sync_slave_trace_level| 32 |+-------------------------------------------+------------+8 rows in set (0.00 sec)复制代码

如果想要开机自启动半复制功能，可以将 rpl_semi_sync_master_enabled 和 rpl_semi_sync_slave_enabled 参数写到 my.cnf 中。

3、在备库中重启 I/O 线程即可激活半同步复制。

mysql> stop slave io_thread;Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave io_thread;Query OK, 0 rows affected, 1 warning (0.02 sec)复制代码

4、在主库中查看半同步复制功能是否正常正常运行

mysql> show global status like ‘%semi%‘;+--------------------------------------------+-------+| Variable_name| Value |+--------------------------------------------+-------+| Rpl_semi_sync_master_clients| 1 || Rpl_semi_sync_master_net_avg_wait_time | 0 || Rpl_semi_sync_master_net_wait_time | 0 || Rpl_semi_sync_master_net_waits | 0 || Rpl_semi_sync_master_no_times| 0 || Rpl_semi_sync_master_no_tx | 0 || Rpl_semi_sync_master_status| ON|| Rpl_semi_sync_master_timefunc_failures | 0 || Rpl_semi_sync_master_tx_avg_wait_time| 0 || Rpl_semi_sync_master_tx_wait_time| 0 || Rpl_semi_sync_master_tx_waits| 0 || Rpl_semi_sync_master_wait_pos_backtraverse | 0 || Rpl_semi_sync_master_wait_sessions | 0 || Rpl_semi_sync_master_yes_tx| 0 |+--------------------------------------------+-------+14 rows in set (0.01 sec)复制代码

其中，Rpl_semi_sync_master_clients 参数代表有一个备库连接到了主库，并且是半同步复制方式。

5、经验证，在主库中操作数据，备库同步数据失败时，会导致主库插入数据缓慢，代表正在等待备库的响应结果，等待超时了，此时查看半同步复制状态，被切换为了异步复制方式。而想从异步复制方式切换为半同步复制方式，需要重启备库的 I/O thread 才行。

GTID

GTID（Global Transaction ID）是一个已提交事务的编号，并且是一个全局唯一的编号，MySQL5.6以后在主从复制类型上新增了 GTID 复制。是由 server_uuid 和事务 id 组成的，即 GTID=server_uuid:transaction_id，server_uuid 是在数据库启动过程中自动生成的，每台机器的 server_uuid 都不同，而 transaction_id 就是事务提交时由系统顺序分配的一个不会重复的序列号。

GTID 和异步复制、半同步复制类似，只不过不再利用传统复制模式的 binlog 文件和 position 号了，而是在备库 “change master to” 时使用 master_auto_position=1 的方式进行搭建，这就让操作变的更加方便和可靠。

使用 GTID 模式搭建过程时，主库my.cnf中要配置以下参数：

gitd_mode=onenforce_gtid_consistency=onlog_bin=on复制代码

备库中要配置：

gitd_mode=onenforce_gtid_consistency=onlog_slave_updates=1复制代码

配置好参数后，如果是新搭建的主从环境，就可以直接在库中之心 change master to 语句了，如果是运行了一段期间的主库，还需要利用备份方式从主库 dump 出数据到备库，先完成基于某个点的 GTID 复制，备库再从那个点之后再开始同步。前面实战的配置方式就是使用的 GTID 模式。

作者：_沸羊羊_链接：https://juejin.cn/post/6989232510030839816来源：掘金著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。