This article applies to the scenario of independent deployment of obdiag. Use the obdiag gather tabledump command to collect table information (including table structure, data distribution information, etc.) with one click.
If it is a cluster deployed by obd, you can directly collect information on the selected cluster through the obd side diagnostic command group.
Instructions for use
obdiag gather tabledump [options]
The options are explained below:
Option name |
Is it required |
Data type |
Default value |
Description |
|---|---|---|---|---|
| --database | Yes | string | Default is empty | The name of the database where the table to be queried is located |
| --table | Yes | string | Default is empty | The name of the table to be queried |
| --user | Yes | string | Default is empty | The username of the tenant where the table to be queried is located |
| --password | Yes | string | Default is empty | The user password of the tenant where the table to be queried is located |
| --store_dir | No | string | Defaults to the current path where the command is executed | The local path where the results are stored. |
| -c | No | string | ~/.obdiag/config.yml |
Configuration file path |
| --inner_config | No | string | Default is empty | obdiag Self-use configuration |
| --config | No | string | Default is empty | Configuration of the cluster to be diagnosed by obdiag, fixed style: --config key1=value1 --config key2=value2.
ExplanationThe parameters that support configuration through this option can be found in obdiag configuration. |
| --config_password | No | string | Default is empty | obdiag When using an encrypted configuration file, you need to pass in the corresponding password through this option.
ExplanationFor details, see Configuration file encryption. |
Usage example
Method 1: Use without configuration file (out of the box)
obdiag gather tabledump --user=<MYUSER> --password=<MYPASSWORD> --database=<MYDATABASE> --table=<MYTABLENAME> \
--config db_host=xx.xx.xx.xx \
--config db_port=xxxx \
--config tenant_sys.user=root@sys \
--config tenant_sys.password=***
The output is as follows:
gather_tabledump start ...
Summary:
+-----------+-------------------------------------------------------------------+--------+
| Status | Result Details | Time |
+===========+===================================================================+========+
| Completed | ./obdiag_gather_report/obdiag_tabledump_result_20240808145346.txt | 0.34 s |
+-----------+-------------------------------------------------------------------+--------+
Trace ID: f5b1f66e-5552-11ef-8a9b-00163e01c7ce
If you want to view detailed obdiag logs, please run: obdiag display-trace f5b1f66e-5552-11ef-8a9b-00163e01c7ce
Method 2: Use with configuration file
You need to ensure that the login information of the node to be collected has been configured in the obdiag configuration file config.yml. For related detailed configuration introduction, see obdiag configuration.
obdiag gather tabledump --user=<MYUSER> --password=<MYPASSWORD> --database=<MYDATABASE> --table=<MYTABLENAME>
Interpretation of results
After the command is executed, a table information report will be generated in the specified directory. The file format is: ./obdiag_gather_report/obdiag_tabledump_result_20240808145346.txt.
Description
Since obdiag V4.2.0, the command will append CREATE TABLE DDL to the create_tables_{timestamp}.sql file to facilitate direct execution of the table creation statement; the output summary will include the SQL file path.
obclient > show create table test1.game
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+=========+=============================================================================================================================================================================+
| game | CREATE TABLE `game` ( |
| | `round` int(11) NOT NULL, |
| | `team` varchar(10) DEFAULT NULL, |
| | `score` int(11) DEFAULT NULL, |
| | PRIMARY KEY (`round`) |
| | ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
| | partition by hash(round) |
| | (partition `p0`, |
| | partition `p1`, |
| | partition `p2`) |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
obclient > select /*+read_consistency(weak) QUERY_TIMEOUT(60000000) */ table_name , ifnull(num_rows,0) as num_rows from oceanbase.cdb_tables where con_id = '1004' and owner = 'test1' and table_name = 'game' order by num_rows desc limit 1
+--------------+------------+
| table_name | num_rows |
+==============+============+
| game | 6 |
+--------------+------------+
obclient > select /*+read_consistency(weak) QUERY_TIMEOUT(60000000) */ t1.SVR_IP,t1.role,ifnull(t2.data_size,0) as total_data_size from (select SVR_IP,tenant_id, database_name, role, table_id, tablet_id from oceanbase.cdb_ob_table_locations) t1 left join (select tenant_id, tablet_id,data_size from oceanbase.cdb_ob_tablet_replicas) t2 on t1.tenant_id = t2.tenant_id and t1.tablet_id = t2.tablet_id where t1.tenant_id = '1004' and t1.table_id = '500003' order by total_data_size desc limit 1
+----------------+--------+-------------------+
| SVR_IP | role | total_data_size |
+================+========+===================+
| xx.xxx.xxx.xxx | LEADER | 413 |
+----------------+--------+-------------------+
| xx.xxx.xxx.xxx | LEADER | 713 |
+----------------+--------+-------------------+