TiDB Ecosystem

https://docs.pingcap.com/tidb/stable/
Admin
Comprehensive documentation for the TiDB distributed SQL database and its ecosystem tools including...
Tokens:1,182,017
Snippets:14,558
Trust Score:10
Update:2 months ago
Show doc for...
Tokens:
Raw
### TiUP Command Examples

Source: https://docs.pingcap.com/tidb/stable/tiup-overview

Illustrates common usage patterns for TiUP commands, including starting a playground, installing components, updating, listing, and cleaning instances.

```bash
tiup playground

```

```bash
tiup playground nightly

```

```bash
tiup install <component>[:version]

```

```bash
tiup update --all

```

```bash
tiup update --nightly

```

```bash
tiup update --self

```

```bash
tiup list

```

```bash
tiup status

```

```bash
tiup clean <name>

```

```bash
tiup clean --all

```

--------------------------------

### Setup and Data Insertion for Binding Examples

Source: https://docs.pingcap.com/tidb/stable/sql-statement-create-binding

Sets up tables and inserts data, demonstrating operations that might be used to generate historical execution plans for binding creation.

```sql
USE test;
CREATE TABLE t1(a INT, b INT, c INT, INDEX ia(a));
CREATE TABLE t2(a INT, b INT, c INT, INDEX ia(a));
INSERT INTO t1 SELECT * FROM t2 WHERE a = 1;
SELECT @@LAST_PLAN_FROM_BINDING;
UPDATE /*+ INL_JOIN(t2) */ t1, t2 SET t1.a = 1 WHERE t1.b = t2.a;
SELECT @@LAST_PLAN_FROM_BINDING;
DELETE /*+ HASH_JOIN(t1) */ t1 FROM t1 JOIN t2 WHERE t1.b = t2.a;
SELECT @@LAST_PLAN_FROM_BINDING;
SELECT * FROM t1 WHERE t1.a IN (SELECT a FROM t2);
SELECT @@LAST_PLAN_FROM_BINDING;
```

--------------------------------

### Start a task with a specified source

Source: https://docs.pingcap.com/tidb/stable/dm-create-task

Example of starting a data migration task using `start-task` with a specific source and configuration file. The `-s` flag is optional and specifies the MySQL source.

```bash
start-task [ -s "mysql-replica-01"] ./task.yaml
```

--------------------------------

### Create a List Partitioned Table with a Default Partition

Source: https://docs.pingcap.com/tidb/stable/partitioned-table

Example of creating a new List partitioned table that includes a default partition from the start.

```sql
CREATE TABLE employees (
    id INT NOT NULL,
    hired DATE NOT NULL DEFAULT '1970-01-01',
    store_id INT
)
PARTITION BY LIST (store_id) (
    PARTITION pNorth VALUES IN (1, 2, 3, 4, 5),
    PARTITION pEast VALUES IN (6, 7, 8, 9, 10),
    PARTITION pWest VALUES IN (11, 12, 13, 14, 15),
    PARTITION pCentral VALUES IN (16, 17, 18, 19, 20),
    PARTITION pDefault DEFAULT
);
```

--------------------------------

### Operate TiUP components

Source: https://docs.pingcap.com/tidb/stable/tiup-component-management

Syntax for starting components and examples of using tags to manage working directories.

```shell
tiup [flags] <component>[:version] [args...]

Flags:
  -T, --tag string                     Specifies the tag for the component instance.
```

```shell
tiup tidb:v8.5.7
```

```shell
tiup --tag=experiment tikv
```

--------------------------------

### Start a task and display result

Source: https://docs.pingcap.com/tidb/stable/dm-create-task

Demonstrates how to start a data migration task and the expected JSON output indicating success. This output confirms the task has started and on which source and worker it is running.

```bash
start-task task.yaml
```

```json
{
    "result": true,
    "msg": "",
    "sources": [
        {
            "result": true,
            "msg": "",
            "source": "mysql-replica-01",
            "worker": "worker1"
        }
    ]
}
```

--------------------------------

### Create a Database, Select It, and Show Tables

Source: https://docs.pingcap.com/tidb/stable/sql-statement-use

Illustrates the process of creating a new database, selecting it as the current database using USE, and then verifying that it is empty by showing tables. This is a common workflow for setting up new schemas.

```sql
mysql> CREATE DATABASE newtest;
Query OK, 0 rows affected (0.10 sec)

mysql> USE newtest;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)
```

--------------------------------

### Display Help for a Specific TiUP Command

Source: https://docs.pingcap.com/tidb/stable/tiup-command-help

To view help for a specific TiUP command, append the command name after `tiup help`. For example, to get help for the `install` command, use `tiup help install`.

```shell
tiup help [command]
```

--------------------------------

### tiup install Command Syntax

Source: https://docs.pingcap.com/tidb/stable/tiup-command-install

Use this syntax to install one or more components, optionally specifying versions. If no version is provided, the latest stable version is installed.

```shell
tiup install <component1>[:version] [component2...N] [flags]
```

--------------------------------

### Install and Enable NTP Service on CentOS 7

Source: https://docs.pingcap.com/tidb/stable/check-before-deployment

These commands install the `ntp` and `ntpdate` packages, start the `ntpd` service, and enable it to start automatically on boot for CentOS 7 systems.

```bash
sudo yum install ntp ntpdate && \
sudo systemctl start ntpd.service && \
sudo systemctl enable ntpd.service
```

--------------------------------

### Setup Sample Data and Enable MPP Mode

Source: https://docs.pingcap.com/tidb/stable/explain-mpp

