MySQL 复制:从属 I/O 线程不断死亡并重新启动

MySQL 复制:从属 I/O 线程不断死亡并重新启动

我在 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*/;

相关内容