Command-line options

OBDUMPER allows you to specify the information required for export by using command-line options. For more information about the options and their scenarios and examples, see the Quick reference table of options section in this topic and the Quick start topic.

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 are 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 are 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 you use OBDUMPER to export data, you must specify at least the connection options, format option, database object type option, and storage path option.

Here is a sample command:

$./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, the --csv option specifies the file format, the --table option specifies the database object type, and the -f option specifies the storage path.

Basic options

Connection options

OBDUMPER can read data from and write data to OceanBase Database only after it is connected to the database. You can connect to an OceanBase database by specifying the following options.

Export types

Feature options

File formats

Compress data for export

OBDUMPER V4.3.0 and later allow you to compress CSV, CUT, POS, and SQL files for export. You can specify related configurations by using the following command-line options:

• --compress

  Specifies whether to compress data during export. It is a Boolean value.

• --compression-algo

  The compression algorithm. The value is a string. Valid values are zstd, zlib, gzip, and snappy. The default value is zstd.

• --compression-level

  The compression level for the specified compression algorithm. The value is an integer. Compression levels supported for different compression algorithms are as follows:

  • zstd: 1 to 22. The default value is 3.

  • zlib: -1 to 9. The default value is -1.

  • gzip and snappy: You cannot specify a compression level for this compression algorithm.

Database object types

• --all

  Exports all database object definitions and table data, including all database object types specified by the --trigger and --view options. Observe the limitations on specific database object types.

  • When this option is used with the --ddl option, all database object definitions are exported. Database object types specified by the --trigger option are supported only in the Oracle compatible mode of OceanBase Database. In the MySQL compatible mode of OceanBase Database, DDL operations on triggers cannot be exported even if you specify both the --all and --ddl options.

  • When this option is used with the --csv, --sql, or --cut option, data in all tables is exported in the specified format. To export all database object definitions and table data, you can specify the --all and --ddl options along with a data format option.

  Notice

  The --all option is mutually exclusive with any database object options. It cannot be specified together with other database object options. If both the --all option and a database object option are specified, the --all option will be executed first.

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

  Exports table group definitions. This option is similar to the --table option, except that this option does not support data export.

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

  Exports table definitions or table data. When this option is used with the --ddl option, only table definitions are exported. When this option is used with any data format option, only table data is exported. To specify multiple tables, separate the table names with commas (,). By default, in the Oracle compatible mode of OceanBase Database, the table names are in uppercase, and in the MySQL compatible mode of OceanBase Database, the table names are in lowercase. For example, in the Oracle compatible mode of OceanBase Database, both --table 'test' and --table 'TEST' indicate the table named TEST, and in the MySQL compatible mode of OceanBase Database, both --table 'test' and --table 'TEST' indicate the table named test. To enable case sensitivity, enclose a table name in brackets ([]). For example, --table '[test]' indicates the table named test, while --table '[TEST]' indicates the table named TEST. If the table name is specified as an asterisk, all table definitions or table data are exported.

  Note

  • OBDUMPER does not support the export of data from internal tables or views of OceanBase Database.
  • OBDUMPER V4.1.0 and later allow you to export temporary table definitions in the Oracle compatible mode of OceanBase Database.

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

  Exports view definitions. This option is similar to the --table option, except that this option does not support data export.

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

  Exports trigger definitions. This option is similar to the --table option, except that this option does not support data export. This option is supported only in the Oracle compatible mode of OceanBase Database.

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

  Exports sequence definitions. This option is similar to the --table option, except that this option does not support data export.

  Notice

  OBDUMPER V4.0.0 and earlier apply only to the the Oracle compatible mode of OceanBase Database. OBDUMPER V4.1.0 and later apply to both the MySQL compatible and the Oracle compatible modes of OceanBase Database.

  Valid values of the --sequence-policy option are restart and preserve. The default value is preserve.

  • If the default value preserve is used when you export a sequence, the start value is set to the current value of the sequence.

  • If you set the value to restart when you export a sequence, the original definition of the start value is used.

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

  Exports synonym definitions. This option is similar to the --table option, except that this option does not support data export. This option is supported only in the Oracle compatible mode of OceanBase Database.

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

  Exports type definitions. This option is similar to the --table option, except that this option does not support data export. This option is supported only in the Oracle compatible mode of OceanBase Database.

  Note

  You can export type definitions only in Oracle-compatible tenants of OceanBase Database V2.2.77 and later.

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

  Exports type body definitions. This option is similar to the --table option, except that this option does not support data export. This option is supported only in the Oracle compatible mode of OceanBase Database.

  Note

  This option must be used with the --type option. The exported type body is saved as a type file.

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

  Exports package definitions. This option is similar to the --table option, except that this option does not support data export. This option is supported only in the Oracle compatible mode of OceanBase Database.

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

  Exports package body definitions. This option is similar to the --table option, except that this option does not support data export. This option is supported only in the Oracle compatible mode of OceanBase Database.

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

  Exports function definitions. This option is similar to the --table option, except that this option does not support data export.

  Note

  Function definitions can be exported for OceanBase Database of any version in the Oracle compatible mode and OceanBase Database of V2.2.30 or later in the MySQL compatible mode.

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

  Exports stored procedure definitions. This option is similar to the --table option, except that this option does not support data export.

  Note

  Stored procedure definitions can be exported for OceanBase Database of any version in the Oracle compatible mode and OceanBase Database of V2.2.30 or later in the MySQL compatible mode.

