解決修改root密碼鏈接不上mysql數據庫

今天用Navicat for MySQL 直接修改的mysql root的密碼，但是卻提示鏈接不到數據庫了,用的是dedecms一直以為是自己的common.inc.php的配置文件出錯，重新保存utf-8格式，重新上傳，直接從服務重啟mysql，重啟IIS還是不行 自己又隨便寫了個test.php鏈接數據庫測試，結果顯示： Could not connect: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD(‘your_existing_password’). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file 說明不是，配置文件的問題，是修改密碼的問題，把如上問題放到百度： 1. Change MySQL to NOT to use old_passwords It seems that even MySQL 5.x versions still default to the old password hashes. You need to change this in “my.cnf” (e.g. /etc/my.cnf): remove or comment out the line that says

	  
		 代碼如下
		復制代碼
	  
	  
		
old_passwords = 1
//old_passwords = 1為16位
//所以這裡要先
//SET old_passwords = 0;
//這樣使新密碼的長度也變成41位

	  
	Restart MySQL. If you don’t, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function. You can test this by running:

	  
		 代碼如下
		復制代碼
	  
	  
		
mysql> SELECT Length(PASSWORD('xyz'));

	  
	結果如下圖：很明顯16位

The old password hashes are 16 characters, the new ones are 41 characters.

2. Change the format of all the passwords in the database to the new format Connect to the database, and run the following query:

	  
		 代碼如下
		復制代碼
	  
	  
		
mysql> SELECT user, Length(`Password`) FROM `mysql`.`user`;

	  
	This will show you which passwords are in the old format, ex:

如圖： Notice here that each user can have multiple rows (one for each different host specification). To update the password for each user, run the following:

	  
		 代碼如下
		復制代碼
	  
	  
		
UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';

	  
	如圖：

Finally, flush privileges:

	  
		 代碼如下
		復制代碼
	  
	  
		
FLUSH PRIVILEGES;

	  
	最後再查詢一下

	  
		 代碼如下
		復制代碼
	  
	  
		
mysql> SELECT user, Length(`Password`) FROM `mysql`.`user`;

	  
	結果如下圖：（變成41位了，成功）

成功後退出exit

最後重新啟動一下mysql

	  
		 代碼如下
		復制代碼
	  
	  
		
service mysqld restart

	  
	ok

 

 代碼如下 復制代碼 mysql>
SET old_passwords = 0;
UPDATE mysql.user SET Password = PASSWORD('testpass') WHERE User = 'testuser' limit 1; 
SELECT LENGTH(Password) FROM mysql.user WHERE User = 'testuser'; 
FLUSH PRIVILEGES;