Export data

Data circulation is one of the most critical parts of an analytics-oriented business scenario. This topic describes how to export data from OceanBase Database to file systems or object storage services. OceanBase Database supports different data export solutions for different data sources.

OBDUMPER

OBDUMPER is a client data export tool developed in Java for OceanBase Database. It allows you to export database objects and table data from OceanBase Database to local disks, Alibaba Cloud Object Storage Service (OSS), Amazon Simple Storage Service (S3), Tencent Cloud Object Storage (COS), and Huawei Cloud Object Storage Service (OBS). You can use the obdumper command to export data as CSV, delimited text, fixed-length text, SQL, Apache ORC, and Apache Parquet files.

Characteristics:

• Allows you to specify a partition name to export only the data in the specified table partition.
• Allows you to specify global filters to export only the data that meets the specified conditions.
• Allows you to specify a custom query statement to export only the result set of the query statement.
• Allows you to specify system change numbers (SCNs) or timestamps to export only the historical snapshot data of desired transaction points or time points.

Scenarios:

• OBDUMPER is suitable for efficient large-scale data export such as backup of computing and analytics clusters, as well as data preprocessing such as desensitization or encryption.

References:

• For more information, see What is OBDUMPER?

OUTFILE

You can use the SELECT INTO OUTFILE statement to export the query result to a file. This statement allows you to specify the fields to be exported. Therefore, you can use this statement when primary key fields do not need to be exported.

Characteristics:

• Supports a variety of output formats, such as TXT, SQL, CSV, fixed-length files, XML, and JSON.
• Allows you to specify the fields to be exported so that you can exclude primary key fields from export.

Scenarios:

• The OUTFILE statement is suitable for small-scale data export, such as simple data backup and migration.

References:

• For more information, see Export data by using OUTFILE statements.

mysqldump

Characteristics:

• Allows you to back up the entire database, specific tables, or query results to an SQL script file.
• Allows you to export backup data to a file system for subsequent restore or migration.

Scenarios:

• mysqldump is suitable for small- and medium-scale data migration.

References:

• You can use mysqldump to back up the entire database. A sample command is as follows: mysqldump -u <username> -p<password> <database_name> > backup.sql. For more information, see mysqldump documentation.

mydumper

Characteristics:

• Supports parallel data backup.
• Supports compression of backup data.
• Allows you to quickly restore back up data to a database system.

Scenarios:

• mydumper is suitable for large databases that require a high backup speed and large storage space.

References:

• A sample command for full backup is as follows: mydumper -u <username> -p <password> -B <database_name> -o <backup_dir>. For more information, see mydumper documentation.

DataX

Characteristics:

• Supports efficient transmission.
• Supports multiple types of data sources.
• Supports flexible configurations.

Scenarios:

• DataX is suitable for migrating tens of gigabytes to several terabytes of data or even more.

References:

• For more information, see DataX documentation.

Choose an export solution