ERROR 2049 (HY000): Connection using old (pre-4.1.1)

#查看当前的配置文件
[root@HKBO ~]# grep -v ^# /etc/my.cnf 
[mysqld]
datadir=/opt/data
socket=/tmp/mysql.sock
user=mysql
old_passwords=1   

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#old_passwords
#This variable controls the password hashing method used by the PASSWORD() function.
#It also influences password hashing performed by CREATE USER and GRANT statements that specify a password using an IDENTIFIED BY clause.
#当值为1的使用正好使用的是Pre-4.1 (“old”) hashing mysql_old_password 旧密码方式，因此先将其禁用

[root@HKBO ~]# vi /etc/my.cnf

#如下，禁用后的old_passwords
[root@HKBO ~]# grep old_passwords /etc/my.cnf 
#old_passwords=1

#重启mysql
[root@HKBO ~]# service mysqld stop
Shutting down MySQL.[  OK  ]

[root@HKBO ~]# service mysqld start
Starting MySQL..[  OK  ]

#登陆还是出现同样的提示
[root@HKBO ~]# mysql -uroot -p
Enter password: 
ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

#下面尝试使用--secure_auth=off登陆，提示需要改变密码到新格式
[root@HKBO ~]# mysql -uroot -p --secure_auth=off
Enter password: 
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format

#下面我们增加secure-auth=off到配置文件
[root@HKBO ~]# grep secure-auth /etc/my.cnf
secure-auth=off

#再次重启mysql
[root@HKBO ~]# service mysqld stop
Shutting down MySQL.[  OK  ]
[root@HKBO ~]# service mysqld start
Starting MySQL.[  OK  ]

#此时可以透过--secure_auth=off方式登陆
[root@HKBO ~]# mysql -uroot -p --secure_auth=off
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.12 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password for 'root'@'localhost' =password('Mysqlxxx');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit
Bye

#通过上述操作后还是无法登陆，依旧需要使用--secure_auth=off方式才能登陆

#查看缺省的mysql客户端
[root@HKBO ~]# which mysql
/app/soft/mysql/bin/mysql

[root@HKBO ~]# /app/soft/mysql/bin/mysql -uroot -p
Enter password: 
ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

[root@HKBO ~]# /app/soft/mysql/bin/mysql --version
/app/soft/mysql/bin/mysql  Ver 14.14 Distrib 5.6.12, for Linux (x86_64) using  EditLine wrapper

[root@HKBO ~]# whereis mysql
mysql: /usr/bin/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

#/usr/bin下也有一个mysql客户端，其版本为5.0.95
[root@HKBO ~]# /usr/bin/mysql --version
/usr/bin/mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

#经排查，当前主机有旧版的mysql
[root@HKBO mysql]# rpm -qa | grep -i mysql
mysql-5.0.95-3.el5

#接下来卸载老版本的mysql
[root@HKBO ~]# rpm -e --nodeps mysql-5.0.95-3.el5
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

[root@HKBO ~]# find / -name mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql
               .........
/app/soft/mysql
/app/soft/mysql/bin/mysql
/app/soft/mysql/include/mysql
/var/lib/mysql
/var/spool/mail/mysql
/opt/data/mysql
/home/mysql
#移除旧版mysql的路径及其文件
[root@HKBO ~]# rm -rf /var/lib/mysql   

#考虑到配置文件的为旧版，直接用5.6.12版的缺省配置文件覆盖
[root@HKBO ~]# cp /app/soft/mysql/support-files/my-default.cnf /etc/my.cnf
[root@HKBO ~]# grep -v ^# /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

#重启mysql
[root@HKBO ~]# service mysqld stop
Shutting down MySQL..[  OK  ]
[root@HKBO ~]# service mysqld start
Starting MySQL.[  OK  ]

[root@HKBO ~]# mysql -uroot -p --secure_auth=off
Enter password: 

mysql> select user,host,password from mysql.user;
+------+--------------+------------------+   # Author : Leshami
| user | host         | password         |   # Blog   : http://blog.csdn.net/leshami
+------+--------------+------------------+
| root | localhost    | 7ca9a8e40dd1bf23 |   #可以看到加密后的密码为16bit
+------+--------------+------------------+

mysql> set password for 'root'@'localhost'=password('Mysql66');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host,password from mysql.user where user='root';
+------+--------------+------------------+
| user | host         | password         |
+------+--------------+------------------+
| root | localhost    | 5614c1a44e6b0c87 |  #更新后还是16bit
+------+--------------+------------------+

#接下来尝试清空root密码
mysql> update mysql.user set password='' where user='root' and host='localhost';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#再一次重启mysql
[root@HKBO ~]# service mysqld stop
Shutting down MySQL.[  OK  ]
[root@HKBO ~]# service mysqld start
Starting MySQL.[  OK  ]

#接下来使用mysqladmin修改密码
[root@HKBO ~]# mysqladmin -u root password 'xxx'

#此时可以成功登陆，且密码的密文明显变长，至此问题解决
[root@HKBO ~]# mysql -uroot -p
Enter password: 

mysql> select user,password,host from mysql.user;
+------+-------------------------------------------+--------------+
| user | password                                  | host         |
+------+-------------------------------------------+--------------+
| root | *3D56A309CD04FA2EEF181462E59011F075C89548 | localhost    |
+------+-------------------------------------------+--------------+