I have MySQL running on Debian 5 (Lenny) between two MySQL databases. The data is being transferring between the two machines, but one of them is logging the fact that it's slave I/O thread is constantly dying and then right away being re-initialized.
A sample of the log entries in question:
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 'replicator@10.10.95.28: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 'replicator@10.10.95.28: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
However, even with the above errors, the machine "foo-db2" is keeping up with "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)
That being said, the "SHOW SLAVE STATUS" output yo-yos between "Slave_IO_Running=Yes" and "Slave_SQL_Running=Yes", and being "No."
Lastly, the "mysql-relay-bin.######" file is constantly a higher number every few seconds, but the file is always 235 bytes:
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*/;
max_binlog_size? – quanta Jul 31 '11 at 6:24