我的复制设置遇到问题。在正常情况下，它会按预期运行，但是如果遇到硬重启（我拔下电源线），它会破裂。
设定
该设置包括两个服务器，服务器1和服务器2。复制设置是循环复制。两台服务器都是从另一台主机复制的从机，就好像它是主服务器一样。
软件
- 红帽6.5
- MySQL 5.1.73-3
- MySQL库5.1.73-3
- MySQL服务器5.1.73-3
冗余配置
服务器1的复制配置如下，服务器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遇到硬重启并且复制中断时，将检索此信息。
服务器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:
来自服务器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
副本访问
为了收集信息并解决问题，我尝试执行的工作之一就是代表复制用户从server-2访问server-1。该尝试未成功，并且收到“拒绝访问”消息。
因此，我研究了server-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;'
我寻找解决方案的尝试
迄今为止，我为解决该问题所做的尝试没有任何成果。不管我尝试什么，我总是从MySQL得到相同的答案。
ERROR 1201 (HY000) at line 1: Could not initialize master info structure; more error messages can be found in the MySQL error log
当我尝试“启动从设备；”时，我尝试的任何配置，FLUSH，RESET或日志操作都导致此错误消息。或“将主文件更改为...”以使复制再次进行。
复制服务器1
使server-2成为server-1的副本很有希望。我使服务器2正常工作，但是服务器1无法从服务器2复制。我使用以下步骤复制服务器。
- 在服务器2上停止mysqld服务
- 在服务器2上清除/ var / lib / mysql /
- rsync / var / lib / mysql /从server-1到server-2
- 将服务器2上的主控主机更改为服务器1
- 在服务器2上启动mysqld服务
看起来这是欺骗服务器2的方法，但是服务器1无法从服务器2复制。我在server-1的从属状态中看到以下内容：
Slave_IO_state: Waiting to reconnect after a failed master even read
Slave_IO_Running: No
我在服务器1的错误日志中找到了这个
[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操作的每个排列。