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.

The Deprecation of Pre-4.1 Password Hashes

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.

Identifying the Connection Failure via SHOW SLAVE STATUS

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

root@localhost [(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 'slave_user@master1: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 

The "Secure Auth" Trap: Last_IO_Errno 2049

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)

Verifying the Master Configuration and Account Hash

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:

root@master1:(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:

root@master1:(none)> show global variables like 'old_passwords'; +-----------------+-------+ | Variable_name   | Value | +-----------------+-------+ | old_passwords   | ON    | +-----------------+-------+ 

Resolution: Rehashing the Replication Account

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

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