我在 Debian 5 (Lenny) 上的两个 MySQL 数据库之间运行 MySQL。数据正在两台机器之间传输,但其中一台机器记录了这样一个事实:它的从属 I/O 线程不断死亡,然后立即被重新初始化。
有问题的日志条目示例:
Jul 19 08:15:43 foo-db2 mysqld[22419]: 110719 8:15:43 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 2107802, relay log '/var/log/mysql/mysql-relay-bin.018257' position: 235
Jul 19 08:15:43 foo-db2 mysqld[22419]: 110719 8:15:43 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-bin.000002' at position 2107802
Jul 19 08:15:44 foo-db2 mysqld[22419]: 110719 8:15:44 [Note] Slave I/O thread killed while reading event
Jul 19 08:15:44 foo-db2 mysqld[22419]: 110719 8:15:44 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000002', position 2107802
Jul 19 08:15:44 foo-db2 mysqld[22419]: 110719 8:15:44 [Note] Error reading relay log event: slave SQL thread was killed
Jul 19 08:15:46 foo-db2 mysqld[22419]: 110719 8:15:46 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 2107802, relay log '/var/log/mysql/mysql-relay-bin.018258' position: 235
Jul 19 08:15:46 foo-db2 mysqld[22419]: 110719 8:15:46 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-bin.000002' at position 2107802
Jul 19 08:15:47 foo-db2 mysqld[22419]: 110719 8:15:47 [Note] Slave I/O thread killed while reading event
Jul 19 08:15:47 foo-db2 mysqld[22419]: 110719 8:15:47 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000002', position 2107802
Jul 19 08:15:47 foo-db2 mysqld[22419]: 110719 8:15:47 [Note] Error reading relay log event: slave SQL thread was killed
但是,即使出现上述错误,机器“foo-db2”仍能跟上“foo-db1”的步伐:
mysql@foo-db2> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.95.28
Master_User: replicator
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2229610
Relay_Log_File: mysql-relay-bin.018379
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[...]
mysql@foo-db1> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 2229610 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
话虽如此,“SHOW SLAVE STATUS”输出在“Slave_IO_Running=Yes”和“Slave_SQL_Running=Yes”之间摇摆不定,并且为“No”。
最后,“mysql-relay-bin.######”文件的数字每隔几秒钟就会不断增加,但该文件始终为 235 字节:
root@foo-db2:/var/log/mysql# mysqlbinlog mysql-relay-bin.0*
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#110719 8:25:40 server id 2 end_log_pos 98 Start: binlog v 4, server v 5.0.51a-24+lenny4-log created 110719 8:25:40
# at 98
#691231 19:00:00 server id 1 end_log_pos 0 Rotate to mysql-bin.000002 pos: 2229610
# at 141
#110719 6:25:02 server id 1 end_log_pos 0 Start: binlog v 4, server v 5.0.51a-24+lenny4-log created 110719 6:25:02
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;