DigitalOcean Managed Database CDC Setup Guide

This guide covers how to enable and configure Change Data Capture (CDC) for DigitalOcean's managed MySQL and PostgreSQL databases.

1. Prerequisites

• DigitalOcean account with managed database cluster
• Database connection credentials
• Access to database configuration
• Firewall/network access to database

2. MySQL Configuration

2.1 Binary Logging Status

Binary logging is enabled by default on DigitalOcean managed MySQL databases. Verify the current configuration:

-- Check binary log settings
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_row_image';
SHOW VARIABLES LIKE 'expire_logs_days';

-- Verify binary logging is enabled
SHOW BINARY LOGS;

-- Check current binary log position
SHOW MASTER STATUS;

⚠️ Note: DigitalOcean managed databases have pre-configured settings for binary logging. Configuration changes require DigitalOcean support assistance.

2.2 Configure MySQL User Privileges

-- Create replication user
CREATE USER 'cdc_user'@'%' IDENTIFIED BY 'strong_password';

-- Grant required privileges
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc_user'@'%';
GRANT SELECT ON database_name.* TO 'cdc_user'@'%';
FLUSH PRIVILEGES;

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

3. PostgreSQL Configuration

3.1 Logical Replication Status

DigitalOcean PostgreSQL databases have logical replication enabled by default. Verify the settings:

-- Check WAL level
SHOW wal_level;

-- Verify replication settings
SELECT name, setting 
FROM pg_settings 
WHERE name IN (
    'wal_level',
    'max_replication_slots',
    'max_wal_senders'
);

3.2 Configure PostgreSQL User Privileges

-- Create replication user
CREATE USER cdc_user WITH REPLICATION PASSWORD 'strong_password';

-- Grant necessary privileges
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO cdc_user;

-- Verify user privileges
\du cdc_user

4. Network Access Configuration

4.1 Configure Trusted Sources

1. Navigate to DigitalOcean Console → Databases
2. Select your database
3. Go to Settings → Trusted Sources
4. Add your application's IP addresses:
   • Click Add trusted source
   • Enter IP address or range
   • Add description (e.g., "CDC Application")
   • Click Save

4.2 Connection Information

1. In the database cluster overview, find the connection details:
   • Host
   • Port
   • Database name
   • Username
   • Password

Connection string formats:

MySQL:

mysql://cdc_user:password@host:port/database

PostgreSQL:

postgresql://cdc_user:password@host:port/database

5. Troubleshooting

Common issues and solutions:

5.1 MySQL Issues

ERROR: Access denied for user
→ Verify user privileges
→ Check IP is in trusted sources
→ Confirm password is correct

ERROR: Binary log position not found
→ Check binary log retention
→ Verify replication user privileges

5.2 PostgreSQL Issues

ERROR: no pg_hba.conf entry
→ Add IP to trusted sources
→ Wait for trusted source to propagate

ERROR: replication slot creation failed
→ Check available replication slots
→ Verify user has REPLICATION privilege

6. Limitations

1. MySQL

   • Cannot modify binary log format
   • Binary log retention period is fixed
   • Limited configuration options

2. PostgreSQL

   • Fixed number of replication slots
   • Cannot modify WAL retention
   • Pre-configured WAL settings

7. Additional Resources

• DigitalOcean Database Documentation
• MySQL Replication Documentation
• PostgreSQL Logical Replication