OBDUMPER allows you to export required data by specifying command-line options.
Sample command
The following command provides an example on how to export data. For more information, see the following option list and the Scenarios topic. Notice
If you connect OBDUMPER to an OceanBase cluster by using OceanBase DataBase Proxy (OBProxy), use the -h and -P options to specify the IP address and port number of the OBProxy host. Then, use the -c option to specify the name of the cluster to be connected to. If you directly connect OBDUMPER to an OBServer, you do not need to use the -c option to specify the name of the cluster to be connected to.
[admin@localhost]>./obdumper -h <host IP address> -P <port number> -u <username> -p <password> --sys-password <password of a user under the SYS tenant> -c <cluster> -t <tenant> -D <database> [--ddl] [--csv|--sql|--cut] [--all|--table 'table name'] -f<data directory>
Options
| Option | Required | Description |
|---|---|---|
| -h(--host) | Yes | The IP address of the OBProxy or OBServer to connect to. |
| -P(--port) | Yes | The port number of the OBProxy or OBServer to connect to. |
| -c(--cluster) | No | The cluster name of the database. You do not need to specify this option if OBDUMPER is directly connected to an OBServer. |
| -t(--tenant) | No | The tenant name of the user. |
| -u(--user) | Yes | The username that you use to log on to the database. |
| -p(--password) | No | The password that you use to log on to the database. |
| -D(--database) | Yes | The name of the database. |
| -f(--file-path) | Yes | The directory to which data is exported. |
| --sys-user | No | The username of the user under the SYS tenant. Default value: root@sys. |
| --sys-password | No | The password of the user specified by the --sys-user option. By default, this option is left empty. |
| --public-cloud | No | Uses the limited mode to export data. You do not need to specify the --sys-user option in limited mode. |
| --log-path | No | The directory to which log files are exported. |
| --file-encoding | No | The file encoding format. Default value: UTF-8. |
| --nls-date-format | No | The date and time format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS. |
| --nls-timestamp-format | No | The timestamp format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9. |
| --nls-timestamp-tz-format | No | The timestamp and timezone format in Oracle mode. Default value: YYYY-MM-DD HH24:MI:SS:FF9 TRZ. This option is supported in OBDUMPER V2.1.11 and later. |
| --csv | No | Exports files in the CSV format. We recommend that you use this option. Default file extension: .csv. |
| --sql | No | Exports files in the SQL format. Default file extension: .sql. |
| --ddl | No | Exports files in the DDL format. Default file extension: -schema.sql. |
| --all | No | Exports all database objects. By default, the database objects to be exported include tables, views, triggers, functions, stored procedures, sequences, and synonyms. |
| --table | No | The table to be exported. Separate multiple tables with commas (,). If you set this option to an asterisk (*), all tables in the database specified by the -D option are exported. |
| --view | No | The view to be exported. Separate multiple views with commas (,). If you set this option to an asterisk (*), all views in the database specified by the -D option are exported. |
| --trigger | No | The trigger to be exported. Separate multiple triggers with commas (,). If you set this option to an asterisk (*), all triggers in the database specified by the -D option are exported. |
| --function | No | The function to be exported. Separate multiple functions with commas (,). If you set this option to an asterisk (*), all functions in the database specified by the -D option are exported. |
| --procedure | No | The stored procedure to be exported. Separate multiple stored procedures with commas (,). If you set this option to an asterisk (*), all stored procedures in the database specified by the -D option are exported. |
| --sequence | No | The sequence to be exported. Separate multiple sequences with commas (,). If you set this option to an asterisk (*), all sequences in the database specified by the -D option are exported. |
| --synonym | No | The synonym to be exported. Separate multiple synonyms with commas (,). If you set this option to an asterisk (*), all synonyms in the database specified by the -D option are exported. |
| --type-body | No | The type body to be exported. Separate multiple type bodies with commas (,). If you set this option to an asterisk (*), all type bodies in the database specified by the -D option are exported. |
| --package | No | The program package to be exported. Separate multiple program packages with commas (,). If you set this option to an asterisk (*), all program packages in the database specified by the -D option are exported. |
| --package-body | No | The package body to be exported. Separate multiple package bodies with commas (,). If you set this option to an asterisk (*), all package bodies in the database specified by the -D option are exported. |
| --drop-object | No | Specifies whether to prepend a DROP statement when DDL statements are exported. |
| --pl-delimiter | No | The delimiter that separates multiple PL statements. Default value: $$ |
| --snapshot | No | Specifies whether to export snapshot data. You can export snapshot data after the last major compaction. |
| --page-size | No | The number of entries to return on a single page. Default value: 10000. |
| --where | No | The global conditions. If you specify this option, only data that meet the conditions can be exported. Example: --where 'age>16 and age<65'. |
| --partition | No | The partitions from which the data is exported. Example: -- partition 'p0,p1,p2'. |
| --flashback-scn | No | Exports data from the most recent valid system change number (SCN). This option is supported in OceanBase Database V2.2.70 and later. |
| --flashback-timestamp | No | Exports data from the most recent valid point in time. This option is supported in OceanBase Database V2.2.70 and later in Oracle mode. Example: --flashback-timestamp '2020-11-03 19:30:00'. |
| --block-size | No | The size of the file to be exported, in MB. Default value: 256. |
| --max-file-size | No | The maximum size of data that can be exported from a table, in MB. By default, no size limit is imposed. Example: --max-file-size 512. |
| --thread | No | The number of threads for data export. Default value: Number of CPU × 2. |
| --parallel-macro | No | The maximum number of macroblocks that a thread can read. The value of this option may affect the import performance. |
| --exclude-data-types | No | The type of data to skip during data export. For example, you can specify this option to skip the export of data of the BLOB type. |
| --retry | No | Specifies whether to resume data export from the last saved point. |
| --skip-header | No | Specifies whether to generate a header row for a CSV file. By default, the header row is generated. |
| --trail-delimiter | No | Deletes the last column delimiter if the data ends with column delimiters. |
| --null-string | No | The string to replace NULL. For the CSV format, the default value is \N. By default, no value is specified for other formats. |
| --empty-string | No | The escape character for an empty string (' '). Default value: \E. |
| --line-separator | No | The row delimiter. Default value: '\n'. |
| --column-separator | No | The column delimiter. Default value: ','. |
| --escape-character | No | The escape character. Default value: '\'. |
| --column-delimiter | No | Uses single quotation marks (') or double quotation marks ("). By default, single quotation marks (') are used. |
| --ctl-path | No | The directory where the control file is stored. The file name extension of control files is .ctrl. |
| --cut | No | Exports files in the CUT format in which data is separated by strings. |
| --column-splitter | No | The column delimiter. Different from the CSV format where the delimiter is a single character, this option allows you to use a string, which can have several characters, to separate data. |
| --distinct | No | Deduplicates data in the table before the export. |
| --exclude-column-names | No | The columns that do not need to be exported in the table. You must use this option together with the --table option. Example: --table 'test' --exclude-column-names 'c3,c4' |
| --query-sql | No | Exports data based on a custom SQL query statement. If the result set of an SQL query is empty, no data is exported. Data can be exported in the CUT and CSV formats. Example: --cut --query-sql 'select t1.col1,t2.col2 from t1 left join t2 on t1.id=t2.id'. |
| --skip-check-dir | No | Skips the check for the empty directory. By default, the export directory must be empty. |
| --oss-endpoint | No | The endpoint of the region where the Object Storage Service (OSS) bucket resides. For example, for the China (Hangzhou) region, the value must be https://oss-cn-hangzhou.aliyuncs.com. |
| --access-key | No | The account used to access the OSS bucket. |
| --secret-key | No | The secret key used to access the OSS bucket. |
| --bucket-uri | No | The URI of the bucket. Format: oss://bucket/key1/key2. Example: oss://oceanbasepublic/tools/obdumper |
| --exclude-table | No | * If you specify a table name, the schema and data of the specified table are not exported. This option does not support fuzzy matching. Example:--exclude-table 'a,b,c'. * If you do not specify a table, all table schemas and data are exported. Example: --exclude-table ''. |
| --include-column-names | No | The fields to be exported and the export order. |
| --file-name | No | * Specifies the name of the file to be generated after the file merge. Usage notes: * --file-name "filename.suffix": merges multiple sub files of a table file into one file. You cannot merge sub files of different tables into one file. * Example: --file-name "/xxx/xxx/.../filename.suffix" * --file-name: merges multiple sub files of a table file into one file. You cannot merge sub files of different tables into one file. * Specifies the name of the file to be exported. You must use this option together with the --table option. If multiple tables are specified by using the --table option, the --file-name option becomes invalid. Example: --table 'test' --file-name 'datafile.txt' Notice The folder to store the exported file must not contain a file with the specified name. |
| --remove-newline | No | This option is applicable to specific business scenarios. It is not a general option. After you specify the option for data export, it deletes the line feeds and carriage returns contained in the data. This option is available for exporting data from files in the CUT format. |