之前配置好的Master-Slave模式的两台数据库,当时测试在主库建库建表并插入数据,都可以同步到从库没有问题,但运行一段时间后(一个月左右),发现备库某表数据与主库不一致了,而且此时测试在主库建表,也不会自动同步到备库。不知道这种问题如何排查?对应的日志可以从哪里找到?麻烦高手指点下解决思路,多谢~
此时主从库状态如下,看上去还是正常的。但不知道还能去哪里看到更详细的信息,或者去trace之前哪些操作影响了数据同步。另外我看了下操作系统日志和mysql进程在这段时间都没有重启或中断过:
主库:
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000248 | 83739904 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从库:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.74
Master_User: backup
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000248
Read_Master_Log_Pos: 83739904
Relay_Log_File: mysqld-relay-bin.000062
Relay_Log_Pos: 60328521
Relay_Master_Log_File: mysql-bin.000247
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 60328376
Relay_Log_Space: 153523245
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: 78533
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)