OBDUMPER is a client data export tool developed in Java. You can use OBDUMPER to export object schemas and data defined in OceanBase Database to files.
About OBDUMPER
OBDUMPER has the following advantages over other export tools such as MyDumper and SQL Developer:
Higher performance: OBDUMPER is specifically optimized for partitioned tables and tables without a primary key.
More features: OBDUMPER provides various features, including data preprocessing, support for 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 database objects.
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 partition names.
Allows you to specify global filters to export only the data that meets the specified conditions.
Allows you to configure data preprocessing rules to convert data before export.
Allows you to export data at any transaction port or point in time after flashback queries based on the system change number (SCN) or timestamp.
Allows you to upload exported data to Object Storage Service (OSS).
Allows you to export data from a follower replica, which is different from a standby cluster, of an OceanBase cluster. which is different from a standby cluster.
Allows you to export custom query result sets in the SQL, CSV, and CUT formats.
Ensures global consistency for the exported data by reading the snapshot version when tables are not locked.
Supported OceanBase Database versions
The following table describes OceanBase Database versions and modes that are supported.
| OceanBase Database mode | Supported version |
|---|---|
| Oracle mode | V2.2.30, V2.2.52, V2.2.7x, V3.1.x, and V3.2.x |
| 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, V3.1.x, and V3.2.x |
Considerations
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 in the script when you try to 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.
To improve the data export performance, we recommend that you trigger a major compaction before you export data.
All exported data files are named in the
table.group.sequence.suffixformat.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 definitions of unique prefix indexes, for example, UNIQUE(c1(10)).
OceanBase Database V1.4.x in MySQL mode does not allow you to export the definitions of generated columns. For example, columns generated by using the GENERATED ALWAYS AS (expr) statement cannot be exported.
OceanBase Database V2.2.7x in MySQL mode does not allow you to export vertical partitions. For example, partitions created by using the PARTITION BY COLUMN statement cannot be exported.
OceanBase Database V2.2.7x in MySQL mode does not allow you to export the definitions of SYNONYM objects.
OceanBase Database V2.2.7x in MySQL mode does not allow you to export columns that store index data. For example, columns in STORING(COLUMN_LIST) cannot be exported.
OceanBase Database of versions earlier than V2.2.50 in Oracle mode do not allow you to export the definitions of function-based indexes.
OceanBase Database in Oracle mode does not allow you to export the definitions of TYPE objects.
OceanBase Database in Oracle mode does not allow you to export data of the INTERVAL DAY(2) TO SECOND(0) type.
On the Windows OS, the exclamation point (!) cannot be used as a separator. An object name in the database cannot contain the following special characters: \ / : * ? " < > |. Otherwise, neither the database object definitions nor the table data can be exported.
Limited mode
Note
The limited mode is applicable to scenarios where you cannot directly specify the
--sys-useror--sys-passwordoption.
If you enable the limited mode (by specifying
--public-cloud) when you export data from an OceanBase cluster deployed in a public cloud, you do not need to specify the-tor-coption.When you export data in limited mode from an OceanBase cluster deployed in a private cloud, you must specify the
-toption, and additionally the-coption for using OceanBase Database Proxy (ODP). If you do not enable the limited mode, the--sys-userand--sys-passwordoptions must be specified for OBDUMPER.If you enable the limited mode by specifying
--public-cloudwhen you export object definitions, you can export definitions of only tables and views. Table definitions cannot contain index and comment information. In addition, data export performance in limited mode is lower than that in unlimited mode.
Flashback export
To use the flashback export feature, you need to set the system variable undo_retention to an appropriate value.
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 undo_retention, the current session only takes effect on the data that is generated after t1. The default value of this system variable is 0, in seconds. Sample statement for setting the undo_retention variable:
SET global undo_retention=900;
Currently, you can only query the
v$ob_timestamp_serviceview in the sys tenant to obtain the valid OceanBase system change number (SCN).You can query data after the latest major compaction at a specific point in time. For example, if you started a major compaction at t1, the earliest data that you can query is the data at t1.
If the table that you want to query has been deleted and is in the recycle bin, you must restore it from the recycle bin.
Flashback queries are affected by minor compactions. If a minor compaction has been performed and the
undo_retentionvariable is not specified, flashback queries cannot be performed.After the
undo_retentionvariable is set, you can query data within the time range of [t1,t1 +undo_retention], where t1 is the point in time when the minor compaction occurs.
Syntax
We recommend that you export schemas and data separately from OceanBase Database. You can use the following syntax to export data:
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, OBProxy 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.
./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 the schema
| Parameter | Required | Description | Example |
|---|---|---|---|
| -h(--host) | Yes | The IP address of the OBProxy or OBServer to connect to. | -h 127.0.0.1 |
| -P(--port) | Yes | The port number of the ODP or OBServer to connect to. Note
|
-P 2881 |
| -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. | -c 'cluste1' |
| -t(--tenant) | No | The tenant name of the user. | -t sys |
| -u(--user) | Yes | The username that you use to log on to the database. | -u root |
| -p(--password) | No | The password that you use to log on to the database. | -p '**1***' |
| -D(--database) | Yes | The name of the database. | -D 'database1' |
| -f(--file-path) | Yes | The directory to which data is exported. | -f '/path/file' |
| --sys-user | No | The username of the user under the sys tenant. Default value: root@sys. | --sys-user 'root@sys' |
| --sys-password | No | The password of the user specified by the --sys-user option. By default, this option is left empty. | --sys-password '**1***' |
| --csv | No | Exports files in the CSV format, which are suffixed by .csv by default. We recommend that you use this option. | --csv |
| --sql | No | Exports files in the SQL format, which are suffixed by .sql by default. | --sql |
| --ddl | No | Exports files in the DDL format, which are suffixed by -schema.sql by default. | --ddl |
| --all | No | Exports all database objects. By default, the database objects to be exported include tables, views, triggers, functions, stored procedures, sequences, and synonyms. | --all |
| --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. | --table 'table1' |
| --where | No | The global conditions. If you specify this option, only data that meet the conditions can be exported. | --where 'id > 0' |
Export only the schemas
Export the table schema in the test database of the obmysql business tenant. Sample code:
[root@****]# 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
1970-01-01 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
1970-01-01 11:34:20 [INFO] The security certificate file: "/mysql/ob-loader-dumper-3.0.0-SNAPSHOT/conf/secure.crt" is not exists
1970-01-01 11:34:20 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished
1970-01-01 11:34:20 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
1970-01-01 11:34:21 [INFO] Query the column metadata for the table: "test0" finished
1970-01-01 11:34:21 [INFO] Query the column metadata for the table: "test1" finished
1970-01-01 11:34:21 [INFO] Found 1 empty tables before dump out records. Elapsed: 152.3 ms
1970-01-01 11:34:21 [WARN] No views are exist in the schema: "test"
1970-01-01 11:34:21 [INFO] Generate 1 dump tasks finished. Total Elapsed: 5.762 ms
1970-01-01 11:34:21 [INFO] Start 1 schema dump threads finished
1970-01-01 11:34:21 [INFO] Build direct com.alibaba.druid.pool.DruidDataSource finished
1970-01-01 11:34:21 [INFO] Build proxyro com.alibaba.druid.pool.DruidDataSource finished
1970-01-01 11:34:21 [INFO] Return the latest compatible version: 3.1.2 -> 2.2.71
1970-01-01 11:34:21 [INFO] DbType: OBMYSQL Version: 3.1.2
1970-01-01 11:34:21 [INFO] ObMySql(3.1.2) is older than 2.2.71 ? false
1970-01-01 11:34:21 [INFO] Load meta/obmysql/obmysql14x.xml, meta/obmysql/obmysql22x.xml, meta/obmysql/obmysql2271.xml successed
1970-01-01 11:34:21 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDependencies()
1970-01-01 11:34:21 [INFO] Query 0 dependencies elapsed 376.0 ms
1970-01-01 11:34:23 [INFO] Query table: "test1" attr finished. Remain: 1
1970-01-01 11:34:23 [INFO] Query table: "test0" attr finished. Remain: 0
1970-01-01 11:34:23 [INFO] Query 2 tables elapsed 2.059 s
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTablespaceMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireSequenceMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireSynonymMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTypeBodyMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquirePackageBodyMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireTriggerMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireProcedureMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireFunctionMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDatabaseLinkMapping()
1970-01-01 11:34:23 [WARN] c.o.t.l.s.o.ObMySqlDatabase does't implement acquireDependencies()
1970-01-01 11:34:23 [INFO] Dump [TABLE] test1 to "/tmp/obdumper/data/test/TABLE/test1-schema.sql" finished
1970-01-01 11:34:23 [INFO] Dump [TABLE] test0 to "/tmp/obdumper/data/test/TABLE/test0-schema.sql" finished
1970-01-01 11:34:23 [INFO] No.1 It has dumped 2 tables finished. Remain: 0
1970-01-01 11:34:23 [INFO] Total dumped 2 tables finished
1970-01-01 11:34:23 [INFO] Dump the ddl of schema: "test" finished
1970-01-01 11:34:24 [INFO] Close connection count: 14 of the DataSource. Key: 11_124_5_29_44564_857230679_test
1970-01-01 11:34:24 [INFO] Close connection count: 11 of the DataSource. Key: 11_124_5_29_44564_167438737_oceanbase
1970-01-01 11:34:24 [INFO] Shutdown task context finished
1970-01-01 11:34:24 [INFO]
Finished Tasks: 1 Running Tasks: 0 Progress: 100.00%
1970-01-01 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: 1970-01-01 11:34:24
1970-01-01 11:34:24 [INFO] Dump schema finished. Total Elapsed: 3.117 s
1970-01-01 11:34:24 [INFO] System exit 0
You can find the exported script or log by using the directory structure exported by OBDUMPER in the following sample code:
[root@****]# 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
Export data only
[root@****]# 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
1970-01-01 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
1970-01-01 11:02:53 [INFO] The security certificate file: "/ob-loader-dumper-3.0.0-SNAPSHOT/conf/secure.crt" is not exists
1970-01-01 11:02:53 [INFO] Load jdbc driver class: "com.oceanbase.jdbc.Driver" finished
1970-01-01 11:02:53 [INFO] The manifest file: "/tmp/obdumper/data/MANIFEST.bin" has been saved
1970-01-01 11:02:54 [INFO] Query the column metadata for the table: "test0" finished
1970-01-01 11:02:54 [INFO] Query the table entry for table: "test0" finished. Remain: 0
1970-01-01 11:03:22 [INFO] Generate 1 csv dump task for non-partitioned table(without primary key): test0. Remain: 0
1970-01-01 11:03:22 [INFO] Generate 1 dump tasks finished. Total Elapsed: 28.03 s
1970-01-01 11:03:22 [INFO] Start 192 record dump threads for 1 dump tasks finished
1970-01-01 11:03:23 [INFO] Dump 10 rows test.test0 to "u_dumper/data/test/TABLE/test0.csv" finished
1970-01-01 11:03:24 [INFO] Close connection count: 3 of the DataSource. Key: 11_124_5_29_44564_857230679_test
1970-01-01 11:03:24 [INFO] Close connection count: 1 of the DataSource. Key: 11_124_5_29_44564_167438737_oceanbase
1970-01-01 11:03:24 [INFO] Shutdown task context finished
1970-01-01 11:03:24 [INFO]
Finished Tasks: 1 Running Tasks: 0 Progress: 100.00%
1970-01-01 11:03:24 [INFO]
All Dump Tasks Finished:
----------------------------------------------------------------------------------------------------------------------------
No.# | Type | Name | Count | Status
----------------------------------------------------------------------------------------------------------------------------
1 | TABLE | test0 | 10 | SUCCESS
----------------------------------------------------------------------------------------------------------------------------
Total Count: 10 End Time: 1970-01-01 11:03:24
1970-01-01 11:03:24 [INFO] Unnecessary to merge the data files. As --file-name is missing
1970-01-01 11:03:24 [INFO] Dump record finished. Total Elapsed: 29.67 s
1970-01-01 11:03:24 [INFO] Unnecessary to upload the data files to the remote cloud storage service
1970-01-01 11:03:24 [INFO] System exit 0
Directory structure:
[root@****]# 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
The exported data files in CSV format are located in the same directory as the schema files.
View the exported files
[root@****]# 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@****OBC]# cat /tmp/obdumper/data/test/TABLE/t1.0.csv
'id','c1'
1,'1970-01-01 21:16:03'
2,'1970-01-01 21:16:05'
3,'1970-01-01 21:16:05'
4,'1970-01-01 21:16:06'
5,'1970-01-01 21:16:06'
6,'1970-01-01 21:16:18'
7,'1970-01-01 21:16:18'
8,'1970-01-01 21:16:19'
Notes
Consistent nonlocking export
One of the differences between OBDUMPER and mysqldump is the implementation of table locking methods. Table locking may have a significant impact on your business. OBDUMPER provides two methods to export globally consistent snapshot data.
Based on the freeze versions in OceanBase Database. For more information about the freeze versions, see the documentation of OceanBase storage.
Based on the database flashback feature. You can specify the SCN or timestamp to enable flashback. The flashback feature of OceanBase Database in Oracle mode is similar to the flashback feature in Oracle.
FAQ on consistent data export:
When do I need a consistent nonlocking export?
You can perform a consistent nonlocking export when the write operation cannot be stopped and the business read and write operations cannot be affected.
What are the precautions for a consistent nonlocking export?
A consistent nonlocking export depends on the retention time of the undo log. If the undo log is recycled on the database, historical snapshot data will be not available. Therefore, you must set the undo retention parameters appropriately before an export. The longer the undo log is retained, the more disk space will be occupied.
What consequences does a consistent nonlocking export have?
A consistent nonlocking export does not export newly written business data. OceanBase Database is a quasi-memory database. Therefore, new business data is written into memory first, rather than disks.
Inconsistent export
By default, OBDUMPER uses non-consistent export. This method allows you to export incremental data from the memory without locking tables. However, this method cannot ensure global data consistency. If write operations can be stopped for the business, you can use the default export method.