Akemi

MySQL高可用之主从复制(已更新)

mysql
2024/09/05

主从复制

主从复制与相关概念介绍

mysql主从复制是mysql自带的一种容灾备份方案，通过逻辑的binlog日志复制到从节点，然后由本地线程读取地址的sql语句，应用到mysql数据库中

推荐架构
1主-1从、1主-2从、1主-1从-2从级联
其中级联模式可以缓解主的IO压力，主节点只需要将binlog复制给二级从节点即可

不推荐架构
1主-1主多主、2主-1从、1主-1主-1主环形

主从复制的步骤

1.客户端访问数据库-修改数据
2.数据改变时，master将提交的事务写入binlog
3.从库的IO线程请求主库binlog，主库dump线程将其发送给IO线程
4.IO线程对比binlog与中继日志，如果不同，IO线程将其同步给中继日志
5.从库sql线程读取中继日志，并将其解析为sql
6.根据解析的sql语句重新解析至从库数据库

主从复制的同步方式

异步复制
搭建简单，使用广泛，性能好，但异步有丢失数据的风险
全同步复制
数据安全，一致性好，性能差
传统半同步复制(5.5之后)
介于异步和全同步复制之间，折中了前两种
无损复制(5.7之后)
增强版的半同步复制
数据零丢失，性能好

GTID特性

GTID全局事务ID，是对一个已提交事务的编号，全局唯一，在5.6.2之后开始支持
GTID和事务回记录到binlog中，用来标识事务，取代了传统的复制方法
每一个节点中，如果存在相同标识符，可以避免同一事务在同节点中出现多次，每个事务在集群中有了唯一性，数据安全性更高，故障切换更简单

GTID组成

Server_uuid
mysql实例的全局唯一标识，位置$datadir/auto.cnf
Sequence_Number
mysql内部事务的编号，一个mysql实例不会重复的序列号，也表示在该实例上已经提交事务的数量，并且跟随事务提交而递增

通过GTID可以知道事务最初在哪个实例上提交，方便故障排查和追踪

1
2
3
cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=fcc43b8d-48e6-11ef-b6a8-bc241123c3a6

补充：数据库错误代码

错误代码 描述
1050 数据库已存在，创建表失败
1051 数据库不存在，删除表失败
1054 字段不存在，或程序文件和数据库有冲突
1060 字段重复
1064 SQL语法错误。
1094 Table ‘xxx’ is too large - 尝试操作的表太大，可能是因为超出了某些限制（如InnoDB表的最大大小）。
1146 Table ‘xxx’ doesn’t exist - 尝试访问的表不存在。
1053 Server shutdown in progress - 服务器宕机
1062 Duplicate entry ‘xxx’ for key ‘yyy’ - 主键冲突

主从复制搭建

1
2
3
4
5
环境
CentOS Linux release 7.9.2009 (Core)
192.168.10.125 mysql-1
192.168.10.126 mysql-2
192.168.10.127 mysql-3

主从复制相关参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
从节点可能要绑定主节点地址
bind-address=192.168.10.125
服务器id，主从不相同，建议使用ip+port的方式
server-id=253306
关闭 跳过主机名解析
skip_name_resolve=off
事务隔离级别使用读已提交
transaction-isolation=read-committed

binlog参数：
log_bin=
log_bin_index=
binlog_format=row
binlog_rows_query_log_events=on 更详细的sql操作
sync_binlog=1 默认，每次提交事务，将binlog内容写入磁盘，保证binlog在服务器崩溃时不丢失
innodb_flush_log_at_trx_commit=1 默认，每次事务提交时将log buffer数据写入log file，并写入磁盘
log_bin_trust_function_creators=1 默认0，选择同步函数和存储过程
max_binlog_size=2024M 默认1024M，最大binlog大小
expire_logs_days=7 binlog最多保留多少天
binlog_cache_size=1M binlog缓存大小，默认32K，使其可以一次性提交更大的事务
innodb_support_xa=1 分布式事务，开启能确保事务写入binlog的顺序和事务time时间线一致，这样恢复时就会严格按照时间线进行恢复

从库的relay_log中继日志参数
relay_log=/usr/local/mysql/logs/relay.log
relay_log_recover=1 打开中继日志恢复，支持自我修复(从主库重新获取)
relay_log_info_repository=table 信息库方式，默认file，但sql线程写数据是写数据库操作，file是写文件操作，很难保持一致
master_info_repository=table 默认是file，IO线程根据收到的event，保存写的位置，设置为表table

