单节点离线部署TiDB 6.1用于测试

最新推荐文章于 2024-07-01 00:00:00 发布

zxrhhm

最新推荐文章于 2024-07-01 00:00:00 发布

阅读量816

点赞数 13

文章标签： tidb mysql

本文链接：https://blog.csdn.net/zxrhhm/article/details/139523138

版权

单节点离线快速部署TiDB 6.1用于测试

1、环境准备

操作系统安装此处省略
系统参数配置请参考官方文档

1.1、挂载光驱及install ntp numactl

-- 挂载光驱
mount /dev/cdrom /mnt
df -h

-- install ntp numactl

yum install -y ntp
yum -y install numactl

2、add user tidb

useradd -m -d /home/tidb tidb
password tidb

visudo

[root@tidb80 ~]# chown -R tidb:tidb /tidb
[root@tidb80 ~]# chmod -R 775 /tidb
[root@tidb80 ~]# su - tidb

3、解压软件

cd /tidb/soft
tar xzvf tidb-community-server-v6.1.0-linux-amd64.tar.gz
cd tidb-community-server-v6.1.0-linux-amd64
ls -lsa

4、install tidb cluster

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ sh local_install.sh
Disable telemetry success
Successfully set mirror to /tidb/soft/tidb-community-server-v6.1.0-linux-amd64
Detected shell: bash
Shell profile:  /home/tidb/.bash_profile
/home/tidb/.bash_profile has been modified to to add tiup to PATH
open a new terminal or source /home/tidb/.bash_profile to use it
Installed path: /home/tidb/.tiup/bin/tiup
1.source /home/tidb/.bash_profile
2.Have a try:   tiup playground
===============================================
[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ source /home/tidb/.bash_profile

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ vi /tidb/config.yaml
# # Global variables are applied to all deployments and used as the default value of
# # the deployments if a specific deployment value is missing.
global:
 user: "tidb"
 ssh_port: 22
 deploy_dir: "/tidb-deploy"
 data_dir: "/tidb-data"

# # Monitored variables are applied to all the machines.
monitored:
 node_exporter_port: 9100
 blackbox_exporter_port: 9115

server_configs:
 tidb:
   log.slow-threshold: 300
 tikv:
   readpool.storage.use-unified-pool: false
   readpool.coprocessor.use-unified-pool: true
 pd:
   replication.enable-placement-rules: true
   replication.location-labels: ["host"]
 tiflash:
   logger.level: "info"

pd_servers:
 - host: 192.168.80.80

tidb_servers:
 - host: 192.168.80.80

tikv_servers:
 - host: 192.168.80.80
   port: 20160
   status_port: 20180
   config:
     server.labels: { host: "tidb-1" }

 - host: 192.168.80.80
   port: 20161
   status_port: 20181
   config:
     server.labels: { host: "tidb-2" }

 - host: 192.168.80.80
   port: 20162
   status_port: 20182
   config:
     server.labels: { host: "tidb-3" }

tiflash_servers:
 - host: 192.168.80.80

monitoring_servers:
 - host: 192.168.80.80

grafana_servers:
 - host: 192.168.80.80

5、tiup cluster deploy 部署集群

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ tiup cluster deploy TiDBcluster v6.1.0 /tidb/config.yaml --user root -p
tiup is checking updates for component cluster ...
Starting component cluster: /home/tidb/.tiup/components/cluster/v1.10.2/tiup-cluster deploy TiDBcluster v6.1.0 /tidb/config.yaml --user root -p
Input SSH password:
- Detect CPU Arch Name
- Detecting node 192.168.80.80 Arch info ... Done
- Detect CPU OS Name
- Detecting node 192.168.80.80 OS info ... Done
Please confirm your topology:
Cluster type:    tidb
Cluster name:    TiDBcluster
Cluster version: v6.1.0
Role        Host           Ports                            OS/Arch       Directories
--------------------------------------------------------------------------------
pd          192.168.80.80  2379/2380                        linux/x86_64  /tidb-deploy/pd-2379,/tidb-data/pd-2379
tikv        192.168.80.80  20160/20180                      linux/x86_64  /tidb-deploy/tikv-20160,/tidb-data/tikv-20160
tikv        192.168.80.80  20161/20181                      linux/x86_64  /tidb-deploy/tikv-20161,/tidb-data/tikv-20161
tikv        192.168.80.80  20162/20182                      linux/x86_64  /tidb-deploy/tikv-20162,/tidb-data/tikv-20162
tidb        192.168.80.80  4000/10080                       linux/x86_64  /tidb-deploy/tidb-4000
tiflash     192.168.80.80  9000/8123/3930/20170/20292/8234  linux/x86_64  /tidb-deploy/tiflash-9000,/tidb-data/tiflash-9000
prometheus  192.168.80.80  9090/12020                       linux/x86_64  /tidb-deploy/prometheus-9090,/tidb-data/prometheus-9090
grafana     192.168.80.80  3000                             linux/x86_64  /tidb-deploy/grafana-3000
Attention:
1. If the topology is not what you expected, check your yaml file.
2. Please confirm there is no port/directory conflicts in same host.
Do you want to continue? [y/N]: (default=N) y
- Generate SSH keys ... Done
- Download TiDB components

- Download pd:v6.1.0 (linux/amd64) ... Done
- Download tikv:v6.1.0 (linux/amd64) ... Done
- Download tidb:v6.1.0 (linux/amd64) ... Done
- Download tiflash:v6.1.0 (linux/amd64) ... Done
- Download prometheus:v6.1.0 (linux/amd64) ... Done
- Download grafana:v6.1.0 (linux/amd64) ... Done
- Download node_exporter: (linux/amd64) ... Done
- Download blackbox_exporter: (linux/amd64) ... Done
- Initialize target host environments

- Prepare 192.168.80.80:22 ... Done
- Deploy TiDB instance

- Copy pd -> 192.168.80.80 ... Done
- Copy tikv -> 192.168.80.80 ... Done
- Copy tikv -> 192.168.80.80 ... Done
- Copy tikv -> 192.168.80.80 ... Done
- Copy tidb -> 192.168.80.80 ... Done
- Copy tiflash -> 192.168.80.80 ... Done
- Copy prometheus -> 192.168.80.80 ... Done
- Copy grafana -> 192.168.80.80 ... Done
- Deploy node_exporter -> 192.168.80.80 ... Done
- Deploy blackbox_exporter -> 192.168.80.80 ... Done
- Copy certificate to remote host
- Init instance configs

- Generate config pd -> 192.168.80.80:2379 ... Done
- Generate config tikv -> 192.168.80.80:20160 ... Done
- Generate config tikv -> 192.168.80.80:20161 ... Done
- Generate config tikv -> 192.168.80.80:20162 ... Done
- Generate config tidb -> 192.168.80.80:4000 ... Done
- Generate config tiflash -> 192.168.80.80:9000 ... Done
- Generate config prometheus -> 192.168.80.80:9090 ... Done
- Generate config grafana -> 192.168.80.80:3000 ... Done
- Init monitor configs

- Generate config node_exporter -> 192.168.80.80 ... Done
- Generate config blackbox_exporter -> 192.168.80.80 ... Done
Enabling component pd
Enabling instance 192.168.80.80:2379
Enable instance 192.168.80.80:2379 success
Enabling component tikv
Enabling instance 192.168.80.80:20162
Enabling instance 192.168.80.80:20160
Enabling instance 192.168.80.80:20161
Enable instance 192.168.80.80:20161 success
Enable instance 192.168.80.80:20160 success
Enable instance 192.168.80.80:20162 success
Enabling component tidb
Enabling instance 192.168.80.80:4000
Enable instance 192.168.80.80:4000 success
Enabling component tiflash
Enabling instance 192.168.80.80:9000
Enable instance 192.168.80.80:9000 success
Enabling component prometheus
Enabling instance 192.168.80.80:9090
Enable instance 192.168.80.80:9090 success
Enabling component grafana
Enabling instance 192.168.80.80:3000
Enable instance 192.168.80.80:3000 success
Enabling component node_exporter
Enabling instance 192.168.80.80
Enable 192.168.80.80 success
Enabling component blackbox_exporter
Enabling instance 192.168.80.80
Enable 192.168.80.80 success
Cluster 
TiDBcluster deployed successfully, you can start it with command: 
tiup cluster start TiDBcluster --init

6、tiup cluster list

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ tiup cluster list
tiup is checking updates for component cluster ...
Starting component 
cluster: /home/tidb/.tiup/components/cluster/v1.10.2/tiup-cluster list
Name         User  Version  Path                                                   PrivateKey
--------------------------------------------------------------------------------
TiDBcluster  tidb  v6.1.0   /home/tidb/.tiup/storage/cluster/clusters/TiDBcluster  /home/tidb/.tiup/storage/cluster/clusters/TiDBcluster/ssh/id_rsa

7、tiup cluster display TiDBcluster

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ tiup cluster display TiDBcluster
tiup is checking updates for component cluster ...
Starting component cluster: /home/tidb/.tiup/components/cluster/v1.10.2/tiup-cluster display TiDBcluster
Cluster type:       tidb
Cluster name:       TiDBcluster
Cluster version:    v6.1.0
Deploy user:        tidb
SSH type:           builtin
Grafana URL:        http://192.168.80.80:3000
ID                   Role        Host           Ports                            OS/Arch       Status  Data Dir                    Deploy Dir
--------------------------------------------------------------------------------
192.168.80.80:3000   grafana     192.168.80.80  3000                             linux/x86_64  Down    -                           /tidb-deploy/grafana-3000
192.168.80.80:2379   pd          192.168.80.80  2379/2380                        linux/x86_64  Down    /tidb-data/pd-2379          /tidb-deploy/pd-2379
192.168.80.80:9090   prometheus  192.168.80.80  9090/12020                       linux/x86_64  Down    /tidb-data/prometheus-9090  /tidb-deploy/prometheus-9090
192.168.80.80:4000   tidb        192.168.80.80  4000/10080                       linux/x86_64  Down    -                           /tidb-deploy/tidb-4000
192.168.80.80:9000   tiflash     192.168.80.80  9000/8123/3930/20170/20292/8234  linux/x86_64  N/A     /tidb-data/tiflash-9000     /tidb-deploy/tiflash-9000
192.168.80.80:20160  tikv        192.168.80.80  20160/20180                      linux/x86_64  N/A     /tidb-data/tikv-20160       /tidb-deploy/tikv-20160
192.168.80.80:20161  tikv        192.168.80.80  20161/20181                      linux/x86_64  N/A     /tidb-data/tikv-20161       /tidb-deploy/tikv-20161
192.168.80.80:20162  tikv        192.168.80.80  20162/20182                      linux/x86_64  N/A     /tidb-data/tikv-20162       /tidb-deploy/tikv-20162
Total nodes: 8

8、tiup cluster start TiDBcluster

tiup cluster start 是 TiUP（TiDB Universal Package Manager）中的一个命令，用于启动指定的 TiDB 集群。

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ tiup cluster start TiDBcluster --init  
tiup is checking updates for component cluster ...
Starting component cluster: /home/tidb/.tiup/components/cluster/v1.10.2/tiup-cluster start TiDBcluster
Starting cluster TiDBcluster...
- [ Serial ] - SSHKeySet: privateKey=/home/tidb/.tiup/storage/cluster/clusters/TiDBcluster/ssh/id_rsa, publicKey=/home/tidb/.tiup/storage/cluster/clusters/TiDBcluster/ssh/id_rsa.pub
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [Parallel] - UserSSH: user=tidb, host=192.168.80.80
- [ Serial ] - StartCluster
Starting component pd
Starting instance 192.168.80.80:2379
Start instance 192.168.80.80:2379 success
Starting component tikv
Starting instance 192.168.80.80:20162
Starting instance 192.168.80.80:20161
Starting instance 192.168.80.80:20160
Start instance 192.168.80.80:20161 success
Start instance 192.168.80.80:20162 success
Start instance 192.168.80.80:20160 success
Starting component tidb
Starting instance 192.168.80.80:4000
Start instance 192.168.80.80:4000 success
Starting component tiflash
Starting instance 192.168.80.80:9000
Start instance 192.168.80.80:9000 success
Starting component prometheus
Starting instance 192.168.80.80:9090
Start instance 192.168.80.80:9090 success
Starting component grafana
Starting instance 192.168.80.80:3000
Start instance 192.168.80.80:3000 success
Starting component node_exporter
Starting instance 192.168.80.80
Start 192.168.80.80 success
Starting component blackbox_exporter
Starting instance 192.168.80.80
Start 192.168.80.80 success
- [ Serial ] - UpdateTopology: cluster=TiDBcluster
Started cluster TiDBcluster successfully
The root password of TiDB database has been changed.
The new password is: '8$1jcxSt_=Kav63*74'.  
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.

-- root password is '8$1jcxSt_=Kav63*74'

注意事项

如果忘记了集群名称，可以使用 tiup cluster list 命令来查看集群列表。
启动集群时，服务会按照预定的顺序（如 PD -> TiKV -> Pump -> TiDB 等）来启动。
如果遇到任何问题，可以查看启动日志以获取更多信息。

9、tiup cluster display TiDBcluster 显示TiDB 集群的状态信息

tiup cluster display 是 TiUP（TiDB Universal Package Manager）的一个命令，用于显示 TiDB 集群的状态信息

[tidb@tidb80 tidb-community-server-v6.1.0-linux-amd64]$ tiup cluster display TiDBcluster
tiup is checking updates for component cluster ...
Starting component 
cluster: /home/tidb/.tiup/components/cluster/v1.10.2/tiup-cluster display TiDBcluster
Cluster type:       tidb
Cluster name:       TiDBcluster
Cluster version:    v6.1.0
Deploy user:        tidb
SSH type:           builtin
Dashboard URL:      http://192.168.80.80:2379/dashboard
Grafana URL:        http://192.168.80.80:3000
ID                   Role        Host           Ports                            OS/Arch       Status   Data Dir                    Deploy Dir
--------------------------------------------------------------------------------
192.168.80.80:3000   grafana     192.168.80.80  3000                             linux/x86_64  Up       -                           /tidb-deploy/grafana-3000
192.168.80.80:2379   pd          192.168.80.80  2379/2380                        linux/x86_64  Up|L|UI  /tidb-data/pd-2379          /tidb-deploy/pd-2379
192.168.80.80:9090   prometheus  192.168.80.80  9090/12020                       linux/x86_64  Up       /tidb-data/prometheus-9090  /tidb-deploy/prometheus-9090
192.168.80.80:4000   tidb        192.168.80.80  4000/10080                       linux/x86_64  Up       -                           /tidb-deploy/tidb-4000
192.168.80.80:9000   tiflash     192.168.80.80  9000/8123/3930/20170/20292/8234  linux/x86_64  Up       /tidb-data/tiflash-9000     /tidb-deploy/tiflash-9000
192.168.80.80:20160  tikv        192.168.80.80  20160/20180                      linux/x86_64  Up       /tidb-data/tikv-20160       /tidb-deploy/tikv-20160
192.168.80.80:20161  tikv        192.168.80.80  20161/20181                      linux/x86_64  Up       /tidb-data/tikv-20161       /tidb-deploy/tikv-20161
192.168.80.80:20162  tikv        192.168.80.80  20162/20182                      linux/x86_64  Up       /tidb-data/tikv-20162       /tidb-deploy/tikv-20162
Total nodes: 8

通过 http://192.168.80.80:9090 访问 TiDB 的 Prometheus 管理界面。
通过 http://192.168.80.80:2379/dashboard 访问 TiDB Dashboard 页面，默认用户名为 root，密码为空。
通过 http://192.168.80.80:3000 访问 TiDB 的 Grafana 界面，默认用户名和密码都为 admin

10、安装mysql client

su - root
yum -y install mysql

su - tidb

vi ~/.bash_profile
export MYSQL_PS1="(\u@\h:\p)[\d]>"
source ~/.bash_profile

[tidb@tidb80 ~]$ mysql -u root -h 192.168.80.80 -P 4000 -p
Enter password: 输入start TiDBcluster --init 生成的密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 407
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(root@192.168.80.80:4000)[(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA     |
| PERFORMANCE_SCHEMA |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

(root@192.168.80.80:4000)[(none)]>use test;
Database changed

(root@192.168.80.80:4000)[test]> create user super identified by 'Xxxx@go2024';
Query OK, 0 rows affected (0.08 sec)
(root@192.168.80.80:4000)[test]> grant all privileges  on *.* to super;
Query OK, 0 rows affected (0.04 sec)
(root@192.168.80.80:4000)[test]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
(root@192.168.80.80:4000)[test]> exit
Bye

11、使用MySQL客户端登陆tidb集群

使用MySQL客户端登陆tidb集群，创建数据库superdb并在superdb中创建测试表

[tidb@tidb80 ~]$ mysql -u super -h 192.168.80.80 -P 4000 -p
Enter password:Xxxx@go2024
-- 或者直接指定数据库
[tidb@tidb80 ~]$ mysql -u super -h 192.168.80.80 -P 4000 -p superdb
-- OR 
[tidb@tidb80 ~]$ mysql -u super -h 192.168.80.80 -P 4000 -p -D superdb
Enter password:Xxxx@go2024

(super@192.168.80.80:4000)[(none)]>create database superdb;
Query OK, 0 rows affected (0.09 sec)

(super@192.168.80.80:4000)[test]>use superdb
Database changed

(super@192.168.80.80:4000)[superdb]>create table t_superdept
    -> ( deptno int unsigned auto_increment primary key COMMENT '部门编号',
    ->   dname  varchar(15) COMMENT '部门名称'  ,
    ->   loc  varchar(50) COMMENT '部门所在位置' 
    -> )engine = InnoDB DEFAULT charset=utf8mb4 COMMENT='员工部门表';
Query OK, 0 rows affected (0.10 sec)

(super@192.168.80.80:4000)[superdb]>create table t_superemp(
    ->     empno int unsigned auto_increment primary key COMMENT '雇员编号',
    ->     ename varchar(15)  COMMENT '雇员姓名' ,  
    ->     job varchar(10)   COMMENT '雇员职位'  ,  
    ->     mgr int unsigned  COMMENT '雇员对应的领导的编号',
    ->     hiredate  date    COMMENT '雇员的雇佣日期' ,
    ->     sal decimal(7,2)  COMMENT '雇员的基本工资' ,
    ->     comm  decimal(7,2)   COMMENT '奖金'  ,
    ->     deptno int unsigned   COMMENT '所在部门' ,
    ->     constraint fk_t_superemp_deptno foreign key(deptno) references t_superdept(deptno)
    -> )engine = innodb default charset =utf8mb4 comment='雇员信息表';
Query OK, 0 rows affected (0.11 sec)

(super@192.168.80.80:4000)[superdb]>create table t_supersalgrade
    -> ( 
    -> grade int COMMENT '工资等级',
    -> losal int COMMENT '此等级的最低工资',
    -> hisal int COMMENT '此等级的最高工资'  
    -> )engine=innodb DEFAULT charset=utf8mb4 COMMENT='工资等级表';
Query OK, 0 rows affected (0.12 sec)

(super@192.168.80.80:4000)[superdb]>create table t_superbonus
    -> (   ename  varchar(10) COMMENT '雇员姓名',
    ->     job    varchar(9) COMMENT '雇员职位',  
    ->     sal    decimal(7,2) COMMENT '雇员工资',   
    ->     comm   decimal(7,2) COMMENT '雇员奖金'
    -> )engine=innodb DEFAULT charset=utf8mb4 COMMENT='雇员奖金表' ;
Query OK, 0 rows affected (0.12 sec)

(super@192.168.80.80:4000)[superdb]>show tables;
+-------------------+
| Tables_in_superdb |
+-------------------+
| t_superbonus      |
| t_superdept       |
| t_superemp        |
| t_supersalgrade   |
+-------------------+
4 rows in set (0.00 sec)

给表插入数据

-- insert t_superdept 
INSERT INTO t_superdept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO t_superdept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO t_superdept VALUES (30,'SALES','CHICAGO');
INSERT INTO t_superdept VALUES (40,'OPERATIONS','BOSTON');

-- insert t_superemp
INSERT INTO t_superemp VALUES    (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO t_superemp VALUES    (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO t_superemp VALUES    (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO t_superemp VALUES    (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO t_superemp VALUES    (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO t_superemp VALUES    (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO t_superemp VALUES    (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO t_superemp VALUES    (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20);
INSERT INTO t_superemp VALUES    (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO t_superemp VALUES    (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO t_superemp VALUES    (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20);
INSERT INTO t_superemp VALUES    (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO t_superemp VALUES    (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO t_superemp VALUES    (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

-- insert t_supersalgrade
INSERT INTO t_supersalgrade VALUES (1,700,1200);
INSERT INTO t_supersalgrade VALUES (2,1201,1400);
INSERT INTO t_supersalgrade VALUES (3,1401,2000);
INSERT INTO t_supersalgrade VALUES (4,2001,3000);
INSERT INTO t_supersalgrade VALUES (5,3001,9999);

commit;
show tables;

(super@192.168.80.80:4000)[superdb]>SELECT  * from t_superemp ;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.01 sec)

(super@192.168.80.80:4000)[superdb]>SELECT  * FROM t_superdept ;
+--------+------------+----------+
| deptno | dname      | loc      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

(super@192.168.80.80:4000)[superdb]>SELECT  * FROM t_supersalgrade ;
+-------+-------+-------+
| grade | losal | hisal |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+
5 rows in set (0.00 sec)

(super@192.168.80.80:4000)[superdb]>select VERSION() ;
+--------------------+
| VERSION()          |
+--------------------+
| 5.7.25-TiDB-v6.1.0 |
+--------------------+
1 row in set (0.00 sec)

12、测试兼容MySQL语法

12.1、支持递归查询（使用 WITH RECURSIVE 语法）

TiDB 是一个兼容 MySQL 协议的分布式关系型数据库。在 SQL 功能上，TiDB 尽可能地与 MySQL 保持一致，包括支持递归查询（使用 WITH RECURSIVE 语法）。

递归查询通常用于处理树形结构或层次结构的数据，例如组织结构、文件系统目录结构等。

以下是一个使用 WITH RECURSIVE 的基本示例，演示如何在 TiDB 中进行递归查询

(super@192.168.80.80:4000)[superdb]>with recursive cte(n)as (
    -> select 1
    -> union 
    -> select n+1 from cte where n<10
    -> )
    -> SELECT * from cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
|    8 |
|    9 |
|   10 |
+------+
10 rows in set (0.00 sec)

12.2 查找t_superemp表中empno=7698 下级员工

(super@192.168.80.80:4000)[superdb]>with recursive cte(empno,ename,mgr,mgr_level) as
    -> (
    -> select empno,ename,mgr,CAST(empno as char(256)) FROM t_superemp where empno=7698
    -> union ALL 
    -> select t1.empno,t1.ename,t1.mgr,CONCAT(t2.mgr_level,',',t1.empno) as mgr_level 
    -> FROM t_superemp t1 inner join cte t2 on t1.mgr=t2.empno
    -> )
    -> select * from cte;
+-------+--------+------+-----------+
| empno | ename  | mgr  | mgr_level |
+-------+--------+------+-----------+
|  7698 | BLAKE  | 7839 | 7698      |
|  7499 | ALLEN  | 7698 | 7698,7499 |
|  7521 | WARD   | 7698 | 7698,7521 |
|  7654 | MARTIN | 7698 | 7698,7654 |
|  7844 | TURNER | 7698 | 7698,7844 |
|  7900 | JAMES  | 7698 | 7698,7900 |
+-------+--------+------+-----------+
6 rows in set (0.01 sec)

12.3、查找t_superemp表中empno=7698 上级领导

(super@192.168.80.80:4000)[superdb]>with recursive cte as(
    -> select empno,ename,mgr,CAST(empno as char(256)) as mgr_level  FROM t_superemp where empno=7698
    -> union ALL 
    -> select t1.empno,t1.ename,t1.mgr,CONCAT(t2.mgr_level,',',t1.empno) as mgr_level 
    -> FROM t_superemp t1 inner join cte t2 on t1.empno=t2.mgr
    -> )
    -> select * from cte;
+-------+-------+------+-----------+
| empno | ename | mgr  | mgr_level |
+-------+-------+------+-----------+
|  7698 | BLAKE | 7839 | 7698      |
|  7839 | KING  | NULL | 7698,7839 |
+-------+-------+------+-----------+
2 rows in set (0.00 sec)

12.4、group_concat 和 row_number() over字句

TiDB 是一个兼容 MySQL 协议的分布式关系型数据库。在 SQL 语句中，GROUP_CONCAT 是一个聚合函数，用于将多个行的值连接成一个字符串。

在 MySQL 和 TiDB 中，GROUP_CONCAT 的用法基本相同。假设你有一个表 t_city_list，其中有两个字段：country和 city。如果你想知道每个country国家的所有city城市名字（用逗号分隔），可以使用 GROUP_CONCAT。

create table t_city_list(
id bigint auto_increment,
country varchar(64),
city varchar(64),
constraint pk_t_city_list_id primary key(id)
);

insert into t_city_list(country,city) values ('中国','北京');
insert into t_city_list(country,city) values ('中国','广州');
insert into t_city_list(country,city) values ('中国','深圳');
insert into t_city_list(country,city) values ('中国','香港');
insert into t_city_list(country,city) values ('中国','上海');
insert into t_city_list(country,city) values ('日本','东京');
insert into t_city_list(country,city) values ('日本','大阪');
insert into t_city_list(country,city) values ('中国','中山');
insert into t_city_list(country,city) values ('中国','珠海');
commit;

(super@192.168.80.80:4000)[superdb]>select * from t_city_list;
+----+---------+--------+
| id | country | city   |
+----+---------+--------+
|  1 | 中国    | 北京   |
|  2 | 中国    | 广州   |
|  3 | 中国    | 深圳   |
|  4 | 中国    | 香港   |
|  5 | 中国    | 上海   |
|  6 | 日本    | 东京   |
|  7 | 日本    | 大阪   |
|  8 | 中国    | 中山   |
|  9 | 中国    | 珠海   |
+----+---------+--------+
9 rows in set (0.00 sec)

(super@192.168.80.80:4000)[superdb]>select COUNTRY,group_concat(CITY separator '-') PERMS from t_city_list group by COUNTRY;
+---------+--------------------------------------------------+
| COUNTRY | PERMS                                            |
+---------+--------------------------------------------------+
| 中国    | 北京-广州-深圳-香港-上海-中山-珠海               |
| 日本    | 东京-大阪                                        |
+---------+--------------------------------------------------+
2 rows in set (0.00 sec)

select COUNTRY,group_concat(distinct CITY order by id desc separator '-') PERMS from t_city_list group by COUNTRY;
-- 按照特定的顺序连接值
(super@192.168.80.80:4000)[superdb]>select COUNTRY,group_concat(distinct CITY order by id desc separator '-') PERMS from t_city_list group by COUNTRY;
+---------+--------------------------------------------------+
| COUNTRY | PERMS                                            |
+---------+--------------------------------------------------+
| 中国    | 珠海-中山-上海-香港-深圳-广州-北京               |
| 日本    | 大阪-东京                                        |
+---------+--------------------------------------------------+
2 rows in set (0.00 sec)

在这个查询中：

GROUP BY class_id 将结果按 class_id 分组。
GROUP_CONCAT(student_name ORDER BY student_name SEPARATOR ', ') 将每个 class_id 下的所有 student_name 连接成一个字符串，并且按照 student_name 的字母顺序进行排序，使用 ', ’ 作为分隔符。

GROUP_CONCAT 函数有一些可选的参数：

DISTINCT：你可以使用 DISTINCT 来去除重复的值。例如：GROUP_CONCAT(DISTINCT student_name …)。
ORDER BY：你可以指定一个或多个列来按照特定的顺序连接值。
SEPARATOR：你可以指定一个字符串作为值之间的分隔符。如果不指定，默认使用逗号 ,

注意：GROUP_CONCAT 有一个长度限制，默认情况下可能不足以处理大量的数据。在 MySQL 和 TiDB 中，你可以通过设置 group_concat_max_len 系统变量来增加这个限制。例如，在 MySQL 中，你可以这样做

SET SESSION group_concat_max_len = 1000000; -- 设置当前会话的限制  
SET GLOBAL group_concat_max_len = 1000000; -- 设置全局的限制

但请注意，增加这个值可能会增加内存使用，因此请根据你的实际情况进行调整。

12.5、row_number() over字句

在 TiDB（以及大多数支持 SQL 标准功能的数据库系统中），ROW_NUMBER() 是一个窗口函数，它用于为结果集中的每一行分配一个唯一的序号。这个序号是根据 OVER() 子句中指定的排序顺序来分配的。

ROW_NUMBER() 通常与 OVER() 子句一起使用，后者定义了窗口函数的操作范围以及如何对数据进行分区和排序。

下面是一个使用 ROW_NUMBER() 的基本示例，假设我们有一个名为 t_city_list 的表，其中包含了国家城市的信息，并且我们想要为每个国家不同城市分配一个城市序号：


select id,COUNTRY,CITY,
       row_number() over(PARTITION BY COUNTRY order by id desc) as rn
from t_city_list;

(super@192.168.80.80:4000)[superdb]>select id,COUNTRY,CITY,row_number() over( PARTITION BY COUNTRY order by id desc) as rn from t_city_list;
+----+---------+--------+------+
| id | COUNTRY | CITY   | rn   |
+----+---------+--------+------+
|  9 | 中国    | 珠海   |    1 |
|  8 | 中国    | 中山   |    2 |
|  5 | 中国    | 上海   |    3 |
|  4 | 中国    | 香港   |    4 |
|  3 | 中国    | 深圳   |    5 |
|  2 | 中国    | 广州   |    6 |
|  1 | 中国    | 北京   |    7 |
|  7 | 日本    | 大阪   |    1 |
|  6 | 日本    | 东京   |    2 |
+----+---------+--------+------+
9 rows in set (0.00 sec)

在这个查询中：

ROW_NUMBER() 为每一行生成一个唯一的序号。
OVER() 子句定义了如何分配这些序号。
- PARTITION BY COUNTRY 表示我们想要为每个 COUNTRY 分区数据，并为每个分区中的行分配序号。
- ORDER BY ID 表示在每个分区中，序号将根据 ID 倒序或升序的值来分配。

结果将包含 id、COUNTRY 、CITY 和 rn 列，其中 rn 是为每个国家不同城市分配一个城市序号。

注意：TiDB 是一个分布式数据库，因此在执行复杂的窗口函数或分析查询时，可能需要考虑数据分区和分布的影响。确保你了解你的数据和查询是如何在 TiDB 集群中分布的，以及这如何影响查询的性能和结果