mysql版本8.0.25，一主一从
原来复制环境GTID是为开启的
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | OFF |
| gtid_executed | |
| gtid_executed_compression_period | 0 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
在主库用sysbench进行压测
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.x.x.201 --mysql-port=3306 --mysql-user=root --mysql-password='123456' --mysql-db=db_test --tables=10 --table_size=100000 --mysql_storage_engine=Innodb prepare
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=10.x.x.201 --mysql-port=3306 --mysql-user=root --mysql-password='123456' --mysql-db=db_test --tables=10 --table_size=100000 --mysql_storage_engine=Innodb --threads=8 --time=30000 --report-interval=10 --rand-type=uniform run
1.在所有数据库上执行SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN;
Query OK, 0 rows affected (0.08 sec)
等一会时间看看error日志是否有警告信息,如果没有警告信息继续下面步骤
2.在所有数据库实例上执行 SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
mysql> SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%enforce_gtid%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
1 row in set (0.01 sec)
3.在所有数据库实例上执行SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
mysql> SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> show variables like '%gtid_mode%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| gtid_mode | OFF_PERMISSIVE |
+---------------+----------------+
1 row in set (0.01 sec)
设置为OFF_PERMISSIVE表示写新事物的gtid是匿名的，查看如下gtid未增加
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 20962982 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.在所有数据库实例上执行SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
mysql> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE;
Query OK, 0 rows affected (0.02 sec)
mysql> show variables like '%gtid_mode%';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| gtid_mode | ON_PERMISSIVE |
+---------------+---------------+
1 row in set (0.00 sec)
设置为ON_PERMISSIVE表示写新事物是gtid，查看如下gtid增加
mysql> show master status;
+------------------+----------+--------------+------------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------------------------------------+
| mysql-bin.000008 | 6968071 | | | 33ab1a2d-cf38-11eb-9661-56c8a95977d1:1-5294 |
+------------------+----------+--------------+------------------+---------------------------------------------+
1 row in set (0.00 sec)
5.在每个数据库上ONGOING_ANONYMOUS_TRANSACTION_COUNT状态变量为0，状态变量变为0后再执行下一步
SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
mysql>
mysql> SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
1 row in set (0.01 sec)
6.等待所有匿名事务都复制完毕，在所有数据库实例上执行SET @@GLOBAL.GTID_MODE = ON;
mysql> SET @@GLOBAL.GTID_MODE = ON;
Query OK, 0 rows affected (0.04 sec)
mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | ON |
+---------------+-------+
1 row in set (0.01 sec)
7.在所有mysql配置文件添加配置项
gtid_mode = on
enforce_gtid_consistency = on
8.现在复制拓扑所有事务都具有GTID,需要在每个从库上启用GTID的自动定位
stop slave；
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;
mysql> stop slave ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.01 sec)
from MySQL 8.0.22 可以使用
mysql> STOP REPLICA;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION = 1 ;
Query OK, 0 rows affected (0.02 sec)
mysql> START REPLICA;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 196
Relay_Log_File: ck1-relay-bin.000002
Relay_Log_Pos: 371
Relay_Master_Log_File: mysql-bin.000009
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: 196
Relay_Log_Space: 578
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: 2013306
Master_UUID: 33ab1a2d-cf38-11eb-9661-56c8a95977d1
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: 33ab1a2d-cf38-11eb-9661-56c8a95977d1:1-421256
Auto_Position: 1
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.01 sec)
从结果中可以看到Auto_Position:1