Configure MySQL data source - Dataphin - Alibaba Cloud Documentation Center

To configure real-time synchronization of MySQL data or utilize Flink SQL for reading MySQL CDC data, refer to this topic for setting up MySQL account permissions and enabling MySQL Binlog.

Limits

Dataphin offers real-time MySQL data integration through subscription to MySQL Binlog. This synchronization feature is compatible with RDS MySQL versions 5.x and 8.x only, and is not applicable to DRDS configurations.

Procedure

Create an account and configure account permissions.

Plan a database login account for subsequent operations, ensuring it has SELECT, REPLICATION SLAVE, and REPLICATION CLIENT permissions on the database.
1. Create an account.
  
  Use the command below to create a database account:
```
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```
2. Configure permissions.
  
  Grant the necessary permissions to the account using the command below, or alternatively, assign SUPER permissions to the account. Replace 'sync account' with the previously created account when executing the statement.
```
-- CREATE USER 'sync account'@'%' IDENTIFIED BY 'password'; // Create a sync account with a password, permitting database access from any host. % denotes any host.
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync account'@'%'; // Bestow SELECT, REPLICATION SLAVE, and REPLICATION CLIENT permissions on the sync account for all database tables.
```
  The wildcard *.* signifies that the sync account has been granted the specified permissions for all tables across every database. To restrict permissions to certain tables within a specific database, for instance, granting the sync account these permissions solely for the user table in the test database, the following command should be used: GRANT SELECT, REPLICATION CLIENT ON test.user TO 'sync account'@'%';.
  
  Note
  REPLICATION SLAVE is a global permission and cannot be restricted to specific tables for the sync account.
Enable MySQL Binlog.

Verify Binlog activation and inquire about the Binlog format with the following steps:
- Check Binlog status using this statement:
```
show variables like "log_bin";
```
  A result of ON confirms that Binlog is active.
- To verify Binlog on a standby database, use this statement:
```
show variables like "log_slave_updates";
```
  An ON result indicates Binlog is enabled on the standby database.
If Binlog is not enabled, consult the MySQL official documentation for activation instructions. Query the Binlog format with the statement below.
```
show variables like "binlog_format";
```
Interpreting the results:
- If the result is ROW, this signifies that the Binlog format is set to ROW.
- A result of STATEMENT signifies that the Binlog format is set to STATEMENT.
- If the result is MIXED, this signifies that the Binlog format is set to MIXED.