🚀 Making AWS DMS Work with RDS MySQL: What You Really Need to Know

Migrating data using AWS Database Migration Service (DMS) is smooth — once you get past the tricky setup. If you're using RDS MySQL as a source, here's exactly what you need to do to avoid common validation failures like:

Error Code [10001]: Binary Logging must be enabled for MySQL server

---

✅ 1. Create or Use a Custom Parameter Group

Go to the RDS Console → Parameter groups:

• Create a new MySQL parameter group (you can't modify the default one).

• Set the following values:

You won’t see log_bin — that’s managed automatically by AWS.

---

✅ 2. Attach the Parameter Group to Your RDS Instance

• Go to RDS → Databases → [your instance]

• Click Modify

• Under DB Parameter Group, select the custom group you created

• Apply immediately or during the next maintenance window

• Then Reboot the RDS instance

---

✅ 3. Enable Backup Retention (This Is Critical)

• Still in the Modify screen, scroll to Backup Retention Period

• Set it to at least 1 day

• This enables binary logging (log_bin), which is required for DMS CDC (change data capture)

If Backup Retention = 0 → log_bin will stay OFF → DMS won’t work.

---

✅ 4. Set Up a MySQL User with Required Privileges

Before you begin to work with a MySQL database as a source for AWS DMS, make sure that you have the following prerequisites. These prerequisites apply to either self-managed or AWS-managed sources.

You must have an account for AWS DMS that has the Replication Admin role. The role needs the following privileges:

Grant core permissions:

sqlCopyEditCREATE USER 'dms_user'@'%' IDENTIFIED BY 'StrongPassword123!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'dms_user'@'%';
FLUSH PRIVILEGES;

If you're using MySQL-specific premigration assessments, add these:

sqlCopyEditGRANT SELECT ON mysql.user TO 'dms_user'@'%';
GRANT SELECT ON mysql.db TO 'dms_user'@'%';
GRANT SELECT ON mysql.tables_priv TO 'dms_user'@'%';
GRANT SELECT ON mysql.role_edges TO 'dms_user'@'%'; -- Only for MySQL 8.0.11 and higher

---

✅ 5. Verify Your Settings

Run these SQL queries:

sqlCopyEditSHOW VARIABLES LIKE 'log_bin';          -- Should be ON
SHOW VARIABLES LIKE 'binlog_format';    -- Should be ROW
SHOW VARIABLES LIKE 'binlog_row_image'; -- Should be FULL

---

✅ 6. Test in AWS DMS

• Create your DMS source endpoint

• Use your RDS instance and the dms_user credentials

• Hit "Test Connection" — you should now pass all validation checks

---

🧠 Bonus Tips

• Always use the writer instance if your RDS setup has read replicas

• Don’t forget subnet group, security groups, and port 3306 access

• For CDC, make sure your DMS task is set to "Full load + CDC"

---

✅ TL;DR – What You Need

---

Let me know if you want this exported to Markdown, PDF, or styled for a blog platform!