[MySQL生产环境复制故障修复] Last_IO_Errno: 1045 Last_IO_Erro

Home

Database

Mysql Tutorial

[MySQL生产环境复制故障修复] Last_IO_Errno: 1045 Last_IO_Erro_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:28 PM

bitsCN.com

[MySQL生产环境复制故障修复] Last_IO_Errno: 1045 Last_IO_Error: error connecting to master 'replication@

1 修复集群，start slave 报错如下：

[sql] mysql> stop slave;  Query OK, 0 rows affected (0.10 sec)  [sql] -- 重新设置复制参数  mysql> change master to master_host=&#39;10.24.54.18&#39;,master_port=3306,master_user=&#39;replication&#39;,master_password=&#39;xxxxxx&#39;;  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 &#39;replication@10.24.54.18:3306&#39; - 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的。

[python] [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  ^C  --- 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的。

[python] [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  ^C

4，关闭防火墙service iptables stop，然后重启slave，还是报 Last_IO_Errno: 1045错误。

5，远程访问主库mysql 服务，报错如下：

[python] [xxx@xxx-c004db1 shared]$ mysql -h 10.24.54.18 -ureplication -pxxx  ERROR 1045 (28000): Access denied for user &#39;replication&#39;@&#39;xxx-xxx.abn-sjl.ea.com&#39; (using password: YES)  [xxx@xxx-c004db1 shared]$

仔细检查帐户，发现原来是执行grant语句的时候，密码串里面多了一个空格导致，同事的文本编辑器里面空格显示不明显，所以没有发现。

6，修复：去掉空格，重新执行grant语句，再重新启动slave，OK。

[sql] 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              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: 34886442                Relay_Log_Space: 452                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: 2  1 row in set (0.00 sec)

总结：DBA最重要的一点：细心，与大家共勉

bitsCN.com

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles