OBDUMPER allows you to export required data by specifying command-line options.
Sample command
The following sample code provides an example on how to export data. For more information, see the following Options table and the Scenarios topic.
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 name> -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 be connected to. | |
| -P (--port) | Yes | The port number of the OBProxy or OBServer to be connected to. | |
| -c (--cluster) | No | The cluster name of the cluster. 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. | |
| --skip-check-dir | No | By default, the directory to which data is exported must be empty. You can specify this option to skip the empty-directory check. | |
| --sys-user | No | The username of a user under the sys tenant. Default value: root@sys. | |
| --sys-password | No | The password of a user under the sys tenant. 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. | |
| --csv | No | Exports CSV files. By default, the file name extension is.csv. We recommend that you export files in the CSV format. | |
| --sql | No | Exports SQL files. By default, the file name extension is.sql. | |
| --ddl | No | Exports DDL files. By default, the file name extension is -schema.sql. | |
| --all | No | Exports all database objects. By default, database objects 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. | |
| --drop-object | No | Specifies whether to prepend a DROP statement when DDL statements are exported. |
|
| --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. 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 an exported file, 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, the value of this option is not limited. Example: --max-file-size 512. |
|
| --thread | No | The number of threads for data export. Default value: CPU × 2. | |
| --parallel-macro | No | The number of macroblocks that a thread can read. The value of this option may affect 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 substitute character of NULL. For the CSV format, the default value is \N. By default, no character is used to replace NULL in 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 a table before the export. | |
| --exclude-column-names | No | The columns that do not need to be exported in a table. You must use this option together with the --table option. Example: --table 'test' --exclude-column-names 'c3,c4' |
|
| --skip-check-dir | No | By default, the directory to which data is exported must be empty. You can specify this option to skip the empty-directory check. | |
| --oss-endpoint | No | The endpoint of the region where a 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 Object Storage Service (OSS) bucket. | |
| --secret-key | No | The secret key used to access the OSS bucket. | |
| --bucket-uri | No | The URI of the bucket. URI format: oss://bucket/key1/key2 Example: oss://oceanbasepublic/tools/obdumper | |
| --include-column-names | No | The fields to be exported and the export order. | |
| --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. |
|
| --file-name | No | Merges multiple data files exported from a table into one file. This option reduces the number of exported data files. * Method 1: You can use the --file-name option to a specific file name. This indicates that multiple subfiles exported from the specified table are merged into one file. In this case, you must use the --file-name option together with the --table option, and the --table option must be used to specify only a single table. If you use the --table option to specify multiple tables, the --file-name option does not take effect. Examples: * --table 'test' --file-name 'datafile.txt' * --table 'test' --file-name '/home/data/datafile.txt' * Method 2: You can use the --file-name option to specify an absolute path. This indicates that the data files of one or more tables to be exported are merged by table. One table corresponds to one data file. Examples: --table '*' --file-name '/home/data/' Notice The folder that stores data files must not contain files with the same name. |
|
| --add-extra-message | No | You can use the option together with the --ddl option to obtain TABLEGROUP information. After you specify this option, TABLEGROUP information is appended to table schemas that are exported. |
|
| --weak-read | No | Exports data from the replica of an OceanBase cluster. Implementation principle: Add the following content to the SQL template: /*+READ_CONSISTENCY(WEAK)*/ sql select /*+READ_CONSISTENCY(WEAK)*/ [field...] from table Note OceanBase Database V3.1.x supports OBServer and OBProxy. |
|
| --exclude-table | No | * If you specify a table name, the schema and data of the specified table are not exported. Example: --exclude-table 'a,b,c'. * If you do not specify a table, all table schemas and data are exported. Example: --exclude-table ''. * You can use asterisks (*) for fuzzy matching. For example, you can specify --exclude-table '*ab*' to exclude tables whose names contain ab. |
|
| --query-sql | No | * Exports data based on a custom SQL query. The result set of the SQL query can be exported to files in the CUT, CSV, and SQL formats. If the result set is empty, no data is exported. Example: --cut --query-sql 'select t1.col1,t2.col2 from t1 left join t2 on t1.id=t2.id'. Notice The CSV format supports delimiters, column delimiters, and line feeds. * You can use the --query-sql and --ctl-path options to cleanse queried data based on the column names or column sequence. You can also export data into files in the following formats: CSV, CUT, and SQL. Example: --query-sql 'select * from table' --ctl-path '/home/admin/test.path'. |
|
| --retain-empty-files | No | Exports empty tables. Note The data file that corresponds to an empty table is also empty. |