忘记MySQL密码，MySQL用户管理

1、忘记MySQL密码-恢复密码

（1）停止服务

[root@localhost ~]# systemctl stop mysqld

（2）修改配置文件

#socket=/var/lib/mysql/mysql.sock
skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks

（3）重起服务

root@localhost ~]# systemctl start mysqld

（4）登入数据库

[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14 Source distribution
Copyright (c) 2000, 2016, 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>

（5）切换当前数据库

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>

（6）修改密码

mysql> update user set authentication_string=password('123456') where
user='root' and Host = 'localhost';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 1

Mysql8.0以上版本

mysql> flush privileges;
mysql> alter user 'root'@'localhost'IDENTIFIED BY 'newpassword';

（7）退出数据库并停止服务

mysql> exit
Bye
[root@localhost /]# systemctl stop mysqld

（8）修改配置文件

#socket=/var/lib/mysql/mysql.sock
#skip-grant-tables
# Disabling symbolic-links is recommended to prevent assorted security risks

（9）重起服务登入系统

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -p'ABC123com'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.14 Source distribution
Copyright (c) 2000, 2016, 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>

2、MySQL用户管理

2.1 登录和退出MySQL

示例：

mysql -h192.168.5.240 -P 3306 -u root -p123 mysql -e ‘select user,host from user’
 -h 指定主机名
 -P MySQL服务器端口
 -u 指定用户名
 -p 指定登录密码
此处mysql为指定登录的数据库
 -e 接SQL语句

2.2 创建用户

方法一：CREATE USER语句创建
CREATE USER user1@’localhost’ IDENTIFIED BY ‘123456’;

方法二： INSERT语句创建
INSERT INTO mysql.user(user,host, password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘user2’,’localhost’,password('123456'),’’,’’,’’);
FLUSH PRIVILEGES;

方法三： GRANT语句创建
GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘123456’;
FLUSH PRIVILEGES;

2.3 删除用户

方法一：DROP USER语句删除
DROP USER user1@’localhost’;

方法二：DELETE语句删除
DELETE FROM mysql.user WHERE user=’user2’ AND host=’localhost’;
FLUSH PRIVILEGES;

2.4修改用户密码

root修改自己密码

方法一：
mysqladmin -uroot -p123 password 'new_password'//123为旧密码

方法二：
UPDATE mysql.user SET password=password(‘new_password’) WHERE user=’root’ AND host=’localhost’;
FLUSH PRIVILEGES;

方法三：
SET PASSWORD=password(‘new_password’);
FLUSH PRIVILEGES;

root修改其他用户密码

方法一：
SET PASSWORD FOR user3@’localhost’=password(‘new_password’);
FLUSH PRIVILEGES;

方法二：
UPDATE mysql.user SET password=password(‘new_password’) WHERE user=’user3’ AND host=’localhost’;
FLUSH PRIVILEGES;

方法三：
GRANT SELECT ON *.* TO user3@’localhost’ IDENTIFIED BY ‘localhost’;
FLUSH PRIVILEGES;

普通用户修改自己密码

方法一：
SET password=password(‘new_password’);

方法二：
mysqladmin -uzhuzhu -p123 password 'new_password' //123为旧密码