Quick start

This topic describes how to have a quick start with OBDUMPER.

Step 1: Download the software

Click here to download the package of the latest version, and decompress the package:

$ unzip ob-loader-dumper-4.2.1-RELEASE.zip
$ cd ob-loader-dumper-4.2.1-RELEASE

Step 2: Configure the running environment

This step aims to modify JVM parameters.

A small JVM memory size may affect the performance and stability of the import and export features. For example, a full garbage collection (GC) or GC crash may occur. We recommend that you modify the JVM memory size, which is -Xms4G -Xmx4G by default, to 60% of the available memory of the server. If you are good at Java performance tuning, you can modify the JVM parameters in the JAVA_OPTS option as needed.

1. Edit the configuration file that contains the JAVA_OPTS option.

   • In Linux, edit the obloader and obdumper scripts in the {ob-loader-dumper}/bin/ directory.

   • In Windows, edit the obloader.bat and obdumper.bat scripts in the {ob-loader-dumper}/bin/windows/ directory.

2. Modify JVM parameters.

   JAVA_OPTS="$JAVA_OPTS -server -Xms4G -Xmx4G -XX:MetaspaceSize=128M -XX:MaxMetaspaceSize=128M -Xss352K"
   JAVA_OPTS="$JAVA_OPTS -XX:+UnlockExperimentalVMOptions -XX:+UseG1GC -Xnoclassgc -XX:+DisableExplicitGC
   

Step 3: Prepare data

When you try the import feature of OBLOADER, you can use existing data files or use the TPC-H tool to generate temporary data files. The format of content in the imported file must meet related specifications. Identify the data format in the file based on Is your data ready.

Step 4: Create a database

1. Deploy an OceanBase cluster by using OceanBase Cloud Platform (OCP) or OceanBase Deployer (OBD).

2. Create a test database.

3. Create a test table and insert data into the table. This operation is optional when you import data.

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

The following table describes the database information that is used in the examples.

Export DDL definition files

Scenario: Export all supported object definition statements in the USERA schema to the /output directory. For OceanBase Database of a version earlier than 4.0.0, the password of the sys tenant must be provided.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --ddl --all -f /output

Task result example:

...
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
...

Exported content example:

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. For OceanBase Database of a version earlier than 4.0.0, the password of the sys tenant must be provided. For more information about the CSV data file specifications, see RFC 4180. CSV data files (.csv files) store data in the form of plain text. You can open CSV data files by using a text editor or Excel.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' -f /output

Task result example:

...
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
...

Exported content example:

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. For OceanBase Database of a version earlier than 4.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 code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --sql --table '*' -f /output

Task result example:

...
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
...

Exported content example:

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. For OceanBase Database of a version earlier than 4.0.0, the password of the sys tenant must be provided. Specify |@| as the column separator string for the exported data. CUT data files (.dat files) use a character or character string to separate values. You can open CUT data files by using a text editor.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --table '*' -f /output --cut --column-splitter '|@|' --trail-delimiter

Task result example:

...
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
...

Exported content example:

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 S3 bucket in the CSV format.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' --skip-check-dir --upload-behavior 'FAST' -f /output --storage-uri 's3://obloaderdumper/obdumper?region=cn-north-1&access-key=******&secret-key=******'

--storage-uri 's3://obloaderdumper/obdumper?region=cn-north-1&access-key=******&secret-key=******' option is the uniform resource identifier (URI) of the storage space, its components include:

Task result example:

...
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 OSS bucket in the CSV format.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table '*' --skip-check-dir --upload-behavior 'FAST' -f /output --storage-uri 'oss://antsys-oceanbasebackup/backup_obloader_obdumper/obdumper?endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=******'

--storage-uri 'oss://antsys-oceanbasebackup/backup_obloader_obdumper/obdumper?endpoint=https://cn-hangzhou-alipay-b.oss-cdn.aliyun-inc.com&access-key=******&secret-key=******' option is the uniform resource identifier (URI) of the storage space, its components include:

Task result example:

...
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. For OceanBase Database of a version earlier than 4.0.0, the password of the sys tenant must be provided. Specify the name of the exported file as filetest.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --csv --table 'table' --file-name 'filetest.txt' -f /output

Task result example:

...
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
...

Exported content example:

View the filetest.txt file in the {ob-loader-dumper}/output/data/chz/TABLE directory.

[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. For OceanBase Database of a version earlier than 4.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 code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA --table'table' -f /output --csv --ctl-path /output

Export data from specified table columns

Scenario: Export all table data in the USERA schema to the /output directory in the CSV format. For OceanBase Database of a version earlier than 4.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 code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -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. For OceanBase Database of a version earlier than V4.0.0, the password of the sys tenant must be provided.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** --sys-user **u*** --sys-password ****** -c cluster_a -t mysql -D USERA -f /output --csv --query-sql 'select deptno,dname from dept where deptno<3000'

Export database objects and data to a public cloud

Scenario: When you cannot provide the password of the sys tenant, export all defined database objects and table data in the USERA schema in a public cloud to the /output directory.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** -D USERA --ddl --csv --public-cloud --all -f /output

Export database objects and data to a private cloud

Scenario: When you cannot provide the password of the sys tenant, export all defined database objects and table data in the USERA schema in a private cloud to the /output directory.

Sample code:

$./obdumper -h xx.x.x.x -P 2883 -u test -p ****** -c cluster_a -t mysql -D USERA --ddl --csv --no-sys --all -f /output

Step 6: Congratulations

You have had a quick start with OBLOADER & OBDUMPER.

For more information, refer to the following steps:

• Learn about the operating principles, major features, and differences from other tools of OBLOADER & OBDUMPER from the product introduction. For more information about the tools, see OBLOADER & OBDUMPER.

• Welcome to join OceanBase community to discuss issues and requirements on import and export and future plans with OceanBase R&D engineers.