Slave_IO_Running: No
报错:
Last_IO_Error: Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).
这种报错是因为你的配置文件中server_id这个选项要么没有或者重复。
解决方案:
- 修改/etc/my.cnf或子配置文件中server_id项,id的值主从不能没有或一样
Slave_IO_Running: Connecting
报错:
Last_IO_Error: error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.150.21:3306' (111)
出现这种错误的原因比较多需要排除:
解决方案:
1.保证两台服务器在同一网段或者可以互相通信
[root@localhost ~]# mysqladmin -urep -p123456 -h192.168.150.21 ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive
2.yo
# 查询日志
[root@localhost ~]# tail /var/log/mysql/mysqld.log
...
2023-12-02T03:17:53.631813Z 5 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 3 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
方案一:修改master库的密码加密方式
mysql> alter user 'rep'@'192.168.150.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
方案二:设置从库的change master 时加get_master_public_key=1参数
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='192.168.20.149',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='binlog.000006',
-> master_log_pos=676,
-> get_master_public_key=1; #加入这个参数
Query OK, 0 rows affected, 9 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
GTID 从库误写入操作处理
查看监控信息:
Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1注入空事物的方法:
stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
这里的xxxxx:N 也就是你的slave sql thread报错的GTID,或者说是你想要跳过的GTID。
最好的解决方案:重新构建主从环境
其他情况还有待研究,一般情况如上解决,error因人而异,自己要多看报错提示,很多时候报错都出现在小细节上面