Command-line options

OBDUMPER specifies the information to be exported by using command-line options. For more information about the options and usage examples, see Options and Usage examples.

Overview

Option styles

OBDUMPER supports command-line options in Unix and GNU styles.

• Unix style: An option is prefixed with a hyphen and each option is a single character, such as ps -e. In this style, you can omit the space between an option and its value, such as -p******.

• GNU style: An option is prefixed with double hyphens and each option is a single character or a string, such as ps --version. An option and its value must be separated with a space, such as --table 'test'.

Option categories

Command-line options in OBDUMPER are classified into basic options and advanced options.

• Basic options: general options of OBDUMPER, including connection options (such as the database connection method), feature options (such as the file format, database object type, and storage path), and other options.

• Advanced options: feature options (such as the timestamp format, allowlist- and blocklist-based table/column filtering settings, and error handling method) and performance options.

Required options

When using OBDUMPER to export data, you must specify at least the connection options, format options, database object type options, and storage path options.

Example:

$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** -D USERA --csv --table '*' -f /output

In this example, -h, -P, -u, -p, and -D are connection options; --csv is a file format option; --table is a database object type option; and -f specifies the storage path.

Basic options

Connection options

OBDUMPER commands can read and write data only after connecting to OceanBase Database. You can specify the following options to connect to OceanBase Database.

Export type

Features

File formats

Database object types

• --all

  This option is used to export all database object definitions and table data. It is a collection of database object types such as --trigger and --view. The usage restrictions apply to specific database object types.

  • When used with --ddl, this option indicates exporting all database object definitions. For database object types applicable only to OceanBase Database Oracle mode, such as --trigger, even if you specify --all --ddl, the DDL of triggers cannot be exported in an OceanBase Database MySQL tenant.

  • When used with --csv, --sql, or --cut, this option indicates exporting all data in tables in the specified format. If you need to export all database object definitions and table data, you can specify --all and --ddl with any data format option.

  Notice

  --all is mutually exclusive with any other database object option. If both --all and any other database object option are specified, --all takes precedence.

• --table-group 'table_group_name [,table_group_name...]' | --table-group '*'

  This option is used to export the definitions of table groups. Except for not supporting data export, the description is the same as that of the --table option.
  
  

• --table 'table_name [,table_name...]' | --table '*'

  This option is used to export the definitions or data of tables. When used with --ddl, this option indicates exporting only the table definitions. When used with any data format option, this option indicates exporting only the table data. If multiple tables are specified, separate the table names with commas (,). By default, the names of tables in OceanBase Database Oracle mode are exported in uppercase, and the names of tables in OceanBase Database MySQL mode are exported in lowercase. For example, in OceanBase Database Oracle mode, --table 'test' and --table 'TEST' both indicate the TEST table. In OceanBase Database MySQL mode, --table 'test' and --table 'TEST' both indicate the test table. If you want to distinguish between uppercase and lowercase, enclose the table name in brackets ([ ]). For example, --table '[test]' indicates the test table, and --table '[TEST]' indicates the TEST table. If the table name is specified as an asterisk (*), all table definitions or data are exported.

  Note

  OBDUMPER 4.1.0 and later versions support exporting the definitions of temporary tables in OceanBase Database Oracle mode.

• --view 'view_name [, view_name...]' | --view '* '

  This option is used to export the definitions of views. Except for not supporting data export, the description is the same as that of the --table option.

• --trigger 'trigger_name [, trigger_name...]' | --trigger '*'

  This option is used to export the definitions of triggers. Except for not supporting data export, the description is the same as that of the --table option. Currently, this option applies only to OceanBase Database Oracle mode.

• --sequence 'sequence_name [, sequence_name...]' | --sequence '*'

  This option is used to export the definitions of sequences. Except for not supporting data export, the description is the same as that of the --table option.

  Notice

  OBDUMPER 4.0.0 and earlier versions apply only to OceanBase Database Oracle mode. OBDUMPER 4.1.0 and later versions apply to both OceanBase Database MySQL and OceanBase Database Oracle modes.