Prepare sample data and enable MPP mode for testing EXPLAIN statements. Ensure the table has a TiFlash replica.

```sql
CREATE TABLE t1 (id int, value int);
INSERT INTO t1 values(1,2),(2,3),(1,3);
ALTER TABLE t1 set tiflash replica 1;
ANALYZE TABLE t1;
SET tidb_allow_mpp = 1;
```

--------------------------------

### Start Log Backup Task Example

Source: https://docs.pingcap.com/tidb/stable/br-pitr-manual

An example of how to start a log backup task named 'pitr'. It specifies the PD address and the S3 storage location, including authentication details.

```shell
tiup br log start \
  --task-name=pitr \
  --pd="${PD_IP}:2379" \
  --storage='s3://backup-101/logbackup?access-key=${access-key}&secret-access-key=${secret-access-key}'
```

--------------------------------

### Start Log Backup Task Help

Source: https://docs.pingcap.com/tidb/stable/br-pitr-manual

Shows detailed help for the `start` subcommand, including available flags and their descriptions. Useful for understanding how to configure a new log backup task.

```shell
tiup br log start --help
```

--------------------------------

### Install and Start MySQL on macOS

Source: https://docs.pingcap.com/tidb/stable/quick-start-with-dm

Use Homebrew to install and manage the MySQL 8.0 service.

```shell
brew update
brew install mysql@8.0
```

```shell
brew link mysql@8.0 --force
```

```shell
brew services start mysql@8.0
```

```shell
mysql -uroot
```

--------------------------------

### Show Create Table Output Example

Source: https://docs.pingcap.com/tidb/stable/partitioned-table

This is an example output of the 'SHOW CREATE TABLE' command for a partitioned table, illustrating the partition definitions and comments.

```sql
*************************** 1. row ***************************
       Table: example
Create Table: CREATE TABLE `example` (
  `id` int NOT NULL,
  `data` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY HASH (`id`)
(
PARTITION `p0`,
 PARTITION `p1`,
 PARTITION `p2`,
 PARTITION `pExample4` COMMENT 'not p3, but pExample4 instead')
1 row in set (0.01 sec)
```

--------------------------------

### Replace substrings with a starting position constraint

Source: https://docs.pingcap.com/tidb/stable/string-functions

The REGEXP_REPLACE function allows specifying a starting position for the match. This example starts searching from the third character, preventing a match.

```sql
SELECT REGEXP_REPLACE('TooDB', 'o{2}', 'i',3);
```

--------------------------------

### Create a table with a prefix index

Source: https://docs.pingcap.com/tidb/stable/system-variables

Initial setup for demonstrating prefix index behavior.

```sql
CREATE TABLE t (a INT, b VARCHAR(10), c INT, INDEX idx_a_b(a, b(5)));
```

--------------------------------

### Install TiUP

Source: https://docs.pingcap.com/tidb/stable/deploy-tidb-lightning

Installs TiUP, a package manager for TiDB components. After installation, start a new terminal session or run `source ~/.bashrc` (or `source ~/.profile`) to add TiUP to your PATH.

```shell
curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
```

--------------------------------

### Example: Creating a Table

Source: https://docs.pingcap.com/tidb/stable/sql-statement-drop-stats

This snippet demonstrates the SQL command to create a sample table used in subsequent examples.

```sql
CREATE TABLE t(a INT);
```

--------------------------------

### Create user with hostname

Source: https://docs.pingcap.com/tidb/stable/system-variables

Example of creating a user with a hostname, which may be affected when skip_name_resolve is enabled.

```sql
CREATE USER 'appuser'@'apphost' IDENTIFIED BY 'app-password';
```

--------------------------------

### Example: Starting a Transaction

Source: https://docs.pingcap.com/tidb/stable/sql-statement-savepoint

Begins a transaction block to demonstrate savepoint functionality.

```sql
BEGIN;
```

--------------------------------

### Example Workflow: Create Database, Use, and Create Table

Source: https://docs.pingcap.com/tidb/stable/sql-statement-create-database

This example demonstrates a common workflow: creating a database, switching to it using the USE statement, and then creating a table within that database.

```sql
mysql> CREATE DATABASE mynewdatabase;
Query OK, 0 rows affected (0.09 sec)

mysql> USE mynewdatabase;
Database changed
mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW TABLES;
+-------------------------+
| Tables_in_mynewdatabase |
+-------------------------+
| t1                      |
+-------------------------+
1 row in set (0.00 sec)
```

--------------------------------

### Configure tidb_skip_isolation_level_check

Source: https://docs.pingcap.com/tidb/stable/system-variables

Example showing how to enable the variable to bypass errors when setting unsupported isolation levels.

```sql
tidb> set tx_isolation='serializable';
ERROR 8048 (HY000): The isolation level 'serializable' is not supported. Set tidb_skip_isolation_level_check=1 to skip this error
tidb> set tidb_skip_isolation_level_check=1;
Query OK, 0 rows affected (0.00 sec)

tidb> set tx_isolation='serializable';
Query OK, 0 rows affected, 1 warning (0.00 sec)
```

--------------------------------

### Start a replication task

Source: https://docs.pingcap.com/tidb/stable/dm-open-api

Starts a specified replication task. This is an asynchronous operation. Use the 'get the information of a replication task' endpoint to check the task's status.

