前言：本文档介绍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)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
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)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
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管理 ）
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++