Problems related to connection failure after MYSQL 5.0 is upgraded

After the MYSQL version is upgraded to MYSQL 5.0, the recently used machine will be migrated to the data center, but the database version is too low during security scanning (4.0 +), and the database must be upgraded. So I upgraded to mysql5.6 and finished the upgrade. After the data migration was completed, I found that the program could not be connected. After comparing the password and configuration file, I found that no error was found.

After the MYSQL version is upgraded to MYSQL 5.0, the recently used machine will be migrated to the data center, but the database version is too low during security scanning (4.0 +), and the database must be upgraded. So I upgraded to mysql5.6 and finished the upgrade. After the data migration was completed, I found that the program could not be connected. After comparing the password and configuration file, I found that no error was found.

Connection failure after MYSQL version is upgraded to 5.0

Recently used machines need to be migrated to the data center, but the database version is too low during security scanning (4.0 +), and the database must be upgraded. So I upgraded

MySQL and later versions are upgraded. After the data migration is completed, you can find that the program cannot be connected. Compared with the password and configuration file, later, I learned that the password encryption method for Versions later than mysql5.0 is different from that for previous versions. To use the original encryption method, you must use

The OLD_PASSWORD function sets the password.

The following is a reprinted solution. My solution is similar to this one.

Error MySQL returned :?
#1251-Client does not support authentication protocol requested by server; consider upgrading MySQL client

The reason is that the new password verification mechanism is used in the mysql server version you are using. This requires the client version to be later than 4.0. The original password function is changed to old_password ();, in this way, it is difficult to use the password () generated by the client on the old version, while the MYSQL client in PHP is 3.23 (except for mysqli extension ), this is the problem.

Currently known solutions:

1. Go to the command line, go to the BIN directory of the MYSQL directory, and enter the MYSQL Command Line Mode:
?????????? Example: d: \ mysql \ bin> mysql-uroot-p123 ?????????? (User root, password 123)
2. Enter the following command:
?????????? Mysql> set password for 'root' @ 'localhost' = old_password ('000000 ');?????????? (Note that the number of colons cannot be small)
3. Return to the MYSQL command line:
?????????? Mysql> \ q

?

A.2.3. the client does not support the authentication protocol.

