old_password is denied in mysql 5.6 replication

Hello. This is Komiya.

I will post the information I was taught by my colleague Yuo.

[The story of the mysql 5.6 replication specification change]

In a certain environment, I tried to pour the fully dumped SQL into 5.6 with mysql5.5, and then tried to replicate the diff data to 5.5→5.6, but there was a symptom that replication could not be done.

After that, I tried to restart mysql and re-paste replication by 'stop slave' to review the mysql 5.6 settings, but this time replication could not be pasted between 5.6 environments.

The reason is that the specification of 'secure_auth' has been changed from mysql 5.6, so even if 'skip-secure-auth' is set in the client ([mysql]) settings, 'old_password' (16-digit password) is rejected at the time of replication IO, so replication itself cannot be started. The status ('show slave status\G') looks like this:

-------------------------------
・・・
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
・・・
Last_IO_Errno: 2049
Last_IO_Error: error connecting to master 'repl@172.17.xx.xx:3306' - retry-time: 60  retries: 1
Last_SQL_Errno: 0
Last_SQL_Error: 
・・・
-------------------------------

As a solution, with the customer's permission, we created a new secure replication account for both MySQL 5.5 and 5.6 environments and pasted replication with it. Here's how to create a secure (not 'old_password') pass.

-------------------------------
set session old_passwords = 0;
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'newrepuser'@'172.17.xxx.%' IDENTIFIED BY 'xxxxxxxxxxxxxx';
-------------------------------

If you are consulted about migrating data to MySQL 5.6, please pay attention to the number of digits in your password. That's the story.

Reference: Daily Memorandum: I/O thread in MySQL 5.6 and MySQL 5.0 replication Error: 2049 Thanks to yoku0825's information, I'm glad I didn't have to call in the middle of the night. Thank you very much.

Thank you for watching.