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: [code lang="sql"] 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 [/code] 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: [code lang="sql"] root@master1:(none)> select user,host,password from mysql.user where user like 'slave_user'; +-----------------+---------+----------------------------+ | user | host | password | +-----------------+---------+----------------------------+ | slave_user | slave1 | 0cb0f9cf14e8431c | +-----------------+---------+----------------------------+ [/code] Also, you might have old_passwords enabled globally [code lang="sql"] root@master1:(none)> show global variables like 'old_passwords'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | old_passwords | ON | +-----------------+-------+ [/code] To “fix” the error, just rehash the replication account password using the new algorithm: [code lang="sql"] 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 | +-----------------+----------+-------------------------------------------+ [/code]