Storage path

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

  The absolute path on a local disk for storing data files.

  Note

  If you use OBDUMPER V4.2.7 or later to export one file, you can use -f to specify the file name.

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

  OBDUMPER allows you to export database object definitions and table data to local disks, Alibaba Cloud Object Storage Service (OSS), Amazon Simple Storage Service (S3), Tencent Cloud Object Storage (COS), and Huawei Cloud Object Storage Service (OBS). The syntax is as follows:

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

  Parameter	Description
  scheme	The storage scheme. Supported storage schemes are Alibaba Cloud OSS, Amazon S3, Tencent COS, and Huawei OBS. If the specified scheme is not one that is listed above, an error is returned.
  host	The name of the storage space. When you export data to Alibaba Cloud OSS, Amazon S3, Tencent Cloud COS, or Huawei Cloud OBS, the host parameter specifies the bucket. For more information, see Bucket.
  path	The data storage path in the storage space. The path must start with a slash (/).
  parameters	The parameters required for the request. The value can be a single key or a key-value pair.

  The following table describes the supported parameters.

  Parameter	Value required?	Description	Supported storage scheme	Supported version
  endpoint	Yes	The endpoint of the region where the OSS host resides. The endpoint for accessing S3, COS, or OBS.	OSS/S3/COS/OBS	OSS: V4.2.0 S3: V4.2.5 COS/OBS: V4.3.0
  region	Yes	The physical location of the bucket.	S3/COS/OBS	S3: V4.2.0 COS/OBS: V4.3.0
  storage-class	Yes	The storage class of Amazon S3.	S3	4.2.0
  access-key	Yes	The AccessKey ID used to access the bucket.	OSS/S3/COS/OBS	OSS/S3: V4.2.0 COS/OBS: V4.3.0
  secret-key	Yes	The AccessKey secret used to access the bucket.	OSS/S3/COS/OBS	OSS/S3: V4.2.0 COS/OBS: V4.3.0

  Here are some examples:

  • Export data to the local disk

    -f '/home/admin/foo/bar' 
    

  • Export data to Amazon S3

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

  • Export data to Alibaba Cloud OSS

    -f 'oss://mybucket/foo/bar?endpoint=myendpoint&access-key=myak&secret-key=mysk' 
    

  • Export data to Tencent Cloud COS

    -f 'cos://mybucket/foo?region=ap-shanghai&access-key=myak&secret-key=mysk'
    -f 'cos://mybucket/foo?endpoint=yourendpoint&access-key=myak&secret-key=mysk'
    

  • Export data to Huawei Cloud OBS

    -f 'obs://mybucket/foo?region=cn-north-1&access-key=myak&secret-key=mysk'
    -f 'obs://mybucket/foo?endpoint=yourendpoint&access-key=myak&secret-key=mysk'
    

• --ctl-path 'control_path'

  The absolute path on a local disk for storing control files. You can configure built-in preprocessing functions in a control file. Data will be preprocessed by these functions before being exported. For example, the functions can perform case conversion and check the data for empty values. For information about the use of control files, see Data processing. When you specify this option on the CLI, enclose the value in single quotation marks (' '), for example, --ctl-path '/home/controls/'.

