This topic describes how to quickly get started with OBDUMPER.
Step 1: Download the software
Note
OBDUMPER is available in only one unified edition in V4.2.1 and later, without providing the community and enterprise editions. You can download the software package from OceanBase Download Center.
Click here to download the package of the latest version and run the following commands to decompress the package:
unzip ob-loader-dumper-4.2.x-RELEASE.zip
cd ob-loader-dumper-4.2.x-RELEASE
Step 2: Configure the runtime environment
Notice
You must install Java 8 and configure the JAVA_HOME environment variable in the local environment. We recommend that you install JDK 1.8.0_3xx or later. For more information about environment configuration, see Prepare the environment.
A small JVM memory size may affect the performance and stability of the import and export features. You can use the --mem parameter to specify the JVM memory size, for example, --mem 4G.
Step 3: Create a database
Notice
When you use OBDUMPER for export, you must also create tables and insert data into the tables after you create a database.
Deploy an OceanBase cluster by using OceanBase Cloud Platform (OCP) or OceanBase Deployer (obd).
Create a test database.
Create a test table and insert data into the table.
Step 4: View configuration files
Configuration files of OBDUMPER include the connection configuration file session.config.json and log configuration file log4j2.xml.
Connection configuration file
The connection configuration file session.config.json stored in the {ob-loader-dumper}/conf directory configures database connection parameters. OBDUMPER builds a JDBC URL to connect to the target database based on the JDBC parameters in the connection configuration file. Then, it sequentially executes SQL statements for initialization in the established connection. You can modify JDBC parameters and SQL statements for initialization in the connection configuration file. Default connection configurations apply to most scenarios. However, in special cases, you must manually modify parameters to adapt to different OceanBase Database versions and extract, transform, and load (ETL) scenarios. For more information about the connection configuration file, see Connection settings.
Log configuration file
In the log configuration file log4j2.xml stored in the {ob-loader-dumper}/conf directory, you can view the log output path and log format, and adjust the log level during self-service troubleshooting. For more information, see the How do I customize log file names for an export job? section in FAQ.
Step 5: Export data
./obdumper -h 'IP address' -P'port' -u'user' -t'tenant' -c'cluster' -p'password' -D'database name' --table 'table name' --csv -f 'file path' --sys-password 'password of the sys tenant' --skip-check-dir
Note
This example exports only data. For more information, see Command-line options of OBDUMPER.
The following table describes the database information used in the examples.
| Database information | Example value |
|---|---|
| Cluster name | cluster_a |
| Host IP address of OceanBase Database Proxy (ODP) | xx.x.x.x |
| Port number of ODP | 2883 |
| Tenant name of the cluster | mysql |
Name of the root or proxyro user in the sys tenant |
**u*** |
Password of the root or proxyro user in the sys tenant |
****** |
| Name of the user (with read/write privileges) in the business tenant | test |
| Password of the user in the business tenant | ****** |
| Schema name | USERA |
Export DDL definition files
Scenario: Export all supported object definition statements in the USERA schema to the /output directory. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --ddl --all -f /output
Note
The --sys-user option is used to connect to a user with required privileges in the sys tenant. If the --sys-user option is not specified during the export, the default value --sys-user root is used. In OceanBase Database V4.0.0 and later, you do not need to specify this option. For more information, see OBDUMPER options.
Sample return result:
...
All Dump Tasks Finished:
---------------------------------------------------
| No.# | Type | Name | Count | Status |
---------------------------------------------------
| 1 | TABLE | table | 1->1 | SUCCESS |
---------------------------------------------------
Total Count: 4 End Time: 2023-04-28 15:32:49
...
Sample exported content:
View the table-schema.sql table in the {ob-loader-dumper}/output/data/chz/TABLE directory.
[xxx@xxx /ob-loader-dumper-4.2.0-RELEASE/bin]
$cat /home/admin/obloaderobdumper/output/data/chz/TABLE/table-schema.sql
create table if not exists `table` (
`id` int(11) comment 'table id',
`name` varchar(120) comment 'table name',
`type` varchar(128) not null default 'COLLABORATION' comment 'organization type, enum values: COLLABORATION, PRIVACY'
)
default charset=gbk
default collate=gbk_chinese_ci;
Export CSV data files
Scenario: Export all table data in the USERA schema to the /output directory in the CSV format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. For more information about CSV data file (.csv file) specifications, see RFC 4180. CSV data files store data in the form of plain text. You can open CSV data files by using a text editor or Excel.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --csv --table '*' -f /output
Sample return result:
...
All Dump Tasks Finished:
----------------------------------------------------
| No.# | Type | Name | Count | Status |
----------------------------------------------------
| 1 | TABLE | table | 4 | SUCCESS |
----------------------------------------------------
Total Count: 4 End Time: 2023-04-28 15:32:49
...
Sample exported content:
View the table.1.0.csv table in the {ob-loader-dumper}/output/data/chz/TABLE directory.
[xxx@xxx /ob-loader-dumper-4.2.0-RELEASE/bin]
$cat table.1.0.csv
'id','name','type'
1001,'xiaoning','COLLABORATION'
1002,'xiaohong','COLLABORATION'
1001,'xiaoning','COLLABORATION'
1002,'xiaohong','COLLABORATION'
Export SQL data files
Scenario: Export all table data in the USERA schema to the /output directory in the SQL format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. SQL data files (.sql files) store INSERT SQL statements. You can open SQL data files by using a text editor or SQL editor.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --sql --table '*' -f /output
Sample return result:
...
All Dump Tasks Finished:
----------------------------------------------------
| No.# | Type | Name | Count | Status |
----------------------------------------------------
| 1 | TABLE | table | 4 | SUCCESS |
----------------------------------------------------
Total Count: 4 End Time: 2023-04-28 15:32:49
...
Sample exported content:
View the table.1.0.sql table in the {ob-loader-dumper}/output/data/chz/TABLE directory.
[xxx@xxx /ob-loader-dumper-4.2.0-RELEASE/bin]
$cat table.1.0.sql
INSERT INTO `chz`.`table` (`id`,`name`,`type`) VALUES (1001,'xiaoning','COLLABORATION');
INSERT INTO `chz`.`table` (`id`,`name`,`type`) VALUES (1002,'xiaohong','COLLABORATION');
INSERT INTO `chz`.`table` (`id`,`name`,`type`) VALUES (1001,'xiaoning','COLLABORATION');
INSERT INTO `chz`.`table` (`id`,`name`,`type`) VALUES (1002,'xiaohong','COLLABORATION');
Export CUT data files
Scenario: Export all table data in the USERA schema to the /output directory in the CUT format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. To export a CUT data file, you must use |@| as the column delimiter string. A CUT data file (.dat file) uses a character or character string to separate values. You can open CUT data files by using a text editor.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --table '*' -f /output --cut --column-splitter '|@|' --trail-delimiter
Sample return result:
...
All Dump Tasks Finished:
----------------------------------------------------
| No.# | Type | Name | Count | Status |
----------------------------------------------------
| 1 | TABLE | table | 4 | SUCCESS |
----------------------------------------------------
Total Count: 4 End Time: 2023-04-28 15:32:49
...
Sample exported content:
View the table.1.0.dat table in the {ob-loader-dumper}/output/data/chz/TABLE directory.
[xxx@xxx /ob-loader-dumper-4.2.0-RELEASE/bin]
$cat table.1.0.dat
1001|xiaoning|COLLABORATION|
1002|xiaohong|COLLABORATION|
1001|xiaoning|COLLABORATION|
1002|xiaohong|COLLABORATION|
Export data files to Amazon S3
Scenario: Export all table data in the USERA schema to an Amazon Simple Storage Service (S3) bucket in the CSV format.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --csv --table '*' --skip-check-dir -f 's3://obloaderdumper/obdumper?region=cn-north-1&access-key=******&secret-key=******'
The -f 's3://obloaderdumper/obdumper?region=cn-north-1&access-key=******&secret-key=******' option specifies the storage URL. The following table describes the components of a storage URL.
| Component | Description |
|---|---|
s3 |
The S3 storage scheme. |
obloaderdumper |
The name of the S3 bucket. |
/obdumper |
The data storage path in the S3 bucket. |
region=cn-north-1&access-key=******&secret-key=****** |
The parameters required for the request.
|
Sample return result:
...
All Dump Tasks Finished:
----------------------------------------------------
| No.# | Type | Name | Count | Status |
----------------------------------------------------
| 1 | TABLE | table | 3 | SUCCESS |
----------------------------------------------------
Total Count: 3 End Time: 2023-05-10 18:46:26
...
Export data to Alibaba Cloud OSS
Scenario: Export all table data in the USERA schema to an Alibaba Cloud Object Storage Service (OSS) bucket in the CSV format.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --csv --table '*' --skip-check-dir -f 'oss://antsys-oceanbasebackup/backup_obloader_obdumper/obdumper?endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=******'
The -f 'oss://antsys-oceanbasebackup/backup_obloader_obdumper/obdumper?endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=******' option specifies the storage URL. The following table describes the components of a storage URL.
| Component | Description |
|---|---|
oss |
The OSS storage scheme. |
antsys-oceanbasebackup |
The name of the OSS bucket. |
/backup_obloader_obdumper/obdumper |
The data storage path in the OSS bucket. |
endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=****** |
The parameters required for the request.
|
Sample return result:
...
All Dump Tasks Finished:
----------------------------------------------------
| No.# | Type | Name | Count | Status |
----------------------------------------------------
| 1 | TABLE | table | 3 | SUCCESS |
----------------------------------------------------
Total Count: 3 End Time: 2023-05-10 18:40:48
...
Customize the name of an exported file
Scenario: Export all table data in the USERA schema to the /output directory in the CSV format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. Specify the name of the exported file as filetest.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --csv --table 'table' -f filetest.txt
Note
If you use OBDUMPER V4.2.7 or later to export one file, you can use -f to specify the file name.
Sample return result:
...
All Dump Tasks Finished:
----------------------------------------------------
| No.# | Type | Name | Count | Status |
----------------------------------------------------
| 1 | TABLE | table | 4 | SUCCESS |
----------------------------------------------------
Total Count: 4 End Time: 2023-04-28 15:32:49
...
Sample exported content:
View the filetest.txt table.
[xxx@xxx /ob-loader-dumper-4.2.0-RELEASE/bin]
$cat filetest.txt
1001,'xiaoning','COLLABORATION'
1002,'xiaohong','COLLABORATION'
1001,'xiaoning','COLLABORATION'
1002,'xiaohong','COLLABORATION'
Use a control file to export data
Scenario: Export all table data in the USERA schema to the /output directory in the CSV format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. Specify /output as the path of the control file for preprocessing the data to be exported.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA --table'table' -f /output --csv --ctl-path /output
Note
The table name defined in the database must be in the same letter case as its corresponding control file name. Otherwise, OBDUMPER fails to recognize the control file. For more information about the rules for defining control files, see Preprocessing functions.
Export data from specified table columns
Scenario: Export all table data in the USERA schema to the /output directory in the CSV format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided. Specify the --exclude-column-names option to exclude the columns that do not need to be exported.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -D USERA -f /output --table'table' --csv --exclude-column-names 'deptno'
Export the result set of a custom query
Scenario: Export the result set of the query statement specified in the --query-sql option to the /output directory in the CSV format. In OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** --sys-user **u*** --sys-password ****** -c -D USERA -f /output --csv --query-sql 'select deptno,dname from dept where deptno<3000'
Note
Make sure that the SQL query statement has the correct syntax and required query performance.
Export database object definitions and table data from ApsaraDB for OceanBase
Scenario: Export table data and the definitions of all defined database objects in the USERA schema of ApsaraDB for OceanBase to the /output directory without specifying the password of the sys tenant.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** -D USERA --ddl --csv --public-cloud --all -f /output
Export database object definitions and table data from OceanBase Database
Scenario: Export table data and the definitions of all defined database objects in the USERA schema of OceanBase Database to the /output directory without specifying the password of the sys tenant.
Sample command:
$./obdumper -h xx.x.x.x -P 2883 -u test@mysql#cluster_a -p ****** -D USERA --ddl --csv --no-sys --all -f /output
Notice
The export of database object definitions may have defects if you cannot provide the password of the sys tenant in ApsaraDB for OceanBase or OceanBase Database. For example, sequence definitions cannot be exported from MySQL-compatible tenants, table group definitions and partition information of unique indexes cannot be exported from OceanBase Database of versions earlier than V2.2.70, index definitions cannot be exported from Oracle-compatible tenants in OceanBase Database V2.2.30 and earlier, and unique index definitions of partitioned tables cannot be exported from Oracle-compatible tenants in OceanBase Database from V2.2.70 (inclusive) to V4.0.0.0.
Step 6: Congratulations
You have got started with OBDUMPER.
For more information, refer to the following steps:
Learn about the operating principles, major features, and differences from other tools of OBDUMPER from the product introduction. For more information about the tools, see OceanBase Loader and Dumper.
Join the OceanBase community to discuss issues and requirements on import and export and plans with OceanBase R&D engineers.