【MySQL 8.0】在线开启与关闭GTID复制

在线开启GTID复制
(root@node01) > set global enforce_gtid_consistency = warn;         
Query OK, 0 rows affected (0.04 sec)
(root@node02) > set global enforce_gtid_consistency = warn;
Query OK, 0 rows affected (0.07 sec)

(root@node01) >  set global enforce_gtid_consistency = on;
Query OK, 0 rows affected (0.04 sec)
(root@node02) > set global enforce_gtid_consistency = on;
Query OK, 0 rows affected (0.01 sec)

(root@node01) > set global gtid_mode = off_permissive;
Query OK, 0 rows affected (0.85 sec)
(root@node02) > set global gtid_mode = off_permissive;
Query OK, 0 rows affected (0.13 sec)

(root@node01) > set global gtid_mode = on_permissive;
Query OK, 0 rows affected (0.62 sec)
(root@node02) > set global gtid_mode = on_permissive;
Query OK, 0 rows affected (1.07 sec)

(root@node02) > show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0     |
+-------------------------------------+-------+
1 row in set (0.00 sec)

(root@node01) > flush logs;
Query OK, 0 rows affected (0.34 sec)

(root@node01) > set global gtid_mode = on;
Query OK, 0 rows affected (0.20 sec)
(root@node02) > set global gtid_mode = on;
Query OK, 0 rows affected (0.07 sec)

(root@node01) > select @@gtid_mode,@@enforce_gtid_consistency;
+-------------+----------------------------+
| @@gtid_mode | @@enforce_gtid_consistency |
+-------------+----------------------------+
| ON          | ON                         |
+-------------+----------------------------+
1 row in set (0.00 sec)

(root@node02) > select @@gtid_mode,@@enforce_gtid_consistency;
+-------------+----------------------------+
| @@gtid_mode | @@enforce_gtid_consistency |
+-------------+----------------------------+
| ON          | ON                         |
+-------------+----------------------------+
1 row in set (0.00 sec)

[mysql@node01 ~]$ vim /etc/my.cnf
gtid_mode = on
enforce_gtid_consistency = on

[mysql@node02 ~]$ vim /etc/my.cnf
gtid_mode = on
enforce_gtid_consistency = on

(root@node02) > stop replica; 
Query OK, 0 rows affected (0.20 sec)

(root@node02) > change replication source to master_auto_position = 1;
Query OK, 0 rows affected, 1 warning (0.12 sec)

(root@node02) > start replica; 
Query OK, 0 rows affected (0.09 sec)

(root@node02) > show replica status\G;

在线关闭GTID复制
(root@node02) > stop replica;
Query OK, 0 rows affected (0.03 sec)

(root@node02) > show replica status\G;
...
        Relay_Source_Log_File: mysql-bin.000033
...
          Exec_Source_Log_Pos: 14848286
...

(root@node02) > change replication source to source_auto_position = 0,source_log_file='mysql-bin.000033', source_log_pos=14848286;

(root@node02) > start replica;
Query OK, 0 rows affected (0.15 sec)

(root@node01) > set global gtid_mode = on_permissive;
Query OK, 0 rows affected (0.11 sec)
(root@node02) > set global gtid_mode = on_permissive;
Query OK, 0 rows affected (0.02 sec)

(root@node01) > set global gtid_mode = off_permissive;
Query OK, 0 rows affected (0.39 sec)
(root@node02) >  set global gtid_mode = off_permissive;
Query OK, 0 rows affected (0.06 sec)

(root@node01) > select @@global.gtid_owned;
+---------------------+
| @@global.gtid_owned |
+---------------------+
|                     |
+---------------------+
1 row in set (0.00 sec)

(root@node02) > select @@global.gtid_owned;
+---------------------+
| @@global.gtid_owned |
+---------------------+
|                     |
+---------------------+
1 row in set (0.00 sec)

(root@node01) > flush logs;
Query OK, 0 rows affected (0.08 sec)

(root@node01) > set global gtid_mode = off; 
Query OK, 0 rows affected (0.06 sec)
(root@node02) > set global gtid_mode = off; 
Query OK, 0 rows affected (0.03 sec)

(root@node01) > set global enforce_gtid_consistency = off;
Query OK, 0 rows affected (0.12 sec)
(root@node02) > set global enforce_gtid_consistency = off;
Query OK, 0 rows affected (0.00 sec)

[mysql@node01 ~]$ vim /etc/my.cnf
gtid_mode = off
enforce_gtid_consistency = off

[mysql@node02 ~]$ vim /etc/my.cnf
gtid_mode = off
enforce_gtid_consistency = off