• --log-path 'log_path'

  The output directory for the operational logs of OBDUMPER. If this option is not specified, OBDUMPER operational logs are stored in the directory specified by the -f option. Redirection is not required for log output, unless otherwise specified.

• --no-nested-dir

  Exports all files to the directory specified by the -f option without generating nested subdirectories.

• OBDUMPER exports data to the specified object storage service in Multipart-Upload mode. By default, the local file system is used as the temporary directory for storing temporary file blocks. You can also use the --tmp-path option to specify a storage directory. You can modify the following parameters in the script of OBDUMPER to adapt to the actual data export environment:

  • upload.buffer.type

    The buffer type. Valid values are disk and bytebuffer. The default value is disk, which specifies to store file blocks in the disk. In this case, The total size of file blocks that can be stored is subject to the maximum available space of the disk. bytebuffer specifies to store file blocks in the memory. This ensures high performance but occupies much JVM stack memory space.

  • upload.buffer.size

    The size of each file block, in bytes. The default value is 64 MB.

  • upload.active.blocks

    The maximum number of blocks (being uploaded or waiting to be uploaded in the queue) concurrently processed by each write thread. The default value is 2. When the specified value is exceeded, the tool is blocked until a file block is uploaded and removed from the queue.

    The --thread option specifies the maximum number of write threads. You can use the following formula to estimate the space that may be occupied by the buffer when the tool runs: Value of thread × Value of active.blocks × Value of buffer.size.

Other options

• -H/--help

  Shows the help information about the tool.

• -V/--version

  Shows the version of the tool.

Advanced options

Feature options

Timestamp formats

• --flashback-timestamp 'timestamp_string'

  Exports data after a flashback point in time. This option can be used only with a data format option and cannot be used with the --query-sql option. This option is supported only in the Oracle compatible mode of OceanBase Database.

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

  The nls_date_format session variable in the Oracle compatible mode of OceanBase Database. This option does not specify to export data of the DATE data type in the specified format. The default value is YYYY-MM-DD HH24:MI:SS.

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

  The nls_timestamp_format session variable in the Oracle compatible mode of OceanBase Database. This option does not specify to export data of the TIMESTAMP data type in the specified format. The default value is YYYY-MM-DD HH24:MI:SS:FF9.

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

  The nls_timestamp_tz_format session variable in the Oracle compatible mode of OceanBase Database. This option does not specify to export data of the TIMESTAMP WITH TIME ZONE data type in the specified format. The default value is YYYY-MM-DD HH24:MI:SS:FF9 TZR.

• --date-value-format

  The export format of the DATE data type. By default, the DATE data type is exported in the yyyy-MM-dd format in the MySQL compatible mode of OceanBase Database and in the yyyy-MM-dd HH:mm:ss format in the Oracle compatible mode of OceanBase Database.

  Note

  • This option can be used only with the --csv or --cut option.
  • the MySQL compatible mode of OceanBase Database, this option is unavailable when the date value is zero.

• --time-value-format

  The export format of the TIME data type. By default, the TIME data type is exported in the HH:mm:ss format in the MySQL compatible mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

  • This option can be used only with the --csv or --cut option.
  • In the MySQL compatible mode of OceanBase Database, this option is unavailable when the time value is zero.

• --datetime-value-format

  The export format of the DATETIME data type. By default, the DATETIME data type is exported in the yyyy-MM-dd HH:mm:ss format in the MySQL compatible mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

  • This option can be used only with the --csv or --cut option.
  • In the MySQL compatible mode of OceanBase Database, this option is unavailable when the datetime value is zero.

• --timestamp-value-format

  The export format of the TIMESTAMP data type. By default, the TIMESTAMP data type is exported in the yyyy-MM-dd HH:mm:ss.SSSSSS format in both the MySQL compatible and the Oracle compatible modes of OceanBase Database. The precision is the same as that in the table definition.

  Note

  • This option can be used only with the --csv or --cut option.
  • In the MySQL compatible mode of OceanBase Database, this option is unavailable when the timestamp value is zero.

