Replicating - Amazon Relational Database Service

Replicating

The following stored procedures control how transactions are replicated from an external database into RDS for MySQL, or from RDS for MySQL to an external database. To learn how to use replication based on global transaction identifiers (GTIDs) with RDS for MySQL, see Using GTID-based replication for Amazon RDS for MySQL.

mysql.rds_next_master_log

Changes the source database instance log position to the start of the next binary log on the source database instance. Use this procedure only if you are receiving replication I/O error 1236 on a read replica.

Syntax

CALL mysql.rds_next_master_log( curr_master_log );

Parameters

curr_master_log

The index of the current master log file. For example, if the current file is named mysql-bin-changelog.012345, then the index is 12345. To determine the current master log file name, run the SHOW REPLICA STATUS command and view the Master_Log_File field.

Note

Previous versions of MySQL used SHOW SLAVE STATUS instead of SHOW REPLICA STATUS. If you are using a MySQL version before 8.0.23, then use SHOW SLAVE STATUS.

Usage notes

The master user must run the mysql.rds_next_master_log procedure.

Warning

Call mysql.rds_next_master_log only if replication fails after a failover of a Multi-AZ DB instance that is the replication source, and the Last_IO_Errno field of SHOW REPLICA STATUS reports I/O error 1236.

Calling mysql.rds_next_master_log can result in data loss in the read replica if transactions in the source instance were not written to the binary log on disk before the failover event occurred.

You can reduce the chance of this happening by setting the source instance parameters sync_binlog and innodb_support_xa to 1, although this might reduce performance. For more information, see Troubleshooting a MySQL read replica problem.

Examples

Assume replication fails on an RDS for MySQL read replica. Running SHOW REPLICA STATUS\G on the read replica returns the following result:

*************************** 1. row *************************** Replica_IO_State: Source_Host: myhost.XXXXXXXXXXXXXXX.rr-rrrr-1.rds.amazonaws.com Source_User: MasterUser Source_Port: 3306 Connect_Retry: 10 Source_Log_File: mysql-bin-changelog.012345 Read_Source_Log_Pos: 1219393 Relay_Log_File: relaylog.012340 Relay_Log_Pos: 30223388 Relay_Source_Log_File: mysql-bin-changelog.012345 Replica_IO_Running: No Replica_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Source_Log_Pos: 30223232 Relay_Log_Space: 5248928866 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Source_SSL_Allowed: No Source_SSL_CA_File: Source_SSL_CA_Path: Source_SSL_Cert: Source_SSL_Cipher: Source_SSL_Key: Seconds_Behind_Master: NULL Source_SSL_Verify_Server_Cert: No Last_IO_Errno: 1236 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin-changelog.013406' at 1219393, the last event read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4, the last byte read from '/rdsdbdata/log/binlog/mysql-bin-changelog.012345' at 4.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Source_Server_Id: 67285976

The Last_IO_Errno field shows that the instance is receiving I/O error 1236. The Master_Log_File field shows that the file name is mysql-bin-changelog.012345, which means that the log file index is 12345. To resolve the error, you can call mysql.rds_next_master_log with the following parameter:

CALL mysql.rds_next_master_log(12345);
Note

Previous versions of MySQL used SHOW SLAVE STATUS instead of SHOW REPLICA STATUS. If you are using a MySQL version before 8.0.23, then use SHOW SLAVE STATUS.

mysql.rds_reset_external_master

Reconfigures an RDS for MySQL DB instance to no longer be a read replica of an instance of MySQL running external to Amazon RDS.

Important

To run this procedure, autocommit must be enabled. To enable it, set the autocommit parameter to 1. For information about modifying parameters, see Modifying parameters in a DB parameter group.

Syntax

CALL mysql.rds_reset_external_master;

Usage notes

The master user must run the mysql.rds_reset_external_master procedure. This procedure must be run on the MySQL DB instance to be removed as a read replica of a MySQL instance running external to Amazon RDS.

