Skip to content

Latest commit

 

History

History
714 lines (569 loc) · 21.4 KB

tidb_http_api.md

File metadata and controls

714 lines (569 loc) · 21.4 KB
Raw

TiDB HTTP API

TiDBIP is the ip of the TiDB server. 10080 is the default status port, and you can edit it in tidb.toml when starting the TiDB server.

  1. Get the current status of TiDB, including the connections, version and git_hash

    curl http://{TiDBIP}:10080/status
    $curl http://127.0.0.1:10080/status
{
    "connections": 0,
    "git_hash": "f572e33854e1c0f942f031e9656d0004f99995c6",
    "version": "5.7.25-TiDB-v2.1.0-rc.3-355-gf572e3385-dirty",
    "status":{
      "init_stats_percentage":100
    }
}

  2. Get all metrics of TiDB

    curl http://{TiDBIP}:10080/metrics

  3. Get the metadata of all regions

    curl http://{TiDBIP}:10080/regions/meta
    $curl http://127.0.0.1:10080/regions/meta
[
    {
        "leader": {
            "id": 5,
            "store_id": 1
        },
        "peers": [
            {
                "id": 5,
                "store_id": 1
            }
        ],
        "region_epoch": {
            "conf_ver": 1,
            "version": 2
        },
        "region_id": 4
    }
]

  4. Get the table/index of hot regions

    curl http://{TiDBIP}:10080/regions/hot
    $curl http://127.0.0.1:10080/regions/hot
{
  "read": [

  ],
  "write": [
    {
      "db_name": "sbtest1",
      "table_name": "sbtest13",
      "index_name": "",
      "flow_bytes": 220718,
      "max_hot_degree": 12,
      "region_count": 1
    }
  ]
}

  5. Get the information of a specific region by ID

    curl http://{TiDBIP}:10080/regions/{regionID}
    $curl http://127.0.0.1:10080/regions/4001
{
    "end_key": "dIAAAAAAAAEk",
    "frames": [
        {
            "db_name": "test",
            "is_record": true,
            "table_id": 286,
            "table_name": "t1"
        }
    ],
    "region_id": 4001,
    "start_key": "dIAAAAAAAAEe"
}

  6. Get regions Information from db.table

    curl http://{TiDBIP}:10080/tables/{db}/{table}/regions
    $curl http://127.0.0.1:10080/tables/test/t1/regions
{
    "id": 286,
    "indices": [],
    "name": "t1",
    "record_regions": [
        {
            "leader": {
                "id": 4002,
                "store_id": 1
            },
            "peers": [
                {
                    "id": 4002,
                    "store_id": 1
                }
            ],
            "region_epoch": {
                "conf_ver": 1,
                "version": 83
            },
            "region_id": 4001
        }
    ]
}

  7. Get schema Information about all db

    curl http://{TiDBIP}:10080/schema
    $curl http://127.0.0.1:10080/schema
[
    {
        "charset": "utf8mb4",
        "collate": "utf8mb4_bin",
        "db_name": {
            "L": "test",
            "O": "test"
        },
        "id": 266,
        "state": 5
    },
    .
    .
    .
]

  8. Get schema Information about db

    curl http://{TiDBIP}:10080/schema/{db}
    curl http://{TiDBIP}:10080/schema/{db}?id_name_only=true
[
{
 "id": 119,
 "name": {
  "O": "t1",
  "L": "t1"
 }
},
{
 "id": 125,
 "name": {
  "O": "t2",
  "L": "t2"
 }
}
]

  9. Get schema Information about db.table, and you can get schema info by tableID (tableID is the unique identifier of table in TiDB)

    curl http://{TiDBIP}:10080/schema/{db}/{table}

curl http://{TiDBIP}:10080/schema?table_id={tableID}

