硬重启导致MySQL复制中断

我遇到了我的复制设置问题。 在正常情况下，它会按照预期运行，但如果遇到硬重启（我拉动电源线）就会中断。

build立

该设置包含两个服务器， server-1和server-2 。 复制设置是一个循环复制。 两台服务器都是从另一台主机复制的从服务器，就好像它是主服务器。

软件

• 红帽6.5
• MySQL 5.1.73-3
• MySQL-libs 5.1.73-3
• MySQL服务器5.1.73-3

冗余configuration

对于server-1 ，复制configuration如下所示，对于server-2 ，镜像为：

GRANT REPLICATION SLAVE ON *.* TO 'replicant'; FLUSH TABLES WITH READ LOCK;" #Use "SHOW MASTER STATUS \G;" to determine log position and log file UNLOCK TABLES; # set status on slave server using the values retieved earlier CHANGE MASTER TO MASTER_HOST='server-2', MASTER_USER='replicant', MASTER_LOG_FILE='[log file]', MASTER_LOG_POS=[log pos];" START SLAVE; 

错误信息

当server-2遇到硬重启并且复制中断时，将检索此信息。

• 非常高的RDS MySQL内存
• 为MySQL Master-Master复制设置keepalived
• MySQL的故事+崩溃
• iptables：远程访问mysql
• 连接到远程MySQL服务器 – 连接被拒绝

服务器1从属状态

 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: server-2 Master_User: replicant Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000004 Read_Master_Log_Pos: 37550 Relay_Log_File: relay.000427 Relay_Log_Pos: 245 Relay_Master_Log_File: bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 37550 Relay_Log_Space: 529 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 

服务器2从属状态

 *************************** 1. row *************************** Slave_IO_State: Master_Host: server-1 Master_User: replicant Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin.000002 Read_Master_Log_Pos: 7208 Relay_Log_File: relay.000010 Relay_Log_Pos: 245 Relay_Master_Log_File: bin.000002 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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: 7208 Relay_Log_Space: 529 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 

来自server-2的相关信息mysql-log-error.log

这些日志来自导致复制失败的启动

 150821 12:52:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 150821 12:52:35 InnoDB: Initializing buffer pool, size = 8.0M 150821 12:52:35 InnoDB: Completed initialization of buffer pool InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 150821 12:52:35 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Last MySQL binlog file position 0 106, file name /var/lib/mysql/bin.000002 150821 12:52:35 InnoDB: Started; log sequence number 0 259987 150821 12:52:35 [Note] Recovering after a crash using /var/lib/mysql/bin 150821 12:52:35 [Note] Starting crash recovery... 150821 12:52:35 [Note] Crash recovery finished. 150821 12:52:35 [ERROR] log listed in the index, but failed to stat 150821 12:52:35 [ERROR] Error counting relay log space 150821 12:52:35 [ERROR] Failed to initialize the master info structure 150821 12:52:35 [Note] Event Scheduler: Loaded 0 events 150821 12:52:35 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.1.73-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution 

复制者访问

为了收集信息并解决问题，我试图从服务器2代表复制用户访问server-1 。 这个尝试不成功，我得到了一个“ 访问被拒绝 ”的信息。

因此，我查看了服务器2上的授权，并获得了以下信息。

 SHOW GRANTS FOR "replicant"@"server-1"; ERROR 1141 (42000) at line 1: There is no such grant defined for user 'replicant' on host 'server-1;' 

我试图find一个解决scheme

我迄今为止解决这个问题的尝试是徒劳的。 无论我尝试什么，我总是从MySQL获得相同的答案。

 ERROR 1201 (HY000) at line 1: Could not initialize master info structure; more error messages can be found in the MySQL error log 

当我尝试“ START SLAVE; ”或“ CHANGE MASTER TO … ”时，我尝试过的任何configuration，FLUSH，RESET或日志操作都会导致出现此错误消息，以使复制再次进行。

复制服务器1

使server-2成为server-1的副本是有希望的。 我得到服务器2工作，但服务器1无法从服务器2复制。 我使用以下步骤复制服务器。

1. 停止server-2上的mysqld服务
2. 在服务器-2上清除/ var / lib / mysql /
3. rsync / var / lib / mysql /从server-1到server-2
4. 将服务器2上的主控主机更改为服务器1
5. 在服务器2上启动mysqld服务

看起来这是伎俩服务器-2 ，但服务器-1无法从服务器-2复制。 我在服务器1的slave状态中看到以下内容：

  Slave_IO_state: Waiting to reconnect after a failed master even read Slave_IO_Running: No 

我在服务器1上的错误日志中find这个

 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'FIRST' at position 4 [ERROR] Error reading packet from server: Access denied; you need the REPLICATION SLAVE privilege for this operation ( server_errno=1227) 

我已经检查了两台服务器上的复制用户的授权，他们没有改变。

 mysql> SHOW GRANTS FOR 'replicant'; +---------------------------------------------------+ | Grants for replicant@% | +---------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'replicant'@'%' | +---------------------------------------------------+ 1 row in set (0.00 sec) 

看起来server-1在访问server-2上的日志时遇到问题。 我试图解决这个问题。

• RESET MASTER; + 重置从属; 在两台服务器上，然后再使用CHANGE MASTER TO …设置从站; 。
• 在rsync之前更改server-1上的MASTER_LOG_FILE和MASTER_LOG_POS以匹配server-1的主服务器状态。
• 尝试我能想到的MASTER_LOG_FILE和MASTER_LOG_POS操作的每一个排列组合。