```APIDOC
## PUT /api/v1/tasks/task-1

### Description
This endpoint is used to create or update a replication task with specified configurations. It allows for detailed setup of migration rules, source and target configurations, and filtering.

### Method
PUT

### Endpoint
`/api/v1/tasks/{task-name}`

### Request Body
- **task** (object) - Required - The configuration object for the replication task.
  - **name** (string) - Required - The name of the task.
  - **task_mode** (string) - Required - The mode of the task (e.g., "all").
  - **shard_mode** (string) - Required - The sharding mode (e.g., "pessimistic").
  - **meta_schema** (string) - Required - The schema for metadata storage.
  - **enhance_online_schema_change** (boolean) - Required - Whether to enhance online schema change.
  - **on_duplicate** (string) - Required - Action to take on duplicate data (e.g., "overwrite").
  - **target_config** (object) - Required - Configuration for the target database.
    - **host** (string) - Required - Target database host.
    - **port** (integer) - Required - Target database port.
    - **user** (string) - Required - Target database user.
    - **password** (string) - Required - Target database password.
    - **security** (object) - Optional - Security settings for the target connection.
      - **ssl_ca_content** (string) - Optional - SSL CA certificate content.
      - **ssl_cert_content** (string) - Optional - SSL certificate content.
      - **ssl_key_content** (string) - Optional - SSL key content.
      - **cert_allowed_cn** (array of strings) - Optional - Allowed common names for the certificate.
  - **binlog_filter_rule** (object) - Optional - Rules for filtering binlog events.
    - **rule-name** (object) - Key-value pairs where keys are rule names and values are rule definitions.
      - **ignore_event** (array of strings) - Optional - Events to ignore.
      - **ignore_sql** (array of strings) - Optional - SQL statements to ignore.
  - **table_migrate_rule** (array of objects) - Optional - Rules for migrating specific tables.
    - **source** (object) - Required - Source table definition.
      - **source_name** (string) - Optional - Name of the source.
      - **schema** (string) - Required - Source schema pattern.
      - **table** (string) - Required - Source table pattern.
    - **target** (object) - Required - Target table definition.
      - **schema** (string) - Required - Target schema name.
      - **table** (string) - Required - Target table name.
    - **binlog_filter_rule** (array of strings) - Optional - References to binlog filter rules.
  - **source_config** (object) - Required - Configuration for the data source.
    - **full_migrate_conf** (object) - Optional - Configuration for full data migration.
      - **export_threads** (integer) - Optional - Number of export threads.
      - **import_threads** (integer) - Optional - Number of import threads.
      - **data_dir** (string) - Optional - Directory for exported data.
      - **consistency** (string) - Optional - Consistency level (e.g., "auto").
      - **import_mode** (string) - Optional - Import mode (e.g., "physical").
      - **sorting_dir** (string) - Optional - Directory for sorting data.
      - **disk_quota** (string) - Optional - Disk quota for data export.
      - **checksum** (string) - Optional - Checksum requirement (e.g., "required").
      - **analyze** (string) - Optional - Whether to analyze tables (e.g., "optional").
      - **range_concurrency** (integer) - Optional - Concurrency for range operations.
      - **compress_kv_pairs** (string) - Optional - Compression settings for KV pairs.
      - **pd_addr** (string) - Optional - PD server address.
      - **on_duplicate_logical** (string) - Optional - Action on duplicate logical data.
      - **on_duplicate_physical** (string) - Optional - Action on duplicate physical data.
    - **incr_migrate_conf** (object) - Optional - Configuration for incremental data migration.
      - **repl_threads** (integer) - Optional - Number of replication threads.
      - **repl_batch** (integer) - Optional - Batch size for replication.
    - **source_conf** (array of objects) - Required - Configuration for each source instance.
      - **source_name** (string) - Required - Name of the source instance.
      - **binlog_name** (string) - Optional - Binlog file name.
      - **binlog_pos** (integer) - Optional - Binlog position.
      - **binlog_gtid** (string) - Optional - Binlog GTID.

### Request Example
```json
{
  "task": {
    "name": "task-1",
    "task_mode": "all",
    "shard_mode": "pessimistic",
    "meta_schema": "dm-meta",
    "enhance_online_schema_change": true,
    "on_duplicate": "overwrite",
    "target_config": {
      "host": "127.0.0.1",
      "port": 3306,
      "user": "root",
      "password": "123456",
      "security": {
        "ssl_ca_content": "",
        "ssl_cert_content": "",
        "ssl_key_content": "",
        "cert_allowed_cn": [
          "string"
        ]
      }
    },
    "binlog_filter_rule": {
      "rule-1": {
        "ignore_event": [
          "all dml"
        ],
        "ignore_sql": [
          "^Drop"
        ]
      },
      "rule-2": {
        "ignore_event": [
          "all dml"
        ],
        "ignore_sql": [
          "^Drop"
        ]
      },
      "rule-3": {
        "ignore_event": [
          "all dml"
        ],
        "ignore_sql": [
          "^Drop"
        ]
      }
    },
    "table_migrate_rule": [
      {
        "source": {
          "source_name": "source-name",
          "schema": "db-*",
          "table": "tb-*"
        },
        "target": {
          "schema": "db1",
          "table": "tb1"
        },
        "binlog_filter_rule": [
          "rule-1",
          "rule-2",
          "rule-3"
        ]
      }
    ],
    "source_config": {
      "full_migrate_conf": {
        "export_threads": 4,
        "import_threads": 16,
        "data_dir": "./exported_data",
        "consistency": "auto",
        "import_mode": "physical",
        "sorting_dir": "./sort_dir",
        "disk_quota": "80G",
        "checksum": "required",
        "analyze": "optional",
        "range_concurrency": 0,
        "compress-kv-pairs": "",
        "pd_addr": "",
        "on_duplicate_logical": "error",
        "on_duplicate_physical": "none"
      },
      "incr_migrate_conf": {
        "repl_threads": 16,
        "repl_batch": 100
      },
      "source_conf": [
        {
          "source_name": "mysql-replica-01",
          "binlog_name": "binlog.000001",
          "binlog_pos": 4,
          "binlog_gtid": "03fc0263-28c7-11e7-a653-6c0b84d59f30:1-7041423,05474d3c-28c7-11e7-8352-203db246dd3d:1-170"
        }
      ]
    }
  }
}
```

### Response
#### Success Response (204)
No content is returned on success.

#### Error Response
- **400 Bad Request**: Invalid request payload or parameters.
- **404 Not Found**: Task not found.
- **500 Internal Server Error**: Server encountered an error.
```

