MySQL CDC Configuration

Important Note

This configuration is only required for CDC (Change Data Capture) mode. If you plan to use convert mode only, you can skip this guide. For basic MySQL server setup, see MySQL Server Configuration.

Overview

MySQL CDC (Change Data Capture) uses Binary Logs (Binlogs) to track database changes. These logs record all data modifications, enabling DBConvert Streams to capture:

• INSERT operations
• UPDATE operations
• DELETE operations
• Schema changes (DDL)

Prerequisites

Before configuring CDC, ensure:

1. Your database is properly configured for DBConvert Streams
2. You're using a supported database version:
   • MySQL 8.0 or later
   • MariaDB
   • Percona
   • SingleStore DB
   • TiDB
   • Vitess

Binary Log Configuration

Check Current Status

Verify if binary logging is enabled:

SELECT @@log_bin;

Results:

• 1: Binary logging is enabled
• 0: Binary logging is disabled

Check detailed configuration:

-- View binary log format
SHOW VARIABLES LIKE 'binlog_format';

-- Check binary log status
SHOW MASTER STATUS;

-- List existing binary logs
SHOW BINARY LOGS;

Enable Binary Logging

If binary logging is disabled, configure your MySQL server:

1. Edit MySQL configuration file:

[mysqld]
server_id        = 1
log_bin          = mysql-bin
binlog_format    = row
binlog_row_image = full

Parameter	Value	Description
binlog_format	row	Required for capturing row-level changes
binlog_row_image	full	Captures complete row data (required)
log_bin	mysql-bin	Log file prefix (Ubuntu: /var/log/mysql/mysql-bin.log)

2. Restart MySQL server
3. Verify configuration using the check commands above

Required Privileges

For CDC operations, the database user needs these privileges:

-- Grant CDC privileges
GRANT SELECT, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'user'@'%';

-- Verify privileges
SHOW GRANTS FOR 'user'@'%';

CDC-Specific Performance Tuning

Optimize binary log handling:

[mysqld]
# Binary log settings
sync_binlog = 1000        # Sync to disk every 1000 transactions
binlog_cache_size = 1M    # Per-transaction cache
max_binlog_size = 1G      # Maximum size before rotation
binlog_expire_logs_days = 10  # Auto-purge old logs

# Replication settings
slave_parallel_workers = 4     # Number of parallel workers
slave_parallel_type = LOGICAL_CLOCK

Monitoring CDC

Monitor replication and binary log status:

-- Check binary log space usage
SHOW BINARY LOGS;

-- View current position
SHOW MASTER STATUS;

-- Monitor replication status
SHOW SLAVE STATUS\G

Cloud Provider CDC Setup

For cloud-specific CDC configuration, refer to:

• Azure Database for MySQL CDC Setup
• Google Cloud SQL MySQL CDC Setup
• Amazon RDS MySQL CDC Setup
• AWS Aurora MySQL CDC Setup
• DigitalOcean MySQL CDC Setup

Troubleshooting

Common CDC issues and solutions:

1. Binary Logs Not Enabled

   -- Check if enabled
   SELECT @@log_bin;
   -- Enable in my.cnf if 0

2. Incorrect Binary Log Format

   -- Check format
   SHOW VARIABLES LIKE 'binlog_format';
   -- Should be 'ROW'

3. Insufficient Privileges

   -- Grant missing privileges
   GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'user'@'%';

4. Binary Log Space Issues

   -- Check space usage
   SHOW BINARY LOGS;
   -- Purge old logs if needed
   PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);

For more details on MySQL replication and binary logs, see the MySQL Replication Documentation.