同步方式参数
loose_rpl_semi_sync_master_enabled=1 mysql5.6主库启用半同步复制
loose_rpl_semi_sync_slave_enabled=1 mysql5.6从库启用半同步复制
loose_rpl_semi_sync_master_timeout=5000 超时五秒，切换为异步
rpl_semi_sync_master_wait_for_slave_count=1 至少收到1个slave发挥的ack
rpl_semi_sync_master_wait_point=AFTER_SYNC mysql5.7开启无损同步
rpl_semi_sync_master_wait_point=AFTER_COMMIT mysql5.7开启半同步复制

GTID参数
log_slave_updates=1 主库开启，从库的binlog会记录主库的操作记录，从库不建议开
enforce_gtid_consistency=on ON如果发现事务或语句不支持gitd就会报错，WARN不支持则返回警告，OFF不检查

gtid_mode=on
ON:产生gtid，从库只接受带gtid的事务
ON_PERMISSIVE:产生gtid，无论带不带gtid，从库都接收
OFF:不产生gtid，从库只接受不带gtid的事务
OFF_PERMISSIVE:不产生gtid，无论带不带gtid，从库都接收
gtid_executed_compression_period 控制表压缩率
gtid_next:automatic 获取下一个事务的方式，默认自动获取，当出现slave故障时需要跳过某事务时，可以通过set global gtid_next='xx'来手动指定
gtid_owned 表示正在执行的事务的gtid和线程id
session_track_gtids 用于捕获和跟踪gitd，OFF关闭、OWN_GTID返回当前事务的gtid、ALL_GTIDS返回系统执行的所有gtid

复制过滤参数
主库：
binlog-do-db 表示和A库相关的指令，会被写入二进制文件
binlog-ignore-db 表示忽略A库，除A库以外全都复制
从库：
replicate_do_db
replicate_ignore_db

replicate_do_table
replicate_ingnore_table
replicate_wild_do_table 和第一条是对应，必须同时使用
replicate_wild_ingnore_table 和第二条是对应，必须同时使用

其他:
binlog_gtid_simple_recovery=1 开启后，加快mysql重启时的恢复性能

并行参数（5.7开始），解决延时问题
slave-parallel-type=LOGICAL_LOCK 基于库的并行方式
slave-parallel-workers=4 4个并行复制的SQL回放线程
read_only=1
slave_preserve_commit_order=1 commit顺序保持一致

slave_skip_errors=ddl_exist_errors 跳过错误日志
默认off不跳过
all全部
ErroCode 特定错误参数，如1062
ddl_exist_errors DDL错误的参数

主从复制部署——（传统异步复制）

适合数据量少于50G

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
环境
192.168.10.125 mysql-1
192.168.10.126 mysql-2

创建一个主从复制用户
mysql -uroot -p
create user 'repuser'@'%' identified by 'repuser123';
grant replication client,replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;

主从的配置文件my.cnf基础添加
1.需要打开binlog
log_bin=/usr/local/mysql/logs/bin.log
log_bin_index=/usr/local/mysql/logs/binlog.index
binlog_format=ROW
binlog_rows_query_log_events=on

2.修改常规与安全性参数
bind-address=192.168.10.125/192.168.10.126
server-id=253306/263306
skip_name_resolve=on
expire_logs_days=7
max_binlog_size=2024M
binlog_cache_size=1M
log_bin_trust_function_creators=1
innodb_support_xa=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
transaction-isolation=read-committed

3.在此基础上，从库额外添加参数
log_slave_updates=1
read_only=1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
master_info_repository=table
relay_log_info_repository=table
relay_log_recovery=1
slave_skip_errors=dll_exist_errors
slave_preserve_commit_order=1

4.slave连接主库（部分信息需要在主库执行show master status查看）
mysql -uroot -p
stop slave;
change master to
	master_host='192.168.10.125',
	master_user='repuser',
	master_password='repuser123',
	master_log_file='bin.000003',
	master_log_pos=194;
start slave;
show slave status\G

