在从节点清除信息
前提：停止slave的相关线程：stop slave
RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如HOST，PORT, USER和 PASSWORD 等
复制错误解决方法
- 在从服务器忽略几个主服务器的复制事件
- 执行跳过指定的sql类型错误
在从服务器忽略几个主服务器的复制事件，此为global变量，或指定跳过事件的ID
#系统变量，指定跳过复制事件的个数
SET GLOBAL sql_slave_skip_counter = N
跳过指定的sql类型错误
#服务器选项，只读系统变量，指定跳过事件的ID
[mysqld]
slave_skip_errors=1007|ALL
注意：
#CentOS7上Mariadb5.5 在slave创建库和表,再在master上创建同名的库和表,会出现复制冲突,而在CentOS8上的Mariadb10.3上不会冲突
#如果添加相同的主键记录都会冲突
范例：复制冲突的解决
#主键冲突导致的错误
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.10
Master_User: repli_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-logbin.000007
Read_Master_Log_Pos: 777
Relay_Log_File: relay-log.000004
Relay_Log_Pos: 697
Relay_Master_Log_File: mysql-logbin.000007
Slave_IO_Running: Yes
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: 1062
Last_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '6' for key 'teachers.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-logbin.000007, end_log_pos 746
Skip_Counter: 0
Exec_Master_Log_Pos: 476
Relay_Log_Space: 1251
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: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table hellodb.teachers; Duplicate entry '6' for key 'teachers.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-logbin.000007, end_log_pos 746
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 0618be64-3ae3-11ed-bc2a-000c295a5cb9
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:
Last_SQL_Error_Timestamp: 220923 12:04:54
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 (0.00 sec)
#停止slave
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
#跳过错误：
方法一：
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.10
Master_User: repli_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-logbin.000007
Read_Master_Log_Pos: 777
Relay_Log_File: relay-log.000005
Relay_Log_Pos: 327
Relay_Master_Log_File: mysql-logbin.000007
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: 777
Relay_Log_Space: 1372
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: 1
Master_UUID: 0618be64-3ae3-11ed-bc2a-000c295a5cb9
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 (0.00 sec)
方法二：
[root@CentOS8 ~]# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
server_id=2
relay-log=/data/relaylog/relay-log
#slave_skip_errors=1007|ALL
更改配置文件需要重启服务才会生效（重新将配置文件内容加载到内存）
[root@CentOS8 ~]# systemctl restart mysqld.service
1.主键冲突
在从节点增加数据以后会导致主从的数据不一致，
比如slave新增一条数据，主键为6，这时候主节点同步不一条主键为6的数据过来，就会导致主键冲突。
-
冲突导致以后，后面所有的都不能同步过来了。后续的复制无法正常进行。
-
此时删除从节点上导致冲突的记录也没用了，因为事务已经发生了，已经卡在这儿了。
解决方法：
-
1.停止salve的两个线程：stop slave
-
2.跳过错误
-
3.重新开始复制线程
-
4.手动解决出错的记录
跳过错误
sql_slave_skip_counter=num： num表示跳过几个复制错误 通过设置这个变量值实现
sql_slave_skip_erros=xx 跳过某一个sql类型的错误（这个选项需要该配置文件）
比如遇到mysql报1062这个错误:sql_slave_skip_erros=1602，那么下次主从复制的时候会直接跳这个错误。
ALL：表示所有sql类型的错误都跳过