MySQL secure_auth error

I addressed the secure_auth errors before when it blocks replication in this blog post.

However, I figured I would make this blog post a more general fix when connecting via MySQL clients. This is for servers before MySQL 5.6.

So if you get a secure_auth error when connection to MySQL the following steps should clear this error.

+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | 34d22ac342c35af2                           |   |+---------------+-------------------------------------------+

SELECT @@session.old_passwords, @@global.old_passwords;                                                            +-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+

mysql> SET @@session.old_passwords = 0;  SET @@global.old_passwords=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       0 |                      0 |
+-------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SET PASSWORD FOR 'authdtestuser'@'localhost' = PASSWORD('sshthisisasecret');
Query OK, 0 rows affected (0.00 sec)

mysql> select User , Password from mysql.user where User = 'authdtestuser';
+---------------+-------------------------------------------+
| User          | Password                                  |
+---------------+-------------------------------------------+
| authdtestuser | *E48BD8BF1B9F29459E5284AD158443B5B33B70E4 |
+---------------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> SET @@session.old_passwords = 1;  SET @@global.old_passwords=1;

mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)