Note

We recommend that you use read replicas to manage replication between two Amazon RDS DB instances when possible. When you do so, we recommend that you use only this and other replication-related stored procedures. These practices enable more complex replication topologies between Amazon RDS DB instances. We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. For information about managing replication between Amazon RDS DB instances, see Working with DB instance read replicas.

For more information about using replication to import data from an instance of MySQL running external to Amazon RDS, see Configuring binary log file position replication with an external source instance.

mysql.rds_set_external_master

Configures an RDS for MySQL DB instance to be a read replica of an instance of MySQL running external to Amazon RDS.

Important

To run this procedure, autocommit must be enabled. To enable it, set the autocommit parameter to 1. For information about modifying parameters, see Modifying parameters in a DB parameter group.

Note

You can use the mysql.rds_set_external_master_with_delay stored procedure to configure an external source database instance and delayed replication.

Syntax

CALL mysql.rds_set_external_master ( host_name , host_port , replication_user_name , replication_user_password , mysql_binary_log_file_name , mysql_binary_log_file_location , ssl_encryption );

Parameters

host_name

The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance.

host_port

The port used by the MySQL instance running external to Amazon RDS to be configured as the source database instance. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH.

replication_user_name

The ID of a user with REPLICATION CLIENT and REPLICATION SLAVE permissions on the MySQL instance running external to Amazon RDS. We recommend that you provide an account that is used solely for replication with the external instance.

replication_user_password

The password of the user ID specified in replication_user_name.

mysql_binary_log_file_name

The name of the binary log on the source database instance that contains the replication information.

mysql_binary_log_file_location

The location in the mysql_binary_log_file_name binary log at which replication starts reading the replication information.

You can determine the binlog file name and location by running SHOW MASTER STATUS on the source database instance.

ssl_encryption

A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0.

Note

The MASTER_SSL_VERIFY_SERVER_CERT option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified.

Usage notes

The master user must run the mysql.rds_set_external_master procedure. This procedure must be run on the MySQL DB instance to be configured as the read replica of a MySQL instance running external to Amazon RDS.

Before you run mysql.rds_set_external_master, you must configure the instance of MySQL running external to Amazon RDS to be a source database instance. To connect to the MySQL instance running external to Amazon RDS, you must specify replication_user_name and replication_user_password values that indicate a replication user that has REPLICATION CLIENT and REPLICATION SLAVE permissions on the external instance of MySQL.

To configure an external instance of MySQL as a source database instance
  1. Using the MySQL client of your choice, connect to the external instance of MySQL and create a user account to be used for replication. The following is an example.

    MySQL 5.7

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

    MySQL 8.0

    CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED WITH mysql_native_password BY 'password';
    Note

    Specify a password other than the prompt shown here as a security best practice.

  2. On the external instance of MySQL, grant REPLICATION CLIENT and REPLICATION SLAVE privileges to your replication user. The following example grants REPLICATION CLIENT and REPLICATION SLAVE privileges on all databases for the 'repl_user' user for your domain.

    MySQL 5.7

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

    MySQL 8.0

    GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com';

To use encrypted replication, configure source database instance to use SSL connections.

Note

We recommend that you use read replicas to manage replication between two Amazon RDS DB instances when possible. When you do so, we recommend that you use only this and other replication-related stored procedures. These practices enable more complex replication topologies between Amazon RDS DB instances. We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. For information about managing replication between Amazon RDS DB instances, see Working with DB instance read replicas.

After calling mysql.rds_set_external_master to configure an Amazon RDS DB instance as a read replica, you can call mysql.rds_start_replication on the read replica to start the replication process. You can call mysql.rds_reset_external_master to remove the read replica configuration.

When mysql.rds_set_external_master is called, Amazon RDS records the time, user, and an action of set master in the mysql.rds_history and mysql.rds_replication_status tables.

Examples

