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

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

    測試環境新裝了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參數

•  --secure-auth

  Command-Line Format	--secure-auth
  System Variable	Name	secure_auth
  	Variable Scope	Global
  	Dynamic Variable	Yes
  Permitted Values (<= 5.6.4)	Type	boolean
  	Default	OFF
  Permitted Values (>= 5.6.5)	Type	boolean
  	Default	ON

  This option causes the server to block connections by clients that attempt to use accounts that have passwords stored in the old (pre-4.1) format. Use it to prevent all use of passwords employing the old format (and hence insecure communication over the network). Before MySQL 5.6.5, this option is disabled by default. As of MySQL 5.6.5, it is enabled by default; to disable it, use --skip-secure-auth.

  Server startup fails with an error if this option is enabled and the privilege tables are in pre-4.1 format. SeeSection B.5.2.4, “Client does not support authentication protocol”.

  The mysql client also has a --secure-auth option, which prevents connections to a server if the server requires a password in old format for the client account.

  Note

  Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them will be removed in a future MySQL release. Consequently, disabling secure authentication using --skip-secure-auth is also deprecated.

 

3、分析及解決

#查看當前的設定檔[root@HKBO ~]# grep -v ^# /etc/my.cnf [mysqld]datadir=/opt/datasocket=/tmp/mysql.sockuser=mysqlold_passwords=1   [mysqld_safe]log-error=/var/log/mysqld.logpid-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 stopShutting down MySQL.[  OK  ][root@HKBO ~]# service mysqld startStarting MySQL..[  OK  ]#登陸還是出現同樣的提示[root@HKBO ~]# mysql -uroot -pEnter 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=offEnter 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.cnfsecure-auth=off#再次重啟mysql[root@HKBO ~]# service mysqld stopShutting down MySQL.[  OK  ][root@HKBO ~]# service mysqld startStarting MySQL.[  OK  ]#此時可以透過--secure_auth=off方式登陸[root@HKBO ~]# mysql -uroot -p --secure_auth=offEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.12 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye#通過上述操作後還是無法登陸，依舊需要使用--secure_auth=off方式才能登陸#查看預設的mysql用戶端[root@HKBO ~]# which mysql/app/soft/mysql/bin/mysql[root@HKBO ~]# /app/soft/mysql/bin/mysql -uroot -pEnter 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 mysqlmysql: /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 mysqlmysql-5.0.95-3.el5#接下來卸載老版本的mysql[root@HKBO ~]# rpm -e --nodeps mysql-5.0.95-3.el5warning: /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 stopShutting down MySQL..[  OK  ][root@HKBO ~]# service mysqld startStarting MySQL.[  OK  ][root@HKBO ~]# mysql -uroot -p --secure_auth=offEnter 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 stopShutting down MySQL.[  OK  ][root@HKBO ~]# service mysqld startStarting MySQL.[  OK  ]#接下來使用mysqladmin修改密碼[root@HKBO ~]# mysqladmin -u root password 'xxx'#此時可以成功登陸，且密碼的密文明顯變長，至此問題解決[root@HKBO ~]# mysql -uroot -pEnter 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參數的作用及其影響