MySQL 高级 - 第四章 | 配置文件与系统变量

怼烎@ 2024-05-06 23:21 5阅读 0赞

#### 目录 ####

*  一、配置文件的使用
 *   *  1.1 配置文件格式
     *  1.2 启动命令和选项组
     *  1.3 特定 MySQL 版本的专用选项
     *  1.4 同一个配置文件中多个组的优先级
     *  1.5 命令行和配置文件中启动选项的区别
 *  二、系统变量
 *   *  2.1 系统变量简介
     *  2.2 查看系统变量
     *  2.3 设置系统变量
     *   *  2.3.1 通过启动选项配置
         *  2.3.2 服务器程序运行过程中设置

--------------------

上篇：[第三章、用户、权限与角色管理][Link 1]  
下篇：[第五章、逻辑架构][Link 2]

本文内容主要源于：[bilibili-尚硅谷-MySQL高级篇][bilibili-_-MySQL]

## 一、配置文件的使用 ##

--------------------

### 1.1 配置文件格式 ###

与在命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干了组，每个组有一个组名，用中括号 `[ ]` 括起来

[server]
    (具体的启动选项 ...)
    
    [mysqld]
    (具体的启动选项 ...)
    
    [mysqld_safe]
    (具体的启动选项 ...)
    
    [client]
    (具体的启动选项 ...)
    
    [mysql]
    (具体的启动选项 ...)
    
    [mysqladmin]
    (具体的启动选项 ...)

像这个配置文件里就定义了多个组，组名分别是：`server`、`mysqld`、`mysqld_safe`、`client`、`mysql` 和 `mysqladmin`，每个组下边可以定义若干个启动选项，这里以 `[server]` 组为例说明启动选项的形式

[server]
    # 这是 option1，该选项不需要选项值
    option1
    # 这是 option2，该选项需要选项值
    option2=value2

在配置文件中指定启动选项的语法类似于命令行语法，但是配置文件中指定的启动选项不允许加 `--` 前缀，并且每行只指定一个选项，而且 `=` 周围可以有空白字符（命令行中选项名`=`选项值之间不允许有空白字符），另外，在配置文件中，可以使用 `#` 来添加注释，从 `#` 出现直到行尾的内容都属于注释内容，读取配置文件时会忽略这些注释内容

--------------------

### 1.2 启动命令和选项组 ###

配置文件中不同的选项组是给不同的启动命令使用的，不过有两个选项组比较特别：

*  `[server]` 组下边的启动选项将作用于 `所有的服务器` 程序
 *  `[client]` 组下边的启动选项将作用于 `所有的客户端` 程序

下面启动命令能读取的选项组都有哪些：

<table> 
 <thead> 
  <tr> 
   <th align="left">启动命令</th> 
   <th align="left">类别</th> 
   <th align="left">能读取的值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">mysqld</td> 
   <td align="left">启动服务器</td> 
   <td align="left">[mysqld]、[server]</td> 
  </tr> 
  <tr> 
   <td align="left">mysqld_safe</td> 
   <td align="left">启动服务器</td> 
   <td align="left">[mysqld]、[server] 、[mysqld_safe]</td> 
  </tr> 
  <tr> 
   <td align="left">mysql.server</td> 
   <td align="left">启动服务器</td> 
   <td align="left">[mysqld]、[server] 、[mysql.server]</td> 
  </tr> 
  <tr> 
   <td align="left">mysql</td> 
   <td align="left">启动客户端</td> 
   <td align="left">[mysql]、[client]</td> 
  </tr> 
  <tr> 
   <td align="left">mysqladmin</td> 
   <td align="left">启动客户端</td> 
   <td align="left">[mysqladmin]、[client]</td> 
  </tr> 
  <tr> 
   <td align="left">mysqldump</td> 
   <td align="left">启动客户端</td> 
   <td align="left">[mysqldump]、[client]</td> 
  </tr> 
 </tbody> 
</table>

比如，在 `/etc/my.cnf` 这个配置文件中添加一些内容：

[server]
    skip-networking
    default-storage-engine=MyISAM

软化直接用 `mysqld` 启动服务器程序

mysqld

虽然在命令行没有添加启动选项，但是在程序启动的时候，就会默认的查询配置文件，又由于 `mysqld` 命令可以读取 `[server]` 选项组的内容，所以 `skip-networking` 和 `default-storage-engine=MyISAM` 这两个选项是生效的，可以把这些启动选项放在 `[client]` 组里再试试用 `mysqld` 启动服务程序就不生效。

--------------------

### 1.3 特定 MySQL 版本的专用选项 ###

