此环境在# MySQL主从的搭建之无损同步（增强版半同步）文章中，附链接juejin.cn/post/711167…
处理主从数据不一致，大大小小的问题，包括在线添加从节点，都可此方法！！
一、备份主库数据并到备库还原
1、备份主库数据
mysqldump -uroot -p --set-gtid-purged=ON --all-databases --single-transaction --master-data=2 --triggers --routines --events >/data01/mysql/backup/alldata.sql
2、备份数据传到从库
scp alldata.sql 10.123.1.12:/data01/mysql/backup/
3、为了更加的贴合生产环境，在备份完成后，在主库添加几条数据，更改几条数据：
insert into 00db1.`00bak11` values (10,'11114'),(11,'11154'),(12,'1114114'),(13,'114114'),(14,'1114');
CREATE TABLE 00db1.0015 ( id INT NOT NULL PRIMARY KEY auto_increment, NAME VARCHAR ( 60 ), age INT );
update 00bak11 set name ='147258' where id>2;
--------现在主库数据与备份还原的数据不一致----------------
下面这个图片是备库导入数据后，所查询的数据
4、备库操作并还原数据
从库操作！！！！
进入数据库停掉slave线程
stop slave;
reset master;
reset slave all;
开始执行命令导入数据
source /data01/mysql/backup/alldata.sql
change master to
master_host='10.123.1.11',
master_port=33306,
master_user='repl',
master_password='rep1',
master_auto_position=1;
start slave;
show slave status \G;
说明：导入时的报错：ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL'
可忽略，此报错对结果无影响
二、测试
插入或更改几条数据，查看从库是否报错或不同步
主库：
create database 00db2;
create table 00db2.00bak21 (id int,name varchar(40));
insert into 00db2.00bak21 values(1,'00211'),(2,'00212'),(3,'00213'),(4,'00214'),(5,'00215');
commit;
select * from 00db2.00bak21;
从库：
select * from 00db2.00bak21;
root@mysqldb 00:25> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.123.1.11
Master_User: repl
Master_Port: 33306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000003
Read_Master_Log_Pos: 5740420
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 3235
Relay_Master_Log_File: mysql-binlog.000003
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: 5740420
Relay_Log_Space: 3438
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: 1133306
Master_UUID: 9bf5c57a-f163-11ec-b530-0800279577b4
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: 9bf5c57a-f163-11ec-b530-0800279577b4:14034-14040
Executed_Gtid_Set: 9bf5c57a-f163-11ec-b530-0800279577b4:1-14034:14037-14040,
d7890fb0-f163-11ec-af62-0800279870c6:1-1820
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)