MySQL 5.1 adopts an authentication protocol based on the cryptographic mixed encoding algorithm, which is incompatible with the protocol used by earlier clients (before MySQL 4.1. If you upgrade the server to 4.1, connection with an earlier client may fail and the following message is displayed:

Shell>?MysqlThe client does not support the authentication protocol for server requests: consider upgrading the MySQL client.

To solve this problem, use one of the following methods:

·?????????

·????????? When you connect to the server using a client earlier than Version 4.1, use an account that still has a password earlier than Version 4.1.

·????????? For each user who needs to use a client before version 4.1, the password will be restored to the style before version 4.1. You can use the set password statement and the OLD_PASSWORD () function to complete this task:

·???????????????? Mysql>?SET PASSWORD·????????????????????????? ->?'Some_user'@'Some_host'= OLD_PASSWORD ('Newpwd');

You can also use UPDATE and flush privileges:

Mysql>?UPDATE mysql. user SET Password = OLD_PASSWORD ('Newpwd')????????? ->?WHERE Host ='Some_hostAND User ='Some_user';Mysql>?Flush privileges;

Replace"Newpwd". MySQL cannot tell you what the original password is. Therefore, you need to select a new password.

·????????? Notify the server to use the old password mixing algorithm:

1 .???? Use the "-- old-passwords" option to startMysqld.

2 .???? For each account whose password has been updated to a longer 4.1 format, specify a password in the old format. You can use the following query to determine these accounts:

3 .????????????????? Mysql>?SELECT Host, User, Password FROM mysql. user4 .?????????????????????????? ->?Where length (Password)> 16;

For each account record displayed in the query, use the Host and User values, and use the OLD_PASSWORD () function and one of set password or UPDATE to specify the PASSWORD, as described above.

Note:In earlier versions of PHP, mysql extensions do not support authentication protocols in MySQL 4.1.1 and later versions. No matter what PHP version is used, it is correct. If you plan to use MySQL extension with mysql 4.1 or later, you need to use one of the options described earlier to configure MySQL for use with earlier clients. Mysqli extension (support for "Improved MySQL" and added in PHP 5) is compatible with the improved password mixing Algorithm Used in MySQL 4.1 and later versions, the MySQL client library can be used without special configuration for MySQL. For more information about mysqli extensions, see http://php.net/mysqli.

?

First of all, I have not tried the following description only for other Windows systems. Mysql has modified the User Password format since 4.1.1, increasing from 16 bits to 41 BITs, A new verification method is adopted, but the client Protocol earlier than 4.1 does not support this method, so it causes the consequences of being unable to log on. even if the password is correct and cannot be properly logged on, the prompt is as follows:
#1250-Client does not support authentication protocol requested by server; consider upgrading MySQL client

Run the following command in the window:
C: \ mysql \ bin> mysql-uroot
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 1 to server version: 5.0.0-alpha-nt

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> select password ('A ');
+ ------------------------------------------- +
| Password ('A') |
+ ------------------------------------------- +
| * DEE59C300700AF9B586F9F2A702231C0AC373A13 |
+ ------------------------------------------- +
1 row in set (0.00 sec)

Mysql>
From the above, we can see that the password result returns a 41-Bit String starting with '*', which is 16 bits in the past.

The official Mysql website provides two solutions

1. Use the new client api,
2. force the server to use the old password

First, I tried the first method to copy libmysql from mysql5.0. dll to the extension directory of php, replacing libmysql with php itself. dll, but the results are disappointing. php prompts that php_mysql.dll loading failed: PHP Warning: PHP Startup: Unable to load dynamic library 'C:/php5/ext/php_mysql.dll '-the specified program cannot be found. Later I found that the new dll version lacks the mysql_drop_db () function: (only when php or mysql releases the updated dll.

The first method does not work. You only need to try the second method. The mysql official FAQ says that you need to add the-old-password parameter. First, I try to start mysql under the command line,
C: \ mysql \ bin> mysqld-nt-old-password

Run in another window
C: \ mysql \ bin> mysql-uroot
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 540 to server version: 5.0.0-alpha-nt

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql> select password ('mypassword ');
+ ------------------------ +
| Password ('mypassword') |
+ ------------------------ +
| 162eebfb6477e5d3 |
+ ------------------------ +
1 row in set (0.55 sec)

Mysql>

It can be seen that the password result is changed to 16 bits, and the change is gratifying.

I am in c: \ windows \ my. in ini (xp, 98, 2000, add "set-variable = old-passwords" to the "mysqld" section in c: \ wint"

Example:
[Mysqld]
Basedir = C:/mysql
Set-variable = old-passwords
Datadir = C:/mysql/data
[WinMySQLadmin]
Server = C:/mysql/bin/mysqld-nt.exe
User = root
Password = mypassword

Start mysql in Service Manager

Very Good, everything is normal,

Later, I found that as long as the length of the password field in the mysql. user table is changed to 16, the system will automatically switch to the oldpassword mode, change to 41, and then automatically change to the new authentication method.
After changing the password method, you need to reset the password and refresh the permission (or restart mysql)

First, check/etc/rc. d/init. d/mysqld status to see if m y s q l has been started.
Check whether the permission is correct.

2. Check whether your mysql. sock is in that location,
Mysql-u your mysql username-p-S/var/lib/mysql. sock

3. Try: service mysqld start

4. If the permission problem occurs, change the permission first # chown-R mysql: mysql/var/lib/mysql

[Root @ localhost ~] #/Etc/init. d/mysqld start
Start MySQL: [OK]
[Root @ localhost ~] # Mysql-uroot-p