View a markdown version of this page

Configuring, starting, and stopping binary log (binlog) replication - Amazon Relational Database Service
mysql.rds_next_master_logmysql.rds_next_source_logmysql.rds_reset_external_mastermysql.rds_reset_external_sourcemysql.rds_set_external_mastermysql.rds_set_external_sourcemysql.rds_set_external_master_with_auto_positionmysql.rds_set_external_source_with_auto_positionmysql.rds_set_external_master_with_delaymysql.rds_set_external_source_with_delaymysql.rds_set_external_source_gtid_purgedmysql.rds_set_master_auto_positionmysql.rds_set_source_auto_positionmysql.rds_set_source_delaymysql.rds_skip_repl_errormysql.rds_start_replicationmysql.rds_start_replication_untilmysql.rds_stop_replication

Configuring, starting, and stopping binary log (binlog) replication

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.

When using these stored procedures to manage replication with a replication user configured with caching_sha2_password, you must configure TLS by specifying SOURCE_SSL=1. caching_sha2_password is the default authentication plugin for RDS for MySQL 8.4 For more information, see Encrypting with SSL/TLS.

For information about configuring, using, and managing read replicas, see Working with MySQL read replicas.

mysql.rds_next_master_log (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)

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.

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, even though 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);

mysql.rds_next_source_log (RDS for MySQL major versions 8.4 and higher)

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_source_log(
curr_source_log
);

Parameters

curr_source_log

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

Usage notes

The administrative user must run the mysql.rds_next_source_log procedure.

Warning

Call mysql.rds_next_source_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_source_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, even though 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_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Client requested source 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 Source_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_source_log with the following parameter:

CALL mysql.rds_next_source_log(12345);

mysql.rds_reset_external_master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)

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 in Amazon RDS.

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_reset_external_source (RDS for MySQL major versions 8.4 and higher)

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 in Amazon RDS.

Syntax

CALL mysql.rds_reset_external_source;

Usage notes

The administrative user must run the mysql.rds_reset_external_source 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 (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)

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 in Amazon RDS.

Note

You can use the mysql.rds_set_external_master_with_delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) 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 (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) 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,
  1);

mysql.rds_set_external_source (RDS for MySQL major versions 8.4 and higher)

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 in Amazon RDS.

Syntax

CALL mysql.rds_set_external_source (