```APIDOC
## POST /api/v1/tasks/{task-name}/start

### Description
Starts a specified replication task. This is an asynchronous operation. Use the 'get the information of a replication task' endpoint to check the task's status.

### Method
POST

### Endpoint
`/api/v1/tasks/{task-name}/start`

### Parameters
#### Path Parameters
- **task-name** (string) - Required - The name of the task to start.

### Request Example
```shell
curl -X 'POST' \
  'http://127.0.0.1:8261/api/v1/tasks/task-1/start' \
  -H 'accept: */*'
```

### Response
#### Success Response (204)
No content is returned on success.

#### Error Response
- **404 Not Found**: Task not found.
- **500 Internal Server Error**: Server encountered an error.
```

--------------------------------

### Create Table for INTO OUTFILE Examples

Source: https://docs.pingcap.com/tidb/stable/sql-statement-select

Sets up a sample table with integer, varchar, and decimal columns for demonstrating the SELECT ... INTO OUTFILE statement.

```sql
mysql> CREATE TABLE t (a INT, b VARCHAR(10), c DECIMAL(10,2));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO t VALUES (1, 'a', 1.1), (2, 'b', 2.2), (3, 'c', 3.3);
Query OK, 3 rows affected (0.01 sec)
```

--------------------------------

### Convert Subquery to Join and Aggregation

Source: https://docs.pingcap.com/tidb/stable/system-variables

Examples demonstrating the transformation of subqueries into joins and aggregations when the optimization rule is enabled.

```sql
select * from t where t.a in (select aa from t1);
```

```sql
select t.* from t, (select aa from t1 group by aa) tmp_t where t.a = tmp_t.aa;
```

```sql
select t.* from t, t1 where t.a=t1.aa;
```

--------------------------------

### Get First Sequence Value with Custom Start

Source: https://docs.pingcap.com/tidb/stable/sql-statement-create-sequence

Shows that the first value returned by NEXTVAL() for a custom sequence is its defined START parameter. This confirms the sequence begins as configured.

```sql
SELECT NEXTVAL(seq2);
```

```sql
+---------------+
| NEXTVAL(seq2) |
+---------------+
|             3 |
+---------------+
1 row in set (0.00 sec)
```

--------------------------------

### Example Table Schema

Source: https://docs.pingcap.com/tidb/stable/handle-failed-ddl-statements

This is an example output of the `SHOW CREATE TABLE` command, illustrating a basic table structure.

```sql
+-------+-----------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------+
| tb    | CREATE TABLE `shard_table` (
  `id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+-------+-----------------------------------------------------------------------------------------------------------+
```

--------------------------------

### Create Table, Insert Data, and Explain Query

Source: https://docs.pingcap.com/tidb/stable/explain-overview

This snippet demonstrates creating a table, inserting sample data, and then using the `EXPLAIN` statement to view the execution plan for a simple select query.

```sql
CREATE TABLE t (id INT NOT NULL PRIMARY KEY auto_increment, a INT NOT NULL, pad1 VARCHAR(255), INDEX(a));
INSERT INTO t VALUES (1, 1, 'aaa'),(2,2, 'bbb');
EXPLAIN SELECT * FROM t WHERE a = 1;
```

--------------------------------

### Start Flink SQL Client

Source: https://docs.pingcap.com/tidb/stable/replicate-data-to-kafka

Launch the Flink SQL client from the installation directory.

```shell
[root@flink flink-1.15.0]# ./bin/sql-client.sh
```

--------------------------------

### Setup User and Resource Groups

Source: https://docs.pingcap.com/tidb/stable/tidb-functions

Create a user and multiple resource groups, then assign the user to a specific group.

```sql
CREATE USER 'user1';
CREATE RESOURCE GROUP rg1 RU_PER_SEC = 1000;
CREATE RESOURCE GROUP rg2 RU_PER_SEC = 2000;
ALTER USER 'user1' RESOURCE GROUP `rg1`;
```

--------------------------------

### Configure Pessimistic Constraint Check Behavior

Source: https://docs.pingcap.com/tidb/stable/system-variables

Examples demonstrating the impact of setting tidb_constraint_check_in_place_pessimistic to OFF versus ON during pessimistic transactions.

```sql
set @@tidb_constraint_check_in_place_pessimistic=OFF;
create table t (i int key);
insert into t values (1);
begin pessimistic;
insert into t values (1);
```

```sql
tidb> commit; -- Check only when a transaction is committed.
```

```sql
set @@tidb_constraint_check_in_place_pessimistic=ON;
begin pessimistic;
insert into t values (1);
```

--------------------------------

### Create a New User

Source: https://docs.pingcap.com/tidb/stable/sql-statement-set-password

This example demonstrates how to create a new user with an initial password. This is often a prerequisite before setting or changing a password for a specific user.

```sql
mysql> CREATE USER 'newuser' IDENTIFIED BY 'test';
Query OK, 1 row affected (0.00 sec)
```

--------------------------------

### Find first occurrence starting from a specific position

Source: https://docs.pingcap.com/tidb/stable/string-functions

This example demonstrates using the third argument of REGEXP_INSTR() to specify a different starting position for the search. Here, the search for 'a' begins from the second character of the string 'abcabc'.

```sql
SELECT REGEXP_INSTR('abcabc','a',2);
```

--------------------------------

### Prepare Database and Table Schema

Source: https://docs.pingcap.com/tidb/stable/tidb-lightning-error-resolution

Creates the schema and table for the example data import. Ensure you are in the 'example' directory before running.

```shell
mkdir example && cd example

