MySql用户操作

ROOT用户登陆并修改密码

mysql -u root -p # root用户登陆mysql，首次登陆的密码为安装时手动设置
alter user 'root'@'localhost' identified WITH mysql_native_password BY 'LI1234MYSQL!'; # 修改root密码，默认root用户只能本地登陆mysql
show variables like '%%version%'; # mysql环境和状态

常用操作

show variables like '%conn%'; # 连接数配置情况，set global max_connections=1000 # 自定义最大连接数
show full processlist; # 连接情况
select USER , count(*) from information_schema.processlist group by USER; # 统计用户的连接数
select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip; # 统计IP的连接数
select host,user,time,state,info from information_schema.processlist order by time desc limit 10; # 连接时间最长的的连接

# 显示所有数据库实例
show databases;
# 删除数据库实例
DROP DATABASE 数据库实例名称;

# 切换到mysql数据库实例
use mysql; 
# 显示所有表
show tables; 
# 查询user表的字段
# show columns from user; 
# 查询user表 - 5.7版本之前
# select host, user, password from mysql.user; 
# 查询user表 - 5.7版本及之后
select host, user, authentication_string from mysql.user; 
# 删除用户
DROP USER '用户账号';

# 用户授权类型
# all privileges：所有权限
# select：读取权限
# create：创建权限
# delete：删除权限
# update：更新权限
# drop：删除数据库、数据表权限
#
# 回收用户权限
revoke all on 数据库实例名称.表名 from '用户账号'@'%';
FLUSH PRIVILEGES;

用户表说明

# 用户列
Host：主机名，双主键之一，值为%时表示匹配所有主机	
User：用户名，双主键之一	
Password：密码名

# 权限列
Select_priv	确定用户是否可以通过SELECT命令选择数据
Insert_priv	确定用户是否可以通过INSERT命令插入数据
Delete_priv	确定用户是否可以通过DELETE命令删除现有数据
Update_priv	确定用户是否可以通过UPDATE命令修改现有数据
Create_priv	确定用户是否可以创建新的数据库和表
Drop_priv	确定用户是否可以删除现有数据库和表
Reload_priv 	确定用户是否可以执行刷新和重新加载MySQL所用各种内部缓存的特定命令,包括日志、权限、主机、查询和表重新加载权限表
Shutdown_priv	确定用户是否可以关闭MySQL服务器在将此权限提供给root账户之外的任何用户时,都应当非常谨慎
Process_priv 	确定用户是否可以通过SHOW PROCESSLIST命令查看其他用户的进程 
File_priv 	确定用户是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令

# 安全列
ssl_type	支持ssl标准加密安全字段
ssl_cipher	支持ssl标准加密安全字段
x509_issuer	支持x509标准字段
x509_subject	支持x509标准字段
password_expired	密码是否过期。Y：说明该用户密码已过期 N：没有过期
plugin	5.5.7开始,mysql引入plugins以进行用户连接时的密码验证,plugin创建外部/代理用户
authentication_string	通过authentication_string可以控制两者的映射关系,(PAM plugin等,PAM可以支持多个服务名)

# 资源控制列
max_questions unsigned	NO	0	每小时允许执行多少次查询：0表示无限制
max_updates	unsigned	NO	0	每小时可以执行多少次更新：0表示无限制
max_connections	unsigned	NO	0	每小时可以建立的多少次连接：0表示无限制
max_user_connections unsigned	NO	0	单用户可以同时具有的连接数：0表示无限制

MySql5.7创建用户并授权

# 模板：grant [权限类型] on [数据库名].[表名] to '[用户名]'@'[ip地址]';
# %表示允许该用户通过任意ip访问数据库实例
# 创建新用户并授权操作相关数据库实例的相关表
grant all privileges on 数据库实例名称.表名 to '用户账号'@'%' identified by '用户密码'; 
FLUSH PRIVILEGES; 

MySql8.0创建用户并授权

# 用户 dev 操作 snoob_dev数据库实例、用户 test 操作 snoob_test数据库实例
# 创建数据库实例
CREATE DATABASE 数据库实例名称 default character set utf8 collate utf8_general_ci;
# 创建用户
CREATE USER '用户账号'@'%' identified by '用户密码'; 
# 为用户添加操作数据库实例的权限
GRANT ALL PRIVILEGES on 数据库实例名称.表名 to '用户账号'@'%';
FLUSH PRIVILEGES; 

• 角色管理

# 需求：用户 lixing 通过角色操作 snoob_dev和snoob_test数据库实例
# 创建角色
create role 角色名称;
# 给角色授权操作数据库实例下相关表的权限
grant 权限类型 on 数据库实例名称.表明 to 角色名称;
# 给用户授权角色
grant 角色名称 to 用户账号;

# 查看用户的授权情况
show grants for 用户账号;

# 查看用户指定角色下的授权情况
show grants for 用户账号 using 角色名称;

# 查询当前激活的角色
SELECT CURRENT_ROLE();

# 临时激活角色
SET DEFAULT role 角色名称 TO 用户名称;

# 收回角色的权限
revoke 权限类型 on 数据库实例名称.表名 from 角色名称;
# 回收用户的角色
revoke 角色名称 from 用户名称;
# 删除角色
drop role 角色名称;