Configure a MySQL data source-阿里云帮助中心

This topic explains how to configure the required network connections and account permissions before you can run a data synchronization node to synchronize data in real time from a MySQL database to an AnalyticDB for MySQL instance.AnalyticDB for MySQL

Prerequisites

Before you configure the data source, ensure you have completed the following preparations:

You have purchased a source MySQL database and a destination AnalyticDB for MySQL instance.
You have purchased and configured an exclusive resource group for Data Integration. For more information, see Plan and configure resources.
Evaluate and plan the network environment: Before you perform data integration, you must select a network connection method based on your business requirements and use the method to connect the data sources to the exclusive resource group for Data Integration. After the data sources and the exclusive resource group for Data Integration are connected, you can refer to the operations described in this topic to configure access settings such as vSwitches and whitelists.
- If the data sources and the exclusive resource group for Data Integration reside in the same region and virtual private cloud (VPC), they are automatically connected.
- If the data sources and the exclusive resource group for Data Integration reside in different network environments, you must connect the data sources and the resource group by using methods such as a VPN gateway.

Background information

To synchronize data, you must ensure that your data sources can communicate with the exclusive resource group for Data Integration and have the necessary account permissions.

Whitelist
If your data source and exclusive resource group for Data Integration are in the same VPC, add the VPC's CIDR block to the data source's whitelist to allow access from the resource group.
Create an account and grant permissions to the account
You must create an account that can be used to access the data sources, read data from the source, and write data to the destination during the data synchronization process.
Other access restrictions.
If the source is a MySQL database, you must enable binary logging (binlog). A binlog is a log that records all database schema changes, such as CREATE and ALTER operations, and data modifications, such as INSERT, UPDATE, and DELETE operations. You can use the content of the binary logs to view the database change history, perform incremental backups and recovery, and replicate data between primary and secondary databases.
The binary log has the following formats:
- Statement: This is SQL statement-based replication. The binlog records each SQL statement that modifies data.
- Row: This is row-based replication. The binlog does not save contextual information about the SQL statement. It saves only the modified records.
- Mixed: This is a mixed-mode replication that combines the Statement and Row formats. The Statement format is used for general statement modifications, such as functions. If an operation cannot be replicated using the Statement format, the Row format is used. MySQL automatically determines which format to use for each executed SQL statement.

Limits

Real-time synchronization of MySQL data in DataWorks relies on subscribing to the MySQL binary log. This feature supports only ApsaraDB RDS for MySQL instances that run MySQL 5.x or 8.x. Do not configure a PolarDB-X 1.0 instance as a MySQL data source. Instead, configure the instance as a PolarDB-X 1.0 data source. For instructions, see Add a DRDS data source.
A data synchronization node synchronizes transaction data that is in the XA PREPARE state. If an XA ROLLBACK statement is later executed, the destination data is not rolled back. To handle this scenario, you must manually remove the table from the data synchronization node, re-add it, and then perform a full data initialization followed by incremental real-time synchronization.
If you add a MySQL instance from a different Alibaba Cloud account as a data source, data synchronization nodes that use it must run on an exclusive resource group for Data Integration. The shared resource group for Data Integration cannot access this data source.

Procedure

Configure the whitelist.
Add the CIDR block of the VPC that contains the exclusive resource group for Data Integration to your MySQL database whitelist.
1. View and record the elastic IP address (EIP) and CIDR block of the exclusive resource group for Data Integration.
  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 exclusive resource group for Data Integration and click View Information in the Actions column.
  4. In the Exclusive Resource Groups dialog box, view and record the values of the EIPAddress and CIDR Blocks parameters.
  5. On the Exclusive Resource Groups tab, find the exclusive resource group for Data Integration and click Network Settings in the Actions column.
  6. On the VPC Binding tab of the page that appears, view and record the CIDR block of the vSwitch with which the exclusive resource group for Data Integration is associated.
2. Add the recorded EIP, CIDR block, and vSwitch CIDR block to the whitelist of your MySQL cluster.
Create an account and grant permissions.
Create a database logon account with the SELECT, REPLICATION SLAVE, REPLICATION CLIENT privileges.
1. Create an account.
  For more information, see Create an account to access a MySQL database.
2. Grant privileges to the account.
  You can run the following commands to grant the required privileges. Alternatively, you can grant the SUPER privilege. In the commands, replace 'sync_account' with your account name.
```
-- Create a synchronization account and set a password, allowing it to connect from any host. The percent sign (%) is a wildcard for any host.
-- CREATE USER 'sync_account'@'%' IDENTIFIED BY 'password'; 
-- Grant the SELECT, REPLICATION SLAVE, and REPLICATION CLIENT privileges to the synchronization account.
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync_account'@'%';
```
  *.* grants the privileges on all tables in all databases. You can also grant privileges on specific tables in a specific database. For example, to grant the privileges on the user table in the test database, run the following statement: GRANT SELECT, REPLICATION CLIENT ON test.user TO 'sync_account'@'%';.
  Note The REPLICATION SLAVE privilege is global. You cannot grant this privilege on a specific table in a specific database.
Enable the binary log for MySQL.
Perform the following steps to check whether the binary log is enabled and to query its format:
- Run the following statement to check whether the binary log is enabled:
```
show variables like "log_bin";
```
  If the value in the result is ON, the binary log is enabled.
- If you use a secondary database for synchronization, run the following statement to check whether the binary log is enabled:
```
show variables like "log_slave_updates";
```
  If the value in the result is ON, the binary log is enabled on the secondary database.
If the result is not ON, refer to the official MySQL documentation to enable the binary log.
Run the following statement to check the binary log format:
```
show variables like "binlog_format";
```
The result can be one of the following values:
- If the result is ROW, the binary log format is row-based.
- If the result is STATEMENT, the binary log format is statement-based.
- If the result is MIXED, the binary log format is mixed.

Next steps

After the data sources are configured, the source, destination, and exclusive resource group for Data Integration are connected. Then, the exclusive resource group for Data Integration can be used to access the data sources. You can add the source and destination to DataWorks, and associate them with a data synchronization solution when you create the solution.

To learn how to add a data source, see Add data sources.