03、MySQL安全管理和特性解析(DBA运维专用)

 03、MySQL安全管理和特性解析

本节主要讲MySQL的安全管理、角色使用、特定场景下的数据库对象、各版本特性以及存储引擎

目录

 03、MySQL安全管理和特性解析

1、 用户和权限管理

2、 MySQL角色管理

3、 MySQL密码管理

4、 用户资源限制

5、 忘记root密码处理办法

6、 SQL MODE详解

7、 视图、存储过程、触发器的使用

8、 MySQL5.5、5.6、5.7、8.0各个版本的特性

5.5特性

MySQL 5.6的特性

MySQL 5.7的特性

MySQL 8.0 新特性

2、MySQL加列三种算法是什么?区别是什么?

COPY 算法

INPLACE 算法

INSTANT 算法

3、假如入你是DBA，一个新业务研发考虑使用5.6，请你说服他们使用 MySQL8.0

9、 MySQL各个存储引擎介绍

1、InnoDB存储引擎和MyISAM存储引擎区别和适用场景？

2、TokuDB有哪些应用场景?

3、适用MEMORY存储引擎要注意哪些问题?

4、CSV存储引整有哪些适用场景?

5.为什么从MySQL5.5开始，MySQL默认的存储引擎改成了InnoDB?

6.假如你管理的业务线，有多张MyISAM表，你会怎么处理?

---

1、 用户和权限管理

创建/删除用户

某个同事要申请一个haohao库的远程只读用户，他的IP是192.168.1.3 可移执行：
CREATE USER 'haohao_r'@'192.168.1.3' IDENTIFIED BY 'password';
如果是客户端多个网段可移植执行：
CREATE USER 'haohao_r'@'192.168.1.%' IDENTIFIED BY 'password';
​
查用户
select user,host from mysql.user;
删除用户
DROP USER 'haohao'@'192.168.1.%';

用户授权

GRANT insert,delete,select,update ON haohao.* TO 'haohao'@'localhost';
如果要给力某列授权就执行：
CREATE USER haohao_r_1@localhost IDENTIFIED BY 'password';
grant select(id,name) on haohao.grant_test to haohao_r_1@localhost;

查用户权限

show grants for haohao_r_1@localhost;

回收权限

REVOKE INSERT ON haohao.* FROM 'haohao'@'localhost';

2、 MySQL角色管理

创建角色

CREATE ROLE 'app_developer';
给角色授权
GRANT select,update,delete,insert ON app_db.* TO 'app_developer';使用角色，受先创建一个用户：
create user 'haohao_role'@'localhost' identified by 'IHB87Edsa';
然后给用户富余角色，用来替换GRANT SELECT,UPDATE,DELETE,INSERT:
grant 'app_developer' to 'haohao_role'@'localhost';用户撤销角色
revoke app_developer from 'haohao_role'@'localhost';
删除角色
drop role app_developer;

3、 MySQL密码管理

修改root密码

ALTER USER user() IDENTIFIED BY 'xxx';

修改普通用户密码

alter user 'haohao'@'localhost' identified by 'xxx';
ALTER USER 'test_user'@'localhost' IDENTIFIED BY 'NewPassword123!';

查看密码相关参数：

show global variables like 'validate_password%';

4、 用户资源限制

创建时限制资源

CREATE USER 'haohao_02'@'localhost' identified by 'Udapadg999a^'
WITH MAX_QUERIES_PER_HOUR 500 
MAX_UPDATES_PER_HOUR 100
MAX_CONNECTIONS_PER_HOUR 50
MAX_USER_CONNECTIONS 50;

修改现有用户的资源限制

ALTER USER 'haohao'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
如果要曲线限制，就把值设置成0
ALTER USER 'haohao'@'localhost' WITH MAX_QUERIES_PER_HOUR 0;

重置资源使用计数

FLUSH USER_RESOURCES;
或者
FLUSH PRIVILEGES

5、 忘记root密码处理办法

编辑配置文件

vim /data/mysql/conf/my.cnf 
增加
skip-grant-tables
重启mysql
/etc/init.d/mysql.server restart

免密方式登录

mysql -uroot -p
执行修改密码报错
alter user user() identified by 'dup81Gcda2';
直接修改用户表，把root密码设置为空：
update mysql.user set authentication_string = '' where user = 'root' and host='localhost';
再重启然后用空密码登录
​
最后修改密码
alter user user() identified by 'dup81Gcda2';

6、 SQL MODE详解

• SQL 模式（SQL Mode）是 MySQL 数据库中的一个配置选项，它用于定义 MySQL 应该如何处理 SQL 语句中的语法、数据验证和其他相关操作。可以把它看作是一组规则，这些规则决定了 MySQL 对不同类型的 SQL 语句和数据的行为方式。

查询全局的sql_mode

select @@global.sql_mode;
查看当前会话的
select @@session.sql_mode;
在配置文件中加入
sql-mode="xxx"

MySQL在运行时，如果要修改全局的sql_mode，执行：

SET GLOBAL sql_mode = 'modes';

修改当前会话执行

SET SESSION sql_mode = 'modes';

在原有基础上增加一种模式

SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

去掉一种模式

SET SESSION sql_mode = sys.list_drop(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

清空sql_mode

SET SESSION sql_mode = '';

7、 视图、存储过程、触发器的使用

视图（View）

• 视图是一个虚拟表，它是从一个或多个表（或其他视图）中的数据通过查询语句定义而来的。它本身不存储数据，数据实际存储在基础表中。当对视图进行查询操作时，数据库会根据视图的定义从相关的基础表中获取数据并返回结果。例如，在一个包含 “学生表（students）”、“课程表（courses）” 和 “选课表（course_selection）” 的数据库中，可以定义一个视图来展示学生所选课程的信息，如：

创建    CREATE VIEW student_courses_view AS      SELECT s.name, c.course_name, cs.grade      FROM st