前言：本文件介紹MYSQL修改使用者密碼的方法和忘記root密碼後的處理方法。

一、三種修改密碼的方式

1、修改當前使用者的登入密碼

指令碼：SET PASSWORD=PASSWORD（'netpassword');

例：

mysql> set password=password('root');

Query OK, 0 rows affected (0.00 sec)

2、使用SET修改其他使用者的密碼，透過ROOT修改其他賬號的密碼

指令碼：SET PASSWORD FOR

'user' @'host'=PASSWORD('newpassword')

例：

mysql> select host,user from mysql.user;

+----------------+--------+

| host | user |

+----------------+--------+

| | test |

| % | john1 |

| % | mytest |

| 127.0.0.1 | root |

| 192.168.189.71 | john |

| 192.168.189.71 | john1 |

| ::1 | john |

| ::1 | root |

| localhost | john |

| localhost | root |

| mysql | root |

+----------------+--------+

11 rows in set (0.00 sec)

mysql> SET PASSWORD FOR

-> 'john1' @'%'=PASSWORD('john');

Query OK, 0 rows affected (0.00 sec)

3、使用UPDATE語句修改其他使用者的密碼，需要有修改MYSQL許可權的使用者，一般用ROOT使用者

指令碼：

UPDATE mysql.user SET

PASSWORD=PASSWORD('newpassword')

WHERE

User='username'

AND

Host='host';

例：

mysql> UPDATE mysql.user SET

-> PASSWORD=PASSWORD('root')

-> where

-> User='root' AND Host='127.0.0.1';

Query OK, 0 rows affected (0.02 sec)

Rows matched: 0 Changed: 0 Warnings: 0

二、忘記ROOT密碼的處理方法

1、關閉資料庫

指令碼：[root@mysql etc]# service mysql stop

2、使用指令碼： mysqld_safe --skip-grant-tables 啟動資料庫

使用/usr/bin/mysqld_safe --skip-grant-tables&啟動資料庫

3、使用空密碼進入資料庫（mysql命令後直接回車）

[root@mysql ~]# mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 3

Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

4、使用UPDATE語句修改ROOT密碼

mysql> UPDATE mysql.user SET

-> PASSWORD=PASSWORD('root')

-> where

-> User='root' AND Host='localhost';

5、關閉資料庫並重新以正常方式啟動資料庫

[root@mysql ~]# service mysql restart;

Shutting down MySQL.. SUCCESS!

Starting MySQL.. SUCCESS!

[root@mysql ~]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@mysql ~]# mysql -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.20-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

顯示可以成功登入，整個過程很簡單。大功告成！！！

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

本文作者：JOHN

ORACLE技術部落格：ORACLE 獵人筆記資料庫技術群：367875324 （請備註ORACLE管理）

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

MYSQL修改密碼和忘記ROOT密碼

相關文章