• --timestamp-tz-value-format

  The export format of the TIMESTAMP WITH TIME ZONE data type. By default, the TIMESTAMP WITH TIME ZONE data type is exported in the yyyy-MM-dd HH:mm:ss.SSSSSS format in the Oracle compatible mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

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

• --timestamp-ltz-value-format

  The export format of the TIMESTAMP WITH LOCAL TIME ZONE data type. By default, the TIMESTAMP WITH LOCAL TIME ZONE data type is exported in the yyyy-MM-dd HH:mm:ss format in the Oracle compatible mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

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

• --preserve-zero-datetime

  Specifies whether to retain the original formats for zero values of datetime data types during export. If this option is specified, NULL values in datetime columns with NOT NULL constraints are exported as 0, and NULL values in nullable columns are still exported as NULL. You can specify this option for DATE, DATETIME, and TIMESTAMP data types in the MySQL compatible mode of OceanBase Database.

Allowlist- and blocklist-based filtering

• Allowlist- and blocklist-based table filtering

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

    Excludes the specified tables from the export of table definitions or table data. Fuzzy match on table names is supported,

    for example, --exclude-table 'test1,test*,*test,te*st'.

    The preceding example specifies to exclude the following tables from the export of table definitions and table data:

    • test1

    • All tables with a table name starting with test

    • All tables with a table name ending with test

    • All tables with a table name starting with te and ending with st

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

    Excludes the specified data types from the export of data.

  • --add-extra-message

    Exports additional information in table definitions. For example, when you specify the --add-extra-message option on the CLI to export table definitions, OBDUMPER exports the name of the table group to which each table belongs. By default, OBDUMPER does not export the additional information contained in table definitions.

    Notice

    If you specify the --add-extra-message option to export table definitions, OBDUMPER exports the name of the table group to which each table belongs. 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

    Generates an empty file in specific export scenarios. If you specify the --partition option to export data from a specific partition or the --where option to export data that meets the specified query condition, you can also specify the --retain-empty-file option to generate an empty file when the partition or the query result set is empty.

• Allowlist- and blocklist-based column filtering

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

    Includes the specified columns in the export of table data.

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

    Excludes the specified columns from the export of table data. Fuzzy match on column names is not supported.

    Notice

    • The letter case of the specified column name must be the same as that of the column name in the table definition.
    • The control file cannot take effect together with --exclude-column-names, as the control file incorporates the functionality of --exclude-column-names.

  • --where 'where_condition_string'

    Exports data that satisfies the specified conditions. This option can only be used with a data format option and cannot be used with the --query-sql option.

    If you also specify the --retain-empty-file option while the --where option is specified to export data that meets the query condition, an empty file can be generated when the query result set is empty.

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

    Exports data of specified partitions. The option specifies the partition names. Separate multiple partition names with commas (,). This option can only be used with a data format option and cannot be used with the --query-sql option.

    Notice

    • When you specify a partition to export, you must specify the subpartition name for a subpartitioned table. You cannot export a partition by specifying only the partition name. For a template-based subpartition, its name is in the format of partition name+s+subpartition name.
    • When you specify the --partition option on the CLI to export data in a partition, you need to specify the name of the subpartition for a composite partitioned table. OBDUMPER cannot export data in a partition of a composite partitioned table. If the specified partition name does not exist, OBDUMPER returns an error.
    • If you also specify the --retain-empty-file option while the --partition option is specified to export data from a specific partition, an empty file can be generated when the partition is empty.

  • --query-sql

    • --query-sql '<select_statement>': directly specifies the query statement, for example, --query-sql 'select c1,c2 from test where c1 is not null'.

      This syntax exports the result set of a custom query statement. This option cannot be used with the --partition or --where option. You must ensure the correctness and query performance of the custom query statement. When you export the result set of a large query, you may wait for a long time before the database responds. To preprocess data to be exported by using a control file, you must use the --table and --ctl-path options. The --table option must be set to the table name exactly the same as that specified in --ctl-path. If preprocessing is not required, you can specify the --table option as any table name.

    • --query-sql 'file://xxxx': specifies the file that stores the query statement, for example, --query-sql 'file:///home/admin/t1.sql'.

      OBDUMPER identifies the protocol header file:// and directly reads the query statement from the file. You can use this syntax if the query statement is excessively long. You can specify only one SQL statement in a file.

  • --exclude-virtual-columns

    Specifies not to export the data of generated columns. By default, the data of generated columns is exported.

  • --enable-hidden-pk

    Specifies whether to use a hidden primary key __pk_increment for a table without a primary key to accelerate export. If you specify this option in OceanBase Database earlier than V4.0.0, you must create a special user to export data. Here is an example:

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

    Notice

    We recommend that you set a local index for a partitioned table in OceanBase Database V3.x. The syntax is create index "your index" on `your table` ("__pk_increment") local;.

  • --fetch-size 'int_num'

    The number of rows read from the database cursor in the Oracle compatible mode of OceanBase Database. The default value is 1000. For more information, see FetchSize.

    Note

    To use this option, you must install the OceanBase Connector/J driver.

