Lesson02 TiDB Connection Management Lesson03 TiDB Configuration

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Lesson02 tidb连接管理 Lesson03 TiDB的配置

| username: huanglao2002

Lesson02 TiDB Connection Management

Started cluster tidbtest successfully
The root password of TiDB database has been changed.
The new password is: ‘14^S5b*9@n7zU+JeR0’.
Copy and record it somewhere safe, it is only displayed once, and will not be stored.
The generated password can NOT be retrieved and shown again.

2.1 Connecting to TiDB Database

mysql -h 127.0.0.1 -P4000 -u root -p’14^S5b*9@n7zU+JeR0’

2.2 TiDB Compatibility

100% compatible with MySQL 5.7
Unsupported storage features:

  • Stored procedures, functions
  • Triggers
  • Foreign keys

2.3 Common Commands

show databases;
show processlist;
select tidb_version();
create database tidb;
use tidb;
show tables;
CREATE TABLE ‘tab_tidb’ (
‘id’ int(11) NOT NULL AUTO_INCREMENT,
‘name’ varchar(20) NOT NULL DEFAULT ‘’,
‘age’ int(11) NOT NULL DEFAULT 0,
‘version’ varchar(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY (‘id’),
KEY ‘idx_age’ (‘age’)
);

Lesson03 TiDB Configuration

There are two types of configuration files in TiDB: 1 is system configuration (global, session), 2 is cluster configuration.

3.1 System Configuration

View through show variables
mysql> show variables like ‘autocommit’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| autocommit | ON |
±--------------±------+
1 row in set (0.01 sec)

mysql> show session variables like ‘autocommit’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| autocommit | ON |
±--------------±------+
1 row in set (0.00 sec)

mysql> show global variables like ‘autocommit’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| autocommit | ON |
±--------------±------+
1 row in set (0.00 sec)

set session autocommit=‘OFF’;

mysql> set session autocommit=‘OFF’;
Query OK, 0 rows affected (0.00 sec)

mysql> show session variables like ‘autocommit’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| autocommit | OFF |
±--------------±------+
1 row in set (0.00 sec)

mysql> show global variables like ‘autocommit’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| autocommit | ON |
±--------------±------+
1 row in set (0.01 sec)

set global autocommit=‘OFF’;
Current session not affected, current session will override global.
Global only takes effect for new sessions.
Configuration will be stored in TiKV.

3.3 Offline Modification of Cluster Configuration Files

tiup cluster edit-config tidbtest
tiup cluster reload tidbtest -N xx/-R tidb…
tiup cluster show-config tidbtest

3.3 Online Modification of Cluster Configuration Files

View parameter configuration
mysql> show config where Type=“tidb”;
mysql> show config where Type=“tidb” and Name=“log.level”;
±-----±---------------±----------±------+
| Type | Instance | Name | Value |
±-----±---------------±----------±------+
| tidb | 127.0.0.1:4000 | log.level | info |
±-----±---------------±----------±------+
1 row in set (0.01 sec)

Modify all TiKV configurations
set config tikv split.qps-threshold=1000
Modify a single TiKV configuration
set config “127.0.0.1:20180” split.qps-threshold=1000

Modify PD configuration
set config pd log.level=‘info’

| username: shigp_TIDBER | Original post link

:grinning::grinning::grinning::grinning::grinning::grinning::grinning::grinning::grinning::grinning::grinning: