云原生俱乐部-mysql知识点归纳（2）

这个系列的第一篇写的内容不多，刚才查看了一下mysql数据库的内容，发现还挺多的。之前还说要一个全篇写完所有的内容，但是肯定是全忘完了才这么想的。大概需要四到五篇完结这个mysql内容系列，很多都是概念性的，而且也不好记。

每次看完mysql印象都不深刻，这咋记呀，比如InnoDB的特性，大概知道一点，但我也不敢说记得全。还有元数据、表数据、数据字典、表空间等等混在一块，至于配置文件怎么写，有哪些系统变量，那内容就多了去了。

这篇文章主要讲讲监视mysql、管理mysql用户和mysql安全三部分内容。监视mysql主要讲的是配置和查看mysql日志文件，识别慢速查询，并使用变量监视mysql的活动。管理mysql用户包括创建并授予用户权限，设计权限结构等。mysql安全则是应用mysql防火墙。

监视mysql

这一部分刚才看了，除了mysql日志以及性能监控数据库外，还包括mysql监视器（web界面）以及mysql企业审计。后两部分不太好总结，知识点比较偏而且未必能够理解，所以就略过了。

[1]mysql日志文件

mysql日志文件包括二进制文件、错误文件（前两个是默认）、审计日志、慢速查询日志、常规查询日志（一般不启用，因为内容太多，占用位置大）。日志文件特征：占用大量存储空间（可以放在单独的文件系统里面），而且一般需要备份重要的日志文件。

慢查询日志是 MySQL 记录执行时间超过阈值​的 SQL 语句的日志文件，找出耗时长的 SQL，优化查询效率；追踪未经优化的全表扫描、大结果集排序等。SET  GLOBAL slow_query_log =  'ON';开启慢速查询日志，SET  GLOBAL long_query_time =  2.0;设置阈值为2秒。

[2]mysql性能模式

`performance_schema`是 MySQL 内置的性能监控数据库​，用于实时采集服务器运行时的底层指标，​并且该数据库零存储开销​，数据仅存于内存，重启后清空（默认占用 < 10MB）。

该数据库中保存着系统变量的值，​​在`performance_schema.variables_info`表中记录着mysql的运行情况。该表在 MySQL 服务关闭后会被销毁​​，数据完全丢失。当 MySQL 重新启动时，该表会​​重新创建并重新加载数据​。

这是因为  `performance_schema`数据库的所有表都是内存表（Memory-Resident Tables）​​，其数据仅存在于 MySQL 进程的生命周期内。

管理mysql用户

包括介绍mysql系统有着怎么样的权限架构，是如何通过权限来控制用户的行为的。包括创建用户和授予用户权限，也包括使用角色来简化权限的授予，后面都可以用revoke来回收用户的权限。

[1]mysql权限系统

mysql服务将验证和授权分开，在每次连接的时候验证身份，在执行sql语句的时候则会验证用户权限，如果有对应的权限，则执行否则不执行。创建具有与用户职能相应权限的账户，遵循最小权限原则，防止未经授权用户访问超出权限的数据。

show grants for role（user@%）用来查看角色或者用户的权限。角色是权限的集合，可以赋予角色权限，然后再将角色赋予给用户，一次创建，多次分配。

[2]创建用户和角色

创建mysql用户使用create user username@% (192.169.%通配该网段ip)identified by '1234';命令，create role role1,role2;用于创建角色（如果不指定主机，默认是%）。角色类似user，可以在mysql.users表中查看，但是不能以角色身份登录。

使用select user,hosts,authentication from mysql.user；可以查看user的表示信息，其中authentication_string是密码密文，使用drop user来删除用户和角色(角色也可以用drop role)。

如果要修改用户密码，推荐使用alter而不是update，因为alter能够自动处理mysql对密码的加密，而update需要手动的加密，并且使用alter会自动处理数据一致性，update需要手动维护。

[3]授予与回收权限

grant delete test.* on user@%;用于授予权限，这是数据库级别的，可以选择表来精确表级别。甚至支持列级别的权限，如GRANT  UPDATE(name, age) ON `db1`.`users` TO  'hr'@'%';

使用grant role to user用于授予用户角色权限，并且可以通过使用with admin option来允许用户将角色权限继续授予他人。REVOKE  DELETE  ON db1.t1 FROM  'user1'@'192.168.1.%';这条命令则是用于回收权限。

[4]激活用户的角色权限

用户被授予角色后，权限不会自动生效，需手动激活。`SET ROLE ALL`是 MySQL 8.0+ 的权限管理命令，用于激活当前会话中用户被赋予的所有角色权限​，可以使用set role read_write仅仅激活用户的reda_write角色的权限。

默认角色激活配置保存在 `mysql.default_roles`系统表中，用户登录时，MySQL 自动隐式执行 `SET ROLE DEFAULT`。可以使用SET  DEFAULT ROLE read_write TO  'user1'@'%';将reda_write角色作为user的默认角色。

可以使用SET  DEFAULT ROLE ALL  TO  user@host; 设置全部角色为默认激活配置，不过该命令慎用！

mysql安全

mysql数据需要通过ssl加密后发出去的（可禁止启用），当然使用unix socket除外。可以使用status来查看mysql会话连接的状态，包括是否使用ssl，mysqld启动的时候可以指定是否使用ssl。

由于是ssl协议，需要用到CA证书，用于防止中间人攻击。我们用浏览器访问https协议的网站是，也需要使用基于x.509标准的受信任的CA机构颁发的证书，否则浏览器会警告（对于自签名证书）。不过一般来说，mysql数据库服务使用的都是mysql服务自带的自签名证书。

但是仅仅是通过加密数据并不能够保证mysql的安全，因为存在sql注入这个安全隐患。我也忘记是哪个天才发现的，反正是一个著名的黑客。因此，应用级mysql防火墙出现了。MySQL防火墙是应用层防护​，专为MySQL设计的SQL语句级防火墙，通常作为插件集成在MySQL服务器（或者说mysqld进程）内。

它能够理解SQL语义，进行语句模式匹配，本地unix socket可以绕过网络防火墙，但也绕不顾MySQL防火墙。使用 CALL mysql.sp_set_firewall_mode('dev_user@%', 'RECORDING');激活防火墙的训练模式，这样会自动记录该账户执行的所有SQL模式（会真实改变）。

训练完切换到保护模式 ，CALL mysql.sp_set_firewall_mode('prod_user@%', 'PROTECTING');，这样如果再执行sql语句的时候，就会比对语句指纹，拒绝执行未匹配指纹的sql语句。