Error handling

• --retry

  Resumes the export task from the location of the last export failure.

  Notice

  The dump.ckpt file is a savepoint file generated when the tool runs and is located in the directory specified by the -f option. You cannot use this option if the dump.ckpt file does not exist.

• --weak-read

  Exports data from the follower replica. For more information, see Weak-consistency read.

• --max-file-size int_num

  The maximum size of data that a process can export. Data export stops when the size of the export file reaches the limit.

• --skip-check-dir

  Skips checking on whether the data export directory is empty. When the export directory is not empty, the tool stops exporting.

  Note

  • If this option is not specified, OBDUMPER checks whether the export directory is empty. If the export directory is not empty, OBDUMPER returns an error and exits.
  • If this option is specified, OBDUMPER skips the check. In this case, an exported file may overwrite an existing file with the same name in this directory.

• --remove-newline

  Forcibly deletes carriage returns or line breaks in the data before the data export. For example, ***\r***, ***\n***, and \r\n are deleted. This option only modifies the data retrieved to the memory and does not modify the data in the source table. This option can be used only with the --cut option.

  Notice

  If data in the source table contains carriage returns or line breaks, the data exported with this option specified is inconsistent with that in the source table. Before you use this option, make sure that the removal of \r, \n, and \r\n does not affect your business. If you do not need to delete carriage returns or line breaks in the data, to avoid data inconsistency, do not specify this option in the command.

• --snapshot

  Exports data of a historical version. This option can be used only with a data format option. You can export data of a historical version to ensure the global consistency of the exported data. If this option is not specified, the real-time data exported from the memory may not be a globally consistent data snapshot.

Performance options

• --page-size int_num

  The page size, namely, the number of rows queried at a time. The default value is 1000000, which specifies to query 1 million rows at a time. This option is used in OBDUMPER of a version later than V4.0.0.

• --thread int_num

  The number of concurrent threads allowed. This option corresponds to the number of export threads. The default value is the number of CPU cores multiplied by 2. If the number of CPU cores is greater than 16, the maximum value is 32. We recommend that you set the --thread option to a value within 4 for exporting the definitions of multiple database objects. High concurrency affects access to system tables in the sys tenant, resulting in timeout errors during data export.

• --block-size

  The block size for a file to be exported. This option is supported for the LONG and STRING data types. If the size of the data file to be exported exceeds the value of this option, the file is split into logical subfiles. When you specify this option, you do not need to explicitly specify the unit. The default unit is MB. The default value is 1024. This option does not take effect for ORC and Parquet formats. OBDUMPER V4.1.0 and later versions allow you to specify the maximum number of rows in each sub-file for data file splitting. The unit of --block-size can be MB or ROW. For example, --block-size 256ROW indicates that the size of a file cannot exceed 256 rows, and --block-size 1024 or --block-size 1024MB indicates that the size of a file cannot exceed 1024 MB. The formats of 1024M and 1GB are not supported.

  Notice

  When you use --block-size, you must specify -f as a directory.

• --parallel-macro int_num

  The number of macroblocks that can be processed by each export thread. The default value is 8.

• --mem

  Supported in OBDUMPER V4.3.2 and later. This option specifies the memory size of the JVM. Valid units are K, M, G, and T. The default value is 4G.

Other options

• --session-config

  Specifies the connection configuration file. The program package provides a default configuration file named session.config.json in the <root directory of the tool>/conf directory. It takes effect without modification. We recommend that you specify this option if you want to load multiple connection configurations by using one program package.

Quick reference table of options