Command-line options

OBDUMPER specifies the information to be exported by using command-line options. For more information about options and examples, see Option list and Examples.

Options

Option styles

OBDUMPER supports Unix and GNU option styles.

• In the Unix style, a single hyphen (-) is used before the option, and the option consists of a single character. For example: ps -e. In this style, you can omit the space between an option and its parameter. For example: -p******.

• In the GNU style, double hyphens (--) are used before the option, and the option can be a single character or a string. For example: ps --version. In this style, a space must be placed between an option and its parameter. For example: --table 'test'.

Option categories

The options of the OBDUMPER command can be classified into basic options and advanced options.

• Basic options are commonly used options of OBDUMPER, including connection options, feature options, and other options.

• Advanced options include feature options and performance options.

Required options

To use OBDUMPER to export data, you must specify the connection options, file 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

Here, -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

To read from and write to OceanBase Database, you must run the OBDUMPER command with the following options.

Export types

Feature options

File formats

Compress and export

OBDUMPER V4.3.0 and later supports to compress and export CSV, CUT, POS, and SQL files. You can use the following command-line options to configure it.

• --compress

  A boolean value that specifies whether to compress when exporting the file.

• --compression-algo

  A string that specifies the compression format. Valid values: zstd, zlib, gzip, and snappy. Default value: zstd.

• --compression-level

  An integer that specifies the compression level for the compression format. The supported compression levels vary by compression format:

  • zstd: 1 to 22. Default value: 3.

  • zlib: -1 to 9. Default value: -1.

  • gzip and snappy: not supported.

Database object types

• --all

  Identifies that all database object definitions and table data are to be exported. You can use this option in combination with other database object types such as --trigger and --view. For more information about this option and other database object types, see the descriptions of the corresponding options.

  • When used with the --ddl option, it indicates that all database object definitions are to be exported. For database object types such as --trigger that apply only to the Oracle compatible mode of OceanBase Database, if you specify the --all --ddl options in the MySQL compatible mode of OceanBase Database, you still cannot export trigger DDL statements.

  • When used with the --csv, --sql, or --cut option, it indicates that all table data are to be exported in the specified format. If you want to export all database object definitions and table data, you can specify the --all or --ddl option in combination with any data format option.

  Notice

  The --all option is mutually exclusive with any database object options. If you specify both the --all option and any database object option, the --all option will take precedence.

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

  Identifies that the definition of a table group is to be exported. It is similar to the --table option except that data cannot be exported.

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

  Identifies that the definition of a table or table data is to be exported. When used with the --ddl option, it indicates that only the table definition is to be exported. When used with any data format option, it indicates that only table data are to be exported. You can export multiple tables at a time. The table names must be separated with commas (,). By default, table names exported from a tenant of the Oracle compatible mode of OceanBase Database are in uppercase, and those exported from a tenant of the MySQL compatible mode of OceanBase Database are in lowercase. For example, in the Oracle compatible mode of OceanBase Database, --table 'test' and --table 'TEST' both refer to the TEST table. In the MySQL compatible mode of OceanBase Database, --table 'test' and --table 'TEST' both refer to the test table. If you want to distinguish between uppercase and lowercase letters, enclose the table name in square brackets ([ ]). For example, --table '[test]' refers to the test table, and --table '[TEST]' refers to the TEST table. If you specify an asterisk (*) for the table name, it indicates that all table definitions or data are to be exported.

  Note

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

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

  Identifies that the definition of a view is to be exported. It is similar to the --table option except that data cannot be exported.

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

  Identifies that the definition of a trigger is to be exported. It is similar to the --table option except that data cannot be exported. It applies only to the Oracle compatible mode of OceanBase Database.

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

  Identifies that the definition of a sequence is to be exported. It is similar to the --table option except that data cannot be exported.

  Notice

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

  The --sequence-policy option specifies the value of the start value. Valid values: restart and preserve. Default value: preserve.

  • The default value or the value preserve specifies that the start value exported will be the current value of the sequence.

  • The value restart specifies that the start value exported will be the original definition of the sequence.

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

  Identifies that the definition of a synonym is to be exported. It is similar to the --table option except that data cannot be exported. It applies only to the Oracle compatible mode of OceanBase Database.

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

  Identifies that the definition of a type is to be exported. It is similar to the --table option except that data cannot be exported. It applies only to the Oracle compatible mode of OceanBase Database.

  Note

  Only OceanBase Database V2.2.77 and later versions of the Oracle compatible mode support the export of type definitions.

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

  Identifies that the definition of a type body is to be exported. It is similar to the --table option except that data cannot be exported. It applies only to the Oracle compatible mode of OceanBase Database.

  Note

  You must use this option in combination with the --type option. The exported type body file is saved in the type file.

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

  Identifies that the definition of a package is to be exported. It is similar to the --table option except that data cannot be exported. It applies only to the Oracle compatible mode of OceanBase Database.

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

  Identifies that the definition of a package body is to be exported. It is similar to the --table option except that data cannot be exported. It applies only to the Oracle compatible mode of OceanBase Database.

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

  Identifies that the definition of a function is to be exported. It is similar to the --table option except that data cannot be exported.

  Note

  Functions defined in a tenant of the Oracle compatible mode of OceanBase Database of all versions can be exported. In the MySQL compatible mode of OceanBase Database, functions defined in a tenant of V2.2.30 and later can be exported.

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

  Identifies that the definition of a stored procedure is to be exported. It is similar to the --table option except that data cannot be exported.

  Note

  StoredProcedure definitions in a tenant of the Oracle compatible mode of OceanBase Database of all versions can be exported. In the MySQL compatible mode of OceanBase Database, stored procedure definitions in a tenant of V2.2.30 and later can be exported.

