2

I'm trying to troubleshoot why my slave cannot connect to the master. This is what I see in the output from the command "show slave status \G"

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.1.1.1
                  Master_User: test_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             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: 0
              Relay_Log_Space: 107
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/ssl/ca-self-cert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/ssl/server-self-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/ssl/server-self-key.pem
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1129
                Last_IO_Error: error connecting to master 'test_user@10.1.1.1:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
1 row in set (0.00 sec)

What I've Checked:

Made sure port 3306 is open on master:

test-dev:/etc/ssl# netstat -lnp | grep 3306
tcp        0      0 10.1.1.1:3306       0.0.0.0:*               LISTEN      -
test-dev:/etc/ssl# 

Made sure firewall is open from slave machine:

test_dev_2:/etc/ssl# nc -vzw 3 10.1.1.1 3306
10.1.1.1 (10.1.1.1:3306) open
test_dev_2:/etc/ssl# 

Checked permissions on the certs on both master and slave:

 master:

-rw-r--r--    1 root     root       10.4K Nov 11  2014 server-cert.pem
-rw-r--r--    1 root     root        1.9K Nov 11  2014 server-key.pem
-rw-r--r--    1 root     root        1.3K Dec  2 15:45 server-self-cert.pem
-rw-r--r--    1 root     root        1.7K Dec  2 15:45 server-self-key.pem
-rw-r--r--    1 root     root         948 Nov 25 13:14 server-self-req.pem
slave: 

-rw-r--r--    1 root     root       10.4K Nov 11  2014 server-cert.pem
-rw-r--r--    1 root     root        1.9K Nov 11  2014 server-key.pem
-rw-r--r--    1 root     root        1.3K Nov 20 12:06 server-self-cert.pem
-rw-r--r--    1 root     root        1.7K Nov 20 12:05 server-self-key.pem
-rw-r--r--    1 root     root         964 Nov 20 12:05 server-self-req.pem

Made sure ssl is enabled on the server.

 mysql> show variables like "%ssl%";
 +---------------+-------------------------------+
 | Variable_name | Value                         |
 +---------------+-------------------------------+
 | have_openssl  | YES                           |
 | have_ssl      | YES                           |
 | ssl_ca        | /etc/ssl/ca-self-cert.pem     |
 | ssl_capath    |                               |
 | ssl_cert      | /etc/ssl/server-self-cert.pem |
 | ssl_cipher    |                               |
 | ssl_key       | /etc/ssl/server-self-key.pem  |
 +---------------+-------------------------------+
 7 rows in set (0.00 sec)

 mysql> 

Finally, I ran the command

 "openssl x509 -in server-self-cert.pem -text -noout" 

on both the master and the slave and it looked like the certs were okay. But I'm no expert and am just learning about certs.

I'm not too sure what else I should check / try. I'm a programmer ... not dba or even sys admin so .. I've been running out of ideas.

  • Use the mysql command line client while logged in on the slave server to try connecting to the master using the same cert, key, and credentials. Replication uses an ordinary client connection from slave to master, so this is an effective approach that may tell you something useful. – Michael - sqlbot Dec 3 '15 at 12:25
2

The manual gives an explanation for IO error number 1129 and also the command that solves the problem:

Error: 1129 SQLSTATE: HY000 (ER_HOST_IS_BLOCKED)

Message: Host '%s' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Note that this error may be a consequence of another underlying problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.