如何从数据中心MySQL Slave配置AWS RDS只读副本（主→从→RDS架构）

Alright, let's tackle this Master→Slave→RDS Read Replica setup. I’ve implemented similar architectures before, so here’s a step-by-step guide to make it work smoothly:

First, make sure you have these bases covered:

• Version Compatibility: Your local MySQL Slave version must match or be compatible with the AWS RDS MySQL version you plan to use (e.g., 5.7 → 5.7, or 5.7 → 8.0 with minor caveats; same major version is safest).
• Binlog Enabled: The local Slave must have binary logging turned on (required for RDS to pull replication data). Check your my.cnf/my.ini for these settings:

  log_bin = mysql-bin
  server_id = [unique-id-different-from-master]
  binlog_format = ROW  # Recommended for consistent replication
  expire_logs_days = 7  # Auto-clean old binlogs to save space
  

  If you had to modify these, restart the MySQL service and verify with SHOW VARIABLES LIKE 'log_bin';
• Replication Health: Confirm the local Slave is already syncing properly with its Master. Run SHOW SLAVE STATUS\G and ensure Slave_IO_Running and Slave_SQL_Running both return Yes.
• Network Access: The local Slave must be able to reach your RDS instance’s endpoint on port 3306. Update your RDS security group to allow inbound traffic from the local Slave’s public/private IP, and ensure no firewalls on the local side block outbound connections to RDS.

RDS needs a dedicated user to pull binlog data from your local Slave. Create one with minimal required permissions:

CREATE USER 'rds_repl'@'%' IDENTIFIED BY 'your-strong-password-here';
GRANT REPLICATION SLAVE ON *.* TO 'rds_repl'@'%';
FLUSH PRIVILEGES;

Note: Restrict the @'%' to RDS’s IP range if possible for better security, but % works for testing or if RDS uses dynamic IPs.

You need to seed the RDS instance with a snapshot of your local Slave’s data so replication can start from a consistent point. Use mysqldump for smaller datasets, or Percona XtraBackup for large ones:

Option 1: mysqldump (Small to Medium Data) #

Run this on your local Slave to create a consistent dump with replication position:

mysqldump -u root -p --single-transaction --master-data=2 --all-databases --events --routines > slave_data_dump.sql

• --single-transaction: Ensures a consistent snapshot without locking tables (for InnoDB)
• --master-data=2: Adds a commented CHANGE MASTER TO line in the dump file with the current binlog position (we’ll use this later)

Then import the dump into your new RDS instance:

mysql -h your-rds-endpoint.rds.amazonaws.com -u rds-admin-user -p < slave_data_dump.sql

Option 2: Percona XtraBackup (Large Data) #

If your dataset is huge, mysqldump will be too slow. Use XtraBackup to create a hot backup, then restore it to RDS via S3 (upload the backup to S3 and use RDS’s restore from S3 feature to load the data into a new RDS instance).

AWS RDS supports creating read replicas from external MySQL instances (your local Slave here). You can do this via the AWS Console or CLI:

Via AWS Console #

1. Go to the RDS Dashboard → Create database
2. Under "Engine options", select MySQL, then scroll to Replication options
3. Choose Create read replica from an external source
4. Fill in the external source details:
   • External master endpoint: Your local Slave’s public IP or domain
   • Port: 3306
   • Replication username: rds_repl (the user we created earlier)
   • Replication password: The password you set for rds_repl
   • Binlog filename & position: Extract these from the slave_data_dump.sql file (look for the line starting with -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000XXX', MASTER_LOG_POS=XXXX;)
5. Configure your RDS instance specs, storage, security group (ensure it allows traffic from your local Slave), and launch the instance.

Via AWS CLI #

If you prefer automation, run this command (replace placeholders with your values):

aws rds modify-db-instance \
  --db-instance-identifier your-rds-instance-name \
  --enable-external-master \
  --external-master-host local-slave-ip-or-domain \
  --external-master-port 3306 \
  --external-master-user rds_repl \
  --external-master-password your-repl-password \
  --external-master-log-file mysql-bin.000XXX \
  --external-master-log-position XXXX

Once the RDS instance is in available state, verify the entire chain works:

1. Check RDS Replication: Log into your RDS instance and run SHOW SLAVE STATUS\G. Confirm Slave_IO_Running and Slave_SQL_Running are Yes, and Seconds_Behind_Master is a low number (or 0).
2. End-to-End Test: Write a test record to your local Master:

   INSERT INTO test_db.test_table (column1) VALUES ('test-replication');
   

   Wait a few seconds, then check if the record exists in your local Slave, then in your RDS instance. If it shows up in all three, the chain is working!

• Monitor Load: Your local Slave now has two replication streams (from Master, to RDS). Keep an eye on its CPU, IO, and memory usage to ensure it can handle the extra load.
• Binlog Retention: Ensure your local Slave doesn’t purge binlogs before RDS has a chance to process them. Adjust expire_logs_days if needed, or monitor RDS’s Read_Master_Log_Pos vs the local Slave’s Purge_Master_Log_To.
• Failover Considerations: If your local Master goes down, your local Slave can promote to Master, but you’ll need to reconfigure RDS to replicate from the new Master. Plan for this scenario in your disaster recovery process.
• Security: Avoid using % for the replication user’s host if possible. Use RDS’s VPC endpoint and restrict access to your local network via security groups/VPC peering.

内容的提问来源于stack exchange，提问作者Roni Baby