# Lab 3: Configuring TiDB

### Overview
In TiDB, there are TiDB system variables and TiDB configuration file parameters to control the TiDB cluster behaviors. In these exercises, you gain some 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.

  ```output
  +-----------------------+---------------+---------------------------------------------------------+
  | 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              |
  +-----------------------+---------------+---------------------------------------------------------+
  ```
+ In the last exercise, you also use placement rules to place hot and cold data on high and low performance storages respectively. 

### Notes
+ Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this demo.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ The required IP addresses for specific commands will be substituted with the actual ones automatically once you have set up the lab environment after Step 1.
+ Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.

### Assumptions
+ You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): `chmod 400 [~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)`
+ Windows users please refer to this [guide](https://github.com/pingcap/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md) for private key preparation.

## Exercise 1
### Subject: Modifying Global Persistent System Variables

### Overview
[`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. 

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ Modify TiDB system variable `tidb_gc_enable`.

### Solution

1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the <kbd>Assumptions</kbd> section under the <kbd>OVERVIEW</kbd> tab. The returned message may be slightly different from what is displayed here. If prompted, answer `yes`.
   ```command
   $ ssh -i ~/.ssh/pe-class-key-oregon.pem ec2-user@monitor1_address
   ```
   ```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. Open a new terminal window (as terminal 1), and connect to the TiDB server 1 by executing the script `connect-db1.sh`. 

   ```command
   $ ./connect-db1.sh
   ```
   ```output
   ...
   tidb:db1>
   ```

3. Open a new terminal window (as terminal 2), and connect to the TiDB server 1 by using `connect-db1.sh`.
   
   ```command
   $ ./connect-db1.sh
   ```
   ```output
   ...
   tidb:db1>
   ```

4. Open a new terminal window again (as terminal 3), and connect to a different TiDB server 2 by using `connect-db2.sh`.

   ```command
   $ ./connect-db2.sh
   ```
   ```output
   ...
   tidb:db2>
   ```

5. In terminal 1, verify the default value of `tidb_gc_enable`. And, change the value from `ON` to `OFF`.

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

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

   ```command   
   SET GLOBAL tidb_gc_enable=OFF;
   ```
   ```output
   tidb:db1> SET GLOBAL tidb_gc_enable=OFF;
   Query OK, 0 rows affected (0.06 sec)
   ```

6. Verify the change on terminal 1. The variable value had been updated at both session and global levels.
   ```command
   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)
   ```

   ```command
   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. Verify the change on terminal 2. The variable value had been updated at both session and global levels.
   ```command
   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)
   ```

   ```command
   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. Verify the change on terminal 3. The variable value had been updated at both session and global levels.
   ```command
   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)

   ```

   ```command
   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.  In any terminal, attempt to change the value of `tidb_gc_enable` on session scope. Observe the failed result. For example, on terminal 3.
   ```command
   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. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. 
   ```command
   $ tiup cluster restart tidb-test --yes
   ```
   ```output
   ...
   Restarted cluster `tidb-test` successfully
   ```

11. Verify that in terminal 1, terminal 2, and terminal 3, the value of `tidb_gc_enable` is `OFF`. We use terminal 1 as an example. You need to re-connect to TiDB servers because the previous restart disconnected your sessions. 
   ```command
   $ ./connect-db1.sh
   ```
   ```output
   ...
   tidb:db1>
   ```

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

   ```

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

12. Change `tidb_gc_enable` back to `ON` in any terminal.
   ```command
   SET GLOBAL tidb_gc_enable=ON;
   ```
   ```output
   tidb:db1> SET GLOBAL tidb_gc_enable=ON;
   Query OK, 0 rows affected (0.07 sec)
   ``` 

13. Keep all three terminals and database sessions open.

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

## Exercise 2
### Subject: Modifying Non-Persistent Global System Variables

### Overview
[`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. 

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ Modify TiDB system variable `max_connections`.

### Solution
1. In terminal 1, verify the default value of `max_connections`. And, change the value from `0` to `1`, then confirm the change.
   ```command
   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)
   ```

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

   ```command
   SET GLOBAL max_connections=1;
   ```
   ```output
   tidb:db1> SET GLOBAL max_connections=1;
   Query OK, 0 rows affected (0.00 sec)   
   ```

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

   ```command
   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. Verify the change on terminal 2, the value is `1`, consistent with the observation from terminal 1.
   ```command
   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)
   ```

   ```command
   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. Verity the value on terminal 3. Confirm that the value has not changed, it is still `0`.
   ```command
   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)
   ```

   ```command
   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. Exit the database session on terminal 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. 
   ```command
   $ ./connect-db1.sh 
   ```
   ```output
   ERROR 1040 (HY000): Too many connections
   ```

5. In terminal 1, try to log in by executing `connect-db2.sh`. The login attempt should be successful, as the TiDB server instance 2, has its `max_connections` system variable set to `0`, which implies that any number of connections can be established. 
   ```command
   $ ./connect-db2.sh 
   ```
   ```output
   ...
   tidb:db2>
   ```

6. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. 
   ```command
   $ tiup cluster restart tidb-test --yes
   ```
   ```output
   ...
   Restarted cluster `tidb-test` successfully
   ```

7. Verify that in terminal 1, terminal 2, and terminal 3, the value of `max_connections` is 0. We use terminal 1 as an example.
   ```command
   $ ./connect-db2.sh
   ```
   ```output
   ...
   tidb:db2>
   ```

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

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

8. Keep all three terminals and database sessions open.

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

## Exercise 3
### Subject: Modifying Session Only System Variables

### Overview
[`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. 

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ Modify TiDB system variable `tidb_allow_remove_auto_inc`.

