环境说明:
MySQL 8.0.3x 双主+Keepalived架构
迁移目标:
将MySQL数据库由Redhat操作系统迁移到麒麟操作系统。
迁移需求:
需求1:
迁移后IP地址保持不变，也就是迁移完成后需要手动修改服务器IP地址，将原、旧服务器IP地址互换。
奇怪的点:
因为应用连接的是MySQL的VIP地址，迁移后实际上没有必要改回原物理IP地址，多了改IP的环节，延长了迁移时间。
需求2:
迁移后IP地址变化，使用新的IP地址，但是数据文件安装在NAS上，要求旧库停库后，新服务器挂载旧NAS后，直接启动并使用MySQL数据库。
奇怪的点:
新服务器直接挂载旧NAS，这种方式实际上更简单、粗暴，去掉了实际迁移的环节，但是比较坑的是这次又要求使用新IP(不改回原IP)，和需求1相比，需求2不改IP实际上更麻烦了，因为mysql主从是通过IP地址进行识别，使用新IP，需要重新配置主从同步关系，延长了迁移时间。
实际上更合理的需求是:
1.如果MySQL数据文件没有安装在NAS，而是安装在本地磁盘，建议迁移后使用新物理IP，VIP地址可以不变。
2.如果MySQL数据文件安装在NAS，可以考虑常规导出导入方式迁移，并且使用新IP；如果要使用旧IP，可以直接迁移NAS，迁移过程更加简单。
下面测试需求2的迁移过程:
原数据库:
MySQL 8.0.3x 双主
cjc-db-01:192.168.0.101
cjc-db-02:192.168.0.102
迁移:
将 192.168.0.102 迁移(替换) 到192.168.0.103服务器。
最终架构:
MySQL 8.0.3x 双主
cjc-db-01:192.168.0.101
cjc-db-03:192.168.0.103
本次迁移不使用mysqldump逻辑导出导入和者物理备份和恢复，采用停库直接cp文件进行迁移(模拟原NAS挂载新服务器的操作)。
cjc-db-01:
mysql> show slave hosts;
+-----------+------+-------+------------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+-------+------------+--------------------------------------+
| 131132225 | | 3308 | 2513113273 | d9b5d19f-929b-11ef-a95b-fa163e0fbb49 |
+-----------+------+-------+------------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)
cjc-db-02:
mysql> show slave hosts;
+------------+------+-------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+------------+------+-------+-----------+--------------------------------------+
| 2513113273 | | 3308 | 131132225 | 326bf8c8-b999-11ee-aac4-fa163eaf634b |
+------------+------+-------+-----------+--------------------------------------+
cjc-db-01:
新增复制用户:
mysql> select user,host,plugin from mysql.user where user='repl';
+------+----------------+-----------------------+
| user | host | plugin |
+------+----------------+-----------------------+
| repl | 192.168.0.101 | mysql_native_password |
| repl | 192.168.0.102 | mysql_native_password |
+------+----------------+-----------------------+
2 rows in set (0.02 sec)
查看当前repl密码:
mysql> select * from mysql.slave_master_info\G;
User_name: repl
User_password: ******
新建用户:
create user repl@'192.168.0.103' identified with mysql_native_password by '******';
grant replication slave on *.* to repl@'192.168.0.103';
flush privileges;
cjc-db-03:
新服务器上有一个测试MySQL，先停库:
#mysql -uroot -p******
#shutdown;
创建目录:
mkdir -p /db/mysqldata/app
chown mysql:mysql /db -R
cjc-db-02:
停 192.168.0.102 源数据库:
mysql> shutdown;
mysql> exit;
ps -ef|grep mysqld|grep -v grep
du -sh /db/mysqldata/3308
5.4G /db/mysqldata/3308
压缩:
tar -zcvf 3308.tar.gz 3308
ls -lrth 3308.tar.gz
-rw-r----- 1 mysql mysql 982M Apr 15 10:46 3308.tar.gz
上传到 cjc-db-03 192.168.0.103 新服务器:
scp -P 16022 3308.tar.gz mysql@192.168.0.103:/db/mysqldata/
3308.tar.gz 100% 982MB 313.2MB/s 00:03
cjc-db-03 解压:
mysql@cjc-db-03:/db/mysqldata$tar -zxvf 3308.tar.gz
mysql@cjc-db-03:/mysqldata/app$cp -r 8.0.3x /db/mysqldata/app/
启动数据库:
mysql@cjc-db-03:/mysqldata/app$mysqld --defaults-file=/db/mysqldata/3308/conf/my.cnf --user=mysql &
登录:
cjc-db-01 到 cjc-db-03 的同步是正常的。
mysql -uroot -p****** -S /db/mysqldata/3308/socket/mysql.sock
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.0.101
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000038
Read_Master_Log_Pos: 47693
Relay_Log_File: mysql-relay-bin.000042
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin.000038
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......
cjc-db-03 到 cjc-db-01 的同步中断，因为 cjc-db-01 主从配置的还是 cjc-db-02 的IP地址。
192.168.0.101:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed source event read
Master_Host: 192.168.0.102
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 262225
Relay_Log_File: mysql-relay-bin.000030
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
......
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: Error reconnecting to source 'repl@192.168.0.102:3308'. This was attempt 92/86400, with a delay of 10 seconds between attempts. Message: Can't connect to MySQL server on '192.168.0.102:3308' (111)
Last_SQL_Errno: 0
Executed_Gtid_Set: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3741,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
Auto_Position: 1
cjc-db-01 重新配置主从:
mysql> stop slave;
保留原复制关系，方便回退，新增复制关系:
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.0.103',
SOURCE_PORT = 3308,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = '******',
SOURCE_AUTO_POSITION = 1,
SOURCE_CONNECT_RETRY=10
FOR CHANNEL "tmp0415";
启动新的复制关系:
start slave for channel "tmp0415";
新的复制关系数据同步恢复正常(也就是2. row部分):
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.102
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 262225
Relay_Log_File: mysql-relay-bin.000030
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 262225
Relay_Log_Space: 752
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: Error reconnecting to source 'repl@192.168.0.102:3308'. This was attempt 93/86400, with a delay of 10 seconds between attempts. Message: Can't connect to MySQL server on '192.168.0.102:3308' (111)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 131132225
Master_UUID: d9b5d19f-929b-11ef-a95b-fa163e0fbb49
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 250415 10:56:24
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3741,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
*************************** 2. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.0.103
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 237
Relay_Log_File: mysql-relay-bin-tmp0415.000002
Relay_Log_Pos: 373
Relay_Master_Log_File: mysql-bin.000006
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: 237
Relay_Log_Space: 591
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: 131132225
Master_UUID: d9b5d19f-929b-11ef-a95b-fa163e0fbb49
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3741,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: tmp0415
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
2 rows in set, 1 warning (0.00 sec)
数据同步测试:
cjc-db-01新增数据:
192.168.0.101:
mysql> create table cjc.t0415(id int,time time);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into cjc.t0415 values(1,now());
Query OK, 1 row affected (0.00 sec)
cjc-db-03新增数据:
192.168.0.103:
mysql> insert into cjc.t0415 values(2,now());
Query OK, 1 row affected (0.01 sec)
192.168.0.101,192.168.0.103查询，双向同步正常:
mysql> select * from cjc.t0415;
+------+----------+
| id | time |
+------+----------+
| 1 | 11:03:35 |
| 2 | 11:03:48 |
+------+----------+
2 rows in set (0.00 sec)
回退
回退，也就是将cjc-db-03替换回cjc-db-02:
理论上，因为 cjc-db-03 已经有新数据了，需要将 cjc-db-03 数据迁移回cjc-db-02，如果是NAS的话，cjc-db-02重新挂载回原NAS旧可以了。
如果 cjc-db-02 使用旧数据，会有什么问题，测试如下:
cjc-db-03:停库
192.168.0.103
mysql> shutdown;
启动原测试MySQL:
mysqld --defaults-file=/etc/my.cnf --user=mysql &
cjc-db-02:启动数据库
192.168.0.102
mysqld --defaults-file=/etc/my.cnf --user=mysql &
检查数据，丢了一条之前在cjc-db-03上新增的数据。
mysql> show slave status\G;
mysql> select * from cjc.t0415;
+------+----------+
| id | time |
+------+----------+
| 1 | 11:03:35 |
+------+----------+
1 row in set (0.00 sec)
cjc-db-01 同步异常:
192.168.0.101
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.102
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 262225
Relay_Log_File: mysql-relay-bin.000030
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 262225
Relay_Log_Space: 752
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: Error reconnecting to source 'repl@192.168.0.102:3308'. This was attempt 93/86400, with a delay of 10 seconds between attempts. Message: Can't connect to MySQL server on '192.168.0.102:3308' (111)
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 131132225
Master_UUID: d9b5d19f-929b-11ef-a95b-fa163e0fbb49
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 250415 10:56:24
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3743,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1-2,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
*************************** 2. row ***************************
Slave_IO_State: Reconnecting after a failed source event read
Master_Host: 192.168.0.103
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 1146
Relay_Log_File: mysql-relay-bin-tmp0415.000002
Relay_Log_Pos: 723
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Connecting
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: 1146
Relay_Log_Space: 941
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1130
Last_IO_Error: Error reconnecting to source 'repl@192.168.0.103:3308'. This was attempt 18/86400, with a delay of 10 seconds between attempts. Message: Host '192.168.0.101' is not allowed to connect to this MySQL server
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 131132225
Master_UUID: d9b5d19f-929b-11ef-a95b-fa163e0fbb49
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 250415 11:08:41
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d9b5d19f-929b-11ef-a95b-fa163e0fbb49:2
Executed_Gtid_Set: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3743,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1-2,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: tmp0415
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
2 rows in set, 1 warning (0.00 sec)
ERROR:
No query specified
先删除之前新增的复制通道:
stop slave;
reset slave all for channel "tmp0415";
同步报错:error 1236
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.0.102
Master_User: repl
Master_Port: 3308
Connect_Retry: 10
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 262225
Relay_Log_File: mysql-relay-bin.000030
Relay_Log_Pos: 420
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
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: 262225
Relay_Log_Space: 752
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Replica has more GTIDs than the source has, using the source's SERVER_UUID. This may indicate that the the last binary log file was truncated or lost, e.g., after a power failure when sync_binlog != 1. The source may have rolled back transactions that were already replicated to the replica. Replicate any transactions that source has rolled back from replica to source, and/or commit empty transactions on source to account for transactions that have been committed on source but are not included in GTID_EXECU'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 131132225
Master_UUID: d9b5d19f-929b-11ef-a95b-fa163e0fbb49
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 250415 11:18:26
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3743,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1-2,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
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.00 sec)
mysql> select * from mysql.slave_master_info\G;
*************************** 1. row ***************************
Number_of_lines: 33
Master_log_name: mysql-bin.000003
Master_log_pos: 262225
Host: 192.168.0.102
User_name: repl
User_password: ******
Port: 3308
Connect_retry: 10
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: d9b5d19f-929b-11ef-a95b-fa163e0fbb49
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
Public_key_path:
Get_public_key: 0
Network_namespace:
Master_compression_algorithm: uncompressed
Master_zstd_compression_level: 3
Tls_ciphersuites: NULL
Source_connection_auto_failover: 0
Gtid_only: 0
1 row in set (0.00 sec)
因为 cjc-db-03 比 cjc-db-02 的数据和 binlog 更新，cjc-db-01 需要基于之前cjc-db-03的最新的binlog数据进行拉取数据进行同步，cjc-db-02 替换 cjc-db-03后，cjc-db-01 在 cjc-db-02 读取不到之前的binlog数据，所有报错 error 1236。
cjc-db-02:检查binlog events，缺失了 cjc-db-03 新增的一条数据，所以在cjc-db-01角度来看，cjc-db-02的 mysql-bin.000003 文件不完整。
cjc-db-02:
show binary logs;
show binlog events in 'mysql-bin.000003';
+------------------+------+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 131132225 | 126 | Server ver: 8.0.3x, Binlog ver: 4 |
| mysql-bin.000003 | 126 | Previous_gtids | 131132225 | 237 | 326bf8c8-b999-11ee-aac4-fa163eaf634b:3214-3374,
......
| mysql-bin.000003 | 261362 | Query | 2513113273 | 261495 | use `cjc`; alter table s2_agent drop column admin_org /* xid=5464 */ |
| mysql-bin.000003 | 261495 | Gtid | 2513113273 | 261581 | SET @@SESSION.GTID_NEXT= '326bf8c8-b999-11ee-aac4-fa163eaf634b:3739' |
| mysql-bin.000003 | 261581 | Query | 2513113273 | 261802 | CREATE USER 'repl'@'192.168.0.103' IDENTIFIED WITH 'mysql_native_password' AS '*FCDB49B65CDF389897E4CF17594FA04F82799FDC' /* xid=5469 */ |
| mysql-bin.000003 | 261802 | Gtid | 2513113273 | 261886 | SET @@SESSION.GTID_NEXT= '326bf8c8-b999-11ee-aac4-fa163eaf634b:3740' |
| mysql-bin.000003 | 261886 | Query | 2513113273 | 262046 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.103' /* xid=5470 */ |
| mysql-bin.000003 | 262046 | Gtid | 2513113273 | 262130 | SET @@SESSION.GTID_NEXT= '326bf8c8-b999-11ee-aac4-fa163eaf634b:3741' |
| mysql-bin.000003 | 262130 | Query | 2513113273 | 262225 | flush privileges |
| mysql-bin.000003 | 262225 | Stop | 131132225 | 262248 |
+------------------+------+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------+
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 796
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 326bf8c8-b999-11ee-aac4-fa163eaf634b:1-3743,
7e6e2abc-43f5-11ef-87db-fa163e566d7c:305-308,
c183f6ac-5095-11ef-816d-fa163e0fbb49:1-2980,
d9b5d19f-929b-11ef-a95b-fa163e0fbb49:1,
dd30fb57-dabd-11ee-8cc1-fa163e4fc32f:1-901
1 row in set (0.00 sec)
测试环境很好解决，因为没有新数据的产生，直接重建 cjc-db-01 和 cjc-db-02 的同步关系:
cjc-db-01:重新配置主从
stop slave;
reset slave all;
reset master;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.0.102',
SOURCE_PORT = 3308,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = '******',
SOURCE_AUTO_POSITION = 1,
SOURCE_CONNECT_RETRY=10;
show slave status\G;
cjc-db-02:重新配置主从
stop slave;
reset slave all;
reset master;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST = '192.168.0.101',
SOURCE_PORT = 3308,
SOURCE_USER = 'repl',
SOURCE_PASSWORD = '******',
SOURCE_AUTO_POSITION = 1,
SOURCE_CONNECT_RETRY=10;
cjc-db-01:删除比 cjc-db-03 多的一条数据:
cjc-db-01:
mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from cjc.t0415 where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into cjc.t0415 values(2,now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from cjc.t0415;
cjc-db-02:可以正常同步数据
mysql> select * from cjc.t0415;
欢迎关注我的公众号《IT小Chen》
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
