Setting up binary log replication for Aurora MySQL

From a command shell, stop the mysql service.

sudo service mysqld stop

Edit the my.cnf file (this file is usually under /etc).

sudo vi /etc/my.cnf

Add the log_bin and server_id options to the [mysqld] section. The log_bin option provides a file name identifier for binary log files. The server_id option provides a unique identifier for the server in source-replica relationships.

If encrypted replication isn't required, ensure that the external MySQL database is started with binlogs enabled and SSL is turned off.

The following are the relevant entries in the /etc/my.cnf file for unencrypted data.

log-bin=mysql-bin
server-id=2133421
innodb_flush_log_at_trx_commit=1
sync_binlog=1

If encrypted replication is required, ensure that the external MySQL database is started with SSL and binlogs enabled.

The entries in the /etc/my.cnf file include the .pem file locations for the MySQL database server.

log-bin=mysql-bin
server-id=2133421
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# Setup SSL.
ssl-ca=/home/sslcerts/ca.pem
ssl-cert=/home/sslcerts/server-cert.pem
ssl-key=/home/sslcerts/server-key.pem

Additionally, the sql_mode option for your MySQL DB instance must be set to 0, or must not be included in your my.cnf file.

While connected to the external MySQL database, record the external MySQL database's binary log position.

mysql> SHOW MASTER STATUS;

Your output should be similar to the following:

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000031 |      107 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

For more information, see Setting the replication source configuration in the MySQL documentation.

Start the mysql service.

sudo service mysqld start

Connect to your DB cluster using a MySQL client.

Issue the mysqldump command. For example:

PROMPT> mysqldump --databases database_name --single-transaction
--order-by-primary -r backup.sql -u local_users -p

After you create the dump file, you can delete the DB cluster copy.

Connect to your read replica and stop replication by running the mysql.rds_stop_replication procedure.

While the read replica is Stopped, Connect to the read replica and run the SHOW SLAVE STATUS (Aurora MySQL version 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command. Retrieve the current binary log file name from the Relay_Master_Log_File field and the log file position from the Exec_Master_Log_Pos field. Save these values for when you start replication.

While the read replica remains Stopped, create a DB snapshot of the read replica. For more information, see Creating a DB snapshot in the Amazon Relational Database Service User Guide.

Delete the read replica.

Before you create a dump, you need to ensure that the binlog location for the dump is current with the data in your source instance. To do this, you must first stop any write operations to the instance with the following command:

mysql> FLUSH TABLES WITH READ LOCK;

Create a dump of your MySQL database using the mysqldump command as shown following:

PROMPT> sudo mysqldump --databases database_name --master-data=2  --single-transaction \
--order-by-primary -r backup.sql -u local_user -p

After you have created the dump, unlock the tables in your MySQL database with the following command:

mysql> UNLOCK TABLES;

Copy the output of the mysqldump command from your replication source to a location that can also connect to your Aurora MySQL DB cluster.

Connect to your Aurora MySQL DB cluster using the mysql command. The following is an example.

PROMPT> mysql -h host_name -port=3306 -u db_master_user -p

At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into the Aurora MySQL DB cluster, for example:

mysql> source backup.sql;

Copy the output of the mysqldump command from your replication source to a location that can also connect to your MySQL DB instance.

Connect to your MySQL DB instance using the mysql command. The following is an example.

PROMPT> mysql -h host_name -port=3306 -u db_master_user -p

At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into the MySQL DB instance, for example:

mysql> source backup.sql;

Copy the output of the mysqldump command from your replication source to a location that can also connect to your MySQL database.

Connect to your MySQL database using the mysql command. The following is an example.

PROMPT> mysql -h host_name -port=3306 -u db_master_user -p

At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into your MySQL database. The following is an example.

mysql> source backup.sql;

Find the starting place for replication. You need the binlog file name and binlog position.

If your DB cluster replica target was created from the following:

Connect to the DB cluster and call the following procedures to start replication with your replication source using the binary log file name and location from the previous step:

The following example is for Aurora MySQL version 3.

CALL mysql.rds_set_external_source ('mydbinstance.123456789012.us-east-1.rds.amazonaws.com', 3306,
    'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 0);
CALL mysql.rds_start_replication;

If your DB instance replica target was created from a DB snapshot, then you need the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS (Aurora MySQL version 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command when you created the snapshot of your replication source.

Connect to the DB instance and call 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 to start replication with your replication source. Use the binary log file name and location from the previous step. The following is an example.

CALL mysql.rds_set_external_master ('mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', 3306,
    'repl_user', 'password', 'mysql-bin-changelog.000031', 107, 0);
CALL mysql.rds_start_replication;

Retrieve the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS (Aurora MySQL version 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command when you created the snapshot of your replication source. If your external MySQL replica target was populated from the output of the mysqldump command with the --master-data=2 option, then the binlog file and binlog position are included in the output. The following is an example.

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.000031', MASTER_LOG_POS=107;

Connect to the external MySQL replica target, and issue CHANGE MASTER TO and START SLAVE (Aurora MySQL version 2) or START REPLICA (Aurora MySQL version 3) to start replication with your replication source using the binary log file name and location from the previous step, for example:

CHANGE MASTER TO
  MASTER_HOST = 'mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com',
  MASTER_PORT = 3306,
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = 'password',
  MASTER_LOG_FILE = 'mysql-bin-changelog.000031',
  MASTER_LOG_POS = 107;
-- And one of these statements depending on your engine version:
START SLAVE; -- Aurora MySQL version 2
START REPLICA; -- Aurora MySQL version 3