## 模块 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)。

+ 在[终端 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`

- 切换到[终端 1](ssh://monitor1-ssh#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)为例。

  ```bash
  ./connect-db1.sh
  ```

  ```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)
  ```

### 步骤 10. 退出并关闭所有终端

### 结论

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

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