error connecting to master ‘user@host:3306’ – retry-time: 60 retries: 7

Read

制作主从同步的时候，可能会遇到类似的报错：

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master    #线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。
                  Master_Host: 172.28.95.116    #主库IP
                  Master_User: byrd    #同步的用户
                  Master_Port: 3306    #主库端口
                Connect_Retry: 60    #失败后多少秒重新连接尝试
              Master_Log_File: mysql-bin.000002    #
          Read_Master_Log_Pos: 908
               Relay_Log_File: mysql-relay-bin.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Connecting    #是否要从Master复制二进制数据
            Slave_SQL_Running: Yes    #是否执行从Master复制过来的二进制数据
              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: 908
              Relay_Log_Space: 120
              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    #Slave的数据落后Master的多少秒，同步完成为0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1130
                Last_IO_Error: error connecting to master 'byrd@172.28.95.116:3306' - retry-time: 60  retries: 7
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /usr/local/mysql/data/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: 140711 14:15:04
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Connecting to master #线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等待持续slave_read_timeout秒，则发生超时。此时，线程认为连接被中断并企图重新连接。

Master_Host: 172.28.95.116 #主库IP

Master_User: byrd #同步的用户

Master_Port: 3306 #主库端口

Connect_Retry: 60 #失败后多少秒重新连接尝试

Master_Log_File: mysql-bin.000002 #

Read_Master_Log_Pos: 908

Relay_Log_File: mysql-relay-bin.000003

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Connecting #是否要从Master复制二进制数据

Slave_SQL_Running: Yes #是否执行从Master复制过来的二进制数据

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

Relay_Log_Space: 120

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 #Slave的数据落后Master的多少秒，同步完成为0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1130

Last_IO_Error: error connecting to master 'byrd@172.28.95.116:3306' - retry-time: 60 retries: 7

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

Master_UUID:

Master_Info_File: /usr/local/mysql/data/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: 140711 14:15:04

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -ubyrd -h 172.28.95.116 -p -P3306
Enter password: 
ERROR 1130 (HY000): Host '172.28.95.117' is not allowed to connect to this MySQL server

[root@Slave-Mysql tmp]# /usr/local/mysql/bin/mysql -ubyrd -h 172.28.95.116 -p -P3306

Enter password:

ERROR 1130 (HY000): Host '172.28.95.117' is not allowed to connect to this MySQL server

我尝试连接，返回报错很明显了，由于没有权限导致，到master服务器查看的时候发现

mysql> grant replication slave on *.* to byrd@'182.28.95.%' identified by 'admin';    #172.28.95.%,写成了182.28.95.%，粗心！！！

0	mysql> grant replication slave on . to byrd@'182.28.95.%' identified by 'admin'; #172.28.95.%,写成了182.28.95.%，粗心！！！

排错思路：
1：由于Slave的Slave_SQL线程是运行的，而提示IO线程是连接状态，哪么基本可以确定是由于用户、连接的问题；
①：尝试是否由于防火墙原因，而导致无法连接到master服务器；
②：尝试是否由于用户或者密码错误而导致连接不到master服务器；SourceByrd's Weblog-https://note.t4x.org/error/error-connecting-to-master/ SourceByrd's Weblog-https://note.t4x.org/error/error-connecting-to-master/

申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!

Comment

Login