Storage path

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

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

  Note

  OBDUMPER V4.2.7 and later versions allow you to specify -f as the specific file name when you export a single file.

  • When you specify --query-sql, it will replace the default file name CUSTOM_SQL.
  • The log directory will be 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. 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 OSS Bucket.
  path	The resource path of the storage space. The path must start with /.
  parameters	The parameters required for the request. parameters can be a single key or key-value.

  The following table describes the supported parameters.

  Parameter	Required	Description	Supported storage types	Supported from OBDUMPER version
  endpoint	Yes	The endpoint of the region where the host is located. You can access OSS, S3, COS, and OBS services via the domain name endpoint. Example: oss://ssmp-xxxx-xxxx/test?endpoint=oss-cn-shenzhen-internal.aliyuncs.com	OSS/S3/COS/OBS	OSS: V4.2.0 S3: V4.2.5 COS and OBS: V4.3.0
  region	Yes	The endpoint of the host, which indicates the physical location where the bucket is located.	OSS, S3, COS, and OBS	OSS and S3: V4.2.0 COS and OBS: V4.3.0
  storage-class	Yes	The Amazon S3 storage class.	S3	4.2.0
  access-key	Yes	The access account for the storage.	OSS, S3, COS, and OBS	OSS and S3: V4.2.0 COS and OBS: V4.3.0
  secret-key	Yes	The access key for the storage.	OSS, S3, COS, and OBS	OSS and S3: V4.2.0 COS and OBS: V4.3.0

  Here are some examples:

  • Export data to a local disk

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

  • Export data to S3

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

  • Export data to OSS

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

  • Export data to 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 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 where control files are stored on the local disk. Control files contain built-in processing functions. The functions preprocess data before the data is exported. For example, the functions can convert data to uppercase or lowercase or check whether data is null. For more information about control files, see Data processing. When you use 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'

  The output directory of OBDUMPER running logs. By default, if you do not specify this option in the command line, OBDUMPER running logs will be output to the directory specified by -f. In most cases, you do not need to use redirection to output logs.

• --no-nested-dir

  When you specify this option, OBDUMPER exports all files to the directory specified by -f and does not generate any subdirectory.

• By default, when OBDUMPER exports data to object storage, it uses the local file system specified by --tmp-path as the temporary directory to store file blocks. OBDUMPER allows you to modify the following parameters in the running script to adapt to your actual export environment.

  • upload.buffer.type

    The buffer type. Valid values: disk and bytebuffer. The default value is disk, which stores file blocks in the disk. This setting is limited by the maximum available disk space. bytebuffer stores file blocks in memory. This setting provides high performance but consumes a large amount of JVM heap memory.

  • upload.buffer.size

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

  • upload.active.blocks

    The maximum number of file blocks that can exist simultaneously for each write thread, which may be uploading or waiting in the queue. The default value is 2. If the number of file blocks exceeds this value, the program will block until a file block is uploaded and removed from the queue.

    The --thread option limits the maximum number of write threads. You can use the formula thread * active.blocks * buffer.size to estimate the space occupied by the buffer when the program is running.

