MySQL配置

MySQL字符集

MySQL5.7和8.0区别与修改

• MySQL 5.7 默认的客户端和服务器都用了latin1，不支持中文，保存中文会报错。

• 从MySQL 8.0开始，数据库的默认编码将改为utf8mb4，从而避免上述乱码的问题。

show variables like 'character%';

我的数据库版本是8.0.26

• MySQL5.7需要修改默认的字符集

  1. 配置文件目录etc/my.cnf
  2. 添加配置项character_set_server=utf8
  3. 重启MySQL服务
  4. 注意：原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

• 创建新的表不指定字符集，默认和当前数据库字符集相同

• 修改已有数据库、表的字符集

  • 数据库：alter database 数据库名 character set 'utf8';
  • 表：alter table 表名 convert to character set 'utf8';

各级别的字符集

• 如下图所示为MySQL的字符集

  

包含的级别的含义是：

• character_set_server：服务器级别的字符集

• character_set_database：当前数据库的字符集

• character_set_client：服务器解码请求时使用的字符集

• character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为

character_set_connection

• character_set_results：服务器向客户端返回数据时使用的字符集

• MySQL提供了四种级别的字符集设置

  1. 服务器级别
  2. 数据库级别
  3. 表级别
  4. 列级别

• 字符集级别规则如下

  1. 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
  2. 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
  3. 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

字符集与比较规则

1. utf8和utf8mb4
   utf8 字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以MySQL的设计定义了两个概念：
   utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。utf8的全称
   utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

   使用show CHARSET;可以查看字符集最大长度和当前字符集默认的比较规则

1. 比较规则

   比较规则后缀含义

查看当前服务器的字符集和比较规则SHOW VARIABLES LIKE '%_server';

SQL大小写规范

• 查看命令SHOW VARIABLES LIKE '%lower_case_table_names%'

  

lower_case_table_names参数值的设置：

• 默认为0，大小写敏感 。

• 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。

• 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。

• 大小写建议

  1. 关键字和函数名称全部大写；
  2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
  3. SQL 语句必须以分号结尾。

sql_mode模式设置

• sql_mode会影响MySQL支持的SQL语法以及它执行的数据验证检查，通过sql_mode可以完成不同严格程度的数据校验，有效地保证数据准确性。sql_mode包含有两种模式：宽松模式和严格模式

• 宽松模式：如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。

  比如某个表字段长度是10，插入0123456789abc的时候，宽松模式并不报错，只是插入的数据变成了0123456789

• 严格模式：对于错误的数据，直接报错不进行插入

  若设置模式中包含了NO_ZERO_DATE，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配DEFAULT ‘0000-00-00 00:00:00’（零时间戳），这显然是不满足sql_mode中的NO_ZERO_DATE而报错。

• 查看sql_mode设置show variables like 'sql_mode';

  

MySQL8.0目录结构

• Linux下可以通过命令find / -name mysql查看mysql相关的目录

  

主要目录

1. 数据库文件的存放路径：/var/lib/mysql

   MySQL的表数据都会存储到对应的数据库目录下的某个文件中

数据目录对应着系统变量datadir：show variables like 'datadir';

1. 相关命令目录：/usr/bin/和/usr/sbin/

   bin目录下

sbin目录下

1. 配置文件目录：/usr/share/mysql和/etc/

   /usr/share/mysql主要是命令以及配置文件

/etc目录下包含有my.cnf配置文件

MySQL数据目录

默认数据库

• 有4个数据库是属于MySQL自带的系统数据库。

  • mysql： MySQL 系统自带的核心数据库，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。
  • information_schema：MySQL 系统自带的数据库，这个数据库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据 。在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表，用于表示内部系统表。
  • performance_schema：MySQL 系统自带的数据库，这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，可以用来监控 MySQL服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等信息。
  • sys：MySQL 系统自带的数据库，这个数据库主要是通过视图的形式把 information_schema 和performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。

数据库在文件系统中的表示

• 数据库在数据目录下都有对应的子目录。

  

表在文件系统中的表示

• 表在文件系统中的存储形式根据MySQL版本和存储引擎的不同均有不同

• MySQL8.0.26，存储引擎为InnoDB

  每一个表都有一个.ibd文件记录表结构表数据、索引和表配置信息

通过命令ibd2sdi --dump-file=生成的文件名.txt 表名.ibd将ibd转换为txt

• MySQL5.7，存储引擎为InnoDB

  相对于8.0，多了一个.frm用于存储描述表结构的文件。.frm文件的格式在不同的平台上都是相同的。这个后缀名为.frm是以 二进制格式存储的