echo 'CREATE SCHEMA;' > example-schema-create.sql
echo 'CREATE TABLE t(a TINYINT PRIMARY KEY, b VARCHAR(12) NOT NULL UNIQUE);' > example.t-schema.sql
```

--------------------------------

### Verify Playground Output

Source: https://docs.pingcap.com/tidb/stable/quick-start-with-dm

Example output showing connection details for the started TiDB and DM components.

```text
TiDB Playground Cluster is started, enjoy!

Connect TiDB:    mysql --host 127.0.0.1 --port 4000 -u root
Connect DM:      tiup dmctl --master-addr 127.0.0.1:8261
TiDB Dashboard:  http://127.0.0.1:2379/dashboard
```

--------------------------------

### Create Sample Data

Source: https://docs.pingcap.com/tidb/stable/quick-start-with-dm

Initialize a test database and table with sample records.

```sql
CREATE DATABASE hello;
USE hello;

CREATE TABLE hello_tidb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO hello_tidb (name) VALUES ('Hello World');

SELECT * FROM hello_tidb;
```

--------------------------------

### TiKV Configuration Example with JSON Output

Source: https://docs.pingcap.com/tidb/stable/command-line-flags-for-tikv-configuration

This example shows how to use the `--config-info` flag to list available configuration parameters in JSON format. It includes parameters from a configuration file and default values.

```json
{
"Component": "TiKV Server",
"Version": "6.2.0",
"Parameters": [
    {
    "Name": "log-level",
    "DefaultValue": "info",
    "ValueInFile": "warn"
    },
    {
    "Name": "log-file",
    "DefaultValue": ""
    },
    ...
]
}
```

--------------------------------

### Get TiCDC Node Status

Source: https://docs.pingcap.com/tidb/stable/ticdc-open-api-v2

Example request and response for retrieving the status of a specific TiCDC node.

```shell
curl -X GET http://127.0.0.1:8300/api/v2/status
```

```json
{
  "version": "v8.5.7",
  "git_hash": "10413bded1bdb2850aa6d7b94eb375102e9c44dc",
  "id": "d2912e63-3349-447c-90ba-72a4e04b5e9e",
  "pid": 1447,
  "is_owner": true,
  "liveness": 0
}
```

--------------------------------

### Get Data Source Response

Source: https://docs.pingcap.com/tidb/stable/dm-open-api

Example JSON response when retrieving a data source with its status information.

```json
{
  "source_name": "mysql-01",
  "host": "127.0.0.1",
  "port": 3306,
  "user": "root",
  "password": "123456",
  "enable_gtid": false,
  "enable": false,
  "flavor": "mysql",
  "task_name_list": [
    "task1"
  ],
  "security": {
    "ssl_ca_content": "",
    "ssl_cert_content": "",
    "ssl_key_content": "",
    "cert_allowed_cn": [
      "string"
    ]
  },
  "purge": {
    "interval": 3600,
    "expires": 0,
    "remain_space": 15
  },
  "status_list": [
    {
      "source_name": "mysql-replica-01",
      "worker_name": "worker-1",
      "relay_status": {
        "master_binlog": "(mysql-bin.000001, 1979)",
        "master_binlog_gtid": "e9a1fc22-ec08-11e9-b2ac-0242ac110003:1-7849",
        "relay_dir": "./sub_dir",
        "relay_binlog_gtid": "e9a1fc22-ec08-11e9-b2ac-0242ac110003:1-7849",
        "relay_catch_up_master": true,
        "stage": "Running"
      },
      "error_msg": "string"
    }
  ],
  "relay_config": {
    "enable_relay": true,
    "relay_binlog_name": "mysql-bin.000002",
    "relay_binlog_gtid": "e9a1fc22-ec08-11e9-b2ac-0242ac110003:1-7849",
    "relay_dir": "./relay_log"
  }
}
```

--------------------------------

### Example: Initial Cluster Configuration

Source: https://docs.pingcap.com/tidb/stable/command-line-flags-for-pd-configuration

Defines the initial cluster for bootstrapping PD. It maps member names to their peer URLs.

```bash
--initial-cluster "pd=http://192.168.100.113:2380"
```

--------------------------------

### Example: Enabling TLS with Client Certificate and Key

Source: https://docs.pingcap.com/tidb/stable/command-line-flags-for-pd-configuration

Configures TLS by providing the paths to the X509 certificate and its corresponding private key in PEM format.

```bash
--cert "/path/to/pd-cert.pem" --key "/path/to/pd-key.pem"
```

--------------------------------

### TiDB Write Conflict Log Example

Source: https://docs.pingcap.com/tidb/stable/troubleshoot-write-conflicts

Example of a TiDB log entry indicating a write conflict. This log provides transaction start and conflict timestamps, as well as the conflicting key and primary key information.

```log
[2020/05/12 15:17:01.568 +08:00] [WARN] [session.go:446] ["commit failed"] [conn=3] ["finished txn"="Txn{state=invalid}"] [error="[kv:9007]Write conflict, txnStartTS=416617006551793665, conflictStartTS=416617018650001409, conflictCommitTS=416617023093080065, key={tableID=47, indexID=1, indexValues={string, }} primary={tableID=47, indexID=1, indexValues={string, }} [try again later]"]
```

--------------------------------

### Create Table Example

Source: https://docs.pingcap.com/tidb/stable/sql-statement-insert

Demonstrates the creation of a simple integer table.

```sql
CREATE TABLE t1 (a INT);

