Command-line options

OBDUMPER allows you to specify the information required for export in command-line options. For more information about the options and their scenarios and examples, see Options and Scenarios and 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 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 statement:

$./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 an OceanBase database only after connecting to the database. You can connect to an OceanBase database by specifying the following options:

Export type

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

  • zlib and gzip: -1 to 9.

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

Database object types

• --all

  Exports all database object definitions and table data. When this option is used in combination with the --ddl option, all database object definitions are exported. When this option is used in combination 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 in combination with the --ddl option, only table definitions are exported. When this option is used in combination with any data format option, only table data is exported. To specify multiple tables, separate the table names with commas (,). By default, for OceanBase Database in Oracle mode, the table names are in uppercase, and for OceanBase Database in MySQL mode, the table names are in lowercase. For example, for OceanBase Database in Oracle mode, both --table 'test' and --table 'TEST' indicate the table named TEST. For OceanBase Database in MySQL mode, both --table 'test' and --table 'TEST' indicate the table named test. If table names are case-sensitive, enclose them 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 is exported.

  Note

  OBDUMPER V4.1.0 and later allow you to export temporary table definitions in OceanBase Database in Oracle mode.

• --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 for OceanBase Database in Oracle mode.

• --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 Oracle mode of OceanBase Database. OBDUMPER V4.1.0 and later apply to both the MySQL and Oracle modes of OceanBase Database.

• --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 for OceanBase Database in Oracle mode.

• --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 for OceanBase Database in Oracle mode.

  Note

  You can export type definitions only in Oracle 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 for OceanBase Database in Oracle mode.

  Note

  This option must be used in combination 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 for OceanBase Database in Oracle mode.

• --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 for OceanBase Database in Oracle mode.

• --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 Oracle mode and OceanBase Database of V2.2.30 or later in MySQL 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 Oracle mode and OceanBase Database of V2.2.30 or later in MySQL 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],...
  

  Component	Description
  scheme	The storage scheme. Valid values: Alibaba Cloud OSS, Amazon S3, Tencent Cloud COS, and Huawei Cloud OBS. If the scheme is not Alibaba OSS, Amazon S3, Tencent Cloud COS, or Huawei Cloud OBS, an error will be 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 OSS 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 multiple key-value pairs.

  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 Amazon S3, Tencent Cloud COS, or Huawei Cloud 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	V4.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 the use of control files, see Data processing. When you specify this option on the command line, enclose the value in single quotation marks (' '), 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 runtime 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 only be used in combination with a data format option and cannot be used in combination with the --query-sql option. This option is supported only for OceanBase Database in Oracle mode.

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

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

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

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

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

  The nls_timestamp_tz_format session variable in Oracle mode of OceanBase Database. This option does not indicate to export data of the TIMESTAMP WITH TIME ZONE data type in the specified format. Default value: 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 MySQL mode of OceanBase Database and in the yyyy-MM-dd HH:mm:ss format in Oracle mode of OceanBase Database.

  Note

  • This option can be used only in combination with the --cut or --csv option.
  • In MySQL 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 MySQL mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

  • This option can be used only in combination with the --cut or --csv option.
  • In MySQL 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 MySQL mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

  • This option can be used only in combination with the --cut or --csv option.
  • In MySQL 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 MySQL and Oracle modes of OceanBase Database. The precision is the same as that in the table definition.

  Note

  • This option can be used only in combination with the --cut or --csv option.
  • In MySQL 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 Oracle mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

  This option can be used only in combination with the --cut or --csv 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 Oracle mode of OceanBase Database. The precision is the same as that in the table definition.

  Note

  This option can be used only in combination with the --cut or --csv 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 column are still exported as NULL. You can specify this option for DATE, DATETIME, and TIMESTAMP data types in MySQL 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,

    such as --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 the 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 command line 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 privileges of the `sys` tenant. If OBDUMPER does not have 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. Data of the columns is exported in the specified order.

  • --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.

  • --where 'where_condition_string'

    Exports data that satisfies the specified conditions. This option can only be used in combination with a data format option and cannot be used in combination 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 in combination with a data format option and cannot be used in combination 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 command line 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 'select_statement'

    Exports the result set of a custom query statement. This option cannot be used in combination 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 need to 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.

  • --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 MySQL mode of OceanBase Database, log on 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 Oracle mode of OceanBase Database, log on 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 Oracle mode of OceanBase Database. The default value is 1000. For more information, see OceanBase 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 about weak-consistency read, 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 will be 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 in combination with the --cut option.

  Notice

  If data in the source table contains carriage returns or line breaks, the data exported with this option specified will be inconsistent with that in the source table. Before using this option, ensure that the removal of \r, \n, and \r\n does not affect the 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 in combination 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. This option is used in OBDUMPER V4.0.0 and later. Default value: 1000000.

• --thread int_num

  The number of concurrent threads allowed. This option corresponds to the number of export threads. The default value is the value of 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 under the sys tenant, resulting in timeout errors during data export.

• --block-size int_num

  The size of a file to be exported. If the size of the data file to be exported exceeds the value of this option, the file will be split into logical sub-files. When specifying this option, you do not need to explicitly specify the unit. The default unit is MB. Default value: 1024MB. 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 256MB indicates that the size of a file cannot exceed 256 MB, and --block-size 256ROW indicates that the size of a file cannot exceed 256 rows.

• --parallel-macro int_num

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

Options