【Mysql】Slave_IO_Running: No---Got fatal error 1236 from master
本地MySQL環境，是兩臺MySQL做M-M複製。今天發現錯誤資訊：
mysql 5.5.28-log> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 88.88.88.88
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: testdbbinlog.000005
Read_Master_Log_Pos: 98359687
Relay_Log_File: mysql-relay-bin.000020
Relay_Log_Pos: 4
Relay_Master_Log_File: testdbbinlog.000005
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: 98359687
Relay_Log_Space: 107
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: 1
1 row in set (0.00 sec)
mysql 5.5.28-log>
參考網上文件： http://blog.csdn.net/billfanggs/article/details/8905991
在source那邊，執行：
flush logs;
show master status;
記下File, Position。 ---記錄一個新的binlog
在target端，執行：
CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=107; ---從新的binlog開始複製，跳過舊的binlog。
slave start;
show slave status \G
一切正常。 但是會有資料丟失，可以檢查後重新搭建從庫
如果使用了GTID新的特新
（原英文地址https://www.percona.com/blog/2013/02/08/how-to-createrestore-a-slave-using-gtid-replication-in-mysql-5-6/）
How to restore a slave in a bad and fast way（快速但不好的方法）
Let’s imagine that our slave has been down for several days and the binary logs from the master have been purged. This is the error we are going to
點選(此處)摺疊或開啟
-
Slave_IO_Running: No
-
Slave_SQL_Running: Yes
- Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
-
master > show global variables like 'GTID%';
-
+---------------+-------------------------------------------+
-
| Variable_name | Value |
-
+---------------+-------------------------------------------+
-
| gtid_executed | 9a511b7b-7059-11e2-9a24-08002762b8af:1-14 |
- +---------------+-------------------------------------------+
And we set it on the slave:
點選(此處)摺疊或開啟
-
slave> set global GTID_EXECUTED="9a511b7b-7059-11e2-9a24-08002762b8af:2"
- ERROR 1238 (HY000): Variable 'gtid_executed' is a read only variable
Error! Remember, we get the GTID_EXECUTED from the master and set is as GTID_PURGED on the slave
點選(此處)摺疊或開啟
-
slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:2";
- ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.
Error again, GTID_EXECUTED should be empty before changing GTID_PURGED manually but we can’t change it with SET because is a read only variable. The only way to change it is with reset master (yes, on a slave server):
-
slave1> reset master;
-
slave1 > show global variables like 'GTID_EXECUTED';
-
+---------------+-------+
-
| Variable_name | Value |
-
+---------------+-------+
-
| gtid_executed | |
- +---------------+-------+
- slave1 > set global GTID_PURGED="9a511b7b-7059-11e2-9a24-08002762b8af:2"; 可透過這篇文章來熟悉一下 http://www.cnblogs.com/cenalulu/p/4309009.html
- 思路：將丟失的binlog放進purge，表示已刪除，跳過報錯的，英文原版的有點特殊：剛做個slave就報錯，slave的purge已經到13了，所以使用的是execute14
- 生產上一般是執行過程中報錯，因為主庫的binarylog已經被purge 但是還沒應用到slave，可以我們可以檢視一下 master與slave的purge，很明顯slave的purge絕壁落後著
-
-
-
slave1> start slave io_thread;
-
slave1> show slave status\G
-
[...]
-
Slave_IO_Running: Yes
-
Slave_SQL_Running: Yes
- [...]
How to restore a slave in a good and slow way（慢但是比較好的方法）
The good way is mysqldump again. We take a dump from the master like we saw before and try to restore it on the slave
-
slave1 [localhost] {msandbox} ((none)) > source test.sql; ---匯入搭建時的全備
-
[...]
-
ERROR 1840 (HY000): GTID_PURGED can only be set when GTID_EXECUTED is empty.
- [...]
Wop! It is important to mention that these kind of error messages can dissapear on the shell buffer because the restore of the dump will continue. Be cautious.
Same problem again so same solution too:
點選(此處)摺疊或開啟
-
slave1> reset master;
-
slave1> source test.sql; ----重新到slave庫導搭建是的那個全備一遍。。相當於搭建master-slave中的那一步
-
slave1> start slave;
-
slave1> show slave status\G
-
[...]
-
Slave_IO_Running: Yes
-
Slave_SQL_Running: Yes
- [...]
模擬實驗
- slave ：stop slave，此時Master_Log_File: mysqlbin.000011 Relay_Master_Log_File: mysqlbin.000011 都停留在binlog 11這個日誌上
-
- master：
-
(root@127.0.0.1) [test]> insert into t1 select * from t1; --------11 binlog裡面記錄了insert8條的記錄
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
(root@127.0.0.1) [test]> flush logs;
Query OK, 0 rows affected (0.02 sec)
(root@127.0.0.1) [test]> insert into t1 select * from t1;
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
(root@127.0.0.1) [test]> show master status; -------12 binlog 裡面記錄了insert 16條的記錄
+-----------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+------------------------------------------+
| mysqlbin.000012 | 567 | | | 8a0d06d7-91cb-11e5-843b-00163ec09859:1-7 |
+-----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
(root@127.0.0.1) [test]> flush logs;
Query OK, 0 rows affected (0.01 sec)
(root@127.0.0.1) [test]> insert into t1 select * from t1; ------13 binlog裡面記錄了insert 32條的記錄
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
(root@127.0.0.1) [test]> flush logs;
Query OK, 0 rows affected (0.02 sec)
(root@127.0.0.1) [test]> show master status;
+-----------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+------------------------------------------+
| mysqlbin.000014 | 191 | | | 8a0d06d7-91cb-11e5-843b-00163ec09859:1-8 |
+-----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
(root@127.0.0.1) [test]> purge master logs to 'mysqlbin.000012'; ---記錄8條的binlog沒了
Query OK, 0 rows affected (0.01 sec)
(root@127.0.0.1) [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
(root@127.0.0.1) [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 218.78.186.162
Master_User: repluser
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysqlbin.000011
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-relay-bin.000014
Relay_Log_Pos: 399
Relay_Master_Log_File: mysqlbin.000011
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: 191
Relay_Log_Space: 689
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: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 13308
Master_UUID: 8a0d06d7-91cb-11e5-843b-00163ec09859
Master_Info_File: /data/mydata/3309/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: 160519 15:06:15
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8a0d06d7-91cb-11e5-843b-00163ec09859:1-5
Executed_Gtid_Set: 8a0d06d7-91cb-11e5-843b-00163ec09859:1-5
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified
解決辦法：
master：
(root@127.0.0.1) [test]> show variables like 'gtid%';
+---------------+------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------+
| gtid_executed | |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | 8a0d06d7-91cb-11e5-843b-00163ec09859:1-6 |
+---------------+------------------------------------------+
slave：
(root@127.0.0.1) [(none)]> reset master;
Query OK, 0 rows affected (0.02 sec)
(root@127.0.0.1) [(none)]> set global gtid_purged='8a0d06d7-91cb-11e5-843b-00163ec09859:1-6';
Query OK, 0 rows affected (0.02 sec)
(root@127.0.0.1) [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
(root@127.0.0.1) [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 218.78.186.162
Master_User: repluser
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysqlbin.000014
Read_Master_Log_Pos: 191
Relay_Log_File: mysql-relay-bin.000019
Relay_Log_Pos: 415
Relay_Master_Log_File: mysqlbin.000014
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: 191
Relay_Log_Space: 721
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: 13308
Master_UUID: 8a0d06d7-91cb-11e5-843b-00163ec09859
Master_Info_File: /data/mydata/3309/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: 8a0d06d7-91cb-11e5-843b-00163ec09859:1-5:7-8 ---出現斷層了，8a0d06d7-91cb-11e5-843b-00163ec09859:6 被我們purge了
Executed_Gtid_Set: 8a0d06d7-91cb-11e5-843b-00163ec09859:1-8
Auto_Position: 1
1 row in set (0.00 sec)
ERROR:
No query specified
(root@127.0.0.1) [test]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 56 | -----只有56條，剛好記錄的那8條被purge了，資料丟失了！
+----------+
1 row in set (0.00 sec)
當線上出現1236的錯誤後可以這樣解決，但是解決完後要檢查一下主從資料的一致性，不然會留下隱患！可能後面會導致1023等一些列錯誤
各種導致1236的原因！：http://blog.itpub.net/22664653/viewspace-1714269/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29096438/viewspace-1808376/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL案例09:Last_IO_Error: Got fatal error 1236 from master when reading data from binary logMySqlASTErrorGo
- MySQL主從複製報錯：Got fatal error 1236 from master when reading data fromMySqlGoErrorAST
- Last_IO_Error: Got fatal error 1236ASTErrorGo
- MySQL主從同步報error 1236MySql主從同步Error
- fatal: Not a valid object name: 'master'ObjectAST
- 故障案例：主從同步報錯Fatal error: The slave I/O thread stops because master and slave have equal MySQL server主從同步ErrorthreadASTMySqlServer
- FATAL - Fatal error: Target Interaction Manager failed at StartupErrorAI
- MySQL報錯Slave: received end packet from server, apparent master shutdownMySqlServerAPPAST
- MySQL 8.0.26 bug ERROR 1064(42000) -master-data is deprecated and will be removeMySqlErrorASTREM
- [Code Composer Studio] fatal error #6001Error
- Mysql：1236常見錯誤MySql
- Renaming the default branch from master to main on GithubASTAIGithub
- Fatal error in launcher: Unable to create process using '"'Error
- Fatal NI connect error 12170 錯誤Error
- Fatal error: Uncaught PDOException: could not find driverErrorException
- Fatal error: Cannot redeclare printerror() (previously declared in …Error
- fatal error: libmemcached/memcached.h: No such file or directoryErrorIBM
- fatal error: openssl/sha.h: No such file or directoryError
- fatal error: sys/cdefs.h: No such file or directoryError
- PHP Fatal error: Allowed memory size of 1610612736 bytesPHPError
- Thread 1: Fatal error: init(coder:) has not been implementedthreadError
- 如何修復 “fatal error: security/pam_modError
- git push origin master 時出現報錯 error: src refspec master does not match any error: failed to push someGitASTErrorAI
- git@github.com: Permission denied (publickey). fatal: Could not read from remoteGithubREM
- Fatal error: can't check configuration file '/home/watt/proxy/dError
- fatal: [192.168.65.128]: UNREACHABLE! => {"changed": false, "msg": "SSH Error: data could not be senFalseError
- git push fatal: HttpRequestException encountered. An error occurred while sending the requestGitHTTPExceptionErrorWhile
- 解決docker: Error response from daemon故障DockerError
- Docker安裝MySQL8.0.39報錯：Fatal glibc error: CPU does not support x86-64-v2DockerMySqlError
- git@github.com: Permission denied (publickey). fatal: Could not read from remote repository.GithubREM
- MySQL資料庫1236錯誤模擬和解決MySql資料庫
- Ubuntu下 fatal error: Python.h: No such file or directory 解決方法UbuntuErrorPython
- [ERROR] [MY-012872] [InnoDB] [FATAL] Semaphore wait has lasted > 600 secondsErrorAIAST
- 解決ERROR 1030 (HY000): Got error 168 from storage engine apparmorErrorGoAPP
- PSQLException: ERROR: failed to find conversion function from unknown to charSQLExceptionErrorAIFunction
- mysql 8 報錯 ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repositoryMySqlErrorAIStruct
- golang原始碼安裝時fatal error: MSpanList_Insert錯誤Golang原始碼Error
- Composer 記憶體不足解決方案 PHP Fatal error: Out of memory記憶體PHPError
- windows終端輸入pip install requests報錯：Fatal error in launcherWindowsError