用户

创建用户

-- 创建用户，并允许其在任何IP登陆
create user 'username'@'%' identified by 'password';

-- 创建用户，并允许其在任何主机登陆，不设置密码
create user 'username'@'%';

-- 创建用户，并仅允许使用domian.com域名的主机登陆
create user 'username'@'domian.com' identified by 'password';

-- 创建用户，并仅允许使用192.168.1.1的主机登陆
create user 'username'@'192.168.1.1' identified by 'password';

-- 创建用户，并仅允许使用192.168.1开头的主机登陆
create user 'username'@'192.168.1.%' identified by 'password';

密码可以为空，如为空，则可以免密登陆
如果主机位使用 %，表示允许任意地址的主机登陆
主机位可以使用域名或者 IP 地址，但是不允许既有数字又有字母
主机位中可以使用 % 进行通配，例如：%.domian.com 或 192.168.1.%

修改用户密码

rename user 'old_username'@'old_ip_addr' to 'new_username'@'new_ip_addr';

set password for 'username'@'ip_addr'=Password('new_password');

//修改完密码，需将配置刷到内存
flush privileges;

用户名重命

1	rename user 'username'@'ip_addr' to 'zhiqiang'@'%';

删除用户

1	drop user 'username'@'ip_addr';

使用户过期

-- 90天过期
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE INTERVAL 90 DAY; 
-- 密码不过期
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE NEVER; 
-- 默认过期策略
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE DEFAULT; 
-- 强制用户密码过期
ALTER USER 'username'@'ip_addr' PASSWORD EXPIRE;

用户权限

查看权限

1	show grants for 'username'@'ip_addr';

权限授予

-- 授予用户所有库所有表的所有权限
grant all privileges on *.* to 'username'@'ip_addr';

-- 授予用户database1库所有表的所有权限
grant all privileges on `database1`.* to 'username'@'ip_addr';

-- 授予用户database1库table1表的所有权限
grant all privileges on `database1`.`table1` to 'username'@'ip_addr';

-- 授予用户database1库所有表的只读权限
grant select on `database1`.* to 'username'@'ip_addr';

-- 授予用户database1库table1表的插入、更新权限
grant insert,update on `database1`.`table1` to 'username'@'ip_addr';

权限回收

-- 回收用户所有权限
revoke all,grant option from 'username'@'ip_addr';

-- 回收用户database1库所有表的只读权限
revoke select ON `database1`.* FROM 'username'@'%';

DDL和DML

DDL(Data Definition Language 数据定义语言):用来定义数据库的对象,比如创建表、修改表结构等操作。主要的DDL语句有CREATE、ALTER、DROP等。
DML(Data Manipulation Language 数据操作语言):用来查询或修改数据库中表记录。主要的DML语句有INSERT、UPDATE、DELETE、SELECT等。
只读权限：SELECT 权限：授予用户查询表数据的权限。是只读权限中最基本的权限。SHOW VIEW 权限：允许用户查看数据库中的视图。REPLICATION CLIENT 权：允许用户查询主从复制状态。EXECUTE 权限：允许用户执行存储过程和函数。该权限无法修改数据。LOCK TABLES 权限：允许用户对表加锁,可用于读操作的性能优化。EVENT 权限：允许用户查看事件的定义,用于只读事件相关信息。TRIGGER 权限：允许用户查看触发器的定义,无法修改触发器。

实战：我现在这个数据连接中有 a，b，c三个数据库

将a和b 给zhiqiang用户DDL和DML（zhiqiang只拥有a和b的管理员权限）

// 先撤销用户对数据库的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//授予zhiqiang用户对a、b数据库的全部权限,包括DDL和DML权限
GRANT ALL PRIVILEGES ON a.* TO 'zhiqiang'@'%';
GRANT ALL PRIVILEGES ON b.* TO 'zhiqiang'@'%';
//最后授权查询mysql.user表的权限,用于确保用户可以验证自己的权限设置
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

场景2：将a和b 给zhiqiang用户DDL权限

//撤销zhiqiang原有所有数据库权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//针对a、b数据库授权常见的DDL操作权限如ALTER、CREATE、DROP等
GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON a.* TO 'zhiqiang'@'%';
GRANT CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON b.* TO 'zhiqiang'@'%';  
//最后再授权查询mysql.user表权限以方便权限验证
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

场景3：将a和b 给zhiqiang用户DML权限

//撤销zhiqiang用户所有数据库的权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//授予zhiqiang对a、b数据库的SELECT/INSERT/UPDATE/DELETE权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON a.* TO 'zhiqiang'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, SHOW VIEW, EVENT, TRIGGER ON b.* TO 'zhiqiang'@'%';
//授权查询mysql.user表权限用于权限验证
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

场景4：将a和b给zhiqiang用户只读权限

//撤销zhiqiang所有数据库权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zhiqiang'@'%';
//授予zhiqiang用户对a、b数据库的SELECT, SHOW VIEW, EVENT, TRIGGER查询权限
GRANT SELECT, LOCK TABLES, SHOW VIEW ON a.* TO 'zhiqiang'@'%';  
GRANT SELECT, LOCK TABLES, SHOW VIEW ON b.* TO 'zhiqiang'@'%';
//授权查询mysql.user表用于权限验证
GRANT SELECT ON mysql.user TO 'zhiqiang'@'%';
//显示的权限设置情况确认是否符合要求
SHOW GRANTS FOR 'zhiqiang'@'%';
//刷新权限
flush privileges;

场景5: 给当前所有数据库给zhiqiang用户子只读权限

//生成只读的sql语句
SELECT CONCAT('GRANT SELECT, LOCK TABLES, SHOW VIEW ON ', schema_name, '.* TO ''zhiqiang''@''%'';') as sqls FROM information_schema.schemata;
//按需执行返回的sqls
.....
//刷新权限
flush privileges;

备份与恢复

-- 备份database1库所有表结构+数据
mysqdump -u username database1 > database1.sql -p

-- 备份database1库所有表结构
mysqdump -u username -d database1 > database1.sql -p

-- 进入数据库
use database1;
-- 将备份文件恢复到数据库中
mysqdump -u username -d database1 < database1.sql -p

-- 备份192.168.3.10中的database数据
mysqldump -h 192.168.3.10 -u root -p123456 database > database.sql
-- 还原数据库
mysql -h 192.168.3.11 -P 3306 -u root -p123456 database < database.sql

常用权限列表

权限	作用范围	作用
all	服务器	所有权限
select	表、列	选择行
insert	表、列	插入行
update	表、列	更新行
delete	表	删除行
create	数据库、表、索引	创建
drop	数据库、表、视图	删除
reload	服务器	允许使用flush语句
shutdown	服务器	关闭服务
process	服务器	查看线程信息
file	服务器	文件操作
grant option	数据库、表、存储过程	授权
references	数据库、表	外键约束的父表
index	表	创建/删除索引
alter	表	修改表结构
show databases	服务器	查看数据库名称
super	服务器	超级权限
create temporary tables	表	创建临时表
lock tables	数据库	锁表
execute	存储过程	执行
replication client	服务器	允许查看主/从/二进制日志状态
replication slave	服务器	主从复制
create view	视图	创建视图
show view	视图	查看视图
create routine	存储过程	创建存储过程
alter routine	存储过程	修改/删除存储过程
create user	服务器	创建用户
event	数据库	创建/更改/删除/查看事件
trigger	表	触发器
create tablespace	服务器	创建/更改/删除表空间/日志文件
proxy	服务器	代理成为其它用户
usage	服务器	没有权限