When run on a MySQL DB instance, the following example configures the DB instance to be a read replica of an instance of MySQL running external to Amazon RDS.

call mysql.rds_set_external_master( 'Externaldb.some.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.0777', 120, 0);

mysql.rds_set_external_master_with_auto_position

Configures an RDS for MySQL DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. This procedure also configures delayed replication and replication based on global transaction identifiers (GTIDs).

Important

To run this procedure, autocommit must be enabled. To enable it, set the autocommit parameter to 1. For information about modifying parameters, see Modifying parameters in a DB parameter group.

Syntax

CALL mysql.rds_set_external_master_with_auto_position ( host_name , host_port , replication_user_name , replication_user_password , ssl_encryption , delay );

Parameters

host_name

The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance.

host_port

The port used by the MySQL instance running external to Amazon RDS to be configured as the source database instance. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH.

replication_user_name

The ID of a user with REPLICATION CLIENT and REPLICATION SLAVE permissions on the MySQL instance running external to Amazon RDS. We recommend that you provide an account that is used solely for replication with the external instance.

replication_user_password

The password of the user ID specified in replication_user_name.

ssl_encryption

A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0.

Note

The MASTER_SSL_VERIFY_SERVER_CERT option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified.

delay

The minimum number of seconds to delay replication from source database instance.

The limit for this parameter is one day (86,400 seconds).

Usage notes

The master user must run the mysql.rds_set_external_master_with_auto_position procedure. This procedure must be run on the MySQL DB instance to be configured as the read replica of a MySQL instance running external to Amazon RDS.

This procedure is supported for all RDS for MySQL 5.7 versions, and RDS for MySQL 8.0.26 and higher 8.0 versions.

Before you run mysql.rds_set_external_master_with_auto_position, you must configure the instance of MySQL running external to Amazon RDS to be a source database instance. To connect to the MySQL instance running external to Amazon RDS, you must specify values for replication_user_name and replication_user_password. These values must indicate a replication user that has REPLICATION CLIENT and REPLICATION SLAVE permissions on the external instance of MySQL.

To configure an external instance of MySQL as a source database instance
  1. Using the MySQL client of your choice, connect to the external instance of MySQL and create a user account to be used for replication. The following is an example.

    CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd'
  2. On the external instance of MySQL, grant REPLICATION CLIENT and REPLICATION SLAVE privileges to your replication user. The following example grants REPLICATION CLIENT and REPLICATION SLAVE privileges on all databases for the 'repl_user' user for your domain.

    GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd'

For more information, see Configuring binary log file position replication with an external source instance.

Note

We recommend that you use read replicas to manage replication between two Amazon RDS DB instances when possible. When you do so, we recommend that you use only this and other replication-related stored procedures. These practices enable more complex replication topologies between Amazon RDS DB instances. We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. For information about managing replication between Amazon RDS DB instances, see Working with DB instance read replicas.

After calling mysql.rds_set_external_master_with_auto_position to configure an Amazon RDS DB instance as a read replica, you can call mysql.rds_start_replication on the read replica to start the replication process. You can call mysql.rds_reset_external_master to remove the read replica configuration.

When you call mysql.rds_set_external_master_with_auto_position, Amazon RDS records the time, the user, and an action of set master in the mysql.rds_history and mysql.rds_replication_status tables.

For disaster recovery, you can use this procedure with the mysql.rds_start_replication_until or mysql.rds_start_replication_until_gtid stored procedure. To roll forward changes to a delayed read replica to the time just before a disaster, you can run the mysql.rds_set_external_master_with_auto_position procedure. After the mysql.rds_start_replication_until_gtid procedure stops replication, you can promote the read replica to be the new primary DB instance by using the instructions in Promoting a read replica to be a standalone DB instance.

To use the mysql.rds_rds_start_replication_until_gtid procedure, GTID-based replication must be enabled. To skip a specific GTID-based transaction that is known to cause disaster, you can use the mysql.rds_skip_transaction_with_gtid stored procedure. For more information about working with GTID-based replication, see Using GTID-based replication for Amazon RDS for MySQL.