### Solution
1. Verify that you have two terminals (terminal 1 and terminal 3) connected to TiDB server 2, and one terminal (terminal 2) connected to TiDB server 1.

2. Check and modify the system variable `tidb_allow_remove_auto_inc` from `OFF` to `ON` in terminal 1, and check it again. 
   ```command
   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)
   ```

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

   ```command
   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. Check the value of `tidb_allow_remove_auto_inc` from another session in terminal 2. Confirm that it is still `OFF`.
   ```command
   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. Check the value of `tidb_allow_remove_auto_inc` from another session in terminal 3. Confirm that it is also still `OFF`.
   ```command
   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. In any terminal, attempt to change the value of `tidb_allow_remove_auto_inc` on global scope. Observe the failed result. For example, on terminal 3.
   ```command
   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. Change `tidb_allow_remove_auto_inc` back to `OFF` on terminal 1.
   ```command
   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)
   ``` 

7. Keep all three terminals and database sessions open.


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

## Exercise 4
### Subject: Modifying Session and Global System Variables

### Overview
[`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. 

### Duration
+ This exercise should take you approximately 10 minutes to complete.

### Tasks
+ Modify TiDB system variable `auto_increment_offset` at session level.
+ Modify TiDB system variable `auto_increment_offset` at global level.

### Solution
1. Verify that you have three terminals with database connections. Two terminals (terminal 1 and terminal 3) connected to TiDB server 2, and one terminal (terminal 2) connected to TiDB server 1.

2. Check and modify the system variable `auto_increment_offset` from `1` to `99` at the session level in terminal 1. Confirm that the variable had been updated at the session level only. 
   ```command
   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)
   ```

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

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

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

   ```command
   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. Check the variable value in terminal 2 and terminal 3 from both session and global levels. Both should remain unchanged, which is `1`.
   + In terminal 2.

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

      ```command
      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)
      ```
   + In terminal 3.
  
      ```command
      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.01 sec)
      ```

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

4. Change `auto_increment_offset` back to `1` on terminal 1.
   ```command
   SET SESSION auto_increment_offset=1;
   ```

5. Change `auto_increment_offset` from `1` to `99` at the global level in terminal 1. Confirm that the variable had been updated at the global level only.
   ```command
   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)
   ```

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

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

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

   ```command
   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. Check the variable value in terminal 2 and terminal 3 from both session and global levels. Confirm that only the variable is updated at the global level only.
   + In terminal 2.
  
      ```command
      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)
      ```

      ```command
      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)
      ```
   + In terminal 3.
  
      ```command
      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.01 sec)
      ```

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

7. In any terminal, exit the connections with TiDB, and restart the TiDB cluster. 
   ```command
   $ tiup cluster restart tidb-test --yes
   ```
   ```output
   ...
   Restarted cluster `tidb-test` successfully
   ```

8. Connect to terminal 1, terminal 2, and terminal 3, and check the value of `auto_increment_offset` from both session and global levels. Confirm that only the variable is updated at both the session and the global level. We use terminal 1 as an example.
   + In terminal 1.
  
      ```command
      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)
      ```

      ```command
      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.  In any terminal, change `auto_increment_offset` back to `1`.
   ```command
   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.

## Exercise 5
### Subject: Modifying the TiKV Configuration File Parameter

### Overview
In this exercise, you modify the configuration of `TiKV server` using `tiup config` and `tiup reload`.

### Duration
+ This exercise should take you approximately 15 minutes to complete.

### Tasks
+ On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file.
+ On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor for you to modify `log.level: warn` of `TiKV server`.
+ On `Control Machine`, use the `tiup cluster reload` command to update the configuration. This step will restart all components.
+ On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file again, and verify that the `log.level = "warn"`.
+ Close all terminal windows.


### Solution
1. On `TiKV server 1`, use a text editor to view the contents of the `tikv.toml` file. Type `:q!` to quit the editor. Enter `yes` if prompted by `ssh`:
   ```command
   $ ssh -i ~/.ssh/pe-class-key-oregon.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. On `Control Machine`, use the `tiup cluster edit-config` command that starts an editor. Enter the following command at the terminal prompt to change the `log.level` to `warn` for `TiKV server`. Press the `ESC` and type `:wq` to quit to the terminal: 
   ```command
   $ 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. On `Control Machine`, use the `tiup cluster reload` command to update the configuration. This step will restart all `TiKV server`. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ tiup cluster reload tidb-test
   ```
   ```output
   tiup is checking updates for component cluster ...
   Starting component `cluster`: /root/.tiup/components/cluster/v1.11.0/tiup-cluster reload tidb-test
   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=/root/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa, publicKey=/root/.tiup/storage/cluster/clusters/tidb-test/ssh/id_rsa.pub
   + [Parallel] - UserSSH: user=tidb, host=172.31.0.154
   + [Parallel] - UserSSH: user=tidb, host=172.31.0.50
   ...
   Reloaded cluster `tidb-test` successfully
   ```


4. 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.
   ```command
   $ ssh -i ~/.ssh/pe-class-key-oregon.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"
   ...
   ```

## End of Exercises