传统mysql主从在从库意外关机情况下，从库可能会失败，因此想将传统的mysql主从模式切换到Gtid模式下，测试下从库的健壮性。

本文，测试一下基于 mysql 8.0 版本，主从同步中，传统模式和 GTID 模式之间相互切换。

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
79
80
81
82
83
84
85
--1)查看主从版本都是8.0
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                   | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000001 |     1816 |       | mysql,information_schema,performance_schema,sys |                |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)


--2)slave同步情况：
mysql> show slave status \G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.72.0.11
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: master-bin.000001
 Read_Master_Log_Pos: 1816
 Relay_Log_File: slave1-relay-bin.000002
 Relay_Log_Pos: 615
 Relay_Master_Log_File: master-bin.000001
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
 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: 1816
 Relay_Log_Space: 825
 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: 573033261
 Master_UUID: 081712e5-f45a-11ed-9af2-0242ac48000b
 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, 1 warning (0.00 sec)

ERROR:
No query specified

可以看到当前同步状态为传统模式，未启用 GTID  

传统模式切换成Gtid模式

1. 主从修改参数 enforce_gtid_consistency

1
2
3
-- 主从都执行，先后顺序不要求
mysql> set @@global.enforce_gtid_consistency=warn;
Query OK, 0 rows affected (0.00 sec)
  • 如果出现 GTID 不兼容的语句用法，在错误日志会记录相关信息，需要先修复，调整应该程序避免不兼容的写法，直到完全没有产生不兼容的语句，可以设置后观察错误日志一段时间，防止出现问题，这一步非常重要。

2. 主从修改参数 enforce_gtid_consistency

​ 确定没有不兼容语法后，修改为 ON

1
2
3
-- 主从都执行，先后顺序不要求
mysql> set @@global.enforce_gtid_consistency=on;
Query OK, 0 rows affected (0.01 sec)

3. 主从修改参数 gtid_mode

​ 先设置 gtid_mode 为 off_permissiv=off_permissiv，再改成 on_permissiv

1
2
3
4
5
6
-- 建议先修改从库的gtid_mode为on_permissive，之后再修改主库的
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

gitd_mode 解释

  • OFF 不产生 GTID，Slave 只接收不带 GTID 的事务

  • OFF_PERMISSIVE 不产生 GTID，Slave 接收不带 GTID 的事务也接收带 GTID 的事务

  • ON_PERMISSIVE 产生 GTID，Slave 接收不带 GTID 的事务也接收带 GTID 的事务

  • ON 产生 GTID，Slave 只接收带 GTID 的事务

注意：需要严格按照以下顺序，不可跳跃，否则抛出如下错误：

OFF <= => OFF_PERMISSIVE <= => ON_PERMISSIVE <= => ON

1
2
3
4
5
6
7
8
9
10
11
-- 比如，直接从off改成on是不行的
mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = on;
ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

4. 检查从库是否已经同步到当前主库位置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--1)master: 取主库的file跟pos到从库去执行查看
mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
| master-bin.000002 |      635 |              | mysql,information_schema,performance_schema,sys |                   |
+-------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)


--2)slave
mysql> SELECT MASTER_POS_WAIT('master-bin.000002', 635);
+-------------------------------------------+
| MASTER_POS_WAIT('master-bin.000002', 635) |
+-------------------------------------------+
|                            0 |     -- 返回值为0，代表从库已经应用了指定的主库上binlog position位置数据
+-------------------------------------------+

5. 确认从库没等待的事务

1
2
3
4
5
6
7
8
-- 检查全部实例 正在进行的匿名交易数目，也就是非GTID事务有没有都传送到从库上了，需要等到这个变量为 0 
mysql>  show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.01 sec)

注意：如果是一主多从，那么需要在所有从库上查看，等待所有从库都变为 0.

6. 启动 GTID

1
2
3
4
5
6
7
8
9
10
11
-- 主从都执行，先后顺序不要求
mysql> SET @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

7. 检查同步状态

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
79
80
81
82
83
84
85
86
87
88
89
90
91
--1)master
mysql> insert into mytb1 values (4,'d');
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+----------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                      |
+-------------------+----------+--------------+-------------------------------------------------+----------------------------------------+
| master-bin.000005 |      446 |              | mysql,information_schema,performance_schema,sys | 081712e5-f45a-11ed-9af2-0242ac48000b:1 |
+-------------------+----------+--------------+-------------------------------------------------+----------------------------------------+
1 row in set (0.00 sec)


--2)slave
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.72.0.11
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: master-bin.000005
 Read_Master_Log_Pos: 446
 Relay_Log_File: slave1-relay-bin.000010
 Relay_Log_Pos: 663
 Relay_Master_Log_File: master-bin.000005
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
 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: 446
 Relay_Log_Space: 921
 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: 573033261
 Master_UUID: 081712e5-f45a-11ed-9af2-0242ac48000b
 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: 081712e5-f45a-11ed-9af2-0242ac48000b:1
 Executed_Gtid_Set: 081712e5-f45a-11ed-9af2-0242ac48000b:1
 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, 1 warning (0.00 sec)

ERROR:
No query specified

可以看到：

  • 主库新插入的数据，可以正常同步到 slave。
  • Retrieved_Gtid_Set、Executed_Gtid_Set 都识别到了事务编号。
  • Auto_Position 仍然是 0，说明还是传统模式。

