测试环境新装了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
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