主从复制错误总结
错误总结:
Slave_IO_Running: No
Slave_SQL_Running: Yes
[ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave 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 slave but this does not always make sense; please check the manual before using it). Error_code: 1593
egrep server-id /data/3306/my.cnf /data/3307/my.cnf
/data/3306/my.cnf:server-id = 6
/data/3307/my.cnf:server-id = 7
如何解决:
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.16.1.51',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='oldboy123',
-> MASTER_LOG_FILE='oldboy-bin.000001',
-> MASTER_LOG_POS=405;
本来该写到3307的写到3306.修改后正确
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_IO_Running: No
Slave_SQL_Running: Yes
[ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave 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 slave but this does not always make sense; please check the manual before using it). Error_code: 1593
egrep server-id /data/3306/my.cnf /data/3307/my.cnf
/data/3306/my.cnf:server-id = 6
/data/3307/my.cnf:server-id = 7
如何解决:
mysql> CHANGE MASTER TO
-> MASTER_HOST='172.16.1.51',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='oldboy123',
-> MASTER_LOG_FILE='oldboy-bin.000001',
-> MASTER_LOG_POS=405;
本来该写到3307的写到3306.修改后正确
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
*****20170320*****
如果克隆虚拟机推了整个目录,启动slave会报错 Slave_IO_Running: No
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决办法: cd /application/mysql/data mv auto.cnf auto.cnf.bak /etc/init.d/mysqld restart
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
解决办法: cd /application/mysql/data mv auto.cnf auto.cnf.bak /etc/init.d/mysqld restart
*****20170321*****
如果没有修改级联server-id和master不一样就会报如下错误
[ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave 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 slave but this does not always make sense; please check the manual before using it). Error_code: 1593
解决办法:修改server-id和master库不一样
*****20170323*****
Slave_IO_Running:Yes
Slave_SQL_Running:NO
Seconds_Behind_Master:NULL
error cant't create database database 'xiaoliu';database exits' on query.Default database:'xiaoliu'.query
解决办法:
解决1:
stop slave; #临时停止同步开关
set global sql_slave_skip_counter = 1; #将同步指针向下移动一个,如果多次不同步,可以重复操作。
start slave;
解决2:
根据可以忽略的错误号事先在配置文件中配置,跳过指定的不影响业务数据错误,如
[root@db03-53 ~]# grep slave-skip /data/3306/my.cnf slave-skip-errors = 1032,1062