Other options

• -H, --help

  Shows the help message and exits.

• -V, --version

  Shows the version number and exits.

Advanced options

Feature options

Timestamp formats

• --flashback-timestamp 'timestamp_string'

  This option is used to export data after the flashback timestamp. This option can be used in combination with any data format option, but cannot be used with the --query-sql option. This option applies only to OceanBase Database in Oracle compatible mode.

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

  This option is used to set the nls_date_format session variable in OceanBase Database in Oracle compatible mode. It does not specify the format for exporting DATE data. Default value: YYYY-MM-DD HH24:MI:SS.

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

  This option is used to set the nls_timestamp_format session variable in OceanBase Database in Oracle compatible mode. It does not specify the format for exporting TIMESTAMP data. Default value: YYYY-MM-DD HH24:MI:SS:FF9.

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

  This option is used to set the nls_timestamp_tz_format session variable in OceanBase Database in Oracle compatible mode. It does not specify the format for exporting TIMESTAMP WITH TIME ZONE data. Default value: YYYY-MM-DD HH24:MI:SS:FF9 TZR.

• --date-value-format

  This option is used to set the export format for DATE data. In MySQL compatible mode of OceanBase Database, the default format for DATE data is yyyy-MM-dd. In Oracle compatible mode of OceanBase Database, the default format for DATE data is yyyy-MM-dd HH:mm:ss.

  Note

  • This option can be used only in combination with the --csv or --cut option.
  • In MySQL compatible mode of OceanBase Database, if the value of a DATE column is NULL, this option cannot be used to set the export format for the DATE data.

• --time-value-format

  This option is used to set the export format for TIME data. In MySQL compatible mode of OceanBase Database, the default format for TIME data is HH:mm:ss, and the precision is the same as that defined in the table.

  Note

  • This option can be used only in combination with the --csv or --cut option.
  • In MySQL compatible mode of OceanBase Database, if the value of a TIME column is NULL, this option cannot be used to set the export format for the TIME data.

• --datetime-value-format

  This option is used to set the export format for DATETIME data. In MySQL compatible mode of OceanBase Database, the default format for DATETIME data is yyyy-MM-dd HH:mm:ss, and the precision is the same as that defined in the table.

  Note

  • This option can be used only in combination with the --csv or --cut option.
  • In MySQL compatible mode of OceanBase Database, if the value of a DATETIME column is NULL, this option cannot be used to set the export format for the DATETIME data.

• --timestamp-value-format

  This option is used to set the export format for TIMESTAMP data. In MySQL or Oracle compatible mode of OceanBase Database, the default format for TIMESTAMP data is yyyy-MM-dd HH:mm:ss.SSSSSS, and the precision is the same as that defined in the table.

  Note

  • This option can be used only in combination with the --csv or --cut option.
  • In MySQL compatible mode of OceanBase Database, if the value of a TIMESTAMP column is NULL, this option cannot be used to set the export format for the TIMESTAMP data.

• --timestamp-tz-value-format

  This option is used to set the export format for TIMESTAMP WITH TIME ZONE data. In Oracle compatible mode of OceanBase Database, the default format for TIMESTAMP WITH TIME ZONE data is yyyy-MM-dd HH:mm:ss.SSSSSS, and the precision is the same as that defined in the table.

  Note

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

• --timestamp-ltz-value-format

  This option is used to set the export format for TIMESTAMP WITH LOCAL TIME ZONE data. In Oracle compatible mode of OceanBase Database, the default format for TIMESTAMP WITH LOCAL TIME ZONE data is yyyy-MM-dd HH:mm:ss, and the precision is the same as that defined in the table.

  Note

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

