This topic describes the common business scenarios in which OBDUMPER is used and provides the corresponding examples.
The following table describes the database information that is used in the examples.
| Database information item | Example value |
|---|---|
| Cluster name | cluster_a |
| IP address of the OceanBase Database Proxy (ODP) host | xx.x.x.x |
| ODP port number | 2883 |
| Tenant name of the cluster | mysql |
| Name of the root/proxyro user under the sys tenant | **u*** |
| Password of the root/proxyro user under the sys tenant | ****** |
| User account (with read/write privileges) under the business tenant | test |
| Password of the user under 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. 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
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 takes effect.
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:
| 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.
|
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
...
Note
The --storage-uri option must be used in combination with the -f option.
OBDUMPER first exports object definitions and table data from OceanBase Database to the path specified in the --f option, and then uploads the object definitions and table data to the S3 bucket specified in the --storage-uri option. After the upload is completed, the data in the path specified in the -f option is automatically deleted.
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:
| 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.
|
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
...
Note
The --storage-uri option must be used in combination with the --f option.
OBDUMPER first exports object definitions and table data from OceanBase Database to the path specified in the --f option, and then uploads the object definitions and table data to the S3 bucket specified in the --storage-uri option. After the upload is completed, the data in the path specified in the --f option is automatically deleted.
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
Note
The table name defined in the database must be in the same letter case as its corresponding control file name. Otherwise, OBLOADER 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. 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'
Note
Make sure that the SQL query statement has correct syntax and required query performance.
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
Notice
The export of database object definitions may have defects when the password of the sys tenant cannot be provided in a public cloud or private cloud environment. For example, sequence definitions cannot be exported form a MySQL tenant, table group definitions cannot be exported from OceanBase Database of versions earlier than V2.2.70, index definitions cannot be exported from Oracle tenants in OceanBase Database V2.2.30 and earlier, partition information of unique indexes cannot be exported from OceanBase Database of versions earlier than V2.2.70, and unique index definitions of partitioned tables cannot be exported from Oracle tenants in OceanBase Database V2.2.70 to V4.0.0.0.