Managing multi-source replication

The following stored procedures set up and manage replication channels on a RDS for MySQL multi-source replica. For more information, see Configuring multi-source-replication for Amazon RDS for MySQL.

These stored procedures are only available with RDS for MySQL DB instances running the following engine versions:

When using stored procedures to manage replication with a replication user configured with caching_sha2_passwword, you must configure TLS by specifying SOURCE_SSL=1. caching_sha2_password is the default authentication plugin for RDS for MySQL 8.4.

mysql.rds_next_source_log_for_channel

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

Syntax

CALL mysql.rds_next_source_log_for_channel(
curr_master_log,
channel_name           
);

Parameters

Usage notes

The master user must run the mysql.rds_next_source_log_for_channel procedure. If there is an IO_Thread error, for example, you can use this procedure to skip all the events in the current binary log file and resume the replication from the next binary log file for the channel specified in channel_name.

Example

Assume replication fails on a channel on a multi-source replica. Running SHOW REPLICA STATUS FOR CHANNEL 'channel_1'\G on the multi-source replica returns the following result:

mysql> SHOW REPLICA STATUS FOR CHANNEL 'channel_1'\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: myhost.XXXXXXXXXXXXXXX.rr-rrrr-1.rds.amazonaws.com
                  Source_User: ReplicationUser
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin-changelog.012345
          Read_Source_Log_Pos: 1219393
               Relay_Log_File: replica-relay-bin.000003
                Relay_Log_Pos: 30223388
        Relay_Source_Log_File: mysql-bin-changelog.012345
           Replica_IO_Running: No
          Replica_SQL_Running: Yes
              Replicate_Do_DB:.
              .
              .
                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:
               .
               .
                 Channel_name: channel_1
              .
              .
 -- Some fields are omitted in this example output
            

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_for_channel with the following parameters:

CALL mysql.rds_next_source_log_for_channel(12345,'channel_1');

mysql.rds_reset_external_source_for_channel

Stops the replication process on the specified channel, and removes the channel and associated configurations from the multi-source replica.

Syntax

CALL mysql.rds_reset_external_source_for_channel (channel_name);

Parameters

Usage notes

The master user must run the mysql.rds_reset_external_source_for_channel procedure. This procedure deletes all relay logs that belong to the channel being removed.

mysql.rds_set_external_source_for_channel

Configures a replication channel on an RDS for MySQL DB instance to replicate the data from another RDS for MySQL DB instance.

Syntax

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

Parameters

Usage notes

The master user must run the mysql.rds_set_external_source_for_channel procedure. This procedure must be run on the target RDS for MySQL DB instance on which you're creating the replication channel.

Before you run mysql.rds_set_external_source_for_channel, configure a replication user on the source DB instance with the privileges required for the multi-source replica. To connect the multi-source replica to the source DB instance, you must specify replication_user_name and replication_user_password values of a replication user that has REPLICATION CLIENT and REPLICATION SLAVE permissions on the source DB instance.

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

After calling mysql.rds_set_external_source_for_channel to configure this replication channel, you can call mysql.rds_start_replication_for_channel on the replica to start the replication process on the channel. You can call mysql.rds_reset_external_source_for_channel to stop replication on the channel and remove the channel configuration from the replica.

When you call mysql.rds_set_external_source_for_channel, Amazon RDS records the time, user, and an action of set channel source in the mysql.rds_history table without channel-specific details, and in the mysql.rds_replication_status table, with the channel name. This information is recorded only for internal usage and monitoring purposes. To record the complete procedure call for auditing purpose, consider enabling audit logs or general logs, based on the specific requirements of your application.

Examples

When run on a RDS for MySQL DB instance, the following example configures a replication channel named channel_1 on this DB instance to replicate data from the source specified by host sourcedb.example.com and port 3306.

call mysql.rds_set_external_source_for_channel(
  'sourcedb.example.com',
  3306,
  'repl_user',
  'password',
  'mysql-bin-changelog.0777',
  120,
  0,
  'channel_1');

mysql.rds_set_external_source_with_auto_position_for_channel