```

```sql
CREATE TABLE t2 LIKE t1;

```

--------------------------------

### Return position after the match

Source: https://docs.pingcap.com/tidb/stable/string-functions

This example shows how to use the fifth argument of REGEXP_INSTR() to return the position immediately following the matched substring, instead of the starting position of the match. The search is for the first 'a' starting from position 1.

```sql
SELECT REGEXP_INSTR('abcabc','a',1,1,1);
```

--------------------------------

### Create Table, Policy, and Show Placement

Source: https://docs.pingcap.com/tidb/stable/information-schema-placement-policies

Demonstrates creating a table, a placement policy, assigning the policy to the table, and then viewing all placement information using `SHOW PLACEMENT` and querying `information_schema.placement_policies`.

```sql
CREATE TABLE t1 (a INT);
CREATE PLACEMENT POLICY p1 primary_region="us-east-1" regions="us-east-1";
CREATE TABLE t3 (a INT) PLACEMENT POLICY=p1;
SHOW PLACEMENT; -- Shows all information, including table t3.
SELECT * FROM information_schema.placement_policies; -- Only shows placement policies, excluding t3.
```

```text
Query OK, 0 rows affected (0.09 sec)

Query OK, 0 rows affected (0.11 sec)

Query OK, 0 rows affected (0.08 sec)

+---------------+------------------------------------------------+
| Target        | Placement                                      |
+---------------+------------------------------------------------+
| POLICY p1     | PRIMARY_REGION="us-east-1" REGIONS="us-east-1" |
| TABLE test.t3 | PRIMARY_REGION="us-east-1" REGIONS="us-east-1" |
+---------------+------------------------------------------------+
2 rows in set (0.00 sec)

+-----------+--------------+-------------+----------------+-----------+-------------+--------------------+----------------------+---------------------+----------+-----------+----------+
| POLICY_ID | CATALOG_NAME | POLICY_NAME | PRIMARY_REGION | REGIONS   | CONSTRAINTS | LEADER_CONSTRAINTS | FOLLOWER_CONSTRAINTS | LEARNER_CONSTRAINTS | SCHEDULE | FOLLOWERS | LEARNERS |
+-----------+--------------+-------------+----------------+-----------+-------------+--------------------+----------------------+---------------------+----------+-----------+----------+
| 1         | def          | p1          | us-east-1      | us-east-1 |             |                    |                      |                     |          | 2         | 0        |
+-----------+--------------+-------------+----------------+-----------+-------------+--------------------+----------------------+---------------------+----------+-----------+----------+
1 rows in set (0.00 sec)
```

--------------------------------

### Example ntpd Service Status Output

Source: https://docs.pingcap.com/tidb/stable/check-before-deployment

This output indicates that the `ntpd` service is loaded, enabled to start, and currently active.

```text
ntpd.service - Network Time Service
Loaded: loaded (/usr/lib/systemd/system/ntpd.service; disabled; vendor preset: disabled)
Active: active (running) since 一 2017-12-18 13:13:19 CST; 3s ago
```

--------------------------------

### INSERT Statement with Index Hint

Source: https://docs.pingcap.com/tidb/stable/sql-statement-create-binding

Example of an INSERT statement using `use_index` and `no_order_index` hints to guide the query optimizer.

```sql
INSERT INTO `test`.`t1` SELECT /*+ use_index(@`sel_1` `test`.`t2` `ia`) no_order_index(@`sel_1` `test`.`t2` `ia`)*/ * FROM `test`.`t2` WHERE `a` = 1
```

--------------------------------

### Sysbench Configuration Example

Source: https://docs.pingcap.com/tidb/stable/benchmark-tidb-using-sysbench

This is an example Sysbench configuration file. Adjust parameters like `TIDB_HOST` and `threads` based on your specific testing environment and needs. It's recommended to set `threads` to 8 or 16 when importing data.

```text
mysql-host={TIDB_HOST}
mysql-port=4000
mysql-user=root
mysql-password=password
mysql-db=sbtest
time=600
threads={8, 16, 32, 64, 128, 256}
report-interval=10
db-driver=mysql
```

--------------------------------

### Get Password Strength with Numbers

Source: https://docs.pingcap.com/tidb/stable/encryption-and-compression-functions

Assesses the strength of 'Abcdefghi123'. This example illustrates the impact of including numbers on the password strength score.

```sql
SELECT VALIDATE_PASSWORD_STRENGTH('Abcdefghi123');
```

--------------------------------

### Create a table

Source: https://docs.pingcap.com/tidb/stable/sql-statement-add-column

This snippet demonstrates the creation of a sample table used in subsequent examples.

```sql
mysql> CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);
Query OK, 0 rows affected (0.11 sec)
```

--------------------------------

### Example: Multi-Node Initial Cluster Configuration

Source: https://docs.pingcap.com/tidb/stable/command-line-flags-for-pd-configuration

Specifies the initial cluster configuration for bootstrapping a multi-node PD cluster, listing each member's name and its advertise peer URL.

```bash
pd1=http://192.168.100.113:2380, pd2=http://192.168.100.114:2380, pd3=192.168.100.115:2380
```

--------------------------------

### Initialize and configure a temporary mirror

Source: https://docs.pingcap.com/tidb/stable/tiup-mirror

Sets up a temporary mirror environment and grants current user ownership.

```bash
$ tiup mirror init /tmp/m
$ tiup mirror genkey
$ tiup mirror set /tmp/m
$ tiup mirror grant $USER
```

--------------------------------

### Get default kernel version

Source: https://docs.pingcap.com/tidb/stable/check-before-deployment

Use the 'grubby' command to retrieve the default kernel version. Ensure the 'grubby' package is installed before execution.

```bash
grubby --default-kernel
```

--------------------------------

### Prepare source data for MySQL instances

Source: https://docs.pingcap.com/tidb/stable/quick-start-create-task

SQL commands to create databases and tables with sample data for migration testing.

```sql
drop database if exists `sharding1`;
create database `sharding1`;
use `sharding1`;
create table t1 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
create table t2 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
insert into t1 (id, uid, name) values (1, 10001, 'Gabriel García Márquez'), (2 ,10002, 'Cien años de soledad');
insert into t2 (id, uid, name) values (3,20001, 'José Arcadio Buendía'), (4,20002, 'Úrsula Iguarán'), (5,20003, 'José Arcadio');
```

```sql
drop database if exists `sharding2`;
create database `sharding2`;
use `sharding2`;
create table t2 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
create table t3 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
insert into t2 (id, uid, name, info) values (6, 40000, 'Remedios Moscote', '{}');
insert into t3 (id, uid, name, info) values (7, 30001, 'Aureliano José', '{}'), (8, 30002, 'Santa Sofía de la Piedad', '{}'), (9, 30003, '17 Aurelianos', NULL);
```

--------------------------------

### List Available TiUP Components

Source: https://docs.pingcap.com/tidb/stable/migration-tools

Lists all available components that can be installed using TiUP. This helps in identifying the tools needed for your TiDB setup.

```bash
tiup list
```

--------------------------------

### Find first occurrence of a character

Source: https://docs.pingcap.com/tidb/stable/string-functions

This example shows how to find the first occurrence of the character 'a' in the string 'abcabc'. By default, the search starts from the beginning of the string.

```sql
SELECT REGEXP_INSTR('abcabc','a');
```

--------------------------------

### Create Partitioned Table and Explain Query in Static Mode

Source: https://docs.pingcap.com/tidb/stable/partitioned-table

Demonstrates creating a partitioned table and then using 'EXPLAIN' to show how TiDB would process a query in 'static' mode, typically involving multiple operators and a 'Union' for merging results from different partitions.

```sql
mysql> create table t1(id int, age int, key(id)) partition by range(id) (
        partition p0 values less than (100),
        partition p1 values less than (200),
        partition p2 values less than (300),
        partition p3 values less than (400));