curl http://{TiDBIP}:10080/schema?table_ids={tableID,...}

  10. Get database information, table information and tidb info schema version by tableID.

    curl http://{TiDBIP}:10080/db-table/{tableID}

  11. Get MVCC Information of the key with a specified handle ID

    curl http://{TiDBIP}:10080/mvcc/key/{db}/{table}/{handle}
    $curl http://127.0.0.1:10080/mvcc/key/test/t/1
{
    "key": "74800000000000006E5F728000000000000001",
    "region_id": 4,
    "value": {
        "info": {
            "writes": [
                {
                    "start_ts": 448662063415296001,
                    "commit_ts": 448662063415296003,
                    "short_value": "gAABAAAAAQEAAQ=="
                }
            ]
        }
    }
}

    If the handle is clustered, specify the primary key column values in the query string

    $curl http://{TiDBIP}:10080/mvcc/key/{db}/{table}?${c1}={v1}&${c2}=${v2}
    $curl http://127.0.0.1:10080/mvcc/key/test/t\?a\=aaa\&b\=2020-01-01
{
    "key": "7480000000000000365F72016161610000000000FA0419A5420000000000",
    "region_id": 52,
    "value": {
        "info": {
            "writes": [
                {
                    "type": 1,
                    "start_ts": 423158426542538752,
                    "commit_ts": 423158426543587328
                },
                {
                    "start_ts": 423158426542538752,
                    "commit_ts": 423158426543587328,
                    "short_value": "gAACAAAAAQMDAAQAYWFhZA=="
                }
            ],
            "values": [
                {
                    "start_ts": 423158426542538752,
                    "value": "gAACAAAAAQMDAAQAYWFhZA=="
                }
            ]
        }
    }
}

    Hint: The meaning of the MVCC operation type:

    enum Op {
    Put = 0;
    Del = 1;
    Lock = 2;
    Rollback = 3;
    // insert operation has a constraint that key should not exist before.
    Insert = 4;
    PessimisticLock = 5;
    CheckNotExists = 6;
}

    Hint: On a partitioned table, use the table(partition) pattern as the table name, t1(p1) for example:

    $curl http://127.0.0.1:10080/mvcc/key/test/t1(p1)/1

    Hint: The method to convert the Hex format key returned by TiDB API into the format recognized by tikv-ctl.

    Step 1: Get the hex format of the key you need. For example you could find the key by the following TiDB API.

       $curl http://127.0.0.1:10080/mvcc/key/test/t1/1
   {
   "key": "7480000000000008C65F728000000000000001",
   "region_id": 10,
   "value": {
       "info": {
           "writes": [
               {
                   "start_ts": 445971968923271174,
                   "commit_ts": 445971968923271175,
                   "short_value": "gAACAAAAAgMIAAkAc2hpcmx5YTQi"
               },
               {
                   "start_ts": 445971959499980803,
                   "commit_ts": 445971959499980804,
                   "short_value": "gAACAAAAAgMIAAkAc2hpcmx5YTQL"
               }
           ]
       }
   }
   }

    Step 2: Convert the key from hex format to escaped format with tikv-ctl

     ./tikv-ctl --to-escaped '7480000000000008C65F728000000000000001'
 t\200\000\000\000\000\000\010\306_r\200\000\000\000\000\000\000\001

    Step 3: Encode the key to make it memcomparable in tikv with tikv-ctl

    ./tikv-ctl --encode 't\200\000\000\000\000\000\010\306_r\200\000\000\000\000\000\000\001'
7480000000000008FFC65F728000000000FF0000010000000000FA

    Step 4: Convert the key from hex format to escaped format again since most tikv-ctl commands only accept keys in escaped format while the --encode command outputs the key in hex format.

    ./tikv-ctl --to-escaped '7480000000000008FFC65F728000000000FF0000010000000000FA'