Configures a replication channel on an RDS for MySQL DB instance with an optional replication delay. The replication is based on global transaction identifiers (GTIDs).

Syntax

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

Parameters

Usage notes

The master user must run the mysql.rds_set_external_source_with_auto_position_for_channel procedure. This procedure must be run on the target RDS for MySQL DB instance on which you're creating the replication channel.

Before you run rds_set_external_source_with_auto_position_for_channel, configure a replication user on the source DB instance with the privileges required for the multi-source replica. To connect the multi-source replica to the source DB instance, you must specify replication_user_name and replication_user_password values of a replication user that has REPLICATION CLIENT and REPLICATION SLAVE permissions on the source DB instance.

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

Before you call mysql.rds_set_external_source_with_auto_position_for_channel, make sure to call mysql.rds_set_external_source_gtid_purged to set the gtid_purged system variable with a specified GTID range from an external source.

After calling mysql.rds_set_external_source_with_auto_position_for_channel to configure an Amazon RDS DB instance as a read replica on a specific channel, you can call mysql.rds_start_replication_for_channel on the read replica to start the replication process on that channel.

After calling mysql.rds_set_external_source_with_auto_position_for_channel to configure this replication channel, you can call mysql.rds_start_replication_for_channel on the replica to start the replication process on the channel. You can call mysql.rds_reset_external_source_for_channel to stop replication on the channel and remove the channel configuration from the replica.

Examples

When run on a RDS for MySQL DB instance, the following example configures a replication channel named channel_1 on this DB instance to replicate data from the source specified by host sourcedb.example.com and port 3306 It sets the minimum replication delay to one hour (3,600 seconds). This means that a change from the source RDS for MySQL DB instance isn't applied on the multi-source replica for at least one hour.

call mysql.rds_set_external_source_with_auto_position_for_channel(
  'sourcedb.example.com',
  3306,
  'repl_user',
  'password',
  1,
  3600,
  'channel_1');

mysql.rds_set_external_source_with_delay_for_channel

Configures a replication channel on an RDS for MySQL DB instance with a specified replication delay.

Syntax

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

Parameters

Usage notes

The master user must run the mysql.rds_set_external_source_with_delay_for_channel procedure. This procedure must be run on the target RDS for MySQL DB instance on which you're creating the replication channel.

Before you run mysql.rds_set_external_source_with_delay_for_channel, configure a replication user on the source DB instance with the privileges required for the multi-source replica. To connect the multi-source replica to the source DB instance, you must specify replication_user_name and replication_user_password values of a replication user that has REPLICATION CLIENT and REPLICATION SLAVE permissions on the source DB instance.

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

After calling mysql.rds_set_external_source_with_delay_for_channel to configure this replication channel, you can call mysql.rds_start_replication_for_channel on the replica to start the replication process on the channel. You can call mysql.rds_reset_external_source_for_channel to stop replication on the channel and remove the channel configuration from the replica.

When you call mysql.rds_set_external_source_with_delay_for_channel, Amazon RDS records the time, user, and an action of set channel source in the mysql.rds_history table without channel-specific details, and in the mysql.rds_replication_status table, with the channel name. This information is recorded only for internal usage and monitoring purposes. To record the complete procedure call for auditing purpose, consider enabling audit logs or general logs, based on the specific requirements of your application.

Examples

When run on a RDS for MySQL DB instance, the following example configures a replication channel named channel_1 on this DB instance to replicate data from the source specified by host sourcedb.example.com and port 3306 It sets the minimum replication delay to one hour (3,600 seconds). This means that a change from the source RDS for MySQL DB instance isn't applied on the multi-source replica for at least one hour.

call mysql.rds_set_external_source_with_delay_for_channel(
  'sourcedb.example.com',
  3306,
  'repl_user',
  'password',
  'mysql-bin-changelog.000777',
  120,
  1,
  3600,
  'channel_1');

mysql.rds_set_source_auto_position_for_channel

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

Syntax

CALL mysql.rds_set_source_auto_position_for_channel (
auto_position_mode
 , channel_name
);

