mysql创建用户并赋予用户权限详细操作教程

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'

mysql> create user 'lisi'@'localhost' identified by '123123';
Query OK, 0 rows affected (0.00 sec)

mysql> select password(123123);
+-------------------------------------------+
| password(123123)  |
+-------------------------------------------+
| *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec
//上面的码就是加密过后的123123

select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> use mysql;

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+-----------+
| user | authentication_string  | host |
+---------------+-------------------------------------------+-----------+
| root |   | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| lisi | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | localhost |
+---------------+-------------------------------------------+-----------+
4 rows in set (0.00 sec)
//lisi就是我们添加的

[root@web3 ~]# mysql -ulisi -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.20-log Source distribution
//成功

RENAME USER 'old_user'@'localhost' TO 'new_user'@'host'

mysql> RENAME USER 'lisi'@'localhost' TO 'zhangsan'@'192.168.200.4';
Query OK, 0 rows affected (0.00 sec)

use mysql

mysql> select user,authentication_string,host from user;
+---------------+-------------------------------------------+---------------+
| user  | authentication_string   | host  |
+---------------+-------------------------------------------+---------------+
| root  |      | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| zhangsan | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | 192.168.200.4 |
+---------------+-------------------------------------------+---------------+
4 rows in set (0.00 sec)

SET PASSWORD = PASSWORD('password')

SET PASSWORD='username'@'host'=PASSWORD('password')；

mysql_safe --skip-grant-tables&
//这时mysql已经启动
mysql 直接不使用密码进入，使用update修改密码
注：修改密码后 刷新数据库
flush privileges；
然后就去尝试登录新密码root

grant all on *.* to lisi@'192.168.1.%' identified by '111111';

SHOW GRANTS FOR 'username'@'主机地址'；

 revoke all on *.* from lisi@'192.168.1.%';