• --preserve-zero-datetime

  This option is used to retain the original format of zero values of DATE and TIME types during export. If you specify this option, non-null constraints, the date and time type column values are exported as 0; for nullable columns, the values NULL are still exported as NULL. This option can be used to set the DATE, DATETIME, and TIMESTAMP types in MySQL compatible mode of OceanBase Database.

Allowlist and blocklist filtering

• Table allowlists and blocklists

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

    Specifies to exclude the specified tables when you export table definitions or data. Table names can be matched by using wildcards.

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

    In this example, the following tables are excluded when you export 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 to exclude the specified data types when you export data.

  • --add-extra-message

    Specifies to add additional information to the exported table definitions. For example, if you specify the --add-extra-message option, obdumper exports the name of the table group to which each table belongs when you export table definitions. By default, obdumper does not export additional information in table definitions.

    Notice

    If you specify the --add-extra-message option, obdumper exports the name of the table group to which each table belongs when you export table definitions. This option depends on the sys tenant privileges. If obdumper does not have the sys tenant privileges, do not specify this option.

  • --retain-empty-files

    Specifies to generate empty files in specific scenarios when files are exported. For example, when you specify the --partition option to export data of specific partitions or specify the --where option to export data that meets specific conditions, you can also specify the --retain-empty-file option to generate empty files when the specified partitions are empty or the result set of the conditional query is empty.

• Column allowlists and blocklists

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

    Specifies to export data by using the specified column names.

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

    Specifies to exclude the specified columns when you export data. Column names cannot be matched by using wildcards.

    Notice

    • The specified column names must be consistent with the column names in the table definition in terms of case.
    • The control file cannot be used together with the --exclude-column-names option. The functionality of the --exclude-column-names option is included in the control file.

  • --where 'where_condition_string'

    Specifies to export data that meets specific conditions. You can use this option only with any of the data format options, but not with the --query-sql option.

    If you specify the --where option to export data that meets specific conditions, and also specify the --retain-empty-file option, an empty file can be generated when the result set of the conditional query is empty.

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

    Specifies to export data of specific partitions. The value of this option is a comma-separated list of partition names. You can use this option only with any of the data format options, but not with the --query-sql option.

    Notice

    • When you export data of specific partitions, you must specify the names of subpartitions for subpartitioned tables. You cannot export data by using the names of only the partitions. If template subpartitions are involved, the name of a subpartition is in the format of: name of the partition+s+name of the subpartition.
    • When you use the --partition option to export data in a partition, you must specify the names of subpartitions for composite partitioned tables. In this case, obdumper cannot export data in the partition. If the specified partition name does not exist, obdumper returns an error.
    • If you specify the --partition option to export data in specific partitions and also specify the --retain-empty-file option, an empty file can be generated when the specified partitions are empty.

  • --query-sql

    • You can directly specify a query statement --query-sql '<select_statement>'. For example, --query-sql 'select c1,c2 from test where c1 is not null'.

      Specifies to export the data that meets the conditions specified in the query statement. You can use this option only with any of the data format options, but not with the --partition or --where option. You must ensure the correctness and performance of the query statement. If you want to export the result set of a large query statement, the response time from the database may be relatively long. If you need to use a control file for preprocessing when you export data, you must specify the --table and --ctl-path options. The table name specified with the --table option must be consistent with the file name specified with the --ctl-path option in terms of case. If no preprocessing is required, you can specify any table name with the --table option.

    • You can also specify the file that stores the query statement --query-sql 'file://xxxx'. For example, --query-sql 'file:///home/admin/t1.sql'.

      Obdumper recognizes the file:// protocol header and reads the SQL query statement from the file. You can use this option to support a single SQL statement of unlimited length. It does not support writing multiple SQL statements in one file.

  • --exclude-virtual-columns

    Specifies not to export data of generated columns (default is to export data of generated columns).

  • --enable-hidden-pk

    Specifies to use the hidden primary key __pk_increment for tables without primary keys to improve the export speed. If you use this option in OceanBase Database of a version earlier than V4.0.0, you must use a special user for the export. Example:

    # In the MySQL compatible mode of OceanBase Database, you can use the root user to create a special user and grant privileges to it.
    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, you can use the SYS user to create a special user and grant privileges to it.
    create user '__OCEANBASE_INNER_DRC_USER'@'%' IDENTIFIED BY u*******;
    grant ALL to '__OCEANBASE_INNER_DRC_USER';
    

    Notice

    If you use this option with partitioned tables in OceanBase Database of a version earlier than V3.x, we recommend that you set the LOCAL index. The syntax is: create index "your index" on `your table` ("__pk_increment") local;

  • --fetch-size 'int_num'

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

    Note

    You need to install OceanBase Connector/J.

