[ERROR]SlaveI/O:errorconnectingtomaster
剛配置的MySQL主從,在從機上看到
點選(此處)摺疊或開啟
mysql> SHOW slave STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.17.210.199
Master_User: my
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: masters-bin.000003
Read_Master_Log_Pos: 1224
Relay_Log_File: testmysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: masters-bin.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
看日誌有很多:
點選(此處)摺疊或開啟
141010 0:02:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 1, Error_code: 2003
141010 0:03:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 2, Error_code: 2003
141010 0:04:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 3, Error_code: 2003
141010 0:05:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 4, Error_code: 2003
141010 0:06:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 5, Error_code: 2003
141010 0:07:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 6, Error_code: 2003
解決方法:
導致lave_IO_Running 為connecting 的原因主要有以下 3 個方面:
1、網路不通
2、密碼不對
3、pos不對
解決步驟:
1、對於第一個問題,一般情況下都是可以排除的,也是最容易排除的。
2、在主庫上修改用來複制的使用者的密碼。
3、 在做chang to 的時候注意log_pos 是否跟此時主機的一樣。在主機上 show master status G ;可以檢視到
mysql> show master status G;
*************************** 1. row ***************************
File: masters-bin.000003
Position: 2392
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
從機上面執行change to 的正確
CHANGE MASTER TO
MASTER_HOST=`172.17.210.199`,
MASTER_USER=`my`,
MASTER_PASSWORD=`123456`,
MASTER_LOG_FILE=`masters-bin.000003`,
MASTER_LOG_POS= 2392;
我的問題,就是主機的防火牆沒有關閉
====================================================================
由於主伺服器異外重啟, 導致從報錯, 錯誤如下:
show slave status錯誤:
mysql> show slave statusG
Master_Log_File: mysql-bin.000288
Read_Master_Log_Pos: 627806304
Relay_Log_File: mysql-relay-bin.000990
Relay_Log_Pos: 627806457
Relay_Master_Log_File: mysql-bin.000288
Slave_IO_Running: No
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 627806304
Relay_Log_Space: 627806663
……
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
`Client requested master to start replication from impossible position`
mysql錯誤日誌:
tail /data/mysql/mysql-error.log
111010 17:35:49 [ERROR] Error reading packet from server: Client requested master
to start replication from impossible position ( server_errno=1236)
111010 17:35:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data
from binary log: `Client requested master to start replication from impossible
position`, Error_code: 1236
111010 17:35:49 [Note] Slave I/O thread exiting, read up to log `mysql-bin.000288`,
position 627806304
按照習慣, 先嚐試必改position位置.
mysql> stop slave;
mysql> change master to master_log_file=`mysql-bin.000288`,master_log_pos=627625751;
mysql> start slave;
錯誤依舊, 接下來登陸到主伺服器檢視binlog日誌.
先按照錯誤點的標記去主伺服器日誌中查詢:
[root@db1 ~]# mysqlbinlog –start-position=627655136 /data/mysql/binlog/mysql-bin.000288
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111010 13:31:19 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log
created 111010 13:31:19
# Warning: this binlog is either in use or was not closed properly.
BINLOG `
F1aTTg8EAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
`/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
沒有看到這個位置.
[root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > test.txt
less text.txt
看最後一部分
# at 627625495
#111010 16:35:46 server id 1 end_log_pos 627625631 Query thread_id=45613333
exec_time=32758 error_code=0
SET TIMESTAMP=1318289746/*!*/;
delete from freeshipping_bef_update where part=`AR-4006WLM` and code=“
/*!*/;
# at 627625631
#111010 16:35:46 server id 1 end_log_pos 627625751 Query thread_id=45613333
exec_time=32758 error_code=0
SET TIMESTAMP=1318289746/*!*/;
delete from shippingFee_special where part=`AR-4006WLM`
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
找到最接近錯誤標記627655136的一個position是627625631.
再回到slave機器上change master, 將postion指向這個位置.
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_log_file=`mysql-bin.000288`,master_log_pos=627625631;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
再次檢視
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.21.105
Master_User: rep
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000289
Read_Master_Log_Pos: 25433767
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 630
Relay_Master_Log_File: mysql-bin.000289
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主從同步正常了, 同樣的方法修復其它slave機器.
點選(此處)摺疊或開啟
mysql> SHOW slave STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.17.210.199
Master_User: my
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: masters-bin.000003
Read_Master_Log_Pos: 1224
Relay_Log_File: testmysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: masters-bin.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
看日誌有很多:
點選(此處)摺疊或開啟
141010 0:02:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 1, Error_code: 2003
141010 0:03:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 2, Error_code: 2003
141010 0:04:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 3, Error_code: 2003
141010 0:05:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 4, Error_code: 2003
141010 0:06:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 5, Error_code: 2003
141010 0:07:48 [ERROR] Slave I/O: error connecting to master `my@172.17.210.199:3306` – retry-time: 60 retries: 6, Error_code: 2003
解決方法:
導致lave_IO_Running 為connecting 的原因主要有以下 3 個方面:
1、網路不通
2、密碼不對
3、pos不對
解決步驟:
1、對於第一個問題,一般情況下都是可以排除的,也是最容易排除的。
2、在主庫上修改用來複制的使用者的密碼。
3、 在做chang to 的時候注意log_pos 是否跟此時主機的一樣。在主機上 show master status G ;可以檢視到
mysql> show master status G;
*************************** 1. row ***************************
File: masters-bin.000003
Position: 2392
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
從機上面執行change to 的正確
CHANGE MASTER TO
MASTER_HOST=`172.17.210.199`,
MASTER_USER=`my`,
MASTER_PASSWORD=`123456`,
MASTER_LOG_FILE=`masters-bin.000003`,
MASTER_LOG_POS= 2392;
我的問題,就是主機的防火牆沒有關閉
====================================================================
由於主伺服器異外重啟, 導致從報錯, 錯誤如下:
show slave status錯誤:
mysql> show slave statusG
Master_Log_File: mysql-bin.000288
Read_Master_Log_Pos: 627806304
Relay_Log_File: mysql-relay-bin.000990
Relay_Log_Pos: 627806457
Relay_Master_Log_File: mysql-bin.000288
Slave_IO_Running: No
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 627806304
Relay_Log_Space: 627806663
……
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
`Client requested master to start replication from impossible position`
mysql錯誤日誌:
tail /data/mysql/mysql-error.log
111010 17:35:49 [ERROR] Error reading packet from server: Client requested master
to start replication from impossible position ( server_errno=1236)
111010 17:35:49 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data
from binary log: `Client requested master to start replication from impossible
position`, Error_code: 1236
111010 17:35:49 [Note] Slave I/O thread exiting, read up to log `mysql-bin.000288`,
position 627806304
按照習慣, 先嚐試必改position位置.
mysql> stop slave;
mysql> change master to master_log_file=`mysql-bin.000288`,master_log_pos=627625751;
mysql> start slave;
錯誤依舊, 接下來登陸到主伺服器檢視binlog日誌.
先按照錯誤點的標記去主伺服器日誌中查詢:
[root@db1 ~]# mysqlbinlog –start-position=627655136 /data/mysql/binlog/mysql-bin.000288
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#111010 13:31:19 server id 4 end_log_pos 106 Start: binlog v 4, server v 5.1.45-log
created 111010 13:31:19
# Warning: this binlog is either in use or was not closed properly.
BINLOG `
F1aTTg8EAAAAZgAAAGoAAAABAAQANS4xLjQ1LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
`/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
沒有看到這個位置.
[root@db1 ~]# mysqlbinlog /data/mysql/binlog/mysql-bin.000288 > test.txt
less text.txt
看最後一部分
# at 627625495
#111010 16:35:46 server id 1 end_log_pos 627625631 Query thread_id=45613333
exec_time=32758 error_code=0
SET TIMESTAMP=1318289746/*!*/;
delete from freeshipping_bef_update where part=`AR-4006WLM` and code=“
/*!*/;
# at 627625631
#111010 16:35:46 server id 1 end_log_pos 627625751 Query thread_id=45613333
exec_time=32758 error_code=0
SET TIMESTAMP=1318289746/*!*/;
delete from shippingFee_special where part=`AR-4006WLM`
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
找到最接近錯誤標記627655136的一個position是627625631.
再回到slave機器上change master, 將postion指向這個位置.
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_log_file=`mysql-bin.000288`,master_log_pos=627625631;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
再次檢視
mysql> show slave statusG
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 192.168.21.105
Master_User: rep
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000289
Read_Master_Log_Pos: 25433767
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 630
Relay_Master_Log_File: mysql-bin.000289
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主從同步正常了, 同樣的方法修復其它slave機器.
相關文章
- [ERROR] Slave I/O: Replication event checksum Error_code: 1743Error
- Last_IO_Error:errorconnectingtomaster'tl@192.168.199.151:3306'ASTError
- InnoDB: Error: space id and page n:o stored in the page?Error
- sloaris 出現“mount: I/O error”錯誤Error
- O/S-Error: (OS 33) The process cannot access the fileError
- [Modules/zlibmodule.o] Error 1錯誤解決IBMError
- error ntcontab.o of makefile ins_net_client.mkErrorclient
- VNC installation error libX11.a(shr4.o) isVNCError
- 執行SQL發生錯誤!錯誤:disk I/O errorSQLError
- ORA-27102 O/S ERROR: (OS 1445)..... trouble shootingError
- org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.SQLExceptionErrorWhile
- yarn add @fortawesome/Font Awesome-free 之後 報錯 EIO: i/o errorYarnError
- Error: Invoke-customs are only supported starting with Android O (--min-api 26)ErrorAndroidAPI
- Could Not Save Service Alias: TNS-04415 : File I/O Error [ID 798206.1]Error
- o(* ̄- ̄*)o
- MySQL [ERROR] Slave I/O: Found a Gtid_log_event or Previous_gtids_log_eventMySqlError
- o2o系統 本地生活服務 微信o2o
- 演算法中的O(1), O(n),O(logn), O(nlogn)演算法
- Xcode - Error ITMS-90635 - Invalid Mach-O in bundle - submitting to App storeXCodeErrorMacMITAPP
- PBOOTCMS網站程式提示“執行SQL發生錯誤!錯誤:DISK I/O ERROR”boot網站SQLError
- 時間複雜度O(1)、O(n)、O(n²)、O(nlogn)的含義時間複雜度
- Error: CreateProcess failed, Command: ‘C:\Keil_v5\ARM\ARMCC\bin\fromelf.exe --bin -o ..\OBJ\outputErrorAIOBJ
- 一起做RGB-D-SLAM(6)g2o報錯error: no matching functionSLAMErrorFunction
- 安裝as10g3的售後報錯Error in invoking target 'ntcontab.o' of makefileError
- ORA-01115 ORA-01110 ORA-27091 ORA-27072 Error: 5: I/O error (Doc ID 559697.1)Error
- 佳弗O2O系統
- Veritas Quick I/O and Cached Quick I/OUI
- ORACLE OMS啟動失敗之BEA-300040 I/O error while writing node manager statusOracleErrorWhile
- 計算機I/O與I/O模型計算機模型
- 12張圖詳解O2O模式模式
- I/O埠和I/O記憶體記憶體
- 高投入佈局O2O致使股價大跌,百度O2O是否仍有機會
- 女性社交O2O平臺如何打造?
- Mach-OMac
- Java I/OJava
- 故障案例:主從同步報錯Fatal error: The slave I/O thread stops because master and slave have equal MySQL server主從同步ErrorthreadASTMySqlServer
- O/S-Error: (OS 1326) 登入失敗: 未知的使用者名稱或錯誤密碼。Error密碼
- 請問PBOOTCMS網站程式提示“執行SQL發生錯誤!錯誤:DISK I/O ERROR”怎麼辦boot網站SQLError