# 实验 2: 配置 TiDB

## 概述

在 TiDB，有 TiDB 系统变量和 TiDB 配置文件参数来控制 TiDB 集群的行为。在此练习中，你将尝试修改 TiDB 系统变量和 TiDB 配置文件参数。根据作用范围，TiDB server 系统变量分为四类。

  | 类别           | 修改方式      | 生效范围 Scope                      |
  | :------------- | :------------ | :---------------------------------- |
  | 会话级         | `SET SESSION` | 当前会话                            |
  | 全局持久化     | `SET GLOBAL`  | 所有 TiDB Server 实例及所有会话     |
  | 全局非持久化   | `SET GLOBAL`  | 当前 TiDB Server 实例及其连接的会话 |
  | 会话级和全局级 | `SET SESSION` | 当前会话                            |
  | 会话级和全局级 | `SET GLOBAL`  | 所有 TiDB Server 实例和新会话       |

在上一个实验中，你使用 placement rules 分别将热数据和冷数据放置在高性能存储和低性能存储上。 

> [!TIP]
> 点击 **开始** 启动实验环境。
> 环境准备就绪后，点击底部工具栏中的 **前进** 进入 **模块 1**。
> 然后，按照实验指南的步骤连接到实践环境并开始你的任务。

## 模块 1: 修改全局持久化系统变量

+ [`tidb_gc_enable`](https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_gc_enable-%E4%BB%8E-v50-%E7%89%88%E6%9C%AC%E5%BC%80%E5%A7%8B%E5%BC%95%E5%85%A5) 是全局持久化的 TiDB 系统变量之一，修改它并观察结果。 

### 步骤 1：登录远程 Linux

