OceanBase Database is compatible with MySQL. Therefore, you can use MySQLDump to back up data in OceanBase databases. MySQLDump is a logical backup tool provided with MySQL. Its backup mechanism is to retrieve the to-be-backed-up data after MySQLDump is connected to the database through a protocol, and convert the retrieved data into corresponding INSERT statements. To restore the data, you only need to execute these INSERT statements.
Recommended versions
| Operating system (OS) | Version |
|---|---|
| Linux | MySQLDump Ver 10.13 Distrib 5.6.37, for Linux (x86_64) |
| MacOS | MySQLDump Ver 10.13 Distrib 5.7.21, for macos10.13 (x86_64) |
Note
You can use MySQLDump to export only data in OceanBase database instances in MySQL mode.
MySQLDump does not support locked databases and tables.
A
TIMEOUT 4012error may be reported when you export a large amount of data by using MySQLDump. To prevent this error, log on to the database as the tenant administrator and run the following statement to modify the system parameters. After the data export is complete, you can restore the system parameters to the original values.
obclient> SET GLOBAL ob_trx_timeout=1000000000,GLOBAL ob_query_timeout=1000000000;
Back up data
The following statement backs up data in OceanBase databases by using MySQLDump. An SQL file is generated after you run this statement.
MySQLDump -h xx.xx.xx.xx -P2883 -u 'user@tenantname#clustenamer' -ppassword --skip-triggers --databases db1 db2 db3 --skip-extended-insert > /tmp/data.sql
The following table describes the parameters in the preceding data backup statement.
| Parameter | Description |
|---|---|
| --host(-h) | The IP address of the server. |
| --port(-P) | The port number of the server. |
| --user(-u) | The MySQL username. |
| --password(-p) | The password of the MySQL user account. |
| -d | Indicates to export only the table structure but not data. |
| --databases | Specifies the databases to be backed up. |
| --all-databases | Indicates to back up all databases. This keyword is not recommended. We recommend that you specify the databases one by one. |
| --compact | Indicates to back up the data in compressed mode to reduce the volume of generated data. |
| --comments | The remarks information. |
| --complete-insert | Indicates to output completed INSERT statements. |
| --force | Indicates to ignore errors. |
| --skip-triggers | OceanBase Database does not support the trigger syntax. Therefore, this parameter is required if you do not specify the --force parameter. Otherwise, the data export fails. |
| --skip-extended-insert | Indicates to export statements in the format of multiple INSERT statements. If this parameter is not used, statements are exported in the format of INSERT INTO table VALUES(...),(...). |
After data is exported, you can run the following statement to import the previously exported data:
source /tmp/data.sql