Scaling reads for your MySQL database with Amazon Aurora

Make the source MySQL DB instance read-only:

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

Run the SHOW MASTER STATUS command on the source MySQL DB instance to determine the binlog location. You receive output similar to the following example:

File                        Position
------------------------------------
 mysql-bin-changelog.000031      107
------------------------------------

Copy the database from the external MySQL DB instance to the Amazon Aurora MySQL DB cluster using mysqldump. For very large databases, you might want to use the procedure in Importing data to a MySQL or MariaDB DB instance with reduced downtime in the Amazon Relational Database Service User Guide.

For Linux, macOS, or Unix:

mysqldump \
    --databases <database_name> \
    --single-transaction \
    --compress \
    --order-by-primary \
    -u local_user \
    -p local_password | mysql \
        --host aurora_cluster_endpoint_address \
        --port 3306 \
        -u RDS_user_name \
        -p RDS_password

For Windows:

mysqldump ^
    --databases <database_name> ^
    --single-transaction ^
    --compress ^
    --order-by-primary ^
    -u local_user ^
    -p local_password | mysql ^
        --host aurora_cluster_endpoint_address ^
        --port 3306 ^
        -u RDS_user_name ^
        -p RDS_password

Use the --host, --user (-u), --port and -p options in the mysql command to specify the hostname, user name, port, and password to connect to your Aurora DB cluster. The host name is the DNS name from the Amazon Aurora DB cluster endpoint, for example, mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com. You can find the endpoint value in the cluster details in the Amazon RDS Management Console.

Make the source MySQL DB instance writeable again:

mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;

For more information on making backups for use with replication, see Backing up a source or replica by making it read only in the MySQL documentation.

In the Amazon RDS Management Console, add the IP address of the server that hosts the source MySQL database to the VPC security group for the Amazon Aurora DB cluster. For more information on modifying a VPC security group, see Security groups for your VPC in the Amazon Virtual Private Cloud User Guide.

You might also need to configure your local network to permit connections from the IP address of your Amazon Aurora DB cluster, so that it can communicate with your source MySQL instance. To find the IP address of the Amazon Aurora DB cluster, use the host command.

host aurora_endpoint_address

The host name is the DNS name from the Amazon Aurora DB cluster endpoint.

Using the client of your choice, connect to the external MySQL instance and create a MySQL user to be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following is an example.

CREATE USER 'repl_user'@'example.com' IDENTIFIED BY 'password';

For the external MySQL instance, grant REPLICATION CLIENT and REPLICATION SLAVE privileges to your replication user. For example, to grant the REPLICATION CLIENT and REPLICATION SLAVE privileges on all databases for the 'repl_user' user for your domain, issue the following command.

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'example.com'
    IDENTIFIED BY 'password'; 

Take a manual snapshot of the Aurora MySQL DB cluster to be the read replica before setting up replication. If you need to reestablish replication with the DB cluster as a read replica, you can restore the Aurora MySQL DB cluster from this snapshot instead of having to import the data from your MySQL DB instance into a new Aurora MySQL DB cluster.

Make the Amazon Aurora DB cluster the replica. Connect to the Amazon Aurora DB cluster as the master user and identify the source MySQL database as the replication source by using the mysql.rds_set_external_master (Aurora MySQL version 2) or mysql.rds_set_external_source (Aurora MySQL version 3) and mysql.rds_start_replication procedures.

Use the binlog file name and position that you determined in Step 2. The following is an example.

For Aurora MySQL version 2:
CALL mysql.rds_set_external_master ('mymasterserver.example.com', 3306,
    'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 0);

For Aurora MySQL version 3:
CALL mysql.rds_set_external_source ('mymasterserver.example.com', 3306,
    'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 0);

On the Amazon Aurora DB cluster, call the mysql.rds_start_replication procedure to start replication.

CALL mysql.rds_start_replication;