• --synonym 'synonym_name [, synonym_name...]' | --synonym '*'

  This option is used to export the definitions of synonyms. Except for not supporting data export, the description is the same as that of the --table option. Currently, this option applies only to OceanBase Database Oracle mode.

• --type 'type_name [, type_name...]' | --type '*'

  This option is used to export the definitions of types. Except for not supporting data export, the description is the same as that of the --table option. This option applies only to OceanBase Database Oracle mode.

  Note

  Only OceanBase Database Oracle 2.2.77 and later versions support exporting type definitions.

• --type-body 'typebody_name [, typebody_name...]' | --type-body '* '

  This option is used to export the definitions of type bodies. Except for not supporting data export, the description is the same as that of the --table option. This option applies only to OceanBase Database Oracle mode.

  Note

  This option must be used with --type. The exported type body files are saved in the type files.

• --package 'package_name [, package_name...]' | --package '*'

  This option is used to export the definitions of packages. Except for not supporting data export, the description is the same as that of the --table option. This option applies only to OceanBase Database Oracle mode.

• --package-body 'packagebody_name [, packagebody_name...]' | --package-body '*'

  This option is used to export the definitions of package bodies. Except for not supporting data export, the description is the same as that of the --table option. This option applies only to OceanBase Database Oracle mode.

• --function 'function_name [, function_name...]' | --function '*'

  This option is used to export the definitions of functions. Except for not supporting data export, the description is the same as that of the --table option.

  Note

  All versions of OceanBase Database Oracle support exporting function definitions. OceanBase Database MySQL 2.2.30 and later versions support exporting function definitions.

• --procedure 'procedure_name [, procedure_name...]' | --procedure '*'

  This option is used to export the definitions of stored procedures. Except for not supporting data export, the description is the same as that of the --table option.

  Note

  All versions of OceanBase Database Oracle support exporting stored procedure definitions. OceanBase Database MySQL 2.2.30 and later versions support exporting stored procedure definitions.

File path

• -f 'file_path', --file-path= 'file_path'

  Specifies the absolute path on the local disk where data files are stored.

  Note

  Starting from OBDUMPER 4.2.7, you can specify -f as a specific file name when you export a single file.

  • If you specify --query-sql, the default file name CUSTOM_SQL will be replaced.
  • The log directory will be generated in the parent directory of the path specified by -f.