5.测试数据同步：主库创建数据库和表数据
create database xueuiying;
use xuehuiying;
create table xhy01 (id int,name varchar(40));
insert into xhy01 values (1,'xhy01'),(2,'xhy02'),(3,'xhy03');
commit;
select * from xhy01;
+------+-------+
| id   | name  |
+------+-------+
|    1 | xhy01 |
|    2 | xhy02 |
|    3 | xhy03 |
+------+-------+

备库检查
select * from xueuiying.xhy01;
+------+-------+
| id   | name  |
+------+-------+
|    1 | xhy01 |
|    2 | xhy02 |
|    3 | xhy03 |
+------+-------+

传统异步复制的开关机步骤

1.主库关闭服务

2.从库等待数据写入完毕后
stop slave;
关闭服务，关机

3.开机，主机先启动服务，从库再启动服务

4.主从进行检查show slave status\G
show processlist;

主从复制部署——（传统无损同步）

适合数据量少于100G

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
半同步或无损同步需要安装插件semisync_master.so
1.查看plugin_dir
show variables like "plugin_dir";
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| plugin_dir    | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+

2.将插件文件放入该目录下
安装
(1)安装方式1，手动安装
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave,so';

(2)安装方式2，直接写入配置文件(建议)
plugin_load="rpl_semi_sync_slave=semisync_slave.so;rpl_semi_sync_master=semisync_master.so"
loose_rpl_semi_sync_master_enabled=1 #旧配置，适用于5.6
loose_rpl_semi_sync_slave_enabled=1 #旧配置，适用于5.6
loose_rpl_semi_sync_master_timeout=5000 #旧配置，适用于5.6
rpl_semi_sync_master_wait_for_slave_count=1 
rpl_semi_sync_master_wait_point=AFTER_SYNC
重启服务

show plugins;

3.从库重新进行指向
stop slave;
change master to
    master_host='192.168.10.125',
    master_user='repuser',
    master_password='repuser123',
    master_log_file='bin.000005',
    master_log_pos=194;
start slave;
show slave status\G

semi相关参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 5000       |
| 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 |-表示是无损同步，AFTER_COMMIT代表半同步
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+

show status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |-客户端个数
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |-平均等待时间ms
| 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     |-没有成功接受slave提交的次数
| Rpl_semi_sync_master_status                | ON    |-看从库on半同步，off异步
| 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     |-成功接受slave事务恢复的次数
| Rpl_semi_sync_slave_status                 | OFF   |-看本库on是半同步，off是异步
+--------------------------------------------+-------+

主从复制部署——（GTID无损同步）（未完成）

主从复制部署——（GTID异步复制）

主库参数添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
bind-address=192.168.10.125
server-id=253306
skip_name_resolve=on
expire_logs_days=7
innodb_flush_log_at_trx_commit=1
sync_binlog=1
transaction_isolation=READ-COMMITTED
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
binlog_gtid_simple_recovery=1
log_bin=/usr/local/mysql/logs/bin.log
log_bin_index=/usr/local/mysql/logs/binlog.index
binlog_format=ROW
binlog_rows_query_log_events=on
max_binlog_size=2024M
binlog_cache_size=1M
plugin_load="rpl_semi_sync_slave=semisync_slave.so;rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_point=AFTER_SYNC

从库参数添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
server-id和bind-address要修改

bind-address=192.168.10.126
server-id=263306
skip_name_resolve=on
expire_logs_days=7
innodb_support_xa=1
innodb_flush_log_at_trx_commit=1
log_bin_trust_function_creators=1
sync_binlog=1
transaction_isolation=READ-COMMITTED
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
binlog_gtid_simple_recovery=1
log_bin=/usr/local/mysql/logs/bin.log
log_bin_index=/usr/local/mysql/logs/binlog.index
binlog_format=ROW
binlog_rows_query_log_events=on
max_binlog_size=2024M
binlog_cache_size=1M

relay_log=/usr/local/mysql/logs/relay.log
read_only=1

rpl_semi_sync_master_wait_for_slave_count=1
plugin_load="rpl_semi_sync_slave=semisync_slave.so;rpl_semi_sync_master=semisync_master.so;plugin-load=AUDIT=libaudit_plugin.so"
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_master_wait_point=AFTER_SYNC

创建复制用户并授权

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql -uroot -proot
create user 'repuser'@'%' identified by 'repuser123';
grant replication client,replication slave on *.* to 'repuser'@'%';
flush privileges;
select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| repuser       | %         |
| root          | %         |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

