Configure MySQL data source-Dataphin(Dataphin)-阿里云帮助中心

Before you synchronize MySQL data in real time or use Flink SQL to read MySQL CDC data, you must configure MySQL account permissions and enable MySQL Binlog.

Limits

Real-time MySQL data integration subscribes to MySQL Binlog and supports only RDS MySQL versions 5.x and 8.x. DRDS configurations are not supported.

Procedure

Create an account and configure account permissions.

Create a database account with SELECT, REPLICATION SLAVE, and REPLICATION CLIENT permissions.
1. Create an account.
  
  Run the following command to create a database account:
```
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
```
2. Configure permissions.
  
  Run the following command to grant permissions to the account. You can also assign SUPER permissions instead. Replace 'sync account' with the account you created.
```
-- 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.
```
  *.* grants the specified permissions on all tables in all databases. To limit permissions to a specific table, such as the user table in the test database, run: 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 that Binlog is enabled and check the Binlog format:
- Check Binlog status:
```
show variables like "log_bin";
```
  A result of ON indicates that Binlog is enabled.
- Check Binlog on a standby database:
```
show variables like "log_slave_updates";
```
  An ON result indicates Binlog is enabled on the standby database.
If Binlog is not enabled, see the MySQL official documentation to enable it. Run the following statement to query the Binlog format:
```
show variables like "binlog_format";
```
Results:
- ROW: The Binlog format is ROW.
- STATEMENT: The Binlog format is STATEMENT.
- MIXED: The Binlog format is MIXED.