Home Database Mysql Tutorial MYSQL版本升级到后5.0后无法连接的有关问题

MYSQL版本升级到后5.0后无法连接的有关问题

Jun 07, 2016 pm 04:25 PM
mysql upgrade Version connect

MYSQL版本升级到后5.0后无法连接的问题 最近使用的机器要迁入机房,但是安全扫描时数据库版本太低了(4.0+)必须升级数据库。于是升级到了 mysql5.6版本,升级完毕,数据迁移完毕后却发现程序怎么也连不上了,对比了密码和配置文件都没有错,后来上网查询才知

MYSQL版本升级到后5.0后无法连接的问题

最近使用的机器要迁入机房,但是安全扫描时数据库版本太低了(4.0+)必须升级数据库。于是升级到了

mysql5.6版本,升级完毕,数据迁移完毕后却发现程序怎么也连不上了,对比了密码和配置文件都没有错,后来上网查询才知道是mysql5.0以后的版本密码加密方式和以前版本不一样,要使用原来的加密方式,必须使用

OLD_PASSWORD函数设置密码。

后面为转载的解决方法,我的解决办法和这个大同小异。

错误MySQL 返回:?
#1251 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

原因是因为你使用的mysql服务器版本中使用了新的密码验证机制,这需要客户端的版本要在4.0以上,原来的密码函数被改为old_password ();,这样使用password()生成的密码在旧的版本上的客户端就不好使了,而PHP中的MYSQL客户端都是3.23的(当然,mysqli的扩 展除外),问题就在这了。

目前已知解决方法:

1、进入命令行下,转到MYSQL目录的BIN目录下,进入MYSQL命令行模式:
?????????? 例:d:\mysql\bin>mysql -uroot -p123?????????? (用户root,密码123)
2、输入命令:
?????????? mysql>set password for 'root'@'localhost'=old_password('123');?????????? (注意冒号不能少)
3、退入MYSQL命令行:
?????????? mysql>\q

?

A.2.3. 客户端不支持鉴定协议

MySQL 5.1采用了基于密码混编算法的鉴定协议,它与早期客户端(4.1之前)使用的协议不兼容。如果你将服务器升级到4.1之上,用早期的客户端进行连接可能失败,并给出下述消息:

shell>?mysql客户端不支持服务器请求的鉴定协议:请考虑升级MySQL客户端。

要想解决该问题,应使用下述方法之一:

·?????????

·?????????用4.1版之前的客户端连接到服务器时,请使用仍具有4.1版之前风格密码的账户。

·?????????对于需要使用4.1版之前的客户端的每位用户,将密码恢复为4.1版之前的风格。可以使用SET PASSWORD语句和OLD_PASSWORD()函数完成该任务:

·????????????????mysql>?SET PASSWORD FOR·????????????????????????? ->?'some_user'@'some_host' = OLD_PASSWORD('newpwd');

也可以使用UPDATE和FLUSH PRIVILEGES:

mysql>?UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')????????? ->?WHERE Host = 'some_host' AND User = 'some_user';mysql>?FLUSH PRIVILEGES;

用你打算使用的密码替换前例中的“newpwd”。MySQL不能告诉你原来的密码是什么,因此,你需要选择新的密码。

·?????????通知服务器使用旧的密码混编算法:

1.????使用“--old-passwords”选项启动mysqld

2.????对于已将密码更新为较长4.1格式的每个账户,为其指定具有旧格式的密码。可以使用下述查询确定这些账户:

3.?????????????????mysql>?SELECT Host, User, Password FROM mysql.user4.?????????????????????????? ->?WHERE LENGTH(Password) > 16;

对于查询显示的每个账户记录,请使用Host和User值,并使用OLD_PASSWORD()函数以及SET PASSWORD或UPDATE之一指定密码,如前面所介绍的那样。

