测试环境新装了MySQL服务器，在登陆时无法成功登陆。其提示为使用的旧的认证协议而被拒绝。其具体的错误提示为ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)以下是关于这个问题的描述及其解决方案，供大家参考。
1、故障现象
[root@HKBO ~]# mysqladmin -u root password 'Mysqlxxx'
[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)
[root@HKBO ~]# mysql -uroot -p --skip-secure-auth
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
?
2、有关secure_auth参数
?
3、分析及解决
#查看当前的配置文件
[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
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
#经排查，当前主机有旧版的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? ? |
+------+-------------------------------------------+--------------+
4、小结
a、绝大多数情况下，MySQL缺省的my.cnf不靠谱，根据情况都需要适度修改。
b、在安装MySQL之前应先检查当前主机是否存在旧版以及多实例(此次由于非生产环境，所以疏忽导致了这个问题)
c、根据错误提示来定位故障原因，如此次的关键字为secure_auth
d、理解有关secure_auth,old_passwords,skip-secure-auth参数的作用及其影响