Error handling

• --retry

  Allows you to continue the export task from the point of failure of the last export.

  Notice

  The dump.ckpt file is a savepoint file generated by the tool during runtime. The path of this file is in the directory specified by -f. This option cannot be used if the dump.ckpt file does not exist.

• --weak-read

  Allows you to export data from a standby replica. For more information about weak-consistency read, see Weak-consistency read.

• --max-file-size int_num

  Specifies the maximum amount of data, in bytes, that can be exported by one process. When the exported data reaches this limit, the export task of this process is stopped.

• --skip-check-dir

  Specifies to skip the check whether the export directory is empty. If the directory is not empty, the program stops exporting data.

  Note

  • If this option is not specified, OBDUMPER checks whether the directory is empty. If the directory is not empty, OBDUMPER returns an error and exits.
  • If this option is specified, OBDUMPER skips the check whether the directory is empty. However, the data in files with the same name as the exported files will be overwritten.

• --remove-newline

  Specifies to forcibly remove carriage returns and line breaks from the data during export. Carriage returns and line breaks include ***\r***, ***\n***, and ***\r\n***. This option modifies only the data read into memory and does not affect the source data. This option can be used only with the --cut option.

  Notice

  If the source data contains carriage returns or line breaks, the data exported with this option specified is inconsistent with the source data. Make sure that removing carriage returns and line breaks, represented by \r, \n, and \r\n, will not affect your business. If you do not want to remove carriage returns or line breaks, do not specify this option to avoid inconsistent business data.

• --snapshot

  Specifies to export historical data. This option can be used only with any of the data format options. Exporting historical data ensures global consistency of the exported data. If this option is not specified in the command line, the real-time data in memory may not be a global-consistent snapshot.

Performance options

• --page-size int_num

  In OBDUMPER V4.0.0 and later, specifies the number of rows returned in each query for a task, with a default value of 1000000. For example, 1000000 rows are returned in each query.

• --thread int_num

  Specifies the number of concurrent threads. This option directly corresponds to the number of export threads. The default value is 2 × CPU. If the CPU number exceeds 16, the default maximum value is 32. We recommend that you set the value of the --thread option to a maximum of 4 when you export multiple database objects. A large concurrency will affect access to system tables in the sys tenant and cause timeout errors during export.

• --block-size

  Specifies the threshold for splitting file blocks. This option applies to the LONG and STRING data types. If the size of the exported data file exceeds this threshold, other logical subfiles are generated sequentially. If you use this option, you do not need to specify the unit. The default unit is MB. The default value is 0, which indicates an unlimited size and generates only one file for an object. This option does not apply to the ORC and Parquet formats. In OBDUMPER V4.1.0 and later, you can also split file blocks by row. If you specify the value of --block-size in this case, the value must be a digit followed by ROW or MB. ROW indicates the number of rows, and MB indicates the storage size. For example, --block-size 256ROW indicates that a file contains no more than 256 rows. --block-size 1024 or --block-size 1024MB indicates that a file contains no more than 1024 MB. The following formats are not supported: 1024M or 1GB.

  Notice

  If you use the --block-size option, you must specify the value of the -f option as a path address.

• --parallel-macro int_num

  Specifies the number of macroblocks that a thread can process. The default value is 8.

• --mem

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

Other options

• --session-config

  Specifies the connection configuration file. A default configuration file is provided in the configuration file directory: /<root directory of the tool>/conf/session.config.json. This option is not required. We recommend that you specify this option only when you want to load multiple connection configurations to the same configuration file directory.

• --retain-schema

  Retains schema information for the exported tables and synonyms. By default, the exported DDL statements do not contain schema information and include only the table names. If you specify the --retain-schema option, the schema names are retained in the schema.table format.

Options