Lesson02 tidb连接管理
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 to somewhere safe, it is only displayed once, and will not be stored.
The generated password can NOT be get and shown again.
2.1连接到tidb数据库
mysql -h 127.0.0.1 -P4000 -u root -p’14^S5b*9@n7zU+JeR0’
2.2 tidb兼容情况
100%兼容mysql 5.7
不支持的存储功能
- 存储过程、函数
- 触发器
- 外键
2.3常用命令
shwo 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的配置
tidb里有两类配置文件，1是系统配置（全局、绘画），2是集群配置。
##3.1 系统配置
https://docs.pingcap.com/zh/tidb/stable/system-variables
通过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’;
当前会话不生效，当前会话会覆盖global。
global只对新建会话生效。
配置情况会存放在tikv中。
3.3 集群配置文件离线修改
https://docs.pingcap.com/zh/tidb/stable/tidb-configuration-file
tiup cluster edit-config tidbtest
tiup cluster reload tidbtest -N xx/-R tidb。。。
tiup cluster show-config tidbtest
3.3 集群配置文件在线修改
https://docs.pingcap.com/zh/tidb/stable/tidb-configuration-file
查看参数配置
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)
修改所有tikv配置
set config tikv split.qps-threshold=1000
修改单个tikv配置
set config “127.0.0.1:20180” split.qps-threshold=1000
修改pd配置
set config pd log.level=‘info’
