What is OBDUMPER?
OBDUMPER is a client data export tool developed in Java. You can use OBDUMPER to export data from OceanBase Database to files in SQL or CSV format. You can also use it to export objects defined in the database to files.
OBDUMPER is advantageous over other export tools such as MyDumper and SQL Developer in the following aspects:
High performance
OBDUMPER is specifically optimized for partitioned tables and tables without a primary key.
Extensive features
OBDUMPER provides various features, including limited data export, various data formats, and global consistent nonlocking export.
Features
OBDUMPER allows you to export data in object schemas and tables in OceanBase Database to files. Specifically, OBDUMPER provides the following features:
Allows you to export the DDL statements of object schemas in a database.
Allows you to export table data to files in the CSV or SQL format.
Allows you to export data from some partitions of a partitioned table by specifying the names of the partitions.
Allows you to specify global filters to export only the data that meets the specified conditions.
Supports simple data cleansing rules.
Supports global consistent nonlocking read to ensure the global consistency of the exported data.
Supports system change number (SCN)-based or timestamp-based flashback queries to ensure the global consistency of the exported data.
Supports data export in limited mode, in which you do not need to specify account information of users under the sys tenant.
Note
The schema definition exported by using this tool in limited mode may be incomplete. Also, the import and export performance is reduced.
Supported OceanBase Database versions
The following table describes OceanBase Database versions that are supported.
| Database | Supported version |
|---|---|
| OceanBase Database in Oracle mode | V2.2.30, V2.2.52, V2.2.7x, and V3.1.x |
| OceanBase Database in MySQL mode | V1.4.70, V1.4.72, V1.4.75, V1.4.78, V1.4.79, V2.2.30, V2.2.50, V2.2.70, V2.2.71, V2.2.72, V2.2.76, and V3.1.x |
Considerations
If a foreign key contains several columns, the order of the columns might change after the export. Example:
FOREIGN KEY(c1,c2) REFERENCE (c1,c2).OceanBase Database V1.4.72 in MySQL mode does not allow you to export the schema definitions of unique prefix indexes, such as
UNIQUE(c1(10)).OceanBase Database V1.4.x in MySQL mode does not allow you to export the definitions of generated columns, such as
GENERATED ALWAYS AS (expr).OceanBase Database of versions earlier than V2.2.50 in Oracle mode do not allow you to export the DDL statements of function-based indexes.
For more information about the standard CSV format, see the RFC 4180 specifications. We recommend that you export data in strict accordance with the RFC 4180 specifications.
You need to modify the VM memory parameter, which is defaulted as -Xms4G -Xmx4G, in the script when you intend to import or export a large amount of data.
The object names, data file names, and rule file names specified by command-line options must be capitalized in the same way. By default, uppercase letters are used in Oracle mode, and lowercase letters are used in MySQL mode.
The exported data files must be named in the format of table name.<random character>.extension.
To improve the data export performance, we recommend that you trigger a major compaction before you export data.
Limited mode
OBDUMPER provides a limited mode. You can export data in limited mode by specifying the --public-cloud option. OBDUMPER in limited mode is supported in OceanBase clusters deployed in the public cloud. In OceanBase clusters deployed in a private cloud, the --sys-password must be specified or a key file must be configured before this tool can be used.
In this mode, you can only export object tables and views. The exported schemas are incomplete because they lack index information. The performance of data export in limited mode is much lower than that in regular mode.
Usage notes
This section describes how to use the undo_retention parameter.
The default value of this parameter is 0, in seconds. Assume that t1 is the timepoint of the Undo operation, and t2 = t1 + 900s. You can find data within the [t1,t2] range when you execute a query at t2. After you set the undo_retention parameter, the current session only takes effect on the data that is generated after t1. Sample statement of setting the undo_retention parameter:
SET global undo_retention=900;
You can obtain the effective SCN of the OceanBase Database by querying the
v$ob_timestamp_serviceview under the sys tenant.If you specify a timepoint in the query, the data after the last major compaction is returned. For example, if you started a major compaction in the cluster at t1, the earliest version of data that you can obtain is the data at t1.
If the queried table has been dropped and is in the recycle bin, you must first restore it from the recycle bin.
Flashback queries are affected by minor compactions. If a minor compaction has been performed and the
undo_retentionparameter is not specified, flashback queries cannot be performed. After theundo_retentionparameter is specified, you can query data within the time range of [t1, t1 +undo_retention], where t1 is the timepoint when the minor compaction was performed.
Examples
We recommend that you export schemas and data separately from OceanBase Database. Command-line options of OBDUMPER:
bin/obdumper -h <host IP address> -P <port> -u <user> -p <password> --sys-user <root or proxyro user in the sys tenant> --sys-password <password in the sys tenant> -c <cluster> -t <tenant> -D <Schema database name> [--ddl] [--csv|--sql] [--all|--table 'table name'] -f<data file or directory>export only table schemas
Notice
- You must specify the username and password for the business tenant.
- To export the schemas, you must enter the password for the
rootuser or theproxyrouser and its password in the sys tenant. By default, to ensure security, ODP restricts the use of theproxyrouser to log on to the OceanBase cluster. However, due to the fact that therootuser has very high privileges, we recommend that you retrieve the table metadata by using theproxyrouser.
Export schemas only
Export the table in the test database of the obmysql business tenant. Sample code:
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]# bin/obdumper -h 10.0.0.0 -P 2883 -u u_dumper -p ****** --sys-user=proxyro --sys-password=******** -c obce-3zones -t obmysql -D test --ddl --all -f /tmp/obdumper
2021-09-29 21:14:55 [INFO] Parsed args:
-h[--host] 10.0.0.0
-P[--port] 2883
-u[--user] u_dumper
-p[--password] ******
[--sys-user] proxyro
[--sys-password] ********
-c[--cluster] obce-3zones
-t[--tenant] obmysql
-D[--database] test
[--ddl]
[--all]
-f[--file-path] /tmp/obdumper
2021-12-29 11:34:20 [INFO] The security certificate file: "/mysql/ob-loader-dumper-3.0.0-SNAPSHOT/conf/secure.crt" is not exists
2021-12-29 11:34:20 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished
2021-12-29 11:34:20 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
2021-12-29 11:34:21 [INFO] Query the column metadata for the table: "test0" finished
2021-12-29 11:34:21 [INFO] Query the column metadata for the table: "test1" finished
2021-12-29 11:34:21 [INFO] Found 1 empty tables before dump out records. Elapsed: 152.3 ms
2021-12-29 11:34:21 [WARN] No views are exist in the schema: "test"
2021-12-29 11:34:21 [INFO] Generate 1 dump tasks finished. Total Elapsed: 5.762 ms
2021-12-29 11:34:21 [INFO] Start 1 schema dump threads finished
2021-12-29 11:34:21 [INFO] Build direct com.alibaba.druid.pool.DruidDataSource finished
2021-12-29 11:34:21 [INFO] Build proxyro com.alibaba.druid.pool.DruidDataSource finished
2021-12-29 11:34:21 [INFO] Return the latest compatible version: 3.1.2 -> 2.2.71
2021-12-29 11:34:21 [INFO] DbType: OBMYSQL Version: 3.1.2
2021-12-29 11:34:21 [INFO] ObMySql(3.1.2) is older than 2.2.71 ? false
2021-12-29 11:34:21 [INFO] Load meta/obmysql/obmysql14x.xml, meta/obmysql/obmysql22x.xml, meta/obmysql/obmysql2271.xml successed
2021-12-29 11:34:21 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDependencies()
2021-12-29 11:34:21 [INFO] Query 0 dependencies elapsed 376.0 ms
2021-12-29 11:34:23 [INFO] Query table: "test1" attr finished. Remain: 1
2021-12-29 11:34:23 [INFO] Query table: "test0" attr finished. Remain: 0
2021-12-29 11:34:23 [INFO] Query 2 tables elapsed 2.059 s
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTablespaceMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireSequenceMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireSynonymMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeBodyMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageBodyMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTriggerMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireProcedureMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireFunctionMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDatabaseLinkMapping()
2021-12-29 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDependencies()
2021-12-29 11:34:23 [INFO] Dump [TABLE] test1 to "/tmp/obdumper/data/test/TABLE/test1-schema.sql" finished
2021-12-29 11:34:23 [INFO] Dump [TABLE] test0 to "/tmp/obdumper/data/test/TABLE/test0-schema.sql" finished
2021-12-29 11:34:23 [INFO] No.1 It has dumped 2 tables finished. Remain: 0
2021-12-29 11:34:23 [INFO] Total dumped 2 tables finished
2021-12-29 11:34:23 [INFO] Dump the ddl of schema: "test" finished
2021-12-29 11:34:24 [INFO] Close connection count: 14 of the DataSource. Key: 11_124_5_29_44564_857230679_test
2021-12-29 11:34:24 [INFO] Close connection count: 11 of the DataSource. Key: 11_124_5_29_44564_167438737_oceanbase
2021-12-29 11:34:24 [INFO] Shutdown task context finished
2021-12-29 11:34:24 [INFO]
Finished Tasks: 1 Running Tasks: 0 Progress: 100.00%
2021-12-29 11:34:24 [INFO]
All Dump Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | test0 | 1 | SUCCESS
2 | TABLE | test1 | 1 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 2 End Time: 2021-12-29 11:34:24
2021-12-29 11:34:24 [INFO] Dump schema finished. Total Elapsed: 3.117 s
2021-12-29 11:34:24 [INFO] System exit 0
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]#
You can find the exported script or log by using the directory structure exported by OBDUMPER in the following sample code:
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]# tree /tmp/obdumper/
/tmp/obdumper/
├── data
│ ├── CHECKPOINT.bin
│ ├── MANIFEST.bin
│ └── test
│ └── TABLE
│ └── t1-schema.sql
└── logs
├── ob-loader-dumper.error
├── ob-loader-dumper.info
└── ob-loader-dumper.warn
4 directories, 6 files
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]#
Export data only
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]# bin/obdumper -h 10.0.0.0 -P 2883 -u u_dumper -p ****** --sys-user=proxyro --sys-password=******** -c obce-3zones -t obmysql -D test --csv --all -f /tmp/obdumper
2021-12-29 11:02:53 [INFO] Parsed args:
[--host] 10.0.0.0
[--port] 2883
[--user] u_dumper
[--tenant] obmysql
[--cluster] obce-3zones
[--password] ******
[--database] test
[--sys-user] proxyro
[--sys-password] ********
[--csv] true
[--file-path] u_dumper
[--all] true
2021-12-29 11:02:53 [INFO] The security certificate file: "/ob-loader-dumper-3.0.0-SNAPSHOT/conf/secure.crt" is not exists
2021-12-29 11:02:53 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished
2021-12-29 11:02:53 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
2021-12-29 11:02:54 [INFO] Query the column metadata for the table: "test0" finished
2021-12-29 11:02:54 [INFO] Query the table entry for table: "test0" finished. Remain: 0
2021-12-29 11:03:22 [INFO] Generate 1 csv dump task for non-partitioned table(without primary key): test0. Remain: 0
2021-12-29 11:03:22 [INFO] Generate 1 dump tasks finished. Total Elapsed: 28.03 s
2021-12-29 11:03:22 [INFO] Start 192 record dump threads for 1 dump tasks finished
2021-12-29 11:03:23 [INFO] Dump 10 rows test.test0 to "u_dumper/data/test/TABLE/test0.csv" finished
2021-12-29 11:03:24 [INFO] Close connection count: 3 of the DataSource. Key: 11_124_5_29_44564_857230679_test
2021-12-29 11:03:24 [INFO] Close connection count: 1 of the DataSource. Key: 11_124_5_29_44564_167438737_oceanbase
2021-12-29 11:03:24 [INFO] Shutdown task context finished
2021-12-29 11:03:24 [INFO]
Finished Tasks: 1 Running Tasks: 0 Progress: 100.00%
2021-12-29 11:03:24 [INFO]
All Dump Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | test0 | 10 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 10 End Time: 2021-12-29 11:03:24
2021-12-29 11:03:24 [INFO] Unnecessary to merge the data files. As --file-name is missing
2021-12-29 11:03:24 [INFO] Dump record finished. Total Elapsed: 29.67 s
2021-12-29 11:03:24 [INFO] Unnecessary to upload the data files to the remote cloud storage service
2021-12-29 11:03:24 [INFO] System exit 0
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]#
Directory structure:
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]# tree /tmp/obdumper
/tmp/obdumper
├── data
│ ├── CHECKPOINT.bin
│ ├── MANIFEST.bin
│ └── test
│ └── TABLE
│ ├── t1.0.csv
│ └── t1-schema.sql
└── logs
├── ob-loader-dumper.error
├── ob-loader-dumper.info
└── ob-loader-dumper.warn
4 directories, 7 files
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]#
The exported data files in CSV format are located in the same directory as the schema files.
View the exported files
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]# cat /tmp/obdumper/data/test/TABLE/t1-schema.sql
create table if not exists `t1` (
`id` bigint(20) not null auto_increment,
`c1` timestamp not null default CURRENT_TIMESTAMP,
primary key (`id`)
)
default charset=utf8mb4
default collate=utf8mb4_general_ci;[root@obce-0000 ob-loader-dumper-2.1.13-SNAPSHOT]#
[root@obce-0000 ob-loader-dumper-3.0.0-SNAPSHOT]# cat /tmp/obdumper/data/test/TABLE/t1.0.csv
'id','c1'
1,'2021-09-29 21:16:03'
2,'2021-09-29 21:16:05'
3,'2021-09-29 21:16:05'
4,'2021-09-29 21:16:06'
5,'2021-09-29 21:16:06'
6,'2021-09-29 21:16:18'
7,'2021-09-29 21:16:18'
8,'2021-09-29 21:16:19'
More information
For more information about how to use OBDUMPER, see Data processing.