从库上和主库建立连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#清空环境
stop slave;
reset master;
reset slave;
#指向主库
change master to
master_host='192.168.10.125',
master_port=3306,
master_user='repuser',
master_password='repuser123',
master_auto_position=1;
#开启从库
start slave;
show slave status\\G
show processlist;

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
主库进行修改:
mysql -uroot -proot
create database wsdb;
use wsdb;
create table wst1 (id int,name varchar(20));
insert into wst1 values(1,'ws1'),(2,'ws2'),(3,'ws3');
commit;
select * from wsdb.wst1;
+------+------+
| id   | name |
+------+------+
|    1 | ws1  |
|    2 | ws2  |
|    3 | ws3  |
+------+------+

备库验证:
select * from wsdb.wst1;
+------+------+
| id   | name |
+------+------+
|    1 | ws1  |
|    2 | ws2  |
|    3 | ws3  |
+------+------+

补充：MASTER_DELAY参数

1
2
3
4
5
6
7
8
9
10
change master to [option]
常规选项
master_host
master_user
master_password
master_log_file
master_log_pos

延迟复制(秒)——在启动后n秒SQL线程才开始复制
MASTER_DELAY

报错处理

1
2
3
4
5
6
7
如果数据异常，需要清空环境从头来过
从库
stop slave;
reset slave;
reset slave all;
主库
reset master;

1.主从节点server-uuid一致报错
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决方法：停止从节点后，修改/usr/local/mysql/data/auto.cnf中的信息，并重启
也可以直接删除auto.cnf，重启后会自动生成

2.修改server-uuid报错
Error ‘Operation CREATE USER failed for ‘repuser‘@’%’’ on query. Default database: ‘’. Query: ‘CREATE USER ‘repuser‘@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘D6B70D90834FFD0E0516A62778445C486176C003’’
解决方法：停止从节点后删除从节点所有binlog和bin-index

3.主从复制在sql语句执行错误时卡住
报错Error ‘Can’t drop database ‘wsdb02’; database doesn’t exist’ on query. Default database: ‘wsdb02’. Query: ‘drop database wsdb02’
解决方法：
方法(1)
通过SHOW SLAVE STATUS\G或查看日志，找到错误事务的gtid，然后使用sql跳过事务
查看SHOW SLAVE STATUS\G
Relay_Master_Log_File: bin.000001
Read_Master_Log_Pos: 1411
Exec_Master_Log_Pos: 537

mysqlbinlog –start-position=537 –stop-position=1411 /usr/local/mysql/logs/bin.000001 | grep -i gtid
STOP SLAVE;
SET GTID_NEXT=’错误事务的GTID’;
BEGIN;
COMMIT;
SET GTID_NEXT=AUTOMATIC;
START SLAVE SQL_THREAD;

方法(2)
重新指向时，指定
change master to
master_host=’192.168.10.125’,
master_port=3306,
master_user=’repuser’,
master_password=’repuser123’,
master_auto_position=0;
相当于指定不适用gtid的方式进行主从复制

注意点：最好不要在初始化数据库阶段直接加入主从复制，因为像授权用户创建‘root‘@’%’这样的操作，从库接受但无法执行就会报错，此时就需要设置过滤器将其过滤掉

原文作者：王盛

原文链接：https://akemi.zj.cn/2024/09/05/MySQL-Replication/

发表日期：September 5th 2024, 5:09:44 pm

更新日期：February 20th 2025, 6:37:27 pm

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. 主从复制
  2. 2. 主从复制与相关概念介绍
    1. 2.1. 主从复制的步骤
    2. 2.2. 主从复制的同步方式
    3. 2.3. GTID特性
      1. 2.3.1. GTID组成
    4. 2.4. 补充：数据库错误代码
  3. 3. 主从复制搭建
    1. 3.1. 主从复制相关参数
    2. 3.2. 主从复制部署——（传统异步复制）
    3. 3.3. 主从复制部署——（传统无损同步）
    4. 3.4. 主从复制部署——（GTID无损同步）（未完成）
    5. 3.5. 主从复制部署——（GTID异步复制）
    6. 3.6. 补充：MASTER_DELAY参数
    7. 3.7. 报错处理
Total : 203
2025
2024
2023
Linux 云原生 云计算 开发 mysql