Local SQL as Read Replica for RDS.

To set up a local MySQL instance as a read replica for your Amazon RDS database without locking the primary database or incurring downtime, you can use the following approach:

1. Use mysqldump with the --single-transaction option to create a consistent snapshot of your database without locking tables. This option uses a consistent read and ensures that the data dumped reflects the state of the database at a single point in time.

2. When running mysqldump, include the following important flags:

• --single-transaction (for a consistent snapshot)
• --compress (to reduce network traffic)
• --order-by-primary (to optimize the dump)

3. Before importing the data, capture the binary log file and position from the source RDS instance by running the SHOW MASTER STATUS command. Note these details as you'll need them to configure replication.

4. Import the dumped data into your local MySQL instance.

5. After the import is complete, configure your local MySQL instance as a replica by setting up the replication connection to point to your RDS source instance using the binary log file and position you captured earlier.

For MySQL and MariaDB databases, you can also consider using delayed replication as an additional safety measure. This allows you to specify a time delay for replication, which can be useful for disaster recovery scenarios.

If you're still experiencing issues with the binary log position changing during the dump process, you might want to consider using the mysql.rds_start_replication_until stored procedure. This allows you to start replication and have it proceed until it reaches a specific binary log file location, giving you more control over the replication process.

Remember that while this approach minimizes disruption to your primary database, there will still be some replication lag as your local replica catches up with all the transactions that occurred during the data transfer process.
Sources
Configuring multi-source-replication for Amazon RDS for MySQL - Amazon Relational Database Service
Configuring delayed replication with MySQL - Amazon Relational Database Service
Promoting a read replica to be a standalone DB instance - Amazon Relational Database Service

One way to achieve this is by using the AWS Database Migration Service (DMS). This service is designed to help you migrate or replicate data from a source database, such as your Amazon RDS instance, to a target database, which can be a database on your local network. The process involves setting up a replication instance within your AWS Virtual Private Cloud (VPC), configuring source and target endpoints for the respective databases, and then creating a replication task to manage the data flow. This solution allows for both one-time data migration and continuous data replication, depending on your needs.

When considering the costs of this approach, several factors come into play. The primary expense is the AWS DMS service itself, which is billed based on the size of the replication instance you choose and the amount of data processed. You'll also incur costs for data transfer, as moving data from AWS to your on-premises network is an egress operation. For the network connection, you would need a reliable and secure link, which could involve services like AWS Site-to-Site VPN or AWS Direct Connect, each with its own associated pricing model.