mysql last_io_error_[MySQL生產環境複製故障修復] Last_IO_Errno: 1045 Last_IO_Error: error connecting to master ...

最新推荐文章于 2023-09-17 17:37:35 发布

Shichun.Z

最新推荐文章于 2023-09-17 17:37:35 发布

阅读量290

点赞数

文章标签： mysql last_io_error

本文链接：https://blog.csdn.net/weixin_33560311/article/details/113908363

版权

-- 重新設置複製參數

mysql> change master to master_host='10.24.54.18',master_port=3306,master_user='replication',master_password='xxxxxx';

Query OK, 0 rows affected, 0 warnings (0.40 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Connecting to master

Master_Host: 10.24.54.18

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1355028

Relay_Log_File: mysql-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Connecting

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: 1355028

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: 1045

Last_IO_Error: error connecting to master 'replication@10.24.54.18:3306' - retry-time: 60 retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

1 row in set (0.00 sec)

-- 報錯如下： Last_IO_Errno: 1045

ERROR: No query specified

看問題，一開始懷疑是密碼問題，但是密碼都是複製上來的，沒有變動，問題不大，還是看看check下別的情況吧。

2 ，check問題所在，ping ip地址，是OK的。

[xxx@xxx-c004db1 shared]$ ping 10.24.54.18

PING 10.24.54.18 (10.24.54.18) 56(84) bytes of data.

64 bytes from 10.24.54.18: icmp_seq=1 ttl=64 time=0.321 ms

64 bytes from 10.24.54.18: icmp_seq=2 ttl=64 time=0.370 ms

--- 10.24.54.18 ping statistics ---

2 packets transmitted, 2 received, 0% packet loss, time 1417ms

rtt min/avg/max/mdev = 0.321/0.345/0.370/0.030 ms

[xxx@xxx-c004db1 shared]$

3 ，access 數據庫端口3306，也是OK的。

[xxx@xxx-c004db1 shared]$ ssh -v -p3306 10.24.54.18

OpenSSH_5.3p1, OpenSSL 1.0.0-fips 29 Mar 2010

debug1: Reading configuration data /etc/ssh/ssh_config

debug1: Applying options for *

debug1: Connecting to 10.24.54.18 [10.24.54.18] port 3306.

debug1: Connection established.

debug1: identity file /home/xxx/.ssh/xxx type -1

debug1: identity file /home/xxx/.ssh/id_rsa type -1

debug1: identity file /home/xxx/.ssh/id_dsa type -1

debug1: ssh_exchange_identification: O

4，關閉防火牆service iptables stop，然後重啟slave，還是報 Last_IO_Errno: 1045錯誤。

5，遠程訪問主庫mysql 服務，報錯如下：

[xxx@xxx-c004db1 shared]$ mysql -h 10.24.54.18 -ureplication -pxxx

ERROR 1045 (28000): Access denied for user 'replication'@'xxx-xxx.abn-sjl.ea.com' (using password: YES)

[xxx@xxx-c004db1 shared]$

仔細檢查帳戶，發現原來是執行grant語句的時候，密碼串裡面多了一個空格導致，同事的文本編輯器裡面空格顯示不明顯，所以沒有發現。

6，修復：去掉空格，重新執行grant語句，再重新啟動slave，OK。

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.24.54.18

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000660

Read_Master_Log_Pos: 34886442

Relay_Log_File: mysql-relay-bin.001320

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000660

Slave_IO_Running: Yes