Why is the slave IO thread in connecting status?

Are you using MySQL version 5.6? Have you triple checked master parameters and network connectivity but your slave IO thread is still not able to connect to the master? Then this blog post may be for you.

As you may know, pre-4.1 password hashes and the mysql_old_password plugin are deprecated as of MySQL 5.6.5 and support for them will be removed in MySQL 5.7.5.

By default MySQL 5.6 client won’t send passwords in pre-4.1 format, preventing connections with any accounts having passwords hashed using this function. However this behavior can be disabled using --skip-secure-auth when starting the client.

Now, when it comes to a slave IO thread (that establishes a client connection to the master), there is no way to force it to send passwords in the older format, thus preventing the slave to access the master if the replication account user password was hashed using the pre-4.1 algorithm.

A slave in this situation will exhibit the following output for SHOW SLAVE STATUS:

[email protected] [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: master1
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql01-bin-log.010420
          Read_Master_Log_Pos: 460893765
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql01-bin-log.010420
             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: 460893765
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2049
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60  retries: 42
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /data/mysql/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: 160517 17:29:20
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0

At the beginning, the situation might be a little confusing especially if all the parameter configured are correct and if for some reason my.cnf has secure_auth set to OFF under [mysql] section on the slave and you are not aware of it: a test connection using the same master parameters will work when you test from mysql client.

The truth is that the error was right in front of our eyes: Last_IO_errno value is 2049 which corresponds to CR_SECURE_AUTH:

Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

If you go to the master and check the password hash for the replication user you will probably found that it was hashed using the old algorithm:

[email protected]:(none)> select user,host,password from mysql.user where user like 'slave_user';
+-----------------+---------+----------------------------+
| user            | host    | password                   |
+-----------------+---------+----------------------------+
| slave_user      | slave1  | 0cb0f9cf14e8431c           |
+-----------------+---------+----------------------------+

Also, you might have old_passwords enabled globally

[email protected]:(none)> show global variables like 'old_passwords';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_passwords   | ON    |
+-----------------+-------+

To “fix” the error, just rehash the replication account password using the new algorithm:

[email protected]:(none)> set session old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)
[email protected]:(none)> select password('testing');
+-------------------------------------------+
| password('testing')                          |
+-------------------------------------------+
| *3F50515DDEE62F18A2B1CE3BE819CFB2F3C869F1 |
+-------------------------------------------+
1 row in set (0.00 sec)
[email protected]:(none)> set password for [email protected]'slave1' = password('topsecretpass');
Query OK, 0 rows affected (0.00 sec)
[email protected]:(none)> select user,host,password from mysql.user where user like 'slave_user';
+-----------------+----------+-------------------------------------------+
| user            | host     | password                                  |
+-----------------+----------+-------------------------------------------+
| slave_user      | slave1   | *C9D10A6224A1924C6A221C6298297C100ED345AB |
+-----------------+----------+-------------------------------------------+