Use DTS to synchronize data between MySQL and OceanBase Database in both directions

Data Transmission Service (DTS) is a real-time data streaming service provided by Alibaba Cloud. It supports data interaction between data sources such as relational databases, non-relational databases, and multidimensional data analytics systems. DTS integrates capabilities such as data synchronization, migration, subscription, integration, and processing. You can use DTS to synchronize data between MySQL and OceanBase Database in both directions, including DDL and DML operations.

Version compatibility

Prerequisites

Before you use DTS, make sure that the following conditions are met:

• You have activated Alibaba Cloud DTS.
• You have deployed OceanBase Database and created a MySQL-compatible user tenant. For more information, see Create a tenant.
• If you need incremental migration, you have enabled the Binlog service for the MySQL-compatible tenant. For more information, see OceanBase Binlog service.
• You have configured network connectivity to ensure that DTS can access OceanBase Database over a private network.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment administrator to obtain the connection string. For example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the connection IP address. For ODP connection, use the ODP address. For direct connection, use the OBServer IP address.
• $port: the connection port. For ODP connection, the default value is 2883. For direct connection, the default value is 2881.
• $database_name: the database name.

  Note

  The user used to connect to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL-compatible mode.

• $user_name: the connection account. For ODP connection, the format is user@tenant#cluster or cluster:tenant:user. For direct connection, the format is user@tenant.
• $password: the account password.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Configure and start the DTS synchronization task

1. Log in to the Alibaba Cloud DTS console, choose Data Synchronization, and click Create Task.
2. On the Source and Destination Database Configuration page, configure the following information:
   • Task Name: Enter a task name.
   • Database Type: Select MySQL for both the source and destination databases.
   • Access Method: For the source database, select Cloud Instance or Self-managed Database on ECS. For the destination database, select Express Connect/VPN Gateway/Smart Access Gateway.
   • Source Database Information: Enter the connection information of the MySQL database.
   • Destination Database Information: Enter the connection information of OceanBase Database by using the connection string obtained in Step 1.
3. Configure Task Objects: Select the databases and tables to be synchronized, and configure data verification rules to ensure data consistency.
4. Run the Pre-check to ensure that the configuration is correct.
5. After the Pre-check passes, start the synchronization task.
6. In the Synchronization Tasks section, check the Task Status to ensure that the task is running properly.

Step 3: Verify the result

Verify forward synchronization

You can execute DDL or DML operations in MySQL to check whether the data is synchronized to OceanBase Database. For example, execute the following statement in MySQL:

ALTER TABLE coffee_sales ADD COLUMN quantity INT NOT NULL DEFAULT 1 COMMENT 'Purchase quantity';

Check the table schema in OceanBase Database to confirm that the column has been added.

Verify reverse synchronization

You can execute DML operations in OceanBase Database to check whether the data is synchronized to MySQL. For example, execute the following statement in OceanBase Database:

UPDATE coffee_sales SET quantity = 2 WHERE id = 1;

Query the data in MySQL to confirm that the update has been synchronized.