---
title: "Configuring TiDB"
version: "v2"
status: "GA"
description: "In this hands-on lab, you will learn how to configure TiDB. Specifically, we will cover how to configure TiDB, interact with the TiDB cluster, and monitor the TiDB cluster. This lab is a practical workshop that allows you to learn how to configure TiDB."
---

# Lab 2: Configuring TiDB

## Overview

>[!NOTE]
> This hands-on lab is also included in the [TiDB Certification Training: TiDB Administration Using TiUP](https://www.pingcap.com/education/course-tidb-database-administration-for-self-hosted-deployments/) course.

In TiDB, there are TiDB system variables and TiDB configuration file parameters to control the TiDB cluster behaviors. In these exercises, you gain experience on modifying TiDB system variables and TiDB configuration file parameters. There are four TiDB server system variable categories classified based on their effective scope.

  | Category              | Change By     | Effective Scope                                         |
  | :-------------------- | :------------ | :------------------------------------------------------ |
  | Session Only          | `SET SESSION` | Current Session                                         |
  | Global Persistent     | `SET GLOBAL`  | All TiDB Server Instances and All Sessions              |
  | Global Non-Persistent | `SET GLOBAL`  | Current TiDB Server Instance and its Connected Sessions |
  | Session and Global    | `SET SESSION` | Current Session                                         |
  | Session and Global    | `SET GLOBAL`  | All TiDB Server Instances and New Sessions              |

> [!TIP]
> Click **Start** to launch the lab environment.
> Once the environment is ready, select **Next** on the bottom toolbar to advance to **Module 1**.
> And, follow the step-by-step lab guide to connect to the hands-on environment and begin your tasks.

## Module 1: Modifying Global Persistent System Variables

+ [`tidb_gc_enable`](https://docs.pingcap.com/tidb/stable/system-variables#tidb_gc_enable-new-in-v50) is one of the global persistent TiDB system variables, modify it and observe the result. 
  
### Step 1. Log into the remote Linux

+ Open three SSH connections to your lab virtual machine, by clicking:
  + [Terminal 1](ssh://monitor1-ssh#1)
  + [Terminal 2](ssh://monitor1-ssh#2)
  + [Terminal 3](ssh://monitor1-ssh#3)

+ You will now have three active terminal sessions. 

+ The banner displayed after logging in appears as follows.

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

### Step 2. In terminal 1, connect to TiDB Server 1

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

### Step 3. In terminal 2, connect to TiDB Server 1

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

### Step 4. In terminal 3, connect to TiDB Server 2

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

### Step 5. In terminal 1, change `tidb_gc_enable` to `OFF`

+ In [Terminal 1](ssh://monitor1-ssh#1), verify the default value of `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)
  ```

+ Change the value from `ON` to `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)
  ```

### Step 6. In terminal 1, verify the change

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

### Step 7. In terminal 2, verify the change

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

### Step 8. In terminal 3, Verify the change

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

### Step 9. Change the value of `tidb_gc_enable` on session scope

+ In any terminal, change the value of `tidb_gc_enable` on session scope.
  
  ```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
  ```

### Step 10. In all terminals, exit from MySQL client

  ```bash
  EXIT
  ```

### Step 11. In terminal 1, restart the TiDB cluster

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

### Step 12. In all terminals, check the value of `tidb_gc_enable`

+ Verify that in [Terminal 1](ssh://monitor1-ssh#1), [Terminal 2](ssh://monitor1-ssh#2), and [Terminal 3](ssh://monitor1-ssh#3), the value of `tidb_gc_enable` is `OFF`. We use [Terminal 1](ssh://monitor1-ssh#1) as an example. You need to re-connect to TiDB servers because the previous restart disconnected your sessions. 
  
  ```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)
  ```

### Step 13. In any terminal, restore `tidb_gc_enable` back to `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)
  ``` 

### Summary
The global persistent TiDB system variable can only be configured on a global level, and its effects will be immediately applied across all TiDB servers and sessions in the cluster.

## Module 2: Modifying Non-Persistent Global System Variables

+ [`max_connections`](https://docs.pingcap.com/tidb/stable/system-variables#max_connections) is one of the non-persistent global system variables, modify it and observe the result. 

### Step 1. In terminal 1, change the `max_connections` from `0` to `1`

+ Check the default value.

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

+ Change the `max_connections` from `0` to `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)
  ```

### Step 2. In terminal 2, verify the change

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

### Step 3. In terminal 3. check the value of `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)
  ```

### Step 4. In terminal 1, exit the database session, and then log in again
 
+ Exit the database session on [Terminal 1](ssh://monitor1-ssh#1) and attempt to log back in by running `connect-db1.sh` again. However, this time the login attempt will fail due to the `max_connections` setting on the TiDB server instance 1. 
  
  ```bash
  ./connect-db1.sh 
  ```
  ```output
  ERROR 1040 (HY000): Too many connections
  ```

### Step 5. In any terminal, restart the TiDB cluster

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

### Step 6. In terminal 1, login to TiDB Server 2

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

### Step 7. In all terminals, check the value of `max_connections`

+ We use [Terminal 1](ssh://monitor1-ssh#1) as an example.

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

### Summary
The non-persistent global TiDB system variable can only be configured on a global level, and its effects will be applied to the TiDB instance that you issued the `SET GLOBAL` statement.

## Module 3: Modifying Session Only System Variables

+ [`tidb_allow_remove_auto_inc`](https://docs.pingcap.com/tidb/stable/system-variables#tidb_allow_remove_auto_inc) is one of the session system variables, modify it and observe the result. 

### Step 1. Check terminal connections

+ Verify that you have two terminals ([Terminal 1](ssh://monitor1-ssh#1) and [Terminal 3](ssh://monitor1-ssh#3)) connected to TiDB server 2, and one terminal ([Terminal 2](ssh://monitor1-ssh#2)) connected to TiDB server 1.

### Step 2. In terminal 1, change `tidb_allow_remove_auto_inc` to `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)
  ```

### Step 3. In terminal 2, check the value of `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)
  ```

### Step 4. In terminal 3, check the value of `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)
  ```

### Step 5. In any terminal, change the value of `tidb_allow_remove_auto_inc`

+ Try to change the value of `tidb_allow_remove_auto_inc` in global scope.
  
  ```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
  ```

### Step 6. In terminal 1, change `tidb_allow_remove_auto_inc` back to `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)
  ``` 

### Summary
The session-only TiDB system variable can only be configured on a session level, and its effects will be applied to the current session that you issued the `SET SESSION` statement.

## Module 4: Modifying Session and Global System Variables

+ [`auto_increment_offset`](https://docs.pingcap.com/tidb/stable/system-variables#auto_increment_offset) is one of the session and global system variables, modify it at the session level and the global level, then observe the results. 

### Step 1. Check terminal connections

+ Verify that you have three terminals with database connections. Two terminals ([Terminal 1](ssh://monitor1-ssh#1) and [Terminal 3](ssh://monitor1-ssh#3)) connected to TiDB server 2, and one terminal ([Terminal 2](ssh://monitor1-ssh#2)) connected to TiDB server 1.

### Step 2. In terminal 1, update `auto_increment_offset` from `1` to `99`

+ Check and modify the system variable `auto_increment_offset` from `1` to `99` at the session level in [Terminal 1](ssh://monitor1-ssh#1). Confirm that the variable had been updated at the session level only. 
  
  ```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)
  ```

### Step 3. In terminal 2 and 3, check the value of `auto_increment_offset`

+ Check the variable value in [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3) from both session and global levels. Both should remain unchanged, which is `1`. Here, we use [Terminal 2](ssh://monitor1-ssh#2) as an example.

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

### Step 4. In terminal 1, change `auto_increment_offset` back to `1`

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

### Step 5. In terminal 1, update `auto_increment_offset` at the global level

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

### Step 6. In terminal 2 and 3, check `auto_increment_offset`

+ Check the value of `auto_increment_offset` in [Terminal 2](ssh://monitor1-ssh#2) and [Terminal 3](ssh://monitor1-ssh#3) from both session and global levels. Confirm that only the variable is updated at the global level only. We use the result from [Terminal 2](ssh://monitor1-ssh#2) as an example.
  
  ```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)
  ```

### Step 7. Restart the TiDB cluster
   
+ In any terminal, exit the connections with TiDB, and then restart the TiDB cluster.
  
  ```bash
  tiup cluster restart tidb-test --yes
  ```
  ```output
  ...
  Restarted cluster `tidb-test` successfully
  ```

### Step 8. In terminal 1, 2 and 3, check the value of `auto_increment_offset`

+ Connect to [Terminal 1](ssh://monitor1-ssh#1), [Terminal 2](ssh://monitor1-ssh#2), and [Terminal 3](ssh://monitor1-ssh#3), and check the value of `auto_increment_offset` from both session and global levels. Confirm that the variable is updated at both the session and the global level. We use [Terminal 1](ssh://monitor1-ssh#1) as an example.
  
  ```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)
  ```

### Step 9. In any terminal, change `auto_increment_offset` back to `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)
  ``` 

### Summary
If you change the session and global variable using `SET SESSION`, the effective scope will be the current session only.

If you change the session and global variables using `SET GLOBAL`, the effective scope will be all TiDB server instances and new sessions.

## Module 5: Modifying TiKV Configuration File Parameters

### Step 1. On `TiKV server 1`, view the contents of the `tikv.toml` file

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

### Step 2. On `Control Machine`, change `log.level` for `TiKV server`

+ On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor. Then, insert `log.level: warn` in the `tikv` section. Please refer to the `output` below for indentations. Press the `ESC` and type `:wq` to quit to the terminal.
  + Note: `Control Machine` is the virtual machine which Web SSH (such as [Terminal 1](ssh://monitor1-ssh#1) and [Terminal 2](ssh://monitor1-ssh#2)) connect to by default.
  
  ```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: {}
  ```

### Step 3. Reloading the `TiDB Cluster`

+ On `Control Machine`, use the `tiup cluster reload` command to update the configuration. This step will restart all `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
  ```

### Step 4. On `TiKV server 1`, view the contents of the `tikv.toml` again

+ On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file again, and verify that `log.level = "warn"`. Type `:q!` to quit the editor.

  ```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"
  ...
  ```

### Step 5. On `Control Machine`, restore the value of `log.level` to default

+ On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor. Change the `log.level` from `warn` to default for `TiKV server`. Press the `ESC` and type `:wq` to quit to the terminal:  

  ```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: {}
  ```

### Step 6. Reloading the `TiDB Cluster`

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

### Step 7. On `TiKV server 1`, view the contents of the `tikv.toml` again

+ On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file again, and verify that `log.level` is back to default. Type `:q!` to quit the editor.
  
  ```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"
  ...
  ```

## End of Exercises