Percona MySQL 5.6 主主複製環境報錯"Got fatal error 1236 from master.."
Percona MySQL 5.6 主主複製環境報錯:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000153
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000153
Slave_IO_Running: No
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: 120
Relay_Log_Space: 601
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160809 11:42:02
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
報錯原因:
Master端二進位制日誌被刪除,導致Slave端的IO執行緒無法在Master端讀取到指定二進位制日誌
mysql> show variables like '%log%bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog/mysqld-bin |
| log_bin_index | /var/lib/mysql/binlog/mysqld-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
[root@dmcdb2 ~]# cd /var/lib/mysql/binlog
[root@dmcdb2 binlog]# ls -trl
total 1613364
-rw-r--r-- 1 root root 1651957529 Feb 17 14:29 3962.sql
-rw-rw---- 1 mysql mysql 40 Aug 9 11:16 mysqld-bin.index
-rw-rw---- 1 mysql mysql 174 Aug 9 14:17 mysqld-relay-bin.000001
-rw-rw---- 1 mysql mysql 92 Aug 9 14:17 mysqld-relay-bin.index
-rw-rw---- 1 mysql mysql 461 Aug 9 14:21 mysqld-bin.000155
-rw-rw---- 1 mysql mysql 104500 Aug 9 14:39 mysqld-relay-bin.000002
修復方法:
由於丟失了Master端的二進位制日誌,只能從現有的二進位制日誌重新配置複製環境
--Master端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000155 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--Slave端
mysql> change master to master_host='vsql02', master_port=3306, master_user='mysql_slave', master_password='mysql_slave', master_log_file='mysqld-bin.000155', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000155
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000155
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: 120
Relay_Log_Space: 458
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
--Master端
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000153
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000153
Slave_IO_Running: No
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: 120
Relay_Log_Space: 601
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 160809 11:42:02
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
報錯原因:
Master端二進位制日誌被刪除,導致Slave端的IO執行緒無法在Master端讀取到指定二進位制日誌
mysql> show variables like '%log%bin%';
+---------------------------------+----------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog/mysqld-bin |
| log_bin_index | /var/lib/mysql/binlog/mysqld-bin.index |
| log_bin_trust_function_creators | ON |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------+
6 rows in set (0.00 sec)
[root@dmcdb2 ~]# cd /var/lib/mysql/binlog
[root@dmcdb2 binlog]# ls -trl
total 1613364
-rw-r--r-- 1 root root 1651957529 Feb 17 14:29 3962.sql
-rw-rw---- 1 mysql mysql 40 Aug 9 11:16 mysqld-bin.index
-rw-rw---- 1 mysql mysql 174 Aug 9 14:17 mysqld-relay-bin.000001
-rw-rw---- 1 mysql mysql 92 Aug 9 14:17 mysqld-relay-bin.index
-rw-rw---- 1 mysql mysql 461 Aug 9 14:21 mysqld-bin.000155
-rw-rw---- 1 mysql mysql 104500 Aug 9 14:39 mysqld-relay-bin.000002
修復方法:
由於丟失了Master端的二進位制日誌,只能從現有的二進位制日誌重新配置複製環境
--Master端
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000155 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--Slave端
mysql> change master to master_host='vsql02', master_port=3306, master_user='mysql_slave', master_password='mysql_slave', master_log_file='mysqld-bin.000155', master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: vsql02
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000155
Read_Master_Log_Pos: 120
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: mysqld-bin.000155
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: 120
Relay_Log_Space: 458
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 22
Master_UUID: d49b3291-289a-11e6-9b9b-2c44fd7f8364
Master_Info_File: /data/dmcdb/mysql/database/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
--Master端
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2123211/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL主從複製報錯:Got fatal error 1236 from master when reading data fromMySqlGoErrorAST
- MySQL複製環境Slave報錯"Got fatal error 1236 from master when reading data"MySqlGoErrorAST
- mysql主從同步失敗Last_IO_Error: Got fatal error 1236 from master解決方法MySql主從同步ASTErrorGo
- 主庫異常當機---從庫複製報fatal error 1236Error
- 【Mysql】Slave_IO_Running: No---Got fatal error 1236 from masterMySqlGoErrorAST
- 【MySQL】Got fatal error 1236原因和解決方法MySqlGoError
- Last_IO_Error: Got fatal error 1236ASTErrorGo
- Windows 環境下,MySQL 的主從複製和主主複製WindowsMySql
- windows環境下,Mysql的主從複製和主主複製WindowsMySql
- Got fatal error 1236 from master when reading data from binary logGoErrorAST
- MySQL主從同步報error 1236MySql主從同步Error
- MySQL案例09:Last_IO_Error: Got fatal error 1236 from master when reading data from binary logMySqlASTErrorGo
- Mysql5.6主從複製MySql
- mysql5.6主主複製及keepalived 高可用MySql
- 【MySql】MySQL Replication Fatal Error 1236MySqlError
- MySQL Replication Fatal Error 1236MySqlError
- MySQL 5.7 多主複製報錯Coordinator stopped because there were error(s)MySqlError
- MySQL主從複製與主主複製MySql
- MySQL 主主複製MySql
- mysql5.6.xGTID主從複製配置MySql
- MySQL的主從複製與MySQL的主主複製MySql
- mysql5.7主從複製,主主複製MySql
- MySQL主從複製、半同步複製和主主複製MySql
- Mysql主機斷電導致複製出錯 server_errno=1236MySqlServer
- MySQL主從複製、半同步複製和主主複製概述MySql
- mysql主主複製(雙主複製)配置步驟MySql
- Mysql5.6主從複製-基於binlogMySql
- mysql之 mysql 5.6不停機主主搭建(活躍雙主基於日誌點複製)MySql
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL 5.6複製報錯Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;MySqlError
- MYSQL主主複製的搭建MySql
- MySQL主主複製(雙主複製)配置過程介紹MySql
- MySQL 5.6主主同步MySql
- MySQL雙主複製環境中BINLOG日誌的解析~MySql
- mysql之 mysql 5.6不停機主從搭建(一主一從基於GTID複製)MySql
- MySQL 5.5 主主複製搭建流程MySql
- MySQL 主從複製MySql