• InnoDB系统表空间和独立表空间

  系统表空间：MySQL5.6.6之前的版本中默认情况下，InnoDB会在数据目录下创建一个名为 ibdata1 、大小为 12M （大小自扩展）的文件，这个文件就是对应的系统表空间在文件系统上的表示。

独立表空间：在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间 ，也就是说我们创建了多少个表，就有多少个独立表空间。也就是.ibd文件

• MyISAM存储引擎，对于一个表的存储文件有3个

  • MySQL5.7 中： 表名.frm ：描述表结构文件，字段长度等。MySQL8.0 中表名.sdi ：描述表结构文件，字段长度等

  • 表名.MYD (MYData)：数据信息文件，存储数据信息(如果采用独立表存储模式)

  • 表名.MYI (MYIndex)：存放索引信息文件

MySQL用户与权限管理

• 每次操作完都需要FLUSH PRIVILEGES;刷新权限

1. 创建用户：CREATE USER '用户名'@'可访问主机' IDENTIFIED BY '密码';，可访问主机%表示任意主机均可访问
2. 删除用户：DROP USER '用户名'@'可访问主机';
3. 设置当前用户密码：SET PASSWORD='new_password';该语句会自动将密码进行加密
4. 修改其他用户密码：SET PASSWORD FOR 'username'@'hostname'='new_password';

权限管理

• 查看MySQL权限：show privileges;

  

• 权限分类

  

• 授予权限的原则

  1. 满足需求的最小权限
  2. 限制用户登录主机
  3. 设置密码复杂度高的密码
  4. 定期清理不需要的用户

• 授予权限：GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];，如果用户不存在，会新建一个用户。授予全部权限（除了GRANT）可以使用GRANT ALL，也就是说具有ALL权限的用户和root用户的区别就是，root用户具有GRANT权限，而ALL用户没有

• 查看当前用户权限：SHOW GRANTS FOR CURRENT_USER;

• 查看某个用户的权限：SHOW GRANTS FOR 'user'@'主机地址' ;

• 收回权限：REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

权限表

• 以下表均在mysql数据库下，MySQL通过权限表来控制用户对数据库的访问，在服务器启动的时候根据权限表为每一个用户赋予相应的权限并存储到内存中。

• user表：user表是MySQL中最重要的一个权限表， 记录用户账号和权限信息 。

  

这些字段，可以分为四类：分别是范围列（或用户列）、权限列、安全列和资源控制列

范围列：host表示连接类型，user表示用户名，password密码（加密）等等

权限列：Grant_priv是否拥有GRANT权限，Shutdown_priv是否拥有停止MySQL服务的权限，Super_priv是否拥有超级权限，Execute_priv是否拥有EXECUTE权限（执行存储过程和函数），Select_priv , Insert_priv等

安全列：安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于 加密 ；两个是x509相关的（x509_issuer、x509_subject），用于 标识用户 ；另外两个Plugin字段用于 验证用户身份 的插件，该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。

资源控制列：资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为：

①max_questions，用户每小时允许执行的查询操作次数；

②max_updates，用户每小时允许执行的更新操作次数；

③max_connections，用户每小时允许执行的连接操作次数；

④max_user_connections，用户允许同时建立的连接次数。

• db表：记录从某个主机连接某个用户对某个数据库的操作权限

  

• tables_priv表：记录用户对表的操作权限

  

• columns_priv表：记录用户对表中某一列的操作权限

  

• procs_priv表：记录用户对存储过程和存储函数的操作权限

  

访问控制

1. 连接核实阶段：即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息。
2. 请求核实阶段：一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表

角色管理

• 角色是权限的集合。引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

• 创建角色：CREATE ROLE 'role_name'@'主机名';

• 赋予角色权限：GRANT privileges ON 表名 TO 'role_name'[@'主机名'];

• 查看角色的权限：SHOW GRANTS FOR 'role_name';

• 撤销角色的权限：REVOKE privileges ON 表名 FROM 'role_name';

• 删除角色：DROP ROLE 'role_name';

• 给用户赋予角色：GRANT 'role_name' TO '用户名'@'主机名';

• 激活角色：MySQL创建角色之后，默认是未激活状态，即便已经给用户赋予角色，也不起效果，需要对角色进行激活。SET DEFAULT ROLE 角色名(ALL) TO '用户名'@'主机名';，还可以激活全部角色SET GLOBAL activate_all_roles_on_login=ON;

• 撤销用户角色：REVOKE role_name FROM user;