RDS - MySQL CDC troubleshooting

Troubleshooting for Data Integration Change Data Capture (CDC) with an Amazon RDS MySQL Replica, covering AWS Console settings, MySQL configurations, required permissions, binlog parameters, and common issues.

1. AWS console configuration

• RDS Instance Type: Ensure to use a Read Replica. Check status: Active.
• MySQL Version: Must be 5.7 or later.
• Network Access:
  • Publicly Accessible: Yes (if Data Integration connects externally).
  • Security Group: Allow TCP 3306 from Data Integration IP range.
• Binlog Retention: Increase if needed (default is 24h).

CALL mysql.rds_set_configuration('binlog retention hours', 168); -- 7 days

2. MySQL configuration

Binlog settings

CDC requires binary logging with ROW format and FULL row image:

SHOW VARIABLES LIKE 'binlog%';

Expected values:

Variable	Value
binlog_format	ROW
binlog_row_image	FULL
log_bin	ON

If values are incorrect, change the Parameter Group in AWS RDS.

Verify binary logs

Ensure logs exist:

SHOW BINARY LOGS;

Check current binlog position:

SHOW MASTER STATUS;

Ensure Data Integration uses the correct File and Position.

3. Data Integration user permissions

CDC requires:

• REPLICATION SLAVE, REPLICATION CLIENT, SELECT Check privileges:

SHOW GRANTS FOR 'data_integration_user'@'%';

If missing, grant:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'data_integration_user'@'%';
GRANT SELECT ON your_database.* TO 'data_integration_user'@'%';
FLUSH PRIVILEGES;

4. Replication status (for read replica)

Ensure replication is active:

SHOW SLAVE STATUS\G;

Expected values:

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

If not syncing, restart:

STOP SLAVE;
START SLAVE;

5. Data Integration connection testing

• Test in Data Integration Data Sources.
• Verify RDS Security Group settings.
• Ensure correct credentials.
• Run sample query.

SELECT * FROM your_database.your_table LIMIT 10;

If it fails, check permissions.

Common errors and fixes

Error	Resolution
Access denied for user	Ensure correct GRANTS for Data Integration user.
Table does not exist	Verify binlog_format=ROW and correct database selection.
Binary logs not found	Increase binlog retention if needed.
Replication lag	Optimize queries, and increase replica size.