# Configuring TiDB System Variables ### Overview + In TiDB, there are TiDB server system variables and TiDB cluster configuration parameters to control the TiDB cluster behaviors. In these exercises, you gain some experience on modifying TiDB system variables. + 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 | ### Notes + Some of the steps require that you type commands in two or more different terminal windows. You might find it convenient to launch two or more terminals and keep them open for the duration of the exercises. + Your environment may be different with these exercises, including but not limited to `IP address`, `port number`, `username`, `password`, and `directory`. + The "`$`" sign in your terminal window is an OS prompt. + The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt. + These exercises can be run on a single machine, such as your laptop. + These exercises are designed for TiDB Playground Cluster. It is crucial to note that TiDB Playground Cluster must not be utilized for production purposes. ### Assumptions + You are using macOS or Linux. + You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git). + You have completed the Exercises - Creating a TiDB Cluster for Testing Purposes. ## Exercise 1: Modifying Persistent Global System Variables ### Overview + [`tidb_gc_enable`](https://docs.pingcap.com/tidb/stable/system-variables#tidb_gc_enable-new-in-v50) is one of the 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 `tidb_gc_enable`. ### Solution 1. Open a new terminal window (as terminal 1), and connect to the TiDB server by executing the script `connect-4000.sh`. ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 2. Open a new terminal window (as terminal 2), and connect to the TiDB server by using `connect-4000.sh`. ``` $ cd tidb-course-201-lab/scripts $ ./connect-4000.sh ... tidb:4000> ``` 3. Open a new terminal window again (as terminal 3), and connect to a different TiDB server by using `connect-4001.sh`. ``` $ cd tidb-course-201-lab/scripts $ ./connect-4001.sh ... tidb:4001> ``` 4. In terminal 1, verify the default value of `tidb_gc_enable`. And, change the value from `ON` to `OFF`. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; SET GLOBAL tidb_gc_enable=OFF; ``` ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | ON | +----------------+-------+ 1 row in set (0.00 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | ON | +----------------+-------+ 1 row in set (0.00 sec) tidb:4000> SET GLOBAL tidb_gc_enable=OFF; -------------- SET GLOBAL tidb_gc_enable=OFF -------------- Query OK, 0 rows affected (0.06 sec) ``` 5. Verify the change on terminal 3. The variable value had been updated at both session and global levels. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` 6. Verify the change on terminal 2. The variable value had been updated at both session and global levels. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- 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 3. The variable value had been updated at both session and global levels. ```sql SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW SESSION VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.01 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable'; -------------- SHOW GLOBAL VARIABLES LIKE 'tidb_gc_enable' -------------- +----------------+-------+ | Variable_name | Value | +----------------+-------+ | tidb_gc_enable | OFF | +----------------+-------+ 1 row in set (0.00 sec) ``` 8. In any terminal, attempt to change the value of `tidb_gc_enable` on session scope. Observe the failed result. For example, on terminal 3. ```sql SET SESSION tidb_gc_enable=ON; ``` ``` tidb:4001> SET SESSION tidb_gc_enable=ON; -------------- SET SESSION tidb_gc_enable=ON -------------- ERROR 1229 (HY000): Variable 'tidb_gc_enable' is a GLOBAL variable and should be set with SET GLOBAL ``` 9. Change `tidb_gc_enable` back to `ON` in any terminal. ```sql SET GLOBAL tidb_gc_enable=ON; ``` ``` tidb:4000> SET GLOBAL tidb_gc_enable=ON; -------------- SET GLOBAL tidb_gc_enable=ON -------------- Query OK, 0 rows affected (0.07 sec) ``` 10. 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: 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. ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; SET GLOBAL max_connections=1; SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- SHOW GLOBAL VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:4000> SET GLOBAL max_connections=1; -------------- SET GLOBAL max_connections=1 -------------- Query OK, 0 rows affected (0.00 sec) tidb:4000> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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. ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 1 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'max_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'max_connections'; -------------- SHOW SESSION VARIABLES LIKE 'max_connections' -------------- +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 0 | +-----------------+-------+ 1 row in set (0.00 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'max_connections'; -------------- 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-4000.sh` again. However, this time the login attempt will fail due to the `max_connections` setting on the TiDB server instance with the operational port 4000. ``` $ ./connect-4000.sh ERROR 1040 (HY000): Too many connections ``` 5. In terminal 1, try to log in by executing `connect-4001.sh`. The login attempt should be successful, as the TiDB server instance, with the operational port 4001, has its `max_connections` system variable set to `0`, which implies that any number of connections can be established. ``` $ ./connect-4001.sh ... tidb:4001> ``` 6. In terminal 2, change `max_connections` back to `0`. ```sql SET GLOBAL max_connections=0; ``` ``` tidb:4000> SET GLOBAL max_connections=0; -------------- SET GLOBAL max_connections=0 -------------- Query OK, 0 rows affected (0.01 sec) ``` 7. 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: 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 on port 4001, and one terminal (terminal 2) connected to TiDB server on port 4000. 2. Check and modify the system variable `tidb_allow_remove_auto_inc` from `OFF` to `ON` in terminal 1, and check it again. ```sql SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; SET SESSION tidb_allow_remove_auto_inc=ON; SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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) tidb:4001> SET SESSION tidb_allow_remove_auto_inc=ON; -------------- SET SESSION tidb_allow_remove_auto_inc=ON -------------- Query OK, 0 rows affected (0.00 sec) tidb:4001> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'tidb_allow_remove_auto_inc'; -------------- 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. ```sql SET GLOBAL tidb_allow_remove_auto_inc=ON; ``` ``` tidb:4001> SET GLOBAL tidb_allow_remove_auto_inc=ON; -------------- 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. ```sql SET SESSION tidb_allow_remove_auto_inc=OFF; ``` ``` tidb:4001> SET SESSION tidb_allow_remove_auto_inc=OFF; -------------- 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: 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 on port 4001, and one terminal (terminal 2) connected to TiDB server on port 4000. 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. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; SET SESSION auto_increment_offset=99; SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4001> SET SESSION auto_increment_offset=99; -------------- SET SESSION auto_increment_offset=99 -------------- Query OK, 0 rows affected (0.00 sec) tidb:4001> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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`. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` In terminal 2. ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` In terminal 3. ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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. ```sql 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. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; SET GLOBAL auto_increment_offset=99; SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4001> SET GLOBAL auto_increment_offset=99; -------------- SET GLOBAL auto_increment_offset=99 -------------- Query OK, 0 rows affected (0.05 sec) tidb:4001> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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 the variable is updated at the global level only. ```sql SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; ``` In terminal 2. ``` tidb:4000> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.00 sec) tidb:4000> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 99 | +-----------------------+-------+ 1 row in set (0.00 sec) ``` In terminal 3. ``` tidb:4001> SHOW SESSION VARIABLES LIKE 'auto_increment_offset'; -------------- SHOW SESSION VARIABLES LIKE 'auto_increment_offset' -------------- +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | auto_increment_offset | 1 | +-----------------------+-------+ 1 row in set (0.01 sec) tidb:4001> SHOW GLOBAL VARIABLES LIKE 'auto_increment_offset'; -------------- 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, change `auto_increment_offset` back to `1`. ```sql SET GLOBAL auto_increment_offset=1; ``` ``` tidb:4000> SET GLOBAL auto_increment_offset=1; -------------- SET GLOBAL auto_increment_offset=1 -------------- Query OK, 0 rows affected (0.07 sec) ``` ## Conclusion | 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 | Enroll the instructor-led training course [TiDB Administration](https://pingcap.com/education/) to learn more and get hands-on practice on the real TiDB cluster. ## End of Exercises