Parameters

Usage notes

The master user must run the mysql.rds_set_source_auto_position_for_channel procedure. This procedure restarts replication on the specified channel to apply the specified auto position mode.

Examples

The following example sets the auto position mode for channel_1 to use the GTID-based replication method.

call mysql.rds_set_source_auto_position_for_channel(1,'channel_1');

mysql.rds_set_source_delay_for_channel

Sets the minimum number of seconds to delay replication from the source database instance to the multi-source replica for the specified channel.

Syntax

CALL mysql.rds_set_source_delay_for_channel(delay, channel_name);

Parameters

Usage notes

The master user must run the mysql.rds_set_source_delay_for_channel procedure. To use this procedure, first call mysql.rds_stop_replication_for_channel to stop the replication. Then, call this procedure to set the replication delay value. When the delay is set, call mysql.rds_start_replication_for_channel to restart the replication.

Examples

The following example sets the delay for replication from the source database instance on channel_1 of the multi-source replica for at least one hour (3,600 seconds).

CALL mysql.rds_set_source_delay_for_channel(3600,'channel_1');

mysql.rds_skip_repl_error_for_channel

Skips a binary log event and deletes a replication error on a MySQL DB multi-source replica for the specified channel.

Syntax

CALL mysql.rds_skip_repl_error_for_channel(channel_name);

Parameters

Usage notes

The master user must run the mysql.rds_skip_repl_error_for_channel procedure on a read replica. You can use this procedure in a similar way mysql.rds_skip_repl_error is used to skip an error on a read replica. For more information, see Calling the mysql.rds_skip_repl_error procedure.

To determine if there are errors, run the MySQL SHOW REPLICA STATUS FOR CHANNEL 'channel_name'\G command. If a replication error isn't critical, you can run mysql.rds_skip_repl_error_for_channel to skip the error. If there are multiple errors, mysql.rds_skip_repl_error_for_channel deletes the first error on the specified replication channel, then warns that others are present. You can then use SHOW REPLICA STATUS FOR CHANNEL 'channel_name'\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.

mysql.rds_start_replication_for_channel

Initiates replication from an RDS for MySQL DB instance to a multi-source replica on the specified channel.

Syntax

CALL mysql.rds_start_replication_for_channel(channel_name);

Parameters

Usage notes

The master user must run the mysql.rds_start_replication_for_channel procedure. After you import the data from the source RDS for MySQL DB instance, run this command on the multi-source replica to start replication on the specified channel.

Examples

The following example starts replication on channel_1 of the multi-source replica.

CALL mysql.rds_start_replication_for_channel('channel_1');

mysql.rds_start_replication_until_for_channel

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

Syntax

CALL mysql.rds_start_replication_until_for_channel (
replication_log_file
  , replication_stop_point
  , channel_name
);

Parameters

Usage notes

The master user must run the mysql.rds_start_replication_until_for_channel procedure. With this procedure, replication starts and then stops when the specified binlog file position is reached. This procedure stops both the SQL_THREAD and IO_THREAD.

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

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

Examples

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

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

mysql.rds_start_replication_until_gtid_for_channel

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

Syntax

CALL mysql.rds_start_replication_until_gtid_for_channel(gtid,channel_name);

Parameters

Usage notes

The master user must run the mysql.rds_start_replication_until_gtid_for_channel procedure. The procedure starts replication on the specified channel and applies all changes up to the specified GTID value. Then, it stops replication on the channel.

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

Before you run this procedure, you must disable multi-threaded replication by setting the value of replica_parallel_workers or slave_parallel_workers to 0.

Examples

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

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

mysql.rds_stop_replication_for_channel

Stops replication from a MySQL DB instance on the specified channel.

Syntax

CALL mysql.rds_stop_replication_for_channel(channel_name);

Parameters

Usage notes

The master user must run the mysql.rds_stop_replication_for_channel procedure.

Examples

The following example stops replication on channel_1 of the multi-source replica.

CALL mysql.rds_stop_replication_for_channel('channel_1');