用户管理

1. 账户管理

1.1 查看当前用户

3306 [(none)]>select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+---------------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)

1.2 用户白名单

支持的方式：

username@'10.0.0.%'
username@'%'
username@'10.0.0.100'
username@'localhost'
username@'www.example.com'
username@'10.0.0.5%'
username@'10.0.0.%'
username@'10.0.0.0/255.255.254.0'

1.3 用户创建

3306 [(none)]>create user yuanzhi@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

1.4 修改用户密码

3306 [(none)]>alter user yuanzhi@'10.0.0.%' identified by 'yuanzhi';
Query OK, 0 rows affected (0.00 sec)

1.5 删除用户

3306 [(none)]>drop user yuanzhi@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

2. 权限管理

2.1 授权

1 授予所有权限

3306 [(none)]>grant all on *.* to root@'10.0.0.%' identified by 'root';
Query OK, 0 rows affected, 1 warning (0.01 sec)

2 授予指定权限

3306 [(none)]>grant select,update,insert,delete on wordpress.* to wordpress@'10.0.0.%' identified by 'wordpress';
Query OK, 0 rows affected, 1 warning (0.00 sec)

2.2 查看权限

3306 [(none)]>show grants for 'wordpress'@'10.0.0.%';
+---------------------------------------------------------------------------------+
| Grants for wordpress@10.0.0.%                                                   |
+---------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wordpress'@'10.0.0.%'                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `wordpress`.* TO 'wordpress'@'10.0.0.%' |
+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2.3 回收权限

3306 [(none)]>revoke delete on wordpress.* from wordpress@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)

3. root密码丢失处理

1. 关闭数据库服务

root@ubuntu:~#:/app/mysql/support-files# ./mysql.server stop
Shutting down MySQL
.... *

2. mysqld_safe 启动

# 关闭认证，关闭TCP/IP连接
root@ubuntu:~#:/app/mysql/support-files# mysqld_safe --skip-grant-tables --skip-networking &
[1] 3991
root@ubuntu:~#:/app/mysql/support-files# 2020-04-20T15:32:55.026585Z mysqld_safe Logging to '/data/mysql/ubuntu.err'.
2020-04-20T15:32:55.060397Z mysqld_safe Starting mysqld daemon with databases from /data/mysql

3. 修改密码

root@ubuntu:~#:/app/mysql/support-files# mysql -uroot
...
3306 [(none)]>flush privileges;
Query OK, 0 rows affected (0.01 sec)

3306 [(none)]>alter user root@'localhost' identified by 'root';
Query OK, 0 rows affected (0.00 sec)