今天把兩臺機器恢復主從關係後，在slave上執行show slave status，結果中顯示

2017-04-21T13:46:15.133435Z 8806 [ERROR] Slave I/O for channel '': error connecting to master 'repl@192.168.2.40:3306' - retry-time: 60 retries: 1, Error_code: 1045

首先檢視slave的錯誤日誌檔案，和上面的錯誤一樣

接著用perror檢視上一部獲得的錯誤程式碼：

[root@iZ2ze5ifr62amhrpcnpn9yZ mysql]# perror 1045
MySQL error code 1045 (ER_ACCESS_DENIED_ERROR): Access denied for user '%-.48s'@'%-.64s' (using password: %s)

有可能是複製用的賬戶存在問題，先在MASTER上確認複製使用者賬戶是否存在且是否賦了正確的許可權

mysql> show grants for repl@192.168.2.41;
+---------------------------------------------------------+
| Grants for repl@192.168.2.41 |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.2.41' |
+---------------------------------------------------------+
1 row in set (0.00 sec)

發現沒有問題，試著從master使用該賬戶連線至slave，果然沒連上

[root@iZ2ze5ifr62amhrpcnpn9yZ ~]# mysql -urepl -h 192.168.2.40 -p -P3306
Enter password:
ERROR 1045 (28000): Access denied for user 'repl'@'192.168.2.40' (using password: YES)

之前的密碼可能不對，修改一個新的密碼

mysql> update mysql.user set authentication_string=PASSWORD('REPLsafe!@#$41') where User='repl' and host='192.168.2.41';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

試著用新的密碼重建複製關係，問題解決。

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to
-> master_host='192.168.2.40',
-> master_port=3306,
-> master_user='repl',
-> master_password='REPLsafe!@#$41',
-> master_log_file='bin.000043',
-> master_log_pos=799072709;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.40
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000043
Read_Master_Log_Pos: 854716379
Relay_Log_File: relay.000002
Relay_Log_Pos: 1135224
Relay_Master_Log_File: bin.000043
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: cus_DEMO,cus_DEMO_0413,cus_DEMO_0414,cus_DEMO_0418
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: 800207619
Relay_Log_Space: 55644221
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: 18559
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: 40
Master_UUID: 1b00b716-cf6a-11e6-b66e-00163e320583
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 1b00b716-cf6a-11e6-b66e-00163e320583:34573831-34680957
Executed_Gtid_Set: 1b00b716-cf6a-11e6-b66e-00163e320583:1-33612321:34573831-34620341
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

MySQL報錯 Error_code: 1045

相關文章