Configuring multi-source-replication for RDS for MySQL

8.0.35 and higher minor versions

5.7.44 and higher minor versions

Applications that need to merge or combine multiple shards on separate DB instances into a single shard.

Applications that need to generate reports from data consolidated from multiple sources.

Requirements to create consolidated long-term backups of data that's distributed among multiple RDS for MySQL DB instances.

Make sure that a DB instance configured as a multi-source replica has sufficient resources such as throughput, memory, CPU, and IOPS to handle the workload from multiple source instances.

Regularly monitor resource utilization on your multi-source replica and adjust the storage or instance configuration to handle the workload without straining resources.

You can configure multi-threaded replication on a multi-source replica by setting the system variable replica_parallel_workers to a value greater than 0. In this case, the number of threads allocated to each channel is the value of this variable, plus one coordinator thread to manage the applier threads.

Configure replication filters appropriately to avoid conflicts. To replicate an entire database to another database on a replica, you can use the --replicate-rewrite-db option. For example, you can replicate all tables in database A to database B on a replica instance. This approach can be helpful when all source instances are using the same schema naming convention. For information about the --replicate-rewrite-db option, see Replica Server Options and Variables in the MySQL documentation.

To avoid replication errors, avoid writing to the replica. We recommended that you enable the read_only parameter on multi-source replicas to block write operations. Doing so helps to eliminate replication issues caused by conflicting write operations.

To increase the performance of read operations such as sorts and high-load joins that are executed on the multi-source replica, consider using RDS Optimized Reads. This feature can help with queries that depend on large temporary tables or sort files. For more information, see Improving query performance for RDS for MySQL with Amazon RDS Optimized Reads.

To minimize replication lag and improve the performance of a multi-source replica, consider enabling optimized writes. For more information, see Improving write performance with RDS Optimized Writes for MySQL.

Perform management operations (such as changing configuration) on one channel at a time, and avoid performing changes to multiple channels from multiple connections. These practices can lead to conflicts in replication operations. For example, simultaneously executing rds_skip_repl_error_for_channel and rds_start_replication_for_channel procedures from multiple connections can cause skipping of events on a different channel than intended.

You can enable backups on a multi-source replication instance and export data from that instance to an Amazon S3 bucket to store it for long-term purposes. However, it's important to also configure backups with appropriate retention on the individual source instances. For information about exporting snapshot data to Amazon S3, see Exporting DB snapshot data to Amazon S3.

To distribute the read workload on a multi-source replica, you can create read replicas from a multi-source replica. You can locate these read replicas in different Amazon Web Services Regions based on your application's requirements. For more information about read replicas, see Working with MySQL read replicas.

Make sure that each source RDS for MySQL DB instance has automatic backups enabled. Enabling automatic backups enables binary logging. To learn how to enable automatic backups, see Enabling automated backups.

To avoid replication errors, we recommended that you block write operations to the source DB instances. You can do so by setting the read-only parameter to ON in a custom parameter group attached to the RDS for MySQL source DB instance. You can use the Amazon Web Services Management Console or the Amazon CLI to create a new custom parameter group or to modify an existing one. For more information, see Creating a DB parameter group and Modifying parameters in a DB parameter group.

For each source DB instance, add the IP address of the instance to the Amazon virtual private cloud (VPC) security group for the multi-source DB instance. To identify the IP address of a source DB instance, you can run the command dig RDS Endpoint. Run the command from an Amazon EC2 instance in the same VPC as the destination multi-source DB instance.

For each source DB instance, use a client to connect to the DB instance and create a database user with the required privileges for replication, as in the following example.

CREATE USER 'repl_user' IDENTIFIED BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user';

Back quotes (``) around the channel names are required.

If you change replication filters in the parameter group, the multi-source replica's sql_thread for all channels with updates are restarted to apply the changes dynamically. If an update involves a global filter, then all replication channels in the running state are restarted.

All global filters are applied before any channel-specific filters.

If a filter is applied globally and at the channel level, then only the channel-level filter is applied. For example, if the filters are replicate_ignore_db="db1,`channel_22`:db2", then replicate_ignore_db set to db1 is applied to all channels except for channel_22, and only channel_22 ignores changes from db2.

To monitor the status of all channels or a specific channel, connect to the multi-source replica and run the SHOW REPLICA STATUS or SHOW REPLICA STATUS FOR CHANNEL 'channel_name' command. For more information, see Checking Replication Status in the MySQL documentation.

To receive notification when a replication channel is started, stopped, or removed, use RDS event notification. For more information, see Working with Amazon RDS event notification.

To monitor the lag for a specific channel, check the ReplicationChannelLag metric for it. Data points for this metric have a period of 60 seconds (1 minute) are available for 15 days. To locate the replication channel lag for a channel, use the instance identifier and the replication channel name. To receive notification when this lag exceeds a particular threshold, you can set up a CloudWatch alarm. For more information, see Monitoring Amazon RDS metrics with Amazon CloudWatch.

Currently, RDS for MySQL supports configuring a maximum of 15 channels for a multi-source replica.

A read replica instance can't be configured as a multi-source replica.

To configure multi-source replication on RDS for MySQL running engine version 5.7, Performance Schema must be enabled on the replica instance. Enabling Performance Schema is optional on RDS for MySQL running engine version 8.0.

For RDS for MySQL running engine version 5.7, replication filters apply to all replication channels. For RDS for MySQL running engine version 8.0, you can configure filters that apply to all replication channels or to individual channels.

Restoring an RDS snapshot or performing a Point-in-time-Restore (PITR) doesn't restore multi-source replica channel configurations.

When you create a read replica of a multi-source replica, it only replicates data from the multi-source instance. It doesn't restore any channel configuration.

MySQL doesn't support setting up a different number of parallel workers for each channel. Every channel gets the same number of parallel workers based on the replica_parallel_workers value.

A channel must be configured for a source RDS for MySQL instance before any writes to that instance occur.

Each source RDS for MySQL instance must have GTID-based replication enabled.

A failover event on the DB cluster removes the multi-source replication configuration. Restoring that configuration requires repeating the configuration steps.