Configure MySQL permissions and binary logging for real-time data synchronization to Hologres - Data Integration - Alibaba Cloud -

This topic describes how to configure the required network, whitelist, and account permissions for the data synchronization task.

Prerequisites

Before you configure the data sources, make sure you have completed the following preparations:

You have purchased the source MySQL and destination Hologres data sources.
You have purchased and configured an exclusive resource group for Data Integration. For more information, see Plan and configure resources.
You have established a network connection between your data sources and the exclusive resource group for Data Integration. After the network is connected, follow the instructions in this topic to configure network access settings, such as vSwitches and whitelists.
- If the data sources and the exclusive resource group for Data Integration are in the same VPC in the same region, they are automatically connected.
- If the data sources and the exclusive resource group for Data Integration are in different network environments, you must connect them by using a VPN gateway or other methods.

Background

To synchronize data, you must ensure that the data sources and the exclusive resource group for Data Integration in DataWorks are connected and that the necessary account permissions are granted.

Whitelist
The following example assumes a VPC environment. You must add the CIDR block of the exclusive resource group for Data Integration to the whitelist. This ensures that the resource group can access the data source.
Account permissions
You need to create an account that can access the data source. Data Integration uses this account to access the data source and perform data extraction and write operations during the synchronization process.
Other access restrictions
If your source data source is MySQL, you must enable binary logging (binlog). A binlog is a log that records all changes to database table structures, such as CREATE and ALTER, and data modifications, such as INSERT, UPDATE, and DELETE. You can use the binlog to view the change history of the database, perform incremental backups and restores, and replicate data from a primary database to a replica.
Binlog supports the following formats:
- Statement: Statement-based replication. The binlog records each SQL statement that modifies data.
- Row: Row-based replication. The binlog does not save the context of SQL statements but only records the details of the modified rows.
- Mixed: Mixed-mode replication. This is a combination of Statement and Row formats. Typically, the Statement format is used for most modifications, such as functions. If an operation cannot be replicated with the Statement format, the Row format is used. MySQL automatically determines which format to use for each executed SQL statement.

Limitations

DataWorks implements real-time synchronization of MySQL data by subscribing to MySQL in real time. This feature currently supports only MySQL data sources that are RDS for MySQL 5.x or 8.x instances. Do not configure a PolarDB-X instance as a MySQL data source. Instead, configure it as a PolarDB-X data source. For more information, see Configure a PolarDB-X data source.
XA ROLLBACK is not supported. For transaction data prepared with XA PREPARE, real-time synchronization synchronizes the data to the destination. If XA ROLLBACK occurs later, real-time synchronization does not roll back the previously synchronized data on the destination. To handle XA ROLLBACK scenarios, you must manually remove the affected tables from the real-time synchronization task, add them back, and then perform full data initialization and incremental real-time synchronization again.
If you add a MySQL instance that belongs to another Alibaba Cloud account as a data source, subsequent synchronization tasks that use this data source can run only on an exclusive resource group for Data Integration. A shared resource group for Data Integration cannot access this data source.

Procedure

Configure the whitelist.
Add the CIDR block of the VPC of the exclusive resource group for Data Integration to the whitelist of the MySQL database.
1. Record network information for the resource group.
  1. Log on to the DataWorks console.
  2. In the left-side navigation pane, click Resource groups.
  3. On the Exclusive resource groups tab, find the target Data Integration resource group and click View Information.
  4. In the dialog box, copy the EIP address and CIDR block.
  5. On the Exclusive resource groups tab, find the target Data Integration resource group and click Network Settings.
  6. On the VPC binding tab, view and record the vSwitch CIDR block.
2. Whitelist the EIP address and CIDR block.
Create an account and grant permissions.
Create a database user account. The account must have the SELECT, REPLICATION SLAVE, REPLICATION CLIENT privileges.
1. Create an account.
  For more information, see Create a MySQL account.
2. Grant privileges.
  You can run the following command to grant privileges to the account, or grant the SUPER privilege. In the following statements, replace 'Synchronization account' with the account that you created.
```
-- CREATE USER 'Synchronization account'@'%' IDENTIFIED BY 'Password'; -- Creates a synchronization account and set a password. This allows the account to log on to the database from any host. % indicates any host.
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'Synchronization account'@'%'; -- Grants the SELECT, REPLICATION SLAVE, and REPLICATION CLIENT privileges to the synchronization account.
```
  *.* grants the synchronization account the preceding privileges on all tables in all databases. You can also grant the preceding privileges on specified tables in a specified database. For example, to grant the synchronization account the preceding privileges on the test.user table, use the following statement: GRANT SELECT, REPLICATION CLIENT ON test.user TO 'Synchronization account'@'%';.
  Note The REPLICATION SLAVE privilege is a global privilege. You cannot grant this privilege on a specific table in a specific database.
Enable MySQL binlog.
Check whether binlog is enabled and query the binlog format:
- Run the following statement to check whether binlog is enabled.
```
show variables like "log_bin";
```
  A return value of ON indicates that binlog is enabled.
- If you use a replica database to synchronize data, you can also run the following statement to check whether binlog is enabled.
```
show variables like "log_slave_updates";
```
  A return value of ON indicates that binlog is enabled for the replica database.
If binlog is not enabled, follow the instructions in the official MySQL documentation to enable it.
Run the following statement to query the binlog format.
```
show variables like "binlog_format";
```
Description of the returned results:
- A return value of ROW indicates the ROW format.
- A return value of STATEMENT indicates the STATEMENT format.
- A return value of MIXED indicates the MIXED format.

Next steps

After you complete these steps, the source and destination data sources are connected through the exclusive resource group for Data Integration, and the necessary access permissions are granted. You can then add the source and destination data sources to the DataWorks data source list and associate them with a data synchronization solution when you create one.

For more information about how to add a data source, see Add a data source.