+ 通过点击以下链接打开三个 SSH 连接到你的实验虚拟机：
  + [终端 1](ssh://monitor1-ssh#1)
  + [终端 2](ssh://monitor1-ssh#2)
  + [终端 3](ssh://monitor1-ssh#3)

+ 现在你将拥有三个活动的终端会话。

+ 登录后显示的横幅如下所示。

    ```output
     ,     #_
    ~\_  ####_        Amazon Linux 2
    ~~  \_#####\
    ~~     \###|      
    ~~       \#/ ___
    ~~       V~' '->
    ~~~         /    A newer version of Amazon Linux is available!
     ~~._.   _/
        _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
      _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
    ```

### 步骤 2. 在终端 1 中，连接到 TiDB Server 1

  ```bash
  ./connect-db1.sh
  ```
  ```output
  ...
  tidb:db1>
  ```

### 步骤 3. 在终端 2 中，连接到 TiDB Server 1

  ```bash
  ./connect-db1.sh
  ```
  ```output
  ...
  tidb:db1>
  ```

### 步骤 4. 在终端 3 中，连接到 TiDB Server 2

  ```bash
  ./connect-db2.sh
  ```
  ```output
  ...
  tidb:db2>
  ```

### 步骤 5. 在终端 1 中，将 `tidb_gc_enable` 更改为 `OFF`

+ 在[终端 1](ssh://monitor1-ssh#1)中，查看 `tidb_gc_enable` 的默认值. 
  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | ON    |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | ON    |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

+ 将 `tidb_gc_enable` 的值从 `ON` 更改为 `OFF`.  
  
  ```sql   
  SET GLOBAL tidb_gc_enable=OFF;
  ```
  ```output
  tidb:db1> SET GLOBAL tidb_gc_enable=OFF;
  Query OK, 0 rows affected (0.06 sec)
  ```

### 步骤 6. 在终端 1 中，验证更改

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 7. 在终端 2 中，验证更改

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.01 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 8. 在终端 3 中，验证更改

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.01 sec)

  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 9. 在会话范围内更改 `tidb_gc_enable` 的值

+ 在任意终端中，在会话范围内更改 `tidb_gc_enable` 的值.
  
  ```sql
  SET SESSION tidb_gc_enable=ON;
  ```
  ```output
  tidb:db2> SET SESSION tidb_gc_enable=ON;
  ERROR 1229 (HY000): Variable 'tidb_gc_enable' is a GLOBAL variable and should be set with SET GLOBAL
  ```

### 步骤 10. 在所有终端中，退出 MySQL 客户端

  ```bash
  EXIT
  ```

### 步骤 11. 在终端 1 中，重启 TiDB 集群

  ```bash
  tiup cluster restart tidb-test --yes
  ```
  ```output
  ...
  Restarted cluster `tidb-test` successfully
  ```

### 步骤 12. 在所有终端中，检查 `tidb_gc_enable` 的值

+ 验证在[终端 1](ssh://monitor1-ssh#1)、[终端 2](ssh://monitor1-ssh#2) 和[终端 3](ssh://monitor1-ssh#3) 中，`tidb_gc_enable` 的值为 `OFF`。我们以[终端 1](ssh://monitor1-ssh#1)为例，你需要重新连接到 TiDB servers ，因为之前的重启断开了你的会话。
  
  ```bash
  ./connect-db1.sh
  ```
  ```output
  ...
  tidb:db1>
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.01 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable';
  +----------------+-------+
  | Variable_name  | Value |
  +----------------+-------+
  | tidb_gc_enable | OFF   |
  +----------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 13. 在任意终端中，将 `tidb_gc_enable` 恢复为 `ON`

  ```sql
  SET GLOBAL tidb_gc_enable=ON;
  ```
  ```output
  tidb:db1> SET GLOBAL tidb_gc_enable=ON;
  Query OK, 0 rows affected (0.07 sec)
  ``` 

### 结论
全局持久化的 TiDB 系统变量只能在全局级别进行配置，其效果将立即应用于集群中的所有 TiDB server 和会话。

## 模块 2: 修改非持久化全局系统变量

+ [`max_connections`](https://docs.pingcap.com/zh/tidb/stable/system-variables#max_connections) 是一个非持久化的全局系统变量，修改它并观察结果。

### 步骤 1. 在终端 1 中，将 `max_connections` 从 `0` 更改为 `1`

+ 检查默认值.

  ```sql
  SHOW SESSION VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 0     |
  +-----------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 0     |
  +-----------------+-------+
  1 row in set (0.00 sec)  
  ```

+ 将 `max_connections` 的值从 `0` 更改为 `1`.
  
  ```sql
  SET GLOBAL max_connections=1;
  ```
  ```output
  tidb:db1> SET GLOBAL max_connections=1;
  Query OK, 0 rows affected (0.00 sec)   
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 1     |
  +-----------------+-------+
  1 row in set (0.00 sec)   
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 1     |
  +-----------------+-------+
  1 row in set (0.01 sec)
  ```

### 步骤 2. 在终端 2 中，验证该更改

  ```sql
  SHOW SESSION VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 1     |
  +-----------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 1     |
  +-----------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 3. 在终端 3 中，检查 `max_connections` 的值

  ```sql
  SHOW SESSION VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 0     |
  +-----------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 0     |
  +-----------------+-------+
  1 row in set (0.01 sec)
  ```

### 步骤 4. 在终端 1 中，退出数据库会话，然后再次登录
 
+ 在[终端 1](ssh://monitor1-ssh#1)上退出数据库会话，并尝试通过再次运行 `connect-db1.sh` 重新登录。然而，这次登录尝试将因 TiDB server 1 实例上的 `max_connections` 设置而失败. 
  
  ```bash
  ./connect-db1.sh 
  ```
  ```output
  ERROR 1040 (HY000): Too many connections
  ```

### 步骤 5. 在任意终端中，重新启动 TiDB 集群

   ```bash
   tiup cluster restart tidb-test --yes
   ```
   ```output
   ...
   Restarted cluster `tidb-test` successfully
   ```

### 步骤 6. 在终端 1 中，登录到 TiDB Server 2

  ```bash
  ./connect-db2.sh 
  ```
  ```output
  ...
  tidb:db2>
  ```

### 步骤 7. 在所有终端中，检查 `max_connections` 的值

+ 我们以[终端 1](ssh://monitor1-ssh#1)为例.

  ```bash
  ./connect-db2.sh
  ```
  ```output
  ...
  tidb:db2>
  ```

  ```bash
  SHOW SESSION VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 0     |
  +-----------------+-------+
  1 row in set (0.00 sec)
  ```

  ```bash
  SHOW GLOBAL VARIABLES LIKE 'max_connections';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'max_connections';
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | max_connections | 0     |
  +-----------------+-------+
  1 row in set (0.01 sec)
  ```

### 结论
非持久化的全局 TiDB 系统变量只能在全局级别进行配置，其效果将应用于你使用的 `SET GLOBAL` 语句的 TiDB 实例。

## 模块 3: 修改会话级系统变量

+ [`tidb_allow_remove_auto_inc`](https://docs.pingcap.com/zh/tidb/stable/system-variables#tidb_allow_remove_auto_inc-%E4%BB%8E-v2118-%E5%92%8C-v304-%E7%89%88%E6%9C%AC%E5%BC%80%E5%A7%8B%E5%BC%95%E5%85%A5) 是会话级系统变量之一，修改它并观察结果。

### 步骤 1. 检查终端连接。

+ 确认你的两个终端（[终端 1](ssh://monitor1-ssh#1) 和[终端 3](ssh://monitor1-ssh#3)）连接到 TiDB server 2，并且有一个终端（[终端 2](ssh://monitor1-ssh#2)）连接到 TiDB server 1。

### 步骤 2. 在终端 1 中，将 `tidb_allow_remove_auto_inc` 更改为 `ON`

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  +----------------------------+-------+
  | Variable_name              | Value |
  +----------------------------+-------+
  | tidb_allow_remove_auto_inc | OFF   |
  +----------------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SET SESSION tidb_allow_remove_auto_inc=ON;
  ```
  ```output
  tidb:db2> SET SESSION tidb_allow_remove_auto_inc=ON;
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  +----------------------------+-------+
  | Variable_name              | Value |
  +----------------------------+-------+
  | tidb_allow_remove_auto_inc | ON    |
  +----------------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 3. 在终端 2 中，检查 `tidb_allow_remove_auto_inc` 的值

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  +----------------------------+-------+
  | Variable_name              | Value |
  +----------------------------+-------+
  | tidb_allow_remove_auto_inc | OFF   |
  +----------------------------+-------+
  1 row in set (0.01 sec)
  ```

### 步骤 4. 在终端 3 中，检查 `tidb_allow_remove_auto_inc` 的值

  ```sql
  SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc';
  +----------------------------+-------+
  | Variable_name              | Value |
  +----------------------------+-------+
  | tidb_allow_remove_auto_inc | OFF   |
  +----------------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 5. 在任意终端中，更改 `tidb_allow_remove_auto_inc` 的值

+ 尝试在全局范围内更改 `tidb_allow_remove_auto_inc` 的值.
  
  ```sql
  SET GLOBAL tidb_allow_remove_auto_inc=ON;
  ```
  ```output
  tidb:db2> SET GLOBAL tidb_allow_remove_auto_inc=ON;
  ERROR 1228 (HY000): Variable 'tidb_allow_remove_auto_inc' is a SESSION variable and can't be used with SET GLOBAL
  ```

### 步骤 6. 在终端 1 中，将 `tidb_allow_remove_auto_inc` 改回 `OFF`

  ```sql
  SET SESSION tidb_allow_remove_auto_inc=OFF;
  ```
  ```output
  tidb:db2> SET SESSION tidb_allow_remove_auto_inc=OFF;
  Query OK, 0 rows affected (0.00 sec)
  ``` 

### 结论
仅会话的 TiDB 系统变量只能在会话级别进行配置，其效果将应用于你使用 `SET SESSION` 语句的当前会话。

## 模块 4: 修改会话级和全局系统变量

+ [`auto_increment_offset`](https://docs.pingcap.com/zh/tidb/stable/system-variables#auto_increment_offset) 是会话级和全局系统变量之一。分别在会话级别和全局级别修改它，然后观察结果。

### 步骤 1. 检查终端连接

+ 确认你的三个终端已连接到数据库。其中两个终端（[终端 1](ssh://monitor1-ssh#1) 和[终端 3](ssh://monitor1-ssh#3)）连接到 TiDB server 2，一个终端（[终端 2](ssh://monitor1-ssh#2)）连接到 TiDB server 1。

### 步骤 2. 在终端 1 中，将 `auto_increment_offset` 从 `1` 更新为 `99`

+ 在[终端 1](ssh://monitor1-ssh#1)的会话级别检查系统变量 `auto_increment_offset` 并将值从 `1` 修改为 `99`。确认该变量仅在会话级别被更新。
  
  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SET SESSION auto_increment_offset=99;
  ```
  ```output
  tidb:db2> SET SESSION auto_increment_offset=99;
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 3. 在终端 2 和 3 中，检查 `auto_increment_offset` 的值

+ 在[终端 2](ssh://monitor1-ssh#2)和[终端 3](ssh://monitor1-ssh#3)中，从会话级别和全局级别检查该变量的值。两者都应保持不变，仍为 1。这里以[终端 2](ssh://monitor1-ssh#2)为例。

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.01 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 4. 在终端 1 中，将 `auto_increment_offset` 改回 `1`

  ```sql
  SET SESSION auto_increment_offset=1;
  ```

### 步骤 5. 在终端 1 中，在全局级别更新 `auto_increment_offset`

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SET GLOBAL auto_increment_offset=99;
  ```
  ```output
  tidb:db2> SET GLOBAL auto_increment_offset=99;
  Query OK, 0 rows affected (0.05 sec)
  ```

  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db2> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 6. 在终端 2 和 3 中，检查 `auto_increment_offset`

+ 在[终端 2](ssh://monitor1-ssh#2)和[终端 3](ssh://monitor1-ssh#3)中，从会话级别和全局级别检查 `auto_increment_offset` 的值。确认只有全局级别的变量被更新。以[终端 2](ssh://monitor1-ssh#2)的结果为例。
  
  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 1     |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 7. 重启 TiDB 集群
   
+ 在任意终端中，退出与 TiDB 的连接，然后重启 TiDB 集群。
  
  ```bash
  tiup cluster restart tidb-test --yes
  ```
  ```output
  ...
  Restarted cluster `tidb-test` successfully
  ```

### 步骤 8. 在终端 1、2 和 3 中，检查 `auto_increment_offset` 的值

+ 连接到[终端 1](ssh://monitor1-ssh#1)、[终端 2](ssh://monitor1-ssh#2)和[终端 3](ssh://monitor1-ssh#3)，从会话级别和全局级别检查 `auto_increment_offset` 的值。确认该变量在会话级别和全局级别都已更新。以[终端 1](ssh://monitor1-ssh#1)为例。
  
  ```sql
  SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db1> SHOW SESSION VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

  ```sql
  SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  ```
  ```output
  tidb:db1> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | auto_increment_offset | 99    |
  +-----------------------+-------+
  1 row in set (0.00 sec)
  ```

### 步骤 9. 在任意终端中，将 `auto_increment_offset` 改回 `1`

  ```sql
  SET GLOBAL auto_increment_offset=1;
  ```
  ```output
  tidb:db1> SET GLOBAL auto_increment_offset=1;
  Query OK, 0 rows affected (0.07 sec)
  ``` 

### 结论
如果你使用 `SET SESSION` 更改会话级和全局变量，其生效范围将仅为当前会话。

如果你使用 `SET GLOBAL` 更改会话级和全局变量，其生效范围将是所有 TiDB server 实例和新会话。

## 模块 5: 修改 TiKV 配置文件参数

### 步骤 1. 在 `TiKV server 1`，查看 `tikv.toml` 文件的内容

  ```bash
  ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${HOST_KV1_PRIVATE_IP} vi /tidb-deploy/tikv-20160/conf/tikv.toml
  ```
  ```output
  # WARNING: This file is auto-generated. Do not edit! All your modification will be overwritten!
  # You can use 'tiup cluster edit-config' and 'tiup cluster reload' to update the configuration
  # All configuration items you want to change can be added to:
  # server_configs:
  #   tikv:
  #     aa.b1.c3: value
  #     aa.b2.c4: value
  ...
  ```                    

### 步骤 2. 在控制机上, 更改 `TiKV server` 的 `log.level` 

+ 在控制机上, 使用 `tiup cluster edit-config` 命令启动一个编辑器。然后，在 `tikv` 段落中插入 `log.level: warn`。针对缩进问题，请参考下面的 `output`。按 `ESC` 键，然后输入 `:wq` 退出回到终端。
  + 说明：控制机（Control Machine）就是 Web SSH（如 [终端 1](ssh://monitor1-ssh#1) 和 [终端 2](ssh://monitor1-ssh#2)）默认连接的虚拟机。
  
  ```bash
  tiup cluster edit-config tidb-test
  ```
  ```output
  ...
  server_configs:
  tidb: {}
  tikv:
     log.level: warn
  pd:
     replication.location-labels:
     - region
     - zone
     - storage-performance
     replication.max-replicas: 3
  tidb_dashboard: {}
  tiflash: {}
  tiproxy: {}
  tiflash-learner: {}
  pump: {}
  drainer: {}
  ```

### 步骤 3. 重新加载 TiDB 集群

+ 在控制机上，使用 `tiup cluster reload` 命令更新配置。此步骤将重启所有 `TiKV server`。 

  ```bash
  tiup cluster reload tidb-test
  ```
  ```output
  Will reload the cluster tidb-test with restart policy is true, nodes: , roles: .
  Do you want to continue? [y/N]:(default=N) y
  + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa.pub
  + [Parallel] - UserSSH: user=ec2-user, host=10.90.2.99
  + [Parallel] - UserSSH: user=ec2-user, host=10.90.2.92
  ...
  Reloaded cluster `tidb-test` successfully
  ```

### 步骤 4. 在 `TiKV server 1`, 再次查看 `tikv.toml` 的内容

+ 在 `TiKV server 1` 上, 使用文本编辑器再次查看 `tikv.toml` 文件的内容，并验证 `log.level = "warn"`. 输入 `:q!` 退出编辑器。

  ```bash
  ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${HOST_KV1_PRIVATE_IP} vi -m /tidb-deploy/tikv-20160/conf/tikv.toml
  ```
  ```output
  # WARNING: This file is auto-generated. Do not edit! All your modification will be overwritten!
  # You can use 'tiup cluster edit-config' and 'tiup cluster reload' to update the configuration
  # All configuration items you want to change can be added to:
  # server_configs:
  #   tikv:
  #     aa.b1.c3: value
  #     aa.b2.c4: value
  [log]
  level = "warn"
  ...
  ```

### 步骤 5. 在控制机上，将 `log.level` 的值恢复为默认值

+ 在控制机上, 使用 `tiup cluster edit-config` 命令启动一个编辑器。将 `TiKV server` 的 `log.level` 从 `warn` 改回默认值。按 `ESC` 键，然后输入 `:wq` 退出回到终端：

  ```bash
  tiup cluster edit-config tidb-test
  ```
  ```output
  ...
  server_configs:
  tidb: {}
  tikv: {}
  pd:
     replication.location-labels:
     - region
     - zone
     - storage-performance
     replication.max-replicas: 3
  tidb_dashboard: {}
  tiflash: {}
  tiproxy: {}
  tiflash-learner: {}
  pump: {}
  drainer: {}
  ```

### 步骤 6. 重新加载 TiDB 集群

  ```bash
  tiup cluster reload tidb-test
  ```
  ```output
  Will reload the cluster tidb-test with restart policy is true, nodes: , roles: .
  Do you want to continue? [y/N]:(default=N) y
  + [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa.pub
  + [Parallel] - UserSSH: user=ec2-user, host=10.90.2.99
  + [Parallel] - UserSSH: user=ec2-user, host=10.90.2.191
  ...
  Reloaded cluster `tidb-test` successfully
  ```

### 步骤 7. 在 `TiKV server 1` 上，再次查看 `tikv.toml` 的内容

+ 在 `TiKV server 1` 上， 使用文本编辑器再次查看 `tikv.toml` 文件的内容，并验证 `log.level` 已恢复为默认值。输入 `:q!` 退出编辑器。
 
  ```bash
  ssh -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${HOST_KV1_PRIVATE_IP} vi -m /tidb-deploy/tikv-20160/conf/tikv.toml
  ```
  ```output
  # WARNING: This file is auto-generated. Do not edit! All your modification will be overwritten!
  # You can use 'tiup cluster edit-config' and 'tiup cluster reload' to update the configuration
  # All configuration items you want to change can be added to:
  # server_configs:
  #   tikv:
  #     aa.b1.c3: value
  #     aa.b2.c4: value
  [server]
  [server.labels]
  region = "seattle"
  storage-performance = "high"
  zone = "az1"
  ...
  ```

## 实验结束