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

The MySQL server is installed in the test environment and cannot be successfully logged in upon login. It prompts that the old authentication protocol is rejected. The specific ERROR message is ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'Secure _ auth' enabled) the following describes the problem and its solution for your reference.

 

1. Fault
[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 Parameters

•  --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 ".

  TheMysqlClient also has--secure-authOption, 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 shoshould 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-authIs also deprecated.

 

3. Analysis and Solution

# View the current configuration file 

[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. pid

 # old_passwords

 # This variable controls the password hashing method used by the PASSWORD () function.

 # It also influences password hashing stored med by create user and GRANT statements that specify a password using an IDENTIFIED B Y clause. 

# The use of the value of 1 is exactly to use the Pre-4.1 ("old") hashing mysql_old_password old password method, so first disable it [root @ HKBO ~]

 # Vi/etc/my. cnf # disabled old_passwords

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

# old_passwords = 1 # restart mysql [root @ HKBO ~] 

# Service mysqld stopShutting down MySQL. [OK] 

[root @ HKBO ~] # Service mysqld startStarting MySQL .. [OK] 

# the same message [root @ HKBO ~] still appears during login

 # Mysql-uroot-pEnter password: ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'Secure _ auth' enabled)

 # Next try to log on with -- secure_auth = off, prompting you to change the password to the new format

 [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 # Next we add secure-auth = off to the configuration file

 [root @ HKBO ~] # Grep secure-auth/etc/my. cnfsecure-auth = off # restart mysql again [root @ HKBO ~] 

# Service mysqld stopShutting down MySQL. [OK] [root @ HKBO ~] # Service mysqld startStarting MySQL. [OK]

 # You can log on to [root @ HKBO ~] using -- secure_auth = off.

 # Mysql-uroot-p -- secure_auth = offEnter password: Welcome to the MySQL monitor. commands end with;

 or \ g. your MySQL connection id is 2 Server version: 5.6.12 Source distributionCopyright (c) 2000,201 3, 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; 'o R' \ 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 # You cannot log on after the preceding operations. 

You still need to use -- secure_auth = off to log on. 

# view the default mysql client [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/man/man1/mysql.1.gz

 #/usr/bin also has a mysql client, its version is 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 

# After troubleshooting, the current host has the old version of mysql [root @ HKBO mysql] 

# rpm-qa | grep-I mysqlmysql-5.0.95-3.el5 

# Next uninstall the old version of 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/bin/mysql/app/soft/mysql/include/mysql/var/lib/mysql/var/spool/mail/mysql /opt/data/mysql/home/mysql # Remove the path and file of the old mysql version [root @ HKBO ~] # Rm-rf/var/lib/mysql 

# considering that the configuration file is an old version, replace [root @ HKBO ~] with the default configuration file 5.6.12. # 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 # restart 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 | # You can see that the encrypted password is 16bit + ------ + -------------- + ------------------ + 

mysql> set password for 'root' @ 'loc Alhost '= 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 | + ------ + -------------- + anonymous + | root | localhost | anonymous | # 16bit + ------ + -------------- + -------------------- + # Next, try clearing the root password 

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 # restart mysql again [root @ HKBO ~] # Service mysqld stopShutting down MySQL. [OK] [root @ HKBO ~] # Service mysqld startStarting MySQL. [OK] # Use mysqladmin to change the password [root @ HKBO ~] # Mysqladmin-u root password 'xxx' # You can log on successfully at this time, and the ciphertext of the password obviously gets longer. solve this problem [root @ HKBO ~]

 # Mysql-uroot-pEnter password: mysql> select user, password, host from mysql. user; + ------ + ------------------------------------------- + -------------- + | user | password | host | + ------ + accept + ------------ + | root | * handle | localhost | + ------ + accept + -------------- +

4. Summary
A. In most cases, MySQL's default my. cnf is unreliable and needs to be modified appropriately.
B. Check whether the current host has an old version and multiple instances before installing MySQL (this error is caused by non-production environment negligence)
C. Locate the cause of the fault according to the error message. The keyword for this time is secure_auth.
D. Understand the functions and impacts of secure_auth, old_passwords, and skip-secure-auth parameters.