Query OK, 0 rows affected (0.01 sec)

mysql> explain select * from t1 where id < 150;
```

--------------------------------

### LIKE for Numeric String Mismatch

Source: https://docs.pingcap.com/tidb/stable/string-functions

Shows a failed numeric string match using LIKE. This example returns 0 as '10000' does not start with '12'.

```sql
SELECT 10000 LIKE '12%' AS result;
```

--------------------------------

### Create Databases and Tables for Cross-Database Binding Example

Source: https://docs.pingcap.com/tidb/stable/sql-plan-management

Sets up the necessary database and table structures required for demonstrating cross-database binding functionality.

```sql
CREATE DATABASE db1;
CREATE TABLE db1.t1 (a INT, KEY(a));
CREATE TABLE db1.t2 (a INT, KEY(a));
CREATE DATABASE db2;
CREATE TABLE db2.t1 (a INT, KEY(a));
CREATE TABLE db2.t2 (a INT, KEY(a));
```

--------------------------------

### Create a RANGE Partitioned Table

Source: https://docs.pingcap.com/tidb/stable/partitioned-table

Example of creating a table with RANGE partitioning based on a region code. This setup is used to illustrate partition pruning.

```sql
CREATE TABLE t1 (
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50) NOT NULL,
    region_code TINYINT UNSIGNED NOT NULL,
    dob DATE NOT NULL
)

PARTITION BY RANGE( region_code ) (
    PARTITION p0 VALUES LESS THAN (64),
    PARTITION p1 VALUES LESS THAN (128),
    PARTITION p2 VALUES LESS THAN (192),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);
```

--------------------------------

### Create Sample Table and Insert Data

Source: https://docs.pingcap.com/tidb/stable/explain-indexes

Sets up a sample table 't1' with an auto-incrementing primary key, an integer column with an index, and a large binary column. It then populates the table with a significant amount of random data to facilitate index usage examples.

```sql
CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 intkey INT NOT NULL,
 pad1 VARBINARY(1024),
 INDEX (intkey)
);

INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1024), RANDOM_BYTES(1024) FROM dual;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
INSERT INTO t1 SELECT NULL, FLOOR(RAND()*1024), RANDOM_BYTES(1024) FROM t1 a JOIN t1 b JOIN t1 c LIMIT 10000;
```

--------------------------------

### Example Output of Blocking Transaction Query

Source: https://docs.pingcap.com/tidb/stable/troubleshoot-lock-conflicts

This is an example output from the query to identify blocking transactions. It shows details of the blocking transaction, including its ID, start time, current SQL, state, and associated SQL digests which can be decoded into normalized SQL statements using `TIDB_DECODE_SQL_DIGESTS`.

```sql
*************************** 1. row ***************************
                    key: 74800000000000004D5F728000000000000001
               INSTANCE: 127.0.0.1:10080
                     ID: 426832040186609668
             START_TIME: 2021-08-06 07:30:16.581000
     CURRENT_SQL_DIGEST: 06da614b93e62713bd282d4685fc5b88d688337f36e88fe55871726ce0eb80d7
CURRENT_SQL_DIGEST_TEXT: update `t` set `v` = `v` + ? where `id` = ? ;
                  STATE: LockWaiting
     WAITING_START_TIME: 2021-08-06 07:30:16.592763
        MEM_BUFFER_KEYS: 1
       MEM_BUFFER_BYTES: 19
             SESSION_ID: 113
                   USER: root
                     DB: test
        ALL_SQL_DIGESTS: ["0fdc781f19da1c6078c9de7eadef8a307889c001e05f107847bee4cfc8f3cdf3","a4e28cc182bdd18288e2a34180499b9404cd0ba07e3cc34b6b3be7b7c2de7fe9","06da614b93e62713bd282d4685fc5b88d688337f36e88fe55871726ce0eb80d7"]
                   sqls: ["begin ;","select * from `t` where `id` = ? for update ;","update `t` set `v` = `v` + ? where `id` = ? ;"]
1 row in set (0.01 sec)
```

--------------------------------

### Create Global Bindings

Source: https://docs.pingcap.com/tidb/stable/sql-statement-drop-binding

Sets up sample tables and creates global SQL bindings for different query patterns.

```sql
CREATE TABLE t1(a INT, b INT, c INT, INDEX ia(a));
CREATE TABLE t2(a INT, b INT, c INT, INDEX ia(a));
CREATE GLOBAL BINDING FOR SELECT * FROM t1 WHERE a > 1 USING SELECT * FROM t1 USE INDEX (ia) WHERE a > 1;
CREATE GLOBAL BINDING FOR SELECT * FROM t2 WHERE a < 1 USING SELECT * FROM t2 USE INDEX (ia) WHERE a < 1;
CREATE GLOBAL BINDING FOR SELECT * FROM t1 JOIN t2 ON t1.b = t2.a USING SELECT /*+ HASH_JOIN(t1) */ * FROM t1 JOIN t2 ON t1.b = t2.a;
SHOW GLOBAL BINDINGS;
```

--------------------------------

### Configure Periodic Full Compaction Start Times

Source: https://docs.pingcap.com/tidb/stable/tikv-configuration-file

Examples of setting the schedule for periodic full compaction using local time or specific UTC offsets.

```toml
periodic-full-compact-start-times = ["03:00", "23:00"]
```

```toml
periodic-full-compact-start-times = ["03:00 +0000", "23:00 +0000"]
```

```toml
periodic-full-compact-start-times = ["03:00 +0800", "23:00 +0800"]
```

--------------------------------

### Start multiple component instances

Source: https://docs.pingcap.com/tidb/stable/tiup-playground

Deploys multiple instances for TiDB, TiKV, and PD components simultaneously.

```shell
tiup playground --db 3 --pd 3 --kv 3
```

--------------------------------

### Create Table and Insert Data

Source: https://docs.pingcap.com/tidb/stable/explain-aggregation

Sets up a table and populates it with sample data for aggregation examples.

```sql
CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY, col1 INT NOT NULL);
INSERT INTO t2 VALUES (1, 9),(2, 3),(3,1),(4,8),(6,3);
```

--------------------------------

### LIKE for Numeric String Matching

Source: https://docs.pingcap.com/tidb/stable/string-functions

Demonstrates using the LIKE operator to match patterns in numeric strings. This example returns 1 as '10' starts with '1'.

```sql
SELECT 10 LIKE '1%' AS result;
```

--------------------------------

### Show Plugins Matching a Pattern

Source: https://docs.pingcap.com/tidb/stable/sql-statement-show-plugins

Filter the list of installed plugins using the LIKE clause to find plugins whose names match a specific pattern, such as starting with 'a'.

```sql
SHOW PLUGINS LIKE 'a%';
```

```text
+-------+--------------+-------+-----------------------------+---------+---------+
| Name  | Status       | Type  | Library                     | License | Version |
+-------+--------------+-------+-----------------------------+---------+---------+
| audit | Ready-enable | Audit | /tmp/tidb/plugin/audit-1.so |         | 1       |
+-------+--------------+-------+-----------------------------+---------+---------+
1 row in set (0.000 sec)
```

--------------------------------

### Create Placement Policy and Table, then Show Placement

Source: https://docs.pingcap.com/tidb/stable/sql-statement-show-placement

Demonstrates the creation of a placement policy and a table using that policy, followed by a SHOW PLACEMENT query to view the configurations.

```sql
CREATE PLACEMENT POLICY p1 PRIMARY_REGION="us-east-1" REGIONS="us-east-1,us-west-1" FOLLOWERS=4;
CREATE TABLE t1 (a INT) PLACEMENT POLICY=p1;
SHOW PLACEMENT;
```

--------------------------------

### TiKV Log Entry for Locks

Source: https://docs.pingcap.com/tidb/stable/troubleshoot-stale-read

Example TiKV log entry showing keys associated with locks in a region. This helps identify the start timestamp of problematic transactions.

```log
[2023/07/17 21:16:44.257 +08:00] [INFO] [resolver.rs:213] ["locks with the minimum start_ts in resolver"] [keys="[74800000000000006A5F7280000000000405F6, ... , 74800000000000006A5F72800000000000EFF6, 74800000000000006A5F7280000000000721D9, 74800000000000006A5F72800000000002F691]"] [start_ts=442918429687808001] [region_id=3121]
```
Tokens:
Raw