MySQL的安全管理
MySQL如何进行用户管理
用户管理
- 创建用户
create user 'local'@'localhost' identified by 'password'
# 本机登录用户
create user 'local'@'192.168.0.1' identified by 'password'
# 远程登录用户
create user 'local'@'192.168.0.%' identified by 'password'
# 网段用户授权
local 用户名
localhost 只允许在本机登录（MySQL安装的机器）
password 密码
- 查询用户
select user,host from mysql.user;
select * from mysql.user\G;
\G 表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向。
- 删除用户
drop user 'local'@'192.168.0.1';
控制MySQL用户权限
- 权限分类
- 全局权限
- 数据库权限
- 表和列权限
- 常用权限
| 权限 | 解释 |
|---|---|
| insert | 允许写入数据 |
| delete | 允许删除数据 |
| select | 允许查询数据 |
| update | 允许更新数据 |
| create | 允许创建库和表 |
| create role | 允许创建角色 |
| create user | 启用创建用户、删除用户、重命名用户和撤销所有权限 |
| drop | 允许删除库、表、视图等 |
| drop role | 开启删除角色 |
| alter | 允许修改表结构 |
| lock tables | 在具有select权限的表上启用锁表权限 |
| show databases | 开启查看所有库的权限 |
| super | 允许使用其他管理操作，比如: CHANGE REPLICATION SOURCE TO,CHANGE MASTER TO,KILLPURGE BINARY LOGS.SET GLOBAL，还包括mysgladmin debug 命令 |
| all | 除grant option 和proxy权限外，赋予其他所有权限 |
| 更多 MySQL 权限可参考官方文档: https://dev.mysql.com/doc/refman/8.0/en/grant.html |
show privileges;
# 显示MySQL支持的所有权限
- 用户授权
grant insert,delete,select,update on database.* to 'local'@'localhost';
# 给 'local'@'localhost' 用户赋予 database 数据库下所有表增删改查权限
grant insert,delete,select,update on database.table to 'local'@'localhost';
# 给用户赋予数据库下某个表的增删改查权限
grant select(id,name) on database.table to local@localhost;
# 给用户 local@localhost 赋予 database.table 的查询id，name字段的权限
- 查询权限
show grants for 'local'@'localhost';
- 权限回收
revoke select,insert,delete on database.* from 'local'@'localhost';
- 权限授予的原则
使用不同的用户
权限最小原则
避免使用root
MySQL角色的管理
- 创建角色
create role 'java_developer'
- 给角色赋权
grant select,update,delete,insert on database.* to 'java_developer';
- 使用角色
create user 'test'@'localhost' indentified by '123456';
grant 'java_developer' to 'test'@'localhost';
# 给用户分配角色
show grants for 'test'@'localhost';
- 撤销角色
revoke java_developer from 'test'@'localhost';
- 删除角色
drop role java_developer;
MySQL密码的管理
- 修改root密码
alter user user() identified by 'password'
- 修改普通用户的密码
alter user 'local'@'localhost' identified by 'password'
- 设置随机密码
create user 'local'@'localhost' identified by random password;
# 新建用户随机密码
alter user 'local'@'localhost' identified by random password;
# 修改已经存在的用户随机密码
- 强制使用强密码
install component 'file://component_validate_password';
show global variables like 'validate_password%';
# 显示密码规则
- 密码试错
create user 'test'@'localhost' identified by '123456' failed_login_attempts 4 password_lock_time 3;
# 连续错误输入4次就会锁定3天
grant select on *.* to 'test'@'localhost';
# 赋权，登录测试
如何限制用户使用资源
- 用户资源限制的参数
| 参数 | 解释 |
|---|---|
| MAX_QUERIES_PER_HOUR | 每小时限制多少次查询 |
| MAX_UPDATES_PER_HOUR | 每小时限制多少次更新 |
| MAX_CONNECTIONS_PER_HOUR | 账户每小时可以连到服务器的次数 |
| MAX_USER_CONNECTION | 一个账号同时连接到服务器的数量 |
- 资源限制的使用
创建用户时限制资源
create user 'test'@'localhost' identified by '123456' with max_queries_per_hour 500 max_updates_per_hour 100 max_connections_per_hour 50 max_user_connections 50;
- 修改现有用户的资源限制
alter user 'test'@'localhost' with max_queries_per_hour 100;
alter user 'test'@'localhost' with max_queries_per_hour 0;
# 取消限制 设为 0
- 重置资源使用计数
flush user_resources;
# 或者
flush privileges;
# 两种，或者重新设置资源次数也会重置
忘记root密码怎么办？
- 配置文件增加跳过权限验证的配置
vim /data/mysql/conf/my.cnf
# 在[mysqld]下增加
skip-grant-tables
- 重启mysql
- 免密方式登录mysql
mysql -uroot -p
- 修改root密码
update mysql.user set authentication_string='' where user = 'root' and host = 'localhost';
# 再去掉免密参数
vim /data/mysql/conf/my.cnf
# 删除 skip-grant-tables
- 重启MySQL
- 修改密码并重新登录
# 空密码登录MySQL
mysql -uroot -p
# 修改密码
alter user user() identified by '123456';
SQL MODE 详解
- 查询SQL_MODE
select @@global.sql_mode;
# 查询当前会话的SQL_MODE
select @@session.sql_mode;
- 设置SQL_MODE
# 启动时设置
mysql --sql-mode=""
# 配置文件中增加
vim /data/mysql/conf/my.cnf
# 在[mysqld]下新增，多个,隔开
sql-mode=""
# 设置全局
set global sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
# 设置会话级别
set session sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
# 原有基础上删除某一个sql_mode
set session sql_mode=sys.list_drop(@@session.sql_mode,'NO_ENGINE_SUBSTITUTION')；
# 原有基础上添加一个sql_mode
set session sql_mode=sys.list_add(@@session.sql_mode,'NO_ENGINE_SUBSTITUTION')；
- 常见SQL MODE解释
| SQL MODE | 解释 |
|---|---|
| STRICT_TRANS_TABLES | 为事务存储引擎启用严格的SQL模式，在插入不合法数据时，MySQL将不再插入"默认值”，而是抛出错误。这样可以确保数据的完整性和一致性。这种模式比较适合对数据完整性要求严格的场景 |
| ANSI | 等于REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、 IGNORE_SPACE和ONLY_FULL_GROUP_BY |
| TRADITIONAL | 可以理解为让MySQL像传统的SQL数据库系统一样运,等于STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO和NO_ENGINE_SUBSTITUTION |
| ONLY_FULL_GROUP_BY | 如果某个列不在group by列表中，此时如果不对该列进行聚合处理，则该列不能出现在select列表、having条件中以及order by列表中 |
| NO_BACKSLASH_ESCAPES | 启用此模式将禁止使用反斜杠字符"\"作为字符串和标识符中的转义字符，反斜杠将像其他字符串一样变成普通字符 |
| PIPES_AS_CONCAT | 将”||“视为字符串连接操作符 |
| NO_ENGINE_SUBTITUTION | 启用此模式时，如果创建或者更改表时指定了不可用的存储引擎，则报错不执行;未启用此模式时，会替换成默认存储引擎 |
| NO_ZERO_DATE | 启用此模式，"0000-00-00" 则允许插入并产生警告，如果启用此模式和严格模式，则不允许"0000-00-00"插入，并会报错；除非IGNORE。对于INSERT IGNORE and UPDATE IGNORE，"0000-00-00"是允许的，插入会产生警告 |
| ERROR_FOR_DIVISION_BY_ZERO | 启用此模式,则除以零会插入NULL并产生警告；启用此模式和严格模式，除以零会产生错误， 除非IGNORE 也给出。对于INSERT IGNORE 和 UPDATE IGNORE，除以零插入NULL并产生警告 |
| NO_ZERO_IN_DATE | 如果启用此模式，则零部分的日期将被插入‘0000-00-00’并产生警告: 如果启用此模式和严格模式，则不允许包含0的日期插入，会直接报错 |
| REAL_AS_FLCAT | 把REAL类型(存储大小4个字节，可精确到小数点后第7位)看成FLOAT类型(存储大小为8字节，可精确到小数点后第15位) |
| ANSI_QUOTES | 双引号将被视为标识符的引号，而不是字符串的引号 |
| IGNORE_SPACE | 函数名称和(之间允许有空格，会导致内置的函数变成保留字 |
| 更多SQL MODE | https://dev.mysql.com/doc/refman/8.0/en/sgl-mode.html |
特定场景下使用MySQL数据库对象
视图创建与应用
- 作用
简化查询
保护数据
重用查询
- 创建视图
# 举例
# 经常使用的SQL
select * from student_info where score >= 90;
# 创建视图
create view view_test as select * from student_info where score >= 90;
# 查询视图,相当于执行SQL
select * from view_test;
# 查看视图结构
show create view view_test;
# 删除视图
drop view view_test;
存储过程
存储过程的使用方法
- 创建存储过程
# 设置SQL语句以 ;; 结束
delimiter ;;
# database 创建存储过程
create procedure user_count_proc(out s int)
begin
select count(*) into s from mysql.user;
end;;
# 改回 ; 结束
delimiter ;
- 使用存储过程
# 把存储过程的结果赋予 变量s
call user_count_proc(@s);
# 查询变量s
select @s;
- 查询存储过程定义
show create procedure user_count_proc \G;
- 删除存储过程
drop procedure user_count_proc;
- 案例
# 创建测试数据
...
# 创建存储过程
delimiter ;;
# database 创建存储过程
create procedure insert_test()
begin
declare i int;
set i=1;
# 循环插入
while(i<=10000)do
insert into table()a,b) values('xxx',i);
set i = i+1;
end while;
end;;
delimiter ;
# 运行
call insert_test()
- 存储过程的缺点
- 不方便维护
- 存储开销大
- 可移植性差
触发器的创建与使用
- 创建触发器
create trigger insert_class_info after insert on class_info_01 for each row insert into class_info_02(id,name) values (new.id,new.name);
# class_info_01 表有insert操作就会触发往 class_info_02 表里面写入 class_info_01 表里面新增的记录
# 测试
- 触发器的应用场景
- 数据复制和同步
- 审计
- 数据备份
MySQL版本特性
- MySQL5.5
- 引入的新功能
- 半同步复制
- 实时线程
- Buffer Pool可拆分为多个Instances
- 元数据锁
- 默认存储引擎改成InnoDb
- 安全性改进
- 缺点
- 不支持全文搜索
- 不支持并行复制
- 并发能力差
- MySQL5.6
- 引入的新功能
- 支持GTID复制
- 密码策略调整
- 元数据锁的优化
- 引入了基于库的并行复制
- 引入了ICP,MRR等特性
- Undo Log可保存在独立表空间中
- 缺点
- 基于库的并行复制在大多数场景都不适用
- 很多全局参数不能动态修改
- 不支持JSON数据类型
- MySQL5.7
- 引入的新功能
- 组复制
- 基于WRITESET的并行复制
- 虚拟列
- 原生支持JSON类型
- 支持动态调整很多全局参数
- 支持临时表设置为InnoDB存储引擎
- 缺点
- 社区版无额外端口
- 大表增加字段开销大
- 所有线程优先级都一样，并且都是共享的
- MySQL8.0
- 引入的新功能
- 事务性数据字典
- 快速加列
- 原子DDL
- 不可见索引
- 降序索引
- 角色管理
- 资源组
- 持久化全局变量
- Hash Join
- 额外端口
- 自增主键的持久化
- 数据字典的好处？
- 保证数据的完整性和一致性
- 支持事务的回滚和恢复
- 提高系统的性能和扩展性
DBA必备选择存储引擎的技能
- 存储引擎常见操作
查看MySQL支持哪些存储引擎
show engines\G;
设置存储引擎
use database;
# 创建时
create table user(id int) engine=InnoDB;
# 修改当前会话的默认存储引擎
set default_storage_engine=Memory;
修改存储引擎
alter table user engine=InnoDB;
- 常用的存储引擎
- InnoDB
- MyISAM
- TokuDB
- Columnstore
- MEMORY
- CSV
- InnoDB的优点
- 支持事务
- 行级锁
- 并发控制
- 高新能
- 支持热备份
- InnoDB的适用场景
- 高可靠性应用程序
- 对并发要求高的场景
- 数据一致性要求高的场景
- MyISAM的缺点
- 并发能力差
- 不支持事务
- 备份困难
- 缓存池只缓存索引，不缓存数据
- 不支持外键约束
- TokuDB的特点
- 高压缩比
- 快速写入和删除
- 在线索引创建，字段增加删除和重名名
- 在线备份
- 快速恢复
- TokuDB适用场景
- 高并发写入的场景
- 数据量大的场景
- 要求高可靠性的场景
- Columnstore的特点
- 列式存储
- 压缩
- 分布式架构
- 高性能查询
- 实时数据加载
- Columnstore的适用场景
- 特别适用大数据的场景
- 高并发OLTP的应用场景
- 实时分析场景
- MEMORY的特点
- 读写快
- 表锁
- 不支持事务
- 容易丢失数据
- 主从架构中的问题
- MEMORY存储引擎的应用场景
- 临时表
- 缓存表
- 计数器和排行榜
- CSV的特点
- 数据文件CSV格式，可直接查看数据
- CSV存储引擎不支持索引
- CSV存储引擎不支持分区
- CSV存储引擎的适用场景
- 临时性数据存储
- 数据导出
- 备份和恢复
网友评论