![MariaDB 复制不会自动重新连接](https://linux22.com/image/802809/MariaDB%20%E5%A4%8D%E5%88%B6%E4%B8%8D%E4%BC%9A%E8%87%AA%E5%8A%A8%E9%87%8D%E6%96%B0%E8%BF%9E%E6%8E%A5.png)
我在两台 MariaDB 5.5 服务器之间建立了一个简单的主从复制设置。服务器之间的连接有些不稳定,时不时会导致断开连接。
然而,在从属服务器上的 my.ini 中设置了“master_retry_count=0”,根据我的理解,这意味着从属服务器应该在断开连接时继续尝试重新连接到主服务器,因此不稳定的连接实际上不应该有任何重大问题。
然而,我曾经历过几次复制停止,并出现以下错误:
从属 I/O 线程停止,因为主服务器上的 SET @master_heartbeat_period 失败。错误:查询期间与 MySQL 服务器失去连接
当我注意到这一点时,复制通常处于停止状态数小时,并且只需发出“START SLAVE”查询即可使事情再次正常,但我不明白为什么会出现这个问题。
有人碰巧有经验或能够对此有所启发吗?有什么方法可以确保复制不会因为临时连接问题而停止?
以下是包含错误的示例日志:
130710 2:42:29 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130710 2:42:30 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000341' at position 20051884
130710 2:56:26 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130710 2:56:26 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000341' at position 20523725
130710 2:57:04 [ERROR] Slave I/O: error reconnecting to master 'rep_user@master_server:3306' - retry-time: 60 retries: 0 message: Lost connection to MySQL server at 'reading au
thorization packet', system error: 0, Error_code: 2013
130710 2:58:05 [Note] Slave: connected to master 'rep_user@master_server:3306',replication resumed in log 'mysql-bin.000341' at position 20523725
130710 3:26:31 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130710 3:26:31 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000341' at position 20893274
130710 3:27:48 [ERROR] Slave I/O: error reconnecting to master 'rep_user@master_server:3306' - retry-time: 60 retries: 0 message: Lost connection to MySQL server at 'reading authorization packet', system error: 0, Error_code: 2013
130710 3:31:01 [Note] Slave: connected to master 'rep_user@master_server:3306',replication resumed in log 'mysql-bin.000341' at position 20893274
130710 3:32:19 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
130710 3:32:19 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000341' at position 21083757
130710 3:48:03 [ERROR] Slave I/O: The slave I/O thread stops because SET @master_heartbeat_period on master failed. Error: Lost connection to MySQL server during query, Error_code: 1593
130710 3:48:03 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000341', position 21083757
(从上面的日志来看,连接确实很差;通常不会这么糟糕,但时有发生。修复连接显然是理想的,但我更希望复制不要因此而停止)
从属设备上可能相关的 my.ini 设置:
master_retry_count=0
slave_sql_verify_checksum=0
slave_net_timeout=1800
net_retry_count=10
我对心跳不太了解,所以从来没有设置过,但是根据状态看,心跳周期是900秒。
谢谢阅读。
答案1
看起来这是 MariaDB 和 MySQL 中的一个错误,已在 MySQL 5.6 中修复。
MariaDB 中的修复将在此处跟踪:https://jira.mariadb.org/browse/MDEV-6188