ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)
If you have tried to connect to a MySQL database and you see this error then you need to have valid 41byte hash password. If you are unsure which you have execute the SQL below. If you have 16 character passwords they are older passwords.
select Password from mysql.user;
The following is how I solved this as part of a migration from MySQL 5.0 to MySQL 5.6.
The MySQL 5.0 server had a mixture of the older pre 4.1 passwords and valid 41byte passwords. Because the MySQL 5.0 server had some accounts with the older passwords I decided to
not dump the MySQL table as part of setting up replication. I did dump all of the databases except the mysql database. This allow ensured that I would keep the valid MySQL 5.6 table enhancements.
The MySQL 5.6 server installed easily and was up and I loaded the dump data. Part of the migration was to use replication while they evaluated the new database. While on the MySQL 5.6 server I tested the replication user account. The response I got was the error at the top of this page. Replication will not run of course without a valid user account. This is why the error logs was giving me this error:
[ERROR] Slave I/O: error connecting to master '<user>@<hostname>:3306' - retry-time: 10 retries: 68, Error_code: 2049
A quick review of the account on the MySQL 5.0 server showed that the new account was established with the pre 4.1 password. So I needed to upgrade the account to a valid 41 byte password.
The following query showed that they did indeed have old passwords enabled. So I have to disable that and update the user account again to set the password as a valid 41 byte hash.
>SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
| 1 | 1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)
>SET @@session.old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)
>GRANT REPLICATION SLAVE ON *.* TO '<user>'@'<ip_address>' IDENTIFIED BY '<Password>';
Query OK, 0 rows affected (0.00 sec)
A check of the password showed the password as the 41byte password now. I was this able to connect to the primary server from the secondary server and avoid the secure_auth error. replication connected easily and problem was solved.
Going forward I needed to get the MySQL 5.0 users accounts onto the MySQL 5.6 server. ( since I skipped them as part of building the secondary server. )
The client needed to set the grants again for each user regardless of valid password or not.
So I instructed them to execute the following sql. I could have done this but I would need to know all of their passwords and that was not needed.
For each user in their system. You do not have to do the root user because you already have a valid root account on the 5.6 system.
>SET @@session.old_passwords = 0;
>show grants for '<User>'@'<Host>';
To gather the sql needed for each user run the following :
SELECT CONCAT("SHOW GRANTS FOR '",User,"'@'",Host,"';") as sql_command from mysql.user;
For each result given execute the "show grants" statement and then execute the statement given.
The statements should be similar to the following:
GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';
Replication then created and populated the MySQL table on the MySQL 5.6 server.
More can be found here:
http://dev.mysql.com/doc/refman/5.6/en/password-hashing.html
Also as an update --
Check out
http://dev.mysql.com/doc/refman/5.6/en/account-upgrades.html