注释:在早期的PHP版本中,mysql扩展不支持MySQL 4.1.1和更高版中的鉴定协议。无论使用的PHP版本是什么,它均是正确的。如果你打算与MySQL 4.1或更高版本一起使用mysql扩展,需要使用前面介绍的选项之一,配置MySQL,以便与较早的客户端一起使用。mysqli扩展(支持“改进的MySQL”,在PHP 5中增加)与MySQL 4.1和更高版本中使用的改进的密码混编算法兼容,不需要对MySQL进行特殊配置就能使用该MySQL客户端库。关于mysqli扩展的更多信息,请参见http://php.net/mysqli。

?

   首先说明一下,下面描述仅限于Win系统 其它系统我没试过,Mysql 自从4.1.1以后修改了用户密码的格式, 从16位增加到了41位, 采用了一种新的验证方法,但4.1以前的客户端协议不支持这种方法,所以造成了不能登临的后果.即使密码正确了不能正常登录,提示如下:
#1250 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

在窗口中执行:
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('aa');
+-------------------------------------------+
| password('aa')              |
+-------------------------------------------+
| *DEE59C300700AF9B586F9F2A702231C0AC373A13 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>
从上可以看出password的结果返回了一个以'*'号开头的41位字符串,而以前是16位的.

Mysql官方网站给出了二种解决方法

1,使用新的客户端api,
2,强制服务器使用旧的密码方案

我首先尝试了第一种方法,从mysql5.0中拷贝libmysql.dll到php 的扩展目录中,替代了php本身附带的libmysql.dll, 但结果令人失望. Php提示装入php_mysql.dll失败:PHP Warning: PHP Startup: Unable to load dynamic library 'c:/php5/ext/php_mysql.dll' - 找不到指定的程序。后来发现新版的dll中缺少了mysql_drop_db() 这一函数 :( 只有等php或mysql出更新的dll了.

第一种方法行不通,只有试试第二种方法,mysql的官方FAQ中说,需要加上-old-password这一参数,我首先尝试 在命令行下起动mysql,
c:\mysql\bin>mysqld-nt -old-password

在另一窗口中 运行
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>

可以看出password的结果又变成16位的了,可喜的变化

我在c:\windows\my.ini (xp,98,中如此,2000中应在c:\wint\下)中的"mysqld"段中加入"set-variable=old-passwords"

例:
[mysqld]
basedir=C:/mysql
set-variable=old-passwords
datadir=C:/mysql/data
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe
user=root
password=mypassword

然后在服务管理器中起动mysql

Very Good, 一切正常,

后来我又发现,只要在mysql.user表中把password的字段长度改成16系统就自动切换到了oldpassword方式,改成改回41后 又自动换到了新的验证方式.
在这里提醒一下,更改password方式后,要重新设制一下密码并刷新一下权限(或重起mysql)

、先查看 /etc/rc.d/init.d/mysqld status 看看m y s q l 是否已经启动.
另外看看是不是权限问题.

2、确定你的mysql.sock是不是在那个位置,
mysql -u 你的mysql用户名 -p -S /var/lib/mysql/mysql.sock

3、试试:service mysqld start

4、如果是权限问题,则先改变权限 #chown -R mysql:mysql /var/lib/mysql

[root@localhost ~]# /etc/init.d/mysqld start
启动 MySQL: [ 确定 ]
[root@localhost ~]# mysql -uroot -p

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Xiaoyi upgraded to an intelligent agent! HarmonyOS NEXT Hongmeng native intelligence opens a new AI era Xiaoyi upgraded to an intelligent agent! HarmonyOS NEXT Hongmeng native intelligence opens a new AI era Jun 22, 2024 am 01:56 AM

On June 21, Huawei Developer Conference 2024 (HDC2024) gathered again in Songshan Lake, Dongguan. At this conference, the most eye-catching thing is that HarmonyOSNEXT officially launched Beta for developers and pioneer users, and comprehensively demonstrated the three "king-breaking" innovative features of HarmonyOSNEXT in all scenarios, native intelligence and native security. HarmonyOSNEXT native intelligence: Opening a new AI era After abandoning the Android framework, HarmonyOSNEXT has become a truly independent operating system independent of Android and iOS, which can be called an unprecedented rebirth. Among its many new features, native intelligence is undoubtedly the new feature that can best bring users intuitive feelings and experience upgrades.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values ​​to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

See all articles