t\200\000\000\000\000\000\010\377\306_r\200\000\000\000\000\377\000\000\001\000\000\000\000\000\372

    Step 5: Add a prefix "z" to the key. Then the key can be recognized by tikv-ctl. For example, use the following command to scan from tikv.

    ./tikv-ctl  --host "<tikv_ip>:<port>" scan --from 'zt\200\000\000\000\000\000\010\377\306_r\200\000\000\000\000\377\000\000\001\000\000\000\000\000\372' --limit 5 --show-cf write,lock,default 
   key: zt\200\000\000\000\000\000\010\377\306_r\200\000\000\000\000\377\000\000\001\000\000\000\000\000\372
    write cf value: start_ts: 445971968923271174 commit_ts: 445971968923271175 short_value: 800002000000020308000900736869726C79613422
    write cf value: start_ts: 445971959499980803 commit_ts: 445971959499980804 short_value: 800002000000020308000900736869726C7961340B

   key: zt\200\000\000\000\000\000\010\377\306_r\200\000\000\000\000\377\000\000\002\000\000\000\000\000\372
    write cf value: start_ts: 445971960836390913 commit_ts: 445971960836390914 short_value: 80000200000002030500060073686972340B

   key: zt\200\000\377\377\377\377\377\377\373_r\200\000\000\000\000\377\000\000\003\000\000\000\000\000\372
    write cf value: r_type: Del start_ts: 444068474890485761 commit_ts: 444068474890485762

   key: zt\200\000\377\377\377\377\377\377\373_r\200\000\000\000\000\377\000\000\005\000\000\000\000\000\372
    write cf value: r_type: Del start_ts: 444068474929545217 commit_ts: 444068474929545218

   key: zt\200\000\377\377\377\377\377\377\373_r\200\000\000\000\000\377\000\000\007\000\000\000\000\000\372
    write cf value: r_type: Del start_ts: 444068474981974017 commit_ts: 444068474981974018

  12. Get MVCC Information of the first key in the table with a specified start ts

    curl http://{TiDBIP}:10080/mvcc/txn/{startTS}/{db}/{table}
    $curl http://127.0.0.1:10080/mvcc/txn/405179368526053377/test/t1
{
    "info": {
        "writes": [
            {
                "commit_ts": 405179368526053380,
                "short_value": "CAICAkE=",
                "start_ts": 405179368526053377
            }
        ]
    },
    "key": "dIAAAAAAAAEzX3KAAAAAAAAAAQ=="
}

  13. Get MVCC Information by a hex value

    curl http://{TiDBIP}:10080/mvcc/hex/{hexKey}

  14. Get MVCC Information of a specified index key, argument example: column_name_1=column_value_1&column_name_2=column_value2...

    curl http://{TiDBIP}:10080/mvcc/index/{db}/{table}/{index}/{handle}?${c1}={v1}&${c2}=${v2}

    Hint: For the index column which column type is timezone dependent, e.g. timestamp, convert its value to UTC timezone.

    $curl http://127.0.0.1:10080/mvcc/index/test/t1/idx/1\?a\=A
{
    "info": {
        "writes": [
            {
                "commit_ts": 405179523374252037,
                "short_value": "MA==",
                "start_ts": 405179523374252036
            }
        ]
    }
}

    Hint: On a partitioned table, use the table(partition) pattern as the table name, t1(p1) for example:

    $curl http://127.0.0.1:10080/mvcc/index/test/t1(p1)/idx/1\?a\=A

    If the handle is clustered, also specify the primary key column values in the query string

    $curl http://{TiDBIP}:10080/mvcc/index/{db}/{table}/{index}?${c1}={v1}&${c2}=${v2}
    $curl http://127.0.0.1:10080/mvcc/index/test/t/idx\?a\=1.1\&b\=111\&c\=1
{
    "key": "74800000000000003B5F69800000000000000203800000000000000105BFF199999999999A013131310000000000FA",
    "region_id": 59,
    "value": {
        "info": {
            "writes": [
                {
                    "start_ts": 424752858505150464,
                    "commit_ts": 424752858506461184,
                    "short_value": "AH0B"
                }
            ],
            "values": [
                {
                     "start_ts": 424752858505150464,
                     "value": "AH0B"
                }
            ]
        }
    }
}

  15. Scatter regions of the specified table, add a scatter-range scheduler for the PD and the range is same as the table range.

    curl http://{TiDBIP}:10080/tables/{db}/{table}/scatter

    Hint: On a partitioned table, use the table(partition) pattern as the table name, test(p1) for example.

    Note: The scatter-range scheduler may conflict with the global scheduler, do not use it for long periods on the larger table.

  16. Stop scatter the regions, disable the scatter-range scheduler for the specified table.

    curl http://{TiDBIP}:10080/tables/{db}/{table}/stop-scatter

    Hint: On a partitioned table, use the table(partition) pattern as the table name, test(p1) for example.

  17. Get TiDB server settings

    curl http://{TiDBIP}:10080/settings

  18. Get TiDB server information.

    curl http://{TiDBIP}:10080/info
    $curl http://127.0.0.1:10080/info
{
    "ddl_id": "f7e73ed5-63b4-4cb4-ba7c-42b32dc74e77",
    "git_hash": "f572e33854e1c0f942f031e9656d0004f99995c6",
    "ip": "",
    "is_owner": true,
    "lease": "45s",
    "listening_port": 4000,
    "status_port": 10080,
    "version": "5.7.25-TiDB-v2.1.0-rc.3-355-gf572e3385-dirty"
}

  19. Get TiDB cluster all servers information.

    curl http://{TiDBIP}:10080/info/all
    $curl http://127.0.0.1:10080/info/all
{
    "servers_num": 2,
    "owner_id": "29a65ec0-d931-4f9e-a212-338eaeffab96",
    "is_all_server_version_consistent": true,
    "all_servers_info": {
        "29a65ec0-d931-4f9e-a212-338eaeffab96": {
            "version": "5.7.25-TiDB-v4.0.0-alpha-669-g8f2a09a52-dirty",
            "git_hash": "8f2a09a52fdcaf9d9bfd775d2c6023f363dc121e",
            "ddl_id": "29a65ec0-d931-4f9e-a212-338eaeffab96",
            "ip": "",
            "listening_port": 4000,
            "status_port": 10080,
            "lease": "45s",
            "binlog_status": "Off"
        },
        "cd13c9eb-c3ee-4887-af9b-e64f3162d92c": {
            "version": "5.7.25-TiDB-v4.0.0-alpha-669-g8f2a09a52-dirty",
            "git_hash": "8f2a09a52fdcaf9d9bfd775d2c6023f363dc121e",
            "ddl_id": "cd13c9eb-c3ee-4887-af9b-e64f3162d92c",
            "ip": "",
            "listening_port": 4001,
            "status_port": 10081,
            "lease": "45s",
            "binlog_status": "Off"
        }
    }
}

  20. Enable/Disable TiDB server general log

    curl -X POST -d "tidb_general_log=1" http://{TiDBIP}:10080/settings