在选项组的名称后加上特定 `MySQL` 版本号，比如对于 `[mysqld]` 选项组来说，可以定义一个 `mysqld-5.7` 的选项组，它的含义和 `[mysqld]` 一样，只不过只有版本为 `5.7` 的 `mysqld` 程序才能使用这个选项组中的选项。

--------------------

### 1.4 同一个配置文件中多个组的优先级 ###

同一个命令可以访问配置文件中的多个组，比如 `mysqld` 可以访问 `[mysqld]`、`[server]` 组，如果在同一个配置文件中，比如 `~/ .my.cnf`，在这些组里出现了同样的配置，比如：

[server]
    default-storage-engine=InnoDB
    
    [mysqld]
    default-storage-engine=MyISAM

那么将以最后一个出现的组中的启动选项为准，上例子中 `default-storage-engine` 既出现在 `[server]` 组也出现在 `[mysqld]` 组中，因为 `[mysqld]` 在 `[server]` 组后面，就以 `[mysqld]` 组中的配置项为准。

--------------------

### 1.5 命令行和配置文件中启动选项的区别 ###

在命令行上指定的绝大部分启动选项都可以放到配置文件中，但是有一些选项是专门为命令行设计的，比方说 `defaults-extra-file`、`defaults-file` 这样的选项本身就是为了指定配置文件路径的，再放到配置文件中就没有什么意义了。

如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以 `命令行中的启动选项为准`，比如在配置文件中写了

[server]
    default-storage-engine=InnoDB

而启动命令是

mysql.server start --default-storage-engine=MyISAM

那最后 `default-storage-engin` 的值就是 `MyISAM`

--------------------

## 二、系统变量 ##

--------------------

### 2.1 系统变量简介 ###

`MySQL` 服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为 MySQL 系统变量，比如：

*  max\_connections：允许同时连入的客户端数量
 *  default\_storage\_engine：表的默认存储引擎用系统变量
 *  query\_cache\_size：查询缓存的大小

--------------------

### 2.2 查看系统变量 ###

查看 MySQL 服务器程序支持的系统变量以及它们的当前值：

*  查看所有全局变量

SHOW GLOBAL VARIABLES;

*  查看所有会话变量

SHOW SESSION VARIABLES;
    # 或
    SHOW VARIABLES;

*  查看满足条件的部分系统变量

SHOW GLOBAL VARIABLES LIKE '%标识符%';

*  查看满足条件的部分会话变量

SHOW SESSION VARIABLES LIKE '%标识符%';
    # 或
    SHOW VARIABLES LIKE '%标识符%';

示例：

mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.26    |
    +-----------+
    1 row in set (0.00 sec)

--------------------

### 2.3 设置系统变量 ###

--------------------

#### 2.3.1 通过启动选项配置 ####

大部分的系统变量都可以通过启动服务器时传送启动选项的方式来进行设置，主要有两种方式：

*  通过命令行添加启动选项（临时性）

比方说在启动服务器程序时用以下命令：

mysqld --default-storage-engine=MyISAM --max-connections=10

*  通过配置文件添加启动选项（永久性）

可以在配置文件 `my.cnf` 中添加系统变量

[server]
    default-storage-engine=InnoDB
    max-connections=10

对于启动项来说，如果启动项名由多个单词组成，各个单词之间用短划线 `-` 或者下划线 `_` 连接起来都可以，但是它对应的系统变量的单词之间必须使用下划线 `_` 连接起来。

--------------------

#### 2.3.2 服务器程序运行过程中设置 ####

对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行行动修改而无需停止并重启服务器，但是系统变量有作用范围之分

**设置不同作用范围的系统变量**

多个客户端程序可以同时连接到一个服务器程序，对于同一个系统变量，有时想让不同的客户端有不同的值，比方说客户端 `A` 想让当前客户端对应的默认存储引擎为 `InnoDB`，所以他可以把系统变量 `default-storage-engine` 设置为 `InnoDB`，但是客户端 `B` 想让他当前客户端对应的默认存储引擎为 `MyISAM`，所以他可以把系统变量 `default-storage-engine` 的值设置为 `MyISAM`，这样两个客户端拥有不同的默认存储引擎，使用时互不影响，十分方便，但是这样各个客户端都私有一份系统变量会产生这么两个问题：

*  ① 有一些系统变量并不是针对单个客户端的，比如允许同时连接到服务器的客户端数量 `max_connections`，查询缓存的大小 `query_cache_size`，这些公有的系统变量让某个客户端私有显然不合适
 *  ② 一个新连接到服务器的客户端对应的系统变量的值怎么设置

为了解决以上的两个问题，`MySQL` 提出了系统变量作用范围的概念，具体来说作用范围分为两种：

*  `GLOBAL`：全局变量，影响服务器的整体操作
 *  `SESSION`：会话变量，影响某个客户端连接的操作。（注：`SESSION` 有个别名 `LOCAL`）

在服务器启动时，会将每个全局变量初始化为其默认值（可以通过命令行或配置文件中指定的选项更改这些默认值）。然后服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。

以 `default-storage-engine` 为例，在服务器启动时会初始化一个名为 `default-storage-engine`，作用范围为 `GLOBAL` 的系统变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为 `default-storage-engine`，作用范围为 `SESSION` 的系统变量，该作用范围为 `SESSION` 的系统变量值按照当前作用范围为 `GLOBAL` 的同名系统变量进行初始化。

很显然，通过启动选项设置的系统变量的作用范围都是 `GLOBAL` 的，也就是对所有客户端都有效，因为在系统启动的时候还没有客户端程序连接进来，了解系统变量的 `GLOBAL` 和 `SESSION` 作用范围之后，可以再看一下再服务器程序运行期间通过客户端程序设置系统变量的语法：

SET [GLOBAL | SESSION] 系统变量名 = 值;

或者写成：

SET [@@(GLOBAL | SESSION).]var_name = vaule;

比如：服务器运行过程中把作用范围分为 `GLOBAL` 的系统变量 `default-storage-engine` 的值修改为 `MyISAM`，也就是想让之后连接到服务器的客户端都用 `MyISAM` 作为默认的存储引擎，那就可以选择以下两条语句中的任意一条进行设置：

# 方式一
    SET GLOBAL default_storage_engine = MyISAM;
    
    # 方式二
    SET @@GLOBAL.default_storage_engine = MyISAM;

如果只想对本客户端生效，也可以选择下边三条语句中的任意一条来设置：

# 方式一
    SET SESSION default_storage_engine = MyISAM;
    
    # 方式二
    SET @@SESSION.default_storage_engine = MyISAM;
    
    # 方式三
    SET default_storage_engine = MyISAM;

从上边的方式三可以看出，如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是 `SESSION`，也就是说 `SET 系统变量 = 值` 和 `SET SESSION 系统变量 = 值` 是等效的。

**查看不同作用范围的系统变量**

既然系统变量有作用范围之分，那使用 `SHOW VARIABLES;` 语句查看的是什么作用范围的系统变量呢？

答案是默认查看的是 `SESSION` 作用范围的系统变量

可以在查看系统变量的语句上加上要查看哪个 `作用范围` 的系统变量：

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

示例：

mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    1 row in set (0.00 sec)
    
    mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    1 row in set (0.01 sec)
    
    mysql> SET SESSION default_storage_engine = MyISAM;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    1 row in set (0.00 sec)
    
    mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | MyISAM |
    +------------------------+--------+
    1 row in set (0.00 sec)

可以看到，最初 `default_storage_engine` 的系统变量无论是在 `GLOBAL` 作用范围上还是在 `SESSION` 作用范围上的值都是 `InnoDB`，在 `SESSION` 作用范围把它的值设置为 `MyISAM` 之后，可以看到 `GLOBAL` 作用范围的值并没有改变。

> 如果某个客户端改变了某个系统变量在 `GLOBAL` 作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为 `SESSION` 的值，只会影响后续连入的客户端在作用范围为 `SESSION` 的值

并不是所有系统变量都具有 `GLOBAL` 和 `SESSION` 的作用范围。

*  有一些系统变量只具有 `GLOBAL` 作用范围，比方说 `max_connections`，表示服务器程序支持同时最多多少个客户端程序进行连接
 *  有一些系统变量只具有 `SESSION` 作用范围，比如 `insert_id`，表示在对某个包含 `AUTO_INCREMENT` 列的表进行插入时，该列初始的值
 *  有一些系统变量的值既有 `GLOBAL` 作用范围，也具有 `SESSION` 作用范围，比如 `default_storage_engine`
 *  有一些系统变量是只读的，并不能设置值，比方说 `version`，表示当前 `MySQL` 的版本，客户端是不能设置它的值的，只能在 `SHOW VARIABLES` 语句中查看

--------------------

上篇：[第三章、用户、权限与角色管理][Link 1]  
下篇：[第五章、逻辑架构][Link 2]

[Link 1]: https://blog.csdn.net/xhmico/article/details/138164322
[Link 2]: https://blog.csdn.net/xhmico/article/details/138251073
[bilibili-_-MySQL]: https://www.bilibili.com/video/BV1iq4y1u7vj?p=109