• --storage-uri 'storage_uri_string'

  Specifies the uniform resource locator (URL) of the storage. Starting from OBDUMPER 4.2.0, you can export data to an Aliyun OSS bucket or an Amazon S3 bucket.

  ' storage_uri_string ' has the following syntax:

  [scheme://host]path[?parameters]
  
  parameters: key[=value],...
  

  The following table describes the components in the syntax.

  Component	Description
  scheme	The storage type. Valid values: Aliyun OSS/Amazon S3. If the specified scheme is not an OSS or S3 bucket, an error is returned.
  host	The name of the storage bucket. When you export data to an OSS or S3 bucket, host specifies the bucket. For more information, see OSS Bucket.
  path	The resource path of the storage bucket. The path must start with /.
  parameters	The parameters required for the request. Parameters can be a single key or a key-value pair.

  Example: Export data to an S3 bucket.

  --storage-uri 's3://bucket/path?region={region}&access-key={accessKey}&secret-key={secretKey}'
  

  • s3: the scheme is s3.
  • bucket: the name of the S3 bucket.
  • path: the resource path of the S3 bucket.
  • ?region={region}&access-key={accessKey}&secret-key={secretKey}: the values of the region, access-key, and secret-key parameters.

  The following table describes the supported parameters.

  Parameter	Whether a value is required	Description	Supported storage types	Supported versions
  endpoint	Yes	Specifies the endpoint of the OSS bucket. Specifies the domain name endpoint of the S3 bucket.	OSS S3	4.2.0 4.2.5
  region	Yes	Specifies the physical location of the S3 bucket.	S3	4.2.0
  storage-class	Yes	Specifies the Amazon S3 storage class.	S3	4.2.0
  access-key	Yes	Specifies the access account of the storage.	OSS/S3	4.2.0
  secret-key	Yes	Specifies the access key of the storage.	OSS/S3	4.2.0

  Notice

  • When you export the definitions of database objects and table data to an Aliyun OSS bucket, you must specify the endpoint, access-key, and secret-key parameters.
  • When you export the definitions of database objects and table data to an Amazon S3 bucket, you must specify the region, access-key, and secret-key parameters.

• --ctl-path 'control_path'

  Specifies the absolute path on the local disk where the control file is stored. You can configure built-in processing functions in the control file. Before data export, the system will preprocess data based on the configured functions. For example: case conversion, null check, etc. For more information about the control file, see Data processing. When you specify this option in the command line, you must enclose the parameter value in single quotation marks. For example: --ctl-path '/home/controls/'.

• --log-path 'log_path'

  Specifies the output directory of the OBDUMPER running logs. If you do not specify this option, the OBDUMPER running logs are output to the directory specified by the -f option by default. In most cases, you do not need to use redirection to output logs.

• --no-nested-dir

  Exports data to a flat directory structure. All files are exported to the directory specified by -f or --storage-uri, and no subdirectories are created.

Other options

• -H, --help

  Displays the help information for the CLI tool.

• -V, --version

  Displays the version number of the current tool.

Advanced options

Feature options

Time stamp format

• --flashback-timestamp 'timestamp_string'

  Specifies the timestamp for flashback. This option can be used only with any data format option, and cannot be used with the --query-sql option. This option is applicable only to OceanBase Database in Oracle mode.
  
  

• --nls-date-format 'date-format-string'

  Specifies the nls_date_format session variable in OceanBase Database in Oracle mode. This option does not specify the export format for DATE type data. Default value: YYYY-MM-DD HH24:MI:SS.
  
  

• --nls-timestamp-format 'timestamp-format-string'

  Specifies the nls_timestamp_format session variable in OceanBase Database in Oracle mode. This option does not specify the export format for TIMESTAMP type data. Default value: YYYY-MM-DD HH24:MI:SS:FF9.
  
  

• --nls-timestamp-tz-format 'timestamp-tz-format-string'

  Specifies the nls_timestamp_tz_format session variable in OceanBase Database in Oracle mode. This option does not specify the export format for TIMESTAMP WITH TIME ZONE type data. Default value: YYYY-MM-DD HH24:MI:SS:FF9 TZR.

• --date-value-format

  Specifies the export format for DATE type data. In OceanBase Database in MySQL mode, the default export format for DATE type data is yyyy-MM-dd. In OceanBase Database in Oracle mode, the default export format for DATE type data is yyyy-MM-dd HH:mm:ss.

  Note

  • This option can be used only with the --csv or --cut option.
  • In OceanBase Database in MySQL mode, this option does not support setting the export format for DATE type data when the value is zero.

• --time-value-format

  Specifies the export format for TIME type data. In OceanBase Database in MySQL mode, the default export format for TIME type data is HH:mm:ss. The precision value is consistent with the precision value defined in the table.

  Note

  • This option can be used only with the --csv or --cut option.
  • In OceanBase Database in MySQL mode, this option does not support setting the export format for TIME type data when the value is zero.

• --datetime-value-format

  Specifies the export format for DATETIME type data. In OceanBase Database in MySQL mode, the default export format for DATETIME type data is yyyy-MM-dd HH:mm:ss. The precision value is consistent with the precision value defined in the table.

  Note

  • This option can be used only with the --csv or --cut option.
  • In OceanBase Database in MySQL mode, this option does not support setting the export format for DATETIME type data when the value is zero.

• --timestamp-value-format

  Specifies the export format for TIMESTAMP type data. In OceanBase Database in MySQL or Oracle mode, the default export format for TIMESTAMP type data is yyyy-MM-dd HH:mm:ss.SSSSSS. The precision value is consistent with the precision value defined in the table.

  Note

  • This option can be used only with the --csv or --cut option.
  • In OceanBase Database in MySQL mode, this option does not support setting the export format for TIMESTAMP type data when the value is zero.

• --timestamp-tz-value-format

  Specifies the export format for TIMESTAMP WITH TIME ZONE type data. In OceanBase Database in Oracle mode, the default export format for TIMESTAMP WITH TIME ZONE type data is yyyy-MM-dd HH:mm:ss.SSSSSS. The precision value is consistent with the precision value defined in the table.

  Note

  This option can be used only with the --csv or --cut option.

• --timestamp-ltz-value-format

  Specifies the export format for TIMESTAMP WITH LOCAL TIME ZONE type data. In OceanBase Database in Oracle mode, the default export format for TIMESTAMP WITH LOCAL TIME ZONE type data is yyyy-MM-dd HH:mm:ss. The precision value is consistent with the precision value defined in the table.

  Note

  This option can be used only with the --csv or --cut option.

• --preserve-zero-datetime

  Specifies the original format of zero values of DATE, TIME, DATETIME, and TIMESTAMP types during export. If you specify this option, the values of non-nullable DATE, TIME, DATETIME, and TIMESTAMP columns that are NULL are exported as 0. The values of nullable DATE, TIME, DATETIME, and TIMESTAMP columns that are NULL are exported as NULL. This option supports setting the export format for DATE, DATETIME, and TIMESTAMP types in MySQL mode.

Allowlist- and blocklist-based filtering

• Table-level filtering

  • --exclude-table 'table_name [, table_name...]'

    Specifies tables to be excluded when exporting table definitions or data. Table names support pattern matching.

    Example: --exclude-table 'test1,test*,*test,te*st'

    The preceding parameter specifies the following tables to be excluded when exporting table definitions or data:

    • test1

    • All tables whose names start with test.

    • All tables whose names end with test.

    • All tables whose names start with te and end with st.
      
      

  • --exclude-data-types 'datatype [, datatype...]'

    Specifies data types to be excluded when exporting data.
    
    

  • --add-extra-message

    Specifies whether to export additional information in table definitions. For example, when you specify the --add-extra-message option, the program exports the table group name of each table. By default, OBDUMPER does not export additional information in table definitions.

    Notice

    When you specify the --add-extra-message option, the program exports the table group name of each table. This option depends on the privileges of the sys tenant. If OBDUMPER does not have the privileges of the sys tenant, do not specify this option.

  • --retain-empty-files

    Specifies whether to generate an empty file when exporting an empty table. If you do not specify this option, no file is generated when exporting an empty table.

• Column-level filtering

  • --include-column-names 'column_name [, column_name...]'

    Specifies columns and their order to be exported.
    
    

  • --exclude-column-names 'column_name [, column_name...]'

    Specifies columns to be excluded when exporting data. Column names do not support pattern matching.

    Notice

    The specified column names must match the case of the column names in the table definition.

  • --where 'where_condition_string'

    Specifies data to be exported that meets the specified conditions. This option can be used only with any of the data format options, not with the --query-sql option.
    
    

  • --partition 'partition_name [, partition_name...]'

    Specifies partitions to be exported. The parameter value is the partition name, and multiple partition names are separated by commas. This option can be used only with any of the data format options, not with the --query-sql option.

    Notice

    • When you specify partitions to be exported, for a subpartitioned table, you must specify the subpartition name. You cannot export data by using the partition name. For a subpartitioned table based on a template, the partition name is the partition name + s + subpartition name.
    • When you specify the --partition option to export data in a partition, for a composite partitioned table, you must specify the subpartition name. OBDUMPER cannot export data in the partition. If the specified partition name does not exist, OBDUMPER returns an error.

  • --query-sql 'select_statement'

    Specifies a custom query statement to be exported. This option cannot be used with the --partition or --where option. You must ensure the correctness and performance of the custom query statement. If you export the result set of a large query statement, you may have to wait for a long time for the database to respond. If you need to preprocess data by using a control file during the export, you must specify the --table and --ctl-path options. The table name specified by the --table option must match the file name specified by the --ctl-path option in case. If you do not need to preprocess data, you can specify any table name for the --table option.
    
    

  • --exclude-virtual-columns

    Specifies whether to export generated columns (generated columns are exported by default).
    
    

  • --enable-hidden-pk

    Specifies whether to use the hidden primary key __pk_increment for tables without a primary key to improve the export speed. In OceanBase Database versions earlier than 4.0.0, you must use a special user to export data by specifying this option. Example:

    # In MySQL mode of OceanBase Database, create a special user and grant privileges to it as the root user.
    create user '__oceanbase_inner_drc_user'@'%' IDENTIFIED BY 'u*******';
    grant ALL on *.* to '__oceanbase_inner_drc_user' WITH GRANT OPTION;
    
    # In Oracle mode of OceanBase Database, create a special user and grant privileges to it as the SYS user.
    create user '__OCEANBASE_INNER_DRC_USER'@'%' IDENTIFIED BY u*******;
    grant ALL to '__OCEANBASE_INNER_DRC_USER';
    

  • --fetch-size 'int_num'

    Specifies the number of rows to be read from the database cursor in Oracle mode of OceanBase Database. Default value: 1000. For more information, see OceanBase FetchSize.

    Note

    You must install the OceanBase JDBC driver.

Error handling

• --retry

  Specifies whether to continue the export task from the position where the previous export failed.

  Notice

  The dump.ckpt file is a checkpoint file generated during the tool run. The file is stored in the directory specified by the -f option. If the dump.ckpt file does not exist, you cannot use this option.

• --weak-read

  Specifies whether to export data from a standby replica. For more information about weak-consistency reads, see Weak-consistency reads.

• --max-file-size int_num

  Specifies the maximum amount of data that can be exported by a process. The export task is stopped when the exported data exceeds this value.
  
  

• --skip-check-dir

  Specifies whether to skip the check of whether the export data directory is empty. If the export directory is not empty, the export task is stopped.

  Note

  • If you do not specify this option, OBDUMPER checks whether the directory is empty. If the export directory is not empty, OBDUMPER returns an error and exits.
  • If you specify this option, OBDUMPER skips the check of whether the directory is empty. However, the exported files may overwrite the data of files with the same name in the directory.

• --remove-newline

  Specifies whether to forcibly delete carriage return characters or line break characters from the exported data. For example, ***\r***, ***\n***, or ***\r\n***. This option modifies only the data read into memory and does not modify the data in the source table. This option can be used only with the --cut option.

  Notice

  If the source table contains carriage return characters or line break characters, the exported data will be inconsistent with the source table data. Before you use this option, make sure that deleting \r, \n, and \r\n characters will not affect your business. If you do not want to delete carriage return characters or line break characters from the data, do not specify this option in the command line to avoid data inconsistency.

• --snapshot

  Specifies whether to export data of historical versions. This option can be used only with a data format option. Exporting data of historical versions ensures global consistency of the exported data. If you do not specify this option, the exported data may not be a globally consistent snapshot.

Performance options

• --page-size int_num

  Specifies the size of the task shard for OBDUMPER 4.0.0 and later. Default value: 1000000.

• --thread int_num

  Specifies the number of concurrent threads. This option directly corresponds to the number of export threads. The default value is CPU multiplied by 2. If the CPU count exceeds 16, the maximum value is 32. When exporting the definitions of multiple database objects, we recommend that you set the value of the --thread option to no more than 4. If the number of concurrent threads is too large, the tool may fail to access system tables in the sys tenant. In this case, a timeout error occurs during the export.

• --block-size

  Specifies the threshold for splitting a file block. This option supports the LONG and STRING data types. If the size of the exported data file exceeds the threshold, other logical subfiles are generated sequentially. The unit of this option is MB by default. The default value is 1024 MB. This option does not take effect for the ORC and Parquet formats.

  OBDUMPER V4.1.0 and later support splitting files based on the number of rows. The value of --block-size is specified in MB or rows. For example, --block-size 256ROW specifies that the size of a single file does not exceed 256 rows. --block-size 1024 or --block-size 1024MB specifies that the size of a single file does not exceed 1024 MB. The 1024M or 1GB format is not supported.

• --parallel-macro int_num

  Specifies the number of macroblocks that a thread can process. Default value: 8.

Other options

• --session-config

  Specifies the connection configuration file. A default configuration file, session.config.json, is provided in the <tool root directory>/conf/ directory. You do not need to configure it. We recommend that you specify this option only when you want to use the same package to load multiple connection configurations.

Options