Examples

When run on a MySQL DB instance, the following example configures the DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. It sets the minimum replication delay to one hour (3,600 seconds) on the MySQL DB instance. A change from the MySQL source database instance running external to Amazon RDS isn't applied on the MySQL DB instance read replica for at least one hour.

call mysql.rds_set_external_master_with_auto_position( 'Externaldb.some.com', 3306, 'repl_user', 'SomePassW0rd', 0, 3600);

mysql.rds_set_external_master_with_delay

Configures an RDS for MySQL DB instance to be a read replica of an instance of MySQL running external to Amazon RDS and configures delayed replication.

Important

To run this procedure, autocommit must be enabled. To enable it, set the autocommit parameter to 1. For information about modifying parameters, see Modifying parameters in a DB parameter group.

Syntax

CALL mysql.rds_set_external_master_with_delay ( host_name , host_port , replication_user_name , replication_user_password , mysql_binary_log_file_name , mysql_binary_log_file_location , ssl_encryption , delay );

Parameters

host_name

The host name or IP address of the MySQL instance running external to Amazon RDS that will become the source database instance.

host_port

The port used by the MySQL instance running external to Amazon RDS to be configured as the source database instance. If your network configuration includes SSH port replication that converts the port number, specify the port number that is exposed by SSH.

replication_user_name

The ID of a user with REPLICATION CLIENT and REPLICATION SLAVE permissions on the MySQL instance running external to Amazon RDS. We recommend that you provide an account that is used solely for replication with the external instance.

replication_user_password

The password of the user ID specified in replication_user_name.

mysql_binary_log_file_name

The name of the binary log on the source database instance contains the replication information.

mysql_binary_log_file_location

The location in the mysql_binary_log_file_name binary log at which replication will start reading the replication information.

You can determine the binlog file name and location by running SHOW MASTER STATUS on the source database instance.

ssl_encryption

A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0.

Note

The MASTER_SSL_VERIFY_SERVER_CERT option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified.

delay

The minimum number of seconds to delay replication from source database instance.

The limit for this parameter is one day (86400 seconds).

Usage notes

The master user must run the mysql.rds_set_external_master_with_delay procedure. This procedure must be run on the MySQL DB instance to be configured as the read replica of a MySQL instance running external to Amazon RDS.

Before you run mysql.rds_set_external_master_with_delay, you must configure the instance of MySQL running external to Amazon RDS to be a source database instance. To connect to the MySQL instance running external to Amazon RDS, you must specify values for replication_user_name and replication_user_password. These values must indicate a replication user that has REPLICATION CLIENT and REPLICATION SLAVE permissions on the external instance of MySQL.

To configure an external instance of MySQL as a source database instance
  1. Using the MySQL client of your choice, connect to the external instance of MySQL and create a user account to be used for replication. The following is an example.

    CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd'
  2. On the external instance of MySQL, grant REPLICATION CLIENT and REPLICATION SLAVE privileges to your replication user. The following example grants REPLICATION CLIENT and REPLICATION SLAVE privileges on all databases for the 'repl_user' user for your domain.

    GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY 'SomePassW0rd'

For more information, see Configuring binary log file position replication with an external source instance.

Note

We recommend that you use read replicas to manage replication between two Amazon RDS DB instances when possible. When you do so, we recommend that you use only this and other replication-related stored procedures. These practices enable more complex replication topologies between Amazon RDS DB instances. We offer these stored procedures primarily to enable replication with MySQL instances running external to Amazon RDS. For information about managing replication between Amazon RDS DB instances, see Working with DB instance read replicas.

After calling mysql.rds_set_external_master_with_delay to configure an Amazon RDS DB instance as a read replica, you can call mysql.rds_start_replication on the read replica to start the replication process. You can call mysql.rds_reset_external_master to remove the read replica configuration.