8. 将传统复制转为 GTID 模式

1
2
3
4
5
6
7
8
9
--slave执行
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_auto_position=1;  -- 设置为1，自动查找位置复制
Query OK, 0 rows affected (0.01 sec)

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

9. 测试同步

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--1)master
mysql> insert into mytb1 values (5,'e');
Query OK, 1 row affected (0.00 sec)

--2)slave: 同步正常
mysql> select * from testdb.mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+
5 rows in set (0.00 sec)

10. 主从修改 my.cnf 文件

1
2
3
4
cat >> /etc/my.cnf <<EOF
gtid-mode=ON
enforce-gtid-consistency=on
EOF

或者利用 MySQL 8.0 新特性，持久化修改参数：

1
2
3
4
5
6
7
8
9
10
11
mysql> set persist gtid_mode = on;
Query OK, 0 rows affected (0.00 sec)

mysql> set persist enforce_gtid_consistency = on;
Query OK, 0 rows affected (0.00 sec)


-- 这种方式会把参数设置写入到mysqld-auto.cnf文件中
root@slave1:/var/lib/mysql# more mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "mysql_server_static_options" : { "enforce_gtid_consistency" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1684293203484259 , "User"
: "root" , "Host" : "localhost" } } , "gtid_mode" : { "Value" : "ON" , "Metadata" : { "Timestamp" : 1684293146585382 , "User" : "root" , "Host" : "localhost" } } } } }

此时，我们已经将原来的传统复制模式，修改为 GTID 复制模式。

Gtid模式切换成传统模式

1. 停止从库

1
2
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

2. 修改复制模式为传统模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--1)master:取主库file跟pos
mysql> show master status;
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                        |
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| master-bin.000005 |      736 |              | mysql,information_schema,performance_schema,sys | 081712e5-f45a-11ed-9af2-0242ac48000b:1-2 |
+-------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)

--2)slave:
mysql> change master to master_log_file='master-bin.000005',master_log_pos=736,master_auto_position=0;
Query OK, 0 rows affected (0.01 sec)

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

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
78
79
80
81
82
83
84
--1)master
mysql> insert into mytb1 values (6,'f');
Query OK, 1 row affected (0.00 sec)

--2)slave
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.72.0.11
 Master_User: repl
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: master-bin.000005
 Read_Master_Log_Pos: 1026
 Relay_Log_File: slave1-relay-bin.000002
 Relay_Log_Pos: 615
 Relay_Master_Log_File: master-bin.000005
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB: information_schema,performance_schema,mysql,sys
 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: 1026
 Relay_Log_Space: 825
 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: 573033261
 Master_UUID: 081712e5-f45a-11ed-9af2-0242ac48000b
 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: 081712e5-f45a-11ed-9af2-0242ac48000b:3
 Executed_Gtid_Set: 081712e5-f45a-11ed-9af2-0242ac48000b:1-3
 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, 1 warning (0.00 sec)

ERROR:
No query specified

可以看到：

  • 主库新插入的数据，可以正常同步到 slave。
  • Read_Master_Log_Pos、Exec_Master_Log_Pos 都产生了变化。
  • Auto_Position 变为 0，已经修改为传统模式。

4. 主从修改参数 gtid_mode

先设置 gtid_mode 为 off_permissiv=on_permissiv，再改成 off_permissiv

1
2
3
4
5
6
-- 主从都执行，先后顺序不要求
mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.00 sec)

5. 禁用 GTID

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 主从都执行，先后顺序不要求
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @@GLOBAL.GTID_MODE = OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF    |
+---------------+-------+
1 row in set (0.00 sec)

7. 检查同步状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--1)master
mysql> insert into mytb1 values (7,'g');
Query OK, 1 row affected (0.00 sec)

--2)slave: 同步正常
mysql> select * from mytb1;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
|    7 | g    |
+------+------+
7 rows in set (0.00 sec)

9. 主从修改 my.cnf 文件

如果使用 MySQL 8.0 新特性，持久化修改参数的，需要再重置回去：

1
2
3
-- 注释
#gtid-mode=ON
#enforce-gtid-consistency=on

此时，我们已经将原来的 GTID 复制模式，修改为传统复制模式。

10. 思考？

在第一步，停止从库复制 (stop slave），到再次启动 slave 复制，期间是有时间差的，如果从库停止复制期间，主库有新的事务产生，那么从库在启动复制后，会丢失这部分数据，因此，从 GTID 模式修改为传统复制模式，需要将主库停止，防止丢失数据。但是从传统模式修改为 GTID 模式，不需要停止主库，可以在线操作。

 
–实验：

1
2
3
4
5
6
7
8
9
10
mysql> reset persist gtid_mode ;
Query OK, 0 rows affected (0.00 sec)

mysql> reset persist enforce_gtid_consistency ;
Query OK, 0 rows affected (0.00 sec)


-- 可以看到mysqld-auto.cnf文件，之前设置的参数已被删除
root@slave1:/var/lib/mysql# more mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : {  } }

slave 重新启动同步后，丢失了主库插入的（8,h）这一行数据。

引用：
Mysql 8.0 主从同步 传统模式和GTID模式相互切换 - 墨天轮 (modb.pro)