curl -X POST -d "tidb_general_log=0" http://{TiDBIP}:10080/settings

  21. Change TiDB server log level

    curl -X POST -d "log_level=debug" http://{TiDBIP}:10080/settings
curl -X POST -d "log_level=info" http://{TiDBIP}:10080/settings

  22. Change TiDB DDL slow log threshold

    The unit is millisecond.

    curl -X POST -d "ddl_slow_threshold=300" http://{TiDBIP}:10080/settings

  23. Get the column value by an encoded row and some information that can be obtained from a column of the table schema information.

    Argument example: rowBin=base64_encoded_row_value

    curl http://{TiDBIP}:10080/tables/{colID}/{colFlag}/{colLen}?rowBin={val}

    Hint: For the column which field type is timezone dependent, e.g. timestamp, convert its value to UTC timezone.

  24. Resign the ddl owner, let tidb start a new ddl owner election.

    curl -X POST http://{TiDBIP}:10080/ddl/owner/resign

    Note: If you request a TiDB that is not ddl owner, the response will be This node is not a ddl owner, can't be resigned.

  25. Get the TiDB DDL job history information.

    curl http://{TiDBIP}:10080/ddl/history

    Note: When the DDL history is very very long, system table may containg too many jobs. This interface will get a maximum of 2048 history ddl jobs by default. If you want get more jobs, consider adding start_job_id and limit.

  26. Get count {number} TiDB DDL job history information.

    curl http://{TiDBIP}:10080/ddl/history?limit={number}

  27. Get count {number} TiDB DDL job history information, start with job {id}

    curl http://{TIDBIP}:10080/ddl/history?start_job_id={id}&limit={number}

  28. Download TiDB debug info

    curl http://{TiDBIP}:10080/debug/zip?seconds=60 --output debug.zip

    zip file will include:

    • Go heap pprof(after GC)
    • Go cpu pprof(10s)
    • Go mutex pprof
    • Full goroutine
    • TiDB config and version

    Param:

    • seconds: profile time(s), default is 10s.

  29. Get statistics data of specified table.

    curl http://{TiDBIP}:10080/stats/dump/{db}/{table}

  30. Get statistics data of specific table and timestamp.

    curl http://{TiDBIP}:10080/stats/dump/{db}/{table}/{yyyyMMddHHmmss}
    curl http://{TiDBIP}:10080/stats/dump/{db}/{table}/{yyyy-MM-dd HH:mm:ss}

  31. Resume the binlog writing when Pump is recovered.

    curl http://{TiDBIP}:10080/binlog/recover

    Return value:

    • timeout, return status code: 400, message: timeout

    • If it returns normally, status code: 200, message example:

      {
  "Skipped": false,
  "SkippedCommitterCounter": 0
}

      Skipped: false indicates that the current binlog is not in the skipped state, otherwise, it is in the skipped state SkippedCommitterCounter: Represents how many transactions are currently being committed in the skipped state. By default, the API will return after waiting until all skipped-binlog transactions are committed. If this value is greater than 0, it means that you need to wait until them are committed .

    Param:

    • op=nowait: return after binlog status is recoverd, do not wait until the skipped-binlog transactions are committed.
    • op=reset: reset SkippedCommitterCounter to 0 to avoid the problem that SkippedCommitterCounter is not cleared due to some unusual cases.
    • op=status: Get the current status of binlog recovery.

  32. Enable/disable async commit feature

    curl -X POST -d "tidb_enable_async_commit=1" http://{TiDBIP}:10080/settings
curl -X POST -d "tidb_enable_async_commit=0" http://{TiDBIP}:10080/settings

  33. Enable/disable one-phase commit feature

    curl -X POST -d "tidb_enable_1pc=1" http://{TiDBIP}:10080/settings
curl -X POST -d "tidb_enable_1pc=0" http://{TiDBIP}:10080/settings

  34. Enable/disable the mutation checker

    curl -X POST -d "tidb_enable_mutation_checker=1" http://{TiDBIP}:10080/settings
curl -X POST -d "tidb_enable_mutation_checker=0" http://{TiDBIP}:10080/settings

  35. Get/Set the size of the Ballast Object

    # get current size of the ballast object
curl -v http://{TiDBIP}:10080/debug/ballast-object-sz
# reset the size of the ballast object (2GB in this example)
curl -v -X POST -d "2147483648" http://{TiDBIP}:10080/debug/ballast-object-sz

  36. Set deadlock history table capacity

    curl -X POST -d "deadlock_history_capacity={number}" http://{TiDBIP}:10080/settings

  37. Set whether deadlock history (DEADLOCKS) collect retryable deadlocks

    curl -X POST -d "deadlock_history_collect_retryable={bool_val}" http://{TiDBIP}:10080/settings

  38. Set transaction_id to digest mapping minimum duration threshold, only transactions which last longer than this threshold will be collected into TRX_SUMMARY.

    curl -X POST -d "transaction_id_digest_min_duration={number}" http://{TiDBIP}:10080/settings

    Unit of duration here is ms.

  39. Set transaction summary table (TRX_SUMMARY) capacity

    curl -X POST -d "transaction_summary_capacity={number}" http://{TiDBIP}:10080/settings

  40. The commands are used to handle smooth upgrade mode(refer to the TiDB Smooth Upgrade for details) operations. We can send these upgrade operations to the cluster. The operations here include start, finish and show.

    curl -X POST http://{TiDBIP}:10080/upgrade/{op}
    $curl -X POST http://127.0.0.1:10080/upgrade/start
"success!"