When you call mysql.rds_set_external_master_with_delay, Amazon RDS records the time, the user, and an action of set master in the mysql.rds_history and mysql.rds_replication_status tables.

For disaster recovery, you can use this procedure with the mysql.rds_start_replication_until or mysql.rds_start_replication_until_gtid stored procedure. To roll forward changes to a delayed read replica to the time just before a disaster, you can run the mysql.rds_set_external_master_with_delay procedure. After the mysql.rds_start_replication_until procedure stops replication, you can promote the read replica to be the new primary DB instance by using the instructions in Promoting a read replica to be a standalone DB instance.

To use the mysql.rds_rds_start_replication_until_gtid procedure, GTID-based replication must be enabled. To skip a specific GTID-based transaction that is known to cause disaster, you can use the mysql.rds_skip_transaction_with_gtid stored procedure. For more information about working with GTID-based replication, see Using GTID-based replication for Amazon RDS for MySQL.

The mysql.rds_set_external_master_with_delay procedure is available in these versions of RDS for MySQL:

  • MySQL 8.0.26 and higher 8.0 versions

  • All 5.7 versions

Examples

When run on a MySQL DB instance, the following example configures the DB instance to be a read replica of an instance of MySQL running external to Amazon RDS. It sets the minimum replication delay to one hour (3,600 seconds) on the MySQL DB instance. A change from the MySQL source database instance running external to Amazon RDS isn't applied on the MySQL DB instance read replica for at least one hour.

call mysql.rds_set_external_master_with_delay( 'Externaldb.some.com', 3306, 'repl_user', 'SomePassW0rd', 'mysql-bin-changelog.000777', 120, 0, 3600);

mysql.rds_set_master_auto_position

Sets the replication mode to be based on either binary log file positions or on global transaction identifiers (GTIDs).

Syntax

CALL mysql.rds_set_master_auto_position ( auto_position_mode );

Parameters

auto_position_mode

A value that indicates whether to use log file position replication or GTID-based replication:

  • 0 – Use the replication method based on binary log file position. The default is 0.

  • 1 – Use the GTID-based replication method.

Usage notes

The master user must run the mysql.rds_set_master_auto_position procedure.

This procedure is supported for all RDS for MySQL 5.7 versions, and RDS for MySQL 8.0.26 and higher 8.0 versions.

mysql.rds_set_source_delay

Sets the minimum number of seconds to delay replication from source database instance to the current read replica. Use this procedure when you are connected to a read replica to delay replication from its source database instance.

Syntax

CALL mysql.rds_set_source_delay( delay );

Parameters

delay

The minimum number of seconds to delay replication from the source database instance.

The limit for this parameter is one day (86400 seconds).

Usage notes

The master user must run the mysql.rds_set_source_delay procedure.

For disaster recovery, you can use this procedure with the mysql.rds_start_replication_until stored procedure or the mysql.rds_start_replication_until_gtid stored procedure. To roll forward changes to a delayed read replica to the time just before a disaster, you can run the mysql.rds_set_source_delay procedure. After the mysql.rds_start_replication_until or mysql.rds_start_replication_until_gtid procedure stops replication, you can promote the read replica to be the new primary DB instance by using the instructions in Promoting a read replica to be a standalone DB instance.

To use the mysql.rds_rds_start_replication_until_gtid procedure, GTID-based replication must be enabled. To skip a specific GTID-based transaction that is known to cause disaster, you can use the mysql.rds_skip_transaction_with_gtid stored procedure. For more information on GTID-based replication, see Using GTID-based replication for Amazon RDS for MySQL.

The mysql.rds_set_source_delay procedure is available in these versions of RDS for MySQL:

  • MySQL 8.0.26 and higher 8.0 versions

  • All 5.7 versions

Examples

To delay replication from source database instance to the current read replica for at least one hour (3,600 seconds), you can call mysql.rds_set_source_delay with the following parameter:

CALL mysql.rds_set_source_delay(3600);

mysql.rds_skip_transaction_with_gtid

Skips replication of a transaction with the specified global transaction identifier (GTID) on a MySQL DB instance.

You can use this procedure for disaster recovery when a specific GTID transaction is known to cause a problem. Use this stored procedure to skip the problematic transaction. Examples of problematic transactions include transactions that disable replication, delete important data, or cause the DB instance to become unavailable.

Syntax

CALL mysql.rds_skip_transaction_with_gtid ( gtid_to_skip );

Parameters

gtid_to_skip

The GTID of the replication transaction to skip.

Usage notes

The master user must run the mysql.rds_skip_transaction_with_gtid procedure.

This procedure is supported for all RDS for MySQL 5.7 versions, and RDS for MySQL 8.0.26 and higher 8.0 versions.

Examples

The following example skips replication of the transaction with the GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

CALL mysql.rds_skip_transaction_with_gtid('3E11FA47-71CA-11E1-9E33-C80AA9429562:23');

mysql.rds_skip_repl_error

Skips and deletes a replication error on a MySQL DB read replica.

Syntax

CALL mysql.rds_skip_repl_error;

Usage notes

The master user must run the mysql.rds_skip_repl_error procedure on a read replica. For more information about this procedure, see Calling the mysql.rds_skip_repl_error procedure.

To determine if there are errors, run the MySQL SHOW REPLICA STATUS\G command. If a replication error isn't critical, you can run mysql.rds_skip_repl_error to skip the error. If there are multiple errors, mysql.rds_skip_repl_error deletes the first error, then warns that others are present. You can then use SHOW REPLICA STATUS\G to determine the correct course of action for the next error. For information about the values returned, see SHOW REPLICA STATUS statement in the MySQL documentation.

Note

Previous versions of MySQL used SHOW SLAVE STATUS instead of SHOW REPLICA STATUS. If you are using a MySQL version before 8.0.23, then use SHOW SLAVE STATUS.

For more information about addressing replication errors with Amazon RDS, see Troubleshooting a MySQL read replica problem.

Replication stopped error

When you call the mysql.rds_skip_repl_error procedure, you might receive an error message stating that the replica is down or disabled.

This error message appears if you run the procedure on the primary instance instead of the read replica. You must run this procedure on the read replica for the procedure to work.

This error message might also appear if you run the procedure on the read replica, but replication can't be restarted successfully.

If you need to skip a large number of errors, the replication lag can increase beyond the default retention period for binary log (binlog) files. In this case, you might encounter a fatal error due to binlog files being purged before they have been replayed on the read replica. This purge causes replication to stop, and you can no longer call the mysql.rds_skip_repl_error command to skip replication errors.

You can mitigate this issue by increasing the number of hours that binlog files are retained on your source database instance. After you have increased the binlog retention time, you can restart replication and call the mysql.rds_skip_repl_error command as needed.

To set the binlog retention time, use the mysql.rds_set_configuration procedure and specify a configuration parameter of 'binlog retention hours' along with the number of hours to retain binlog files on the DB cluster. The following example sets the retention period for binlog files to 48 hours.

CALL mysql.rds_set_configuration('binlog retention hours', 48);

mysql.rds_start_replication

Initiates replication from an RDS for MySQL DB instance.

Note

You can use the mysql.rds_start_replication_until or mysql.rds_start_replication_until_gtid stored procedure to initiate replication from an RDS for MySQL DB instance and stop replication at the specified binary log file location.

Syntax

CALL mysql.rds_start_replication;

Usage notes

The master user must run the mysql.rds_start_replication procedure.

To import data from an instance of MySQL external to Amazon RDS, call mysql.rds_start_replication on the read replica to start the replication process after you call mysql.rds_set_external_master to build the replication configuration. For more information, see Restoring a backup into a MySQL DB instance.

To export data to an instance of MySQL external to Amazon RDS, call mysql.rds_start_replication and mysql.rds_stop_replication on the read replica to control some replication actions, such as purging binary logs. For more information, see Exporting data from a MySQL DB instance by using replication.

You can also call mysql.rds_start_replication on the read replica to restart any replication process that you previously stopped by calling mysql.rds_stop_replication. For more information, see Working with DB instance read replicas.

mysql.rds_start_replication_until

Initiates replication from an RDS for MySQL DB instance and stops replication at the specified binary log file location.

Syntax

CALL mysql.rds_start_replication_until ( replication_log_file , replication_stop_point );

Parameters

replication_log_file

The name of the binary log on the source database instance that contains the replication information.

replication_stop_point

The location in the replication_log_file binary log at which replication will stop.

Usage notes

The master user must run the mysql.rds_start_replication_until procedure.

The mysql.rds_start_replication_until procedure is available in these versions of RDS for MySQL:

  • MySQL 8.0.26 and higher 8.0 versions

  • All 5.7 versions

You can use this procedure with delayed replication for disaster recovery. If you have delayed replication configured, you can use this procedure to roll forward changes to a delayed read replica to the time just before a disaster. After this procedure stops replication, you can promote the read replica to be the new primary DB instance by using the instructions in Promoting a read replica to be a standalone DB instance.

You can configure delayed replication using the following stored procedures:

The file name specified for the replication_log_file parameter must match the source database instance binlog file name.

When the replication_stop_point parameter specifies a stop location that is in the past, replication is stopped immediately.

Examples

The following example initiates replication and replicates changes until it reaches location 120 in the mysql-bin-changelog.000777 binary log file.

call mysql.rds_start_replication_until( 'mysql-bin-changelog.000777', 120);

mysql.rds_start_replication_until_gtid

Initiates replication from an RDS for MySQL DB instance and stops replication immediately after the specified global transaction identifier (GTID).

Syntax

CALL mysql.rds_start_replication_until_gtid(gtid);

Parameters

gtid

The GTID after which replication is to stop.

Usage notes

The master user must run the mysql.rds_start_replication_until_gtid procedure.

This procedure is supported for all RDS for MySQL 5.7 versions, and RDS for MySQL 8.0.26 and higher 8.0 versions.

You can use this procedure with delayed replication for disaster recovery. If you have delayed replication configured, you can use this procedure to roll forward changes to a delayed read replica to the time just before a disaster. After this procedure stops replication, you can promote the read replica to be the new primary DB instance by using the instructions in Promoting a read replica to be a standalone DB instance.

You can configure delayed replication using the following stored procedures:

When the gtid parameter specifies a transaction that has already been run by the replica, replication is stopped immediately.

Examples

The following example initiates replication and replicates changes until it reaches GTID 3E11FA47-71CA-11E1-9E33-C80AA9429562:23.

call mysql.rds_start_replication_until_gtid('3E11FA47-71CA-11E1-9E33-C80AA9429562:23');

mysql.rds_stop_replication

Stops replication from a MySQL DB instance.

Syntax

CALL mysql.rds_stop_replication;

Usage notes

The master user must run the mysql.rds_stop_replication procedure.

If you are configuring replication to import data from an instance of MySQL running external to Amazon RDS, you call mysql.rds_stop_replication on the read replica to stop the replication process after the import has completed. For more information, see Restoring a backup into a MySQL DB instance.

If you are configuring replication to export data to an instance of MySQL external to Amazon RDS, you call mysql.rds_start_replication and mysql.rds_stop_replication on the read replica to control some replication actions, such as purging binary logs. For more information, see Exporting data from a MySQL DB instance by using replication.

You can also use mysql.rds_stop_replication to stop replication between two Amazon RDS DB instances. You typically stop replication to perform a long running operation on the read replica, such as creating a large index on the read replica. You can restart any replication process that you stopped by calling mysql.rds_start_replication on the read replica. For more information, see Working with DB instance read replicas.