# RDS for MySQL stored procedure reference RDS for MySQL stored procedures These topics describe system stored procedures that are available for Amazon RDS instances running the MySQL DB engine. The master user must run these procedures. **Topics** + [ # Collecting and maintaining the Global Status History ](mysql-stored-proc-gsh.md) + [ # Configuring, starting, and stopping binary log (binlog) replication ](mysql-stored-proc-replicating.md) + [ # Ending a session or query ](mysql-stored-proc-ending.md) + [ # Managing active-active clusters ](mysql-stored-proc-active-active-clusters.md) + [ # Managing multi-source replication ](mysql-stored-proc-multi-source-replication.md) + [ # Replicating transactions using GTIDs ](mysql-stored-proc-gtid.md) + [ # Rotating the query logs ](mysql-stored-proc-logging.md) + [ # Setting and showing binary log configuration ](mysql-stored-proc-configuring.md) + [ # Warming the InnoDB cache ](mysql-stored-proc-warming.md) # Collecting and maintaining the Global Status History Amazon RDS provides a set of procedures that take snapshots of the values of status variables over time and write them to a table, along with any changes since the last snapshot. This infrastructure is called Global Status History. For more information, see [Managing the Global Status History for RDS for MySQL](Appendix.MySQL.CommonDBATasks.GoSH.md). The following stored procedures manage how the Global Status History is collected and maintained. **Topics** + [ ## mysql.rds\$1collect\$1global\$1status\$1history ](#mysql_rds_collect_global_status_history) + [ ## mysql.rds\$1disable\$1gsh\$1collector ](#mysql_rds_disable_gsh_collector) + [ ## mysql.rds\$1disable\$1gsh\$1rotation ](#mysql_rds_disable_gsh_rotation) + [ ## mysql.rds\$1enable\$1gsh\$1collector ](#mysql_rds_enable_gsh_collector) + [ ## mysql.rds\$1enable\$1gsh\$1rotation ](#mysql_rds_enable_gsh_rotation) + [ ## mysql.rds\$1rotate\$1global\$1status\$1history ](#mysql_rds_rotate_global_status_history) + [ ## mysql.rds\$1set\$1gsh\$1collector ](#mysql_rds_set_gsh_collector) + [ ## mysql.rds\$1set\$1gsh\$1rotation ](#mysql_rds_set_gsh_rotation) ## mysql.rds\$1collect\$1global\$1status\$1history mysql.rds\$1collect\$1global\$1status\$1history Takes a snapshot on demand for the Global Status History. ### Syntax Syntax ``` CALL mysql.rds_collect_global_status_history; ``` ## mysql.rds\$1disable\$1gsh\$1collector mysql.rds\$1disable\$1gsh\$1collector Turns off snapshots taken by the Global Status History. ### Syntax Syntax ``` CALL mysql.rds_disable_gsh_collector; ``` ## mysql.rds\$1disable\$1gsh\$1rotation mysql.rds\$1disable\$1gsh\$1rotation Turns off rotation of the `mysql.global_status_history` table. ### Syntax Syntax ``` CALL mysql.rds_disable_gsh_rotation; ``` ## mysql.rds\$1enable\$1gsh\$1collector mysql.rds\$1enable\$1gsh\$1collector Turns on the Global Status History to take default snapshots at intervals specified by `rds_set_gsh_collector`. ### Syntax Syntax ``` CALL mysql.rds_enable_gsh_collector; ``` ## mysql.rds\$1enable\$1gsh\$1rotation mysql.rds\$1enable\$1gsh\$1rotation Turns on rotation of the contents of the `mysql.global_status_history` table to `mysql.global_status_history_old` at intervals specified by `rds_set_gsh_rotation`. ### Syntax Syntax ``` CALL mysql.rds_enable_gsh_rotation; ``` ## mysql.rds\$1rotate\$1global\$1status\$1history mysql.rds\$1rotate\$1global\$1status\$1history Rotates the contents of the `mysql.global_status_history` table to `mysql.global_status_history_old` on demand. ### Syntax Syntax ``` CALL mysql.rds_rotate_global_status_history; ``` ## mysql.rds\$1set\$1gsh\$1collector mysql.rds\$1set\$1gsh\$1collector Specifies the interval, in minutes, between snapshots taken by the Global Status History. ### Syntax Syntax ``` CALL mysql.rds_set_gsh_collector(intervalPeriod); ``` ### Parameters Parameters *intervalPeriod* The interval, in minutes, between snapshots. Default value is `5`. ## mysql.rds\$1set\$1gsh\$1rotation mysql.rds\$1set\$1gsh\$1rotation Specifies the interval, in days, between rotations of the `mysql.global_status_history` table. ### Syntax Syntax ``` CALL mysql.rds_set_gsh_rotation(intervalPeriod); ``` ### Parameters Parameters *intervalPeriod* The interval, in days, between table rotations. Default value is `7`. # 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](mysql-ssl-connections.md). For information about configuring, using, and managing read replicas, see [Working with MySQL read replicas](USER_MySQL.Replication.ReadReplicas.md). **Topics** + [ ## mysql.rds\$1next\$1master\$1log (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) ](#mysql_rds_next_master_log) + [ ## mysql.rds\$1next\$1source\$1log (RDS for MySQL major versions 8.4 and higher) ](#mysql_rds_next_source_log) + [ ## mysql.rds\$1reset\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) ](#mysql_rds_reset_external_master) + [ ## mysql.rds\$1reset\$1external\$1source (RDS for MySQL major versions 8.4 and higher) ](#mysql_rds_reset_external_source) + [ ## mysql.rds\$1set\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) ](#mysql_rds_set_external_master) + [ ## mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher) ](#mysql_rds_set_external_source) + [ ## mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position (RDS for MySQL major versions 8.0 and lower) ](#mysql_rds_set_external_master_with_auto_position) + [ ## mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position (RDS for MySQL major versions 8.4 and higher) ](#mysql_rds_set_external_source_with_auto_position) + [ ## mysql.rds\$1set\$1external\$1master\$1with\$1delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) ](#mysql_rds_set_external_master_with_delay) + [ ## mysql.rds\$1set\$1external\$1source\$1with\$1delay (RDS for MySQL major versions 8.4 and higher) ](#mysql_rds_set_external_source_with_delay) + [ ## mysql.rds\$1set\$1external\$1source\$1gtid\$1purged ](#mysql_rds_set_external_source_gtid_purged) + [ ## mysql.rds\$1set\$1master\$1auto\$1position (RDS for MySQL major versions 8.0 and lower) ](#mysql_rds_set_master_auto_position) + [ ## mysql.rds\$1set\$1source\$1auto\$1position (RDS for MySQL major versions 8.4 and higher) ](#mysql_rds_set_source_auto_position) + [ ## mysql.rds\$1set\$1source\$1delay ](#mysql_rds_set_source_delay) + [ ## mysql.rds\$1skip\$1repl\$1error ](#mysql_rds_skip_repl_error) + [ ## mysql.rds\$1start\$1replication ](#mysql_rds_start_replication) + [ ## mysql.rds\$1start\$1replication\$1until ](#mysql_rds_start_replication_until) + [ ## mysql.rds\$1stop\$1replication ](#mysql_rds_stop_replication) ## mysql.rds\$1next\$1master\$1log (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) mysql.rds\$1next\$1master\$1log 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\$1master\$1log* 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](USER_ReadRepl.Troubleshooting.md). ### 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\$1next\$1source\$1log (RDS for MySQL major versions 8.4 and higher) mysql.rds\$1next\$1source\$1log 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\$1source\$1log* 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](USER_ReadRepl.Troubleshooting.md). ### 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\$1reset\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) mysql.rds\$1reset\$1external\$1master 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](USER_WorkingWithParamGroups.Modifying.md). ### 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](USER_ReadRepl.md). 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.Procedural.Importing.External.Repl.md). ## mysql.rds\$1reset\$1external\$1source (RDS for MySQL major versions 8.4 and higher) mysql.rds\$1reset\$1external\$1source 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](USER_WorkingWithParamGroups.Modifying.md). ### 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](USER_ReadRepl.md). 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.Procedural.Importing.External.Repl.md). ## mysql.rds\$1set\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) mysql.rds\$1set\$1external\$1master 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](USER_WorkingWithParamGroups.Modifying.md). **Note** You can use the [mysql.rds\$1set\$1external\$1master\$1with\$1delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](#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\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance. *host\$1port* 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\$1user\$1name* 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\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source database instance that contains the replication information. *mysql\$1binary\$1log\$1file\$1location* 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\$1encryption* 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. 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. 1. 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\$1user' 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](USER_ReadRepl.md). After calling `mysql.rds_set_external_master` to configure an Amazon RDS DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](#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, 1); ``` ## mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher) mysql.rds\$1set\$1external\$1source 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](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_set_external_source ( host_name , host_port , replication_user_name , replication_user_password , mysql_binary_log_file_name , mysql_binary_log_file_location , ssl_encryption ); ``` ### Parameters *host\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance. *host\$1port* 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\$1user\$1name* 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\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source database instance that contains the replication information. *mysql\$1binary\$1log\$1file\$1location* 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\$1encryption* 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. The `SOURCE_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 administrative user must run the `mysql.rds_set_external_source` procedure. This procedure must be run on the RDS for 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_source`, 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. ``` CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY 'password'; ``` **Note** Specify a password other than the prompt shown here as a security best practice. 1. 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\$1user' user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com'; ``` To use encrypted replication, configure source database instance to use SSL connections. Also, import the certificate authority certificate, client certificate, and client key into the DB instance or DB cluster using the [mysql.rds\$1import\$1binlog\$1ssl\$1material](url-rds-user;mysql_rds_import_binlog_ssl_material.html) procedure. **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](USER_ReadRepl.md). After calling `mysql.rds_set_external_source` to configure an RDS for MySQL DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](#mysql_rds_reset_external_source) to remove the read replica configuration. When `mysql.rds_set_external_source` 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 an RDS for 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_source( 'Externaldb.some.com', 3306, 'repl_user', 'password', 'mysql-bin-changelog.0777', 120, 1); ``` ## mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position (RDS for MySQL major versions 8.0 and lower) mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position 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 in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). ### 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\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance. *host\$1port* 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\$1user\$1name* 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\$1user\$1password* The password of the user ID specified in `replication_user_name`. *ssl\$1encryption* 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. 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' ``` 1. 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](MySQL.Procedural.Importing.External.Repl.md). **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](USER_ReadRepl.md). Before you call `mysql.rds_set_external_master_with_auto_position`, make sure to call [mysql.rds\$1set\$1external\$1source\$1gtid\$1purged](#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_master_with_auto_position` to configure an Amazon RDS DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](#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\$1start\$1replication\$1until](#mysql_rds_start_replication_until) or [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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](USER_ReadRepl.Promote.md). 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\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). ### 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', 1, 3600); ``` ## mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position (RDS for MySQL major versions 8.4 and higher) mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position 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 in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_set_external_source_with_auto_position ( host_name , host_port , replication_user_name , replication_user_password , ssl_encryption , delay ); ``` ### Parameters *host\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance. *host\$1port* 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\$1user\$1name* 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\$1user\$1password* The password of the user ID specified in `replication_user_name`. *ssl\$1encryption* 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. The `SOURCE_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 administrative user must run the `mysql.rds_set_external_source_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. Before you run `mysql.rds_set_external_source_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' ``` 1. 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](MySQL.Procedural.Importing.External.Repl.md). **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](USER_ReadRepl.md). Before you call `mysql.rds_set_external_source_with_auto_position`, make sure to call [mysql.rds\$1set\$1external\$1source\$1gtid\$1purged](#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` to configure an Amazon RDS DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](#mysql_rds_reset_external_source) to remove the read replica configuration. When you call `mysql.rds_set_external_source_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\$1start\$1replication\$1until](#mysql_rds_start_replication_until) or [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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_source_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](USER_ReadRepl.Promote.md). 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\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). ### 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_source_with_auto_position( 'Externaldb.some.com', 3306, 'repl_user', 'SomePassW0rd', 1, 3600); ``` ## mysql.rds\$1set\$1external\$1master\$1with\$1delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower) mysql.rds\$1set\$1external\$1master\$1with\$1delay 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 in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). ### 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\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS that will become the source database instance. *host\$1port* 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\$1user\$1name* 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\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source database instance contains the replication information. *mysql\$1binary\$1log\$1file\$1location* 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\$1encryption* 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. 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' ``` 1. 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](MySQL.Procedural.Importing.External.Repl.md). **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](USER_ReadRepl.md). 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\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](#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\$1start\$1replication\$1until](#mysql_rds_start_replication_until) or [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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](USER_ReadRepl.Promote.md). 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\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). 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, 1, 3600); ``` ## mysql.rds\$1set\$1external\$1source\$1with\$1delay (RDS for MySQL major versions 8.4 and higher) mysql.rds\$1set\$1external\$1source\$1with\$1delay 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 in Amazon RDS](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_set_external_source_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\$1name* The host name or IP address of the MySQL instance running external to Amazon RDS that will become the source database instance. *host\$1port* 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\$1user\$1name* 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\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source database instance contains the replication information. *mysql\$1binary\$1log\$1file\$1location* 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\$1encryption* 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. The `SOURCE_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 administrative user must run the `mysql.rds_set_external_source_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_source_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' ``` 1. 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](MySQL.Procedural.Importing.External.Repl.md). **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](USER_ReadRepl.md). After calling `mysql.rds_set_external_source_with_delay` to configure an Amazon RDS DB instance as a read replica, you can call [mysql.rds\$1start\$1replication](#mysql_rds_start_replication) on the read replica to start the replication process. You can call [mysql.rds\$1reset\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](#mysql_rds_reset_external_source) to remove the read replica configuration. When you call `mysql.rds_set_external_source_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\$1start\$1replication\$1until](#mysql_rds_start_replication_until) or [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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_source_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](USER_ReadRepl.Promote.md). 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\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). ### 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_source_with_delay( 'Externaldb.some.com', 3306, 'repl_user', 'SomePassW0rd', 'mysql-bin-changelog.000777', 120, 1, 3600); ``` ## mysql.rds\$1set\$1external\$1source\$1gtid\$1purged Sets the [gtid\$1purged](https://dev.mysql.com/doc/refman/8.0/en/replication-options-gtids.html#sysvar_gtid_purged) system variable with a specified GTID range from an external source. The `gtid_purged` value is required for configuring GTID-based replication to resume the replication using auto positioning. **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](USER_WorkingWithParamGroups.Modifying.md). ### Syntax ``` CALL mysql.rds_set_external_source_gtid_purged( server_uuid , start_pos , end_pos ); ``` ### Parameters *server\$1uuid* The universally unique identifier (UUID) of the external server from which the GTID range is being imported. *start\$1pos* The starting position of the GTID range to be set. *end\$1pos* The ending position of the GTID range to be set. ### Usage notes The `mysql.rds_set_external_source_gtid_purged` procedure is only available with MySQL 8.0.37 and higher 8.0 versions. Call `mysql.rds_set_external_source_gtid_purged` before you call [mysql.rds\$1set\$1external\$1master\$1with\$1auto\$1position (RDS for MySQL major versions 8.0 and lower)](#mysql_rds_set_external_master_with_auto_position), [mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position (RDS for MySQL major versions 8.4 and higher)](#mysql_rds_set_external_source_with_auto_position), or [mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position\$1for\$1channel](mysql-stored-proc-multi-source-replication.md#mysql_rds_set_external_source_with_auto_position_for_channel). Before you call `mysql.rds_set_external_source_gtid_purged`, make sure to stop all active replication channels for the database. To check the status of a channel, use the `SHOW REPLICA STATUS` MySQL statement. To stop replication on a channel, call [mysql.rds\$1stop\$1replication\$1for\$1channel](mysql-stored-proc-multi-source-replication.md#mysql_rds_stop_replication_for_channel). The GTID range that you specify must be a superset of the existing `GTID_PURGED` value. This stored procedure checks the following values before it sets the `GTID_PURGED` value: + The `server_uuid` is valid. + The value of `start_pos` is greater than `0` and less than the value of `end_pos`. + The value of `end_pos` is greater than or equal to the value of `start_pos`. If the GTID set on your external server contains multiple ranges of values, consider calling the procedure multiple times with different GTID set values. When you call `mysql.rds_set_external_source_gtid_purged`, Amazon RDS records the time, the user, and an action of `set gtid_purged` in the `mysql.rds_history` table. If you don't set the `gtid_purged` value appropriately for the backup that you use for replication, this can result in missing or duplicated transactions during the replication process. Perform the following steps to set the correct `gtid_purged` value. **To set the gtid\$1purged value on the replica** 1. Determine the point in time or the specific backup file to use as the starting point for replication. This could be a logical backup (a mysqldump file) or a physical backup (an Amazon RDS snapshot). 1. Determine the `gtid_executed` value. This value represents the set of all GTIDs that were committed on the server. To retrieve this value, on the source instance, do one of the following: + Run the SQL statement `SELECT @@GLOBAL.GTID_EXECUTED;` at the time the backup was taken. + If any related options are included in the respective backup utility, extract the value from the backup file. For more information, see the [set-gtid-purged](https://dev.mysql.com/doc/refman/8.4/en/mysqldump.html#option_mysqldump_set-gtid-purged) option in the MySQL documentation. 1. Determine the `gtid_purged` value to use for the call to `mysql.rds_set_external_source_gtid_purged`. The `gtid_purged` value should include all the GTIDs that were executed on the source instance and are no longer needed for replication. Therefore, the `gtid_purged` value should be a subset of the `gtid_executed` value that you retrieved in the previous step. To determine the `gtid_purged` value, identify the GTIDs that aren't included in the backup and are no longer needed for replication. You can do so by analyzing the binary logs or by using a tool such as mysqlbinlog to find the GTIDs that were purged from the binary logs. Alternatively, if you have a consistent backup that includes all of the binary logs up to the backup point, you can set the `gtid_purged` value to be the same as the `gtid_executed` value at the backup point. 1. After you determine the appropriate `gtid_purged` value that's consistent with your backup, call the `mysql.rds_set_external_source_gtid_purged` stored procedure on your RDS for MySQL DB instance to set the value. ### Examples When run on a MySQL DB instance, the following example sets the GTID range from an external MySQL server with the UUID `12345678-abcd-1234-efgh-123456789abc`, a starting position of `1`, and an ending position of `100`. The resulting GTID value is set to `+12345678-abcd-1234-efgh-123456789abc:1-100`. ``` CALL mysql.rds_set_external_source_gtid_purged('12345678-abcd-1234-efgh-123456789abc', 1, 100); ``` ## mysql.rds\$1set\$1master\$1auto\$1position (RDS for MySQL major versions 8.0 and lower) mysql.rds\$1set\$1master\$1auto\$1position 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\$1position\$1mode* 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\$1set\$1source\$1auto\$1position (RDS for MySQL major versions 8.4 and higher) mysql.rds\$1set\$1source\$1auto\$1position Sets the replication mode to be based on either binary log file positions or on global transaction identifiers (GTIDs). ### Syntax ``` CALL mysql.rds_set_source_auto_position (auto_position_mode); ``` ### Parameters *auto\$1position\$1mode* 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 administrative user must run the `mysql.rds_set_source_auto_position` procedure. ## mysql.rds\$1set\$1source\$1delay 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\$1start\$1replication\$1until](#mysql_rds_start_replication_until) stored procedure or the [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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](USER_ReadRepl.Promote.md). 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\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information on GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). The `mysql.rds_set_source_delay` procedure is available in these versions of RDS for MySQL: + All RDS for MySQL 8.4 versions + 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\$1skip\$1repl\$1error 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\$1skip\$1repl\$1error procedure](Appendix.MySQL.CommonDBATasks.SkipError.md#Appendix.MySQL.CommonDBATasks.SkipError.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](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) in the MySQL documentation. For more information about addressing replication errors with Amazon RDS, see [Troubleshooting a MySQL read replica problem](USER_ReadRepl.Troubleshooting.md). #### 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 because of 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\$1set\$1configuration](mysql-stored-proc-configuring.md#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\$1start\$1replication Initiates replication from an RDS for MySQL DB instance. **Note** You can use the [mysql.rds\$1start\$1replication\$1until](#mysql_rds_start_replication_until) or [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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\$1set\$1external\$1master (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](#mysql_rds_set_external_master) or [mysql.rds\$1set\$1external\$1source (RDS for MySQL major versions 8.4 and higher)](#mysql_rds_set_external_source) to build the replication configuration. For more information, see [Restoring a backup into an Amazon RDS for MySQL DB instance](MySQL.Procedural.Importing.md). 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](MySQL.Procedural.Exporting.NonRDSRepl.md). 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](USER_ReadRepl.md). ## mysql.rds\$1start\$1replication\$1until mysql.rds\$1start\$1replication\$1until 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\$1log\$1file* The name of the binary log on the source database instance that contains the replication information. *replication\$1stop\$1point * 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: + All RDS for MySQL 8.4 versions + 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](USER_ReadRepl.Promote.md). You can configure delayed replication using the following stored procedures: + [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) + [mysql.rds\$1set\$1external\$1master\$1with\$1delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](#mysql_rds_set_external_master_with_delay) + [mysql.rds\$1set\$1external\$1source\$1with\$1delay (RDS for MySQL major versions 8.4 and higher)](#mysql_rds_set_external_source_with_delay) + [mysql.rds\$1set\$1source\$1delay](#mysql_rds_set_source_delay) 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\$1stop\$1replication 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 an Amazon RDS for MySQL DB instance](MySQL.Procedural.Importing.md). 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](MySQL.Procedural.Exporting.NonRDSRepl.md). 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\$1start\$1replication](#mysql_rds_start_replication) on the read replica. For more information, see [Working with DB instance read replicas](USER_ReadRepl.md). # Ending a session or query The following stored procedures end a session or query. **Topics** + [ ## mysql.rds\$1kill ](#mysql_rds_kill) + [ ## mysql.rds\$1kill\$1query ](#mysql_rds_kill_query) ## mysql.rds\$1kill mysql.rds\$1kill Ends a connection to the MySQL server. ### Syntax Syntax ``` CALL mysql.rds_kill(processID); ``` ### Parameters Parameters *processID* The identity of the connection thread to be ended. ### Usage notes Usage notes Each connection to the MySQL server runs in a separate thread. To end a connection, use the `mysql.rds_kill` procedure and pass in the thread ID of that connection. To obtain the thread ID, use the MySQL [SHOW PROCESSLIST](https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html) command. For information about limitations, see [MySQL stored procedure limitations](MySQL.KnownIssuesAndLimitations.md#MySQL.Concepts.KnownIssuesAndLimitations.KillProcedures). ### Examples Examples The following example ends a connection with a thread ID of 4243: ``` CALL mysql.rds_kill(4243); ``` ## mysql.rds\$1kill\$1query mysql.rds\$1kill\$1query Ends a query running against the MySQL server. ### Syntax Syntax ``` CALL mysql.rds_kill_query(processID); ``` ### Parameters Parameters *processID* The identity of the process or thread that is running the query to be ended. ### Usage notes Usage notes To stop a query running against the MySQL server, use the `mysql_rds_kill_query` procedure and pass in the connection ID of the thread that is running the query. The procedure then terminates the connection. To obtain the ID, query the MySQL [INFORMATION\$1SCHEMA PROCESSLIST table](https://dev.mysql.com/doc/refman/8.0/en/information-schema-processlist-table.html) or use the MySQL [SHOW PROCESSLIST](https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html) command. The value in the ID column from `SHOW PROCESSLIST` or `SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST` is the *processID*. For information about limitations, see [MySQL stored procedure limitations](MySQL.KnownIssuesAndLimitations.md#MySQL.Concepts.KnownIssuesAndLimitations.KillProcedures). ### Examples Examples The following example stops a query with a query thread ID of 230040: ``` CALL mysql.rds_kill_query(230040); ``` # Managing active-active clusters The following stored procedures set up and manage RDS for MySQL active-active clusters. For more information, see [Configuring active-active clusters for RDS for MySQL](mysql-active-active-clusters.md). These stored procedures are only available with RDS for MySQL DB instances running the following versions: + All MySQL 8.4 versions + MySQL 8.0.35 and higher minor versions **Topics** + [ ## mysql.rds\$1group\$1replication\$1advance\$1gtid ](#mysql_rds_group_replication_advance_gtid) + [ ## mysql.rds\$1group\$1replication\$1create\$1user ](#mysql_rds_group_replication_create_user) + [ ## mysql.rds\$1group\$1replication\$1set\$1recovery\$1channel ](#mysql_rds_group_replication_set_recovery_channel) + [ ## mysql.rds\$1group\$1replication\$1start ](#mysql_rds_group_replication_start) + [ ## mysql.rds\$1group\$1replication\$1stop ](#mysql_rds_group_replication_stop) ## mysql.rds\$1group\$1replication\$1advance\$1gtid Creates placeholder GTIDs on the current DB instance. ### Syntax ``` CALL mysql.rds_group_replication_advance_gtid( begin_id , end_id , server_uuid ); ``` ### Parameters *begin\$1id* The start transaction ID to be created. *end\$1id* The end transaction ID to be created. *begin\$1id* The `group_replication_group_name` for the transaction to be created. The `group_replication_group_name` is specified as a UUID in the DB parameter group associated with the DB instance. ### Usage notes In an active-active cluster, for a DB instance to join a group, all GTID transactions executed on the new DB instance must exist on the other members in the cluster. In unusual cases, a new DB instance might have more transactions when transactions are executed before joining the instance to group. In this case, you can't remove any existing transactions, but you can use this procedure to create the corresponding placeholder GTIDs on the othe DB instances in the group. Before doing so, verify that the transactions *don't affect the replicated data*. When you call this procedure, GTID transactions of `server_uuid:begin_id-end_id` are created with empty content. To avoid replication issues, don't use this procedure under any other conditions. **Important** Avoid calling this procedure when the active-active cluster is functioning normally. Don't call this procedure unless you understand the possible consequences of the transactions you are creating. Calling this procedure might result in inconsistent data. ### Example The following example creates placeholder GTIDs on current DB instance.: ``` CALL mysql.rds_group_replication_advance_gtid(5, 6, '11111111-2222-3333-4444-555555555555'); ``` ## mysql.rds\$1group\$1replication\$1create\$1user Creates the replication user `rdsgrprepladmin` for group replication on the DB instance. ### Syntax ``` CALL mysql.rds_group_replication_create_user( replication_user_password ); ``` ### Parameters *replication\$1user\$1password* The password of the replication user `rdsgrprepladmin`. ### Usage notes + The password of the replication user `rdsgrprepladmin` must be the same on all of the DB instances in an active-active cluster. + The `rdsgrprepladmin` user name is reserved for group replication connections. No other user, including the master user, can have this user name. ### Example The following example creates the replication user `rdsgrprepladmin` for group replication on the DB instance: ``` CALL mysql.rds_group_replication_create_user('password'); ``` ## mysql.rds\$1group\$1replication\$1set\$1recovery\$1channel Sets the `group_replication_recovery` channel for an active-active cluster. The procedure uses the reserved `rdsgrprepladmin` user to configure the channel. ### Syntax ``` CALL mysql.rds_group_replication_set_recovery_channel( replication_user_password); ``` ### Parameters *replication\$1user\$1password* The password of the replication user `rdsgrprepladmin`. ### Usage notes The password of the replication user `rdsgrprepladmin` must be the same on all of the DB instances in an active-active cluster. A call to the `mysql.rds_group_replication_create_user` specifies the password. ### Example The following example sets the `group_replication_recovery` channel for an active-active cluster: ``` CALL mysql.rds_group_replication_set_recovery_channel('password'); ``` ## mysql.rds\$1group\$1replication\$1start Starts group replication on the current DB instance. ### Syntax ``` CALL mysql.rds_group_replication_start( bootstrap ); ``` ### Parameters *bootstrap* A value that specifies whether to initialize a new group or join an existing group. `1` initializes a new group with the current DB instance. `0` joins the current DB instance to an existing group by connecting to the endpoints defined in `group_replication_group_seeds` parameter in the of DB parameter group associated with the DB instance. ### Example The following example initializes a new group with the current DB instance: ``` CALL mysql.rds_group_replication_start(1); ``` ## mysql.rds\$1group\$1replication\$1stop Stops group replication on the current DB instance. ### Syntax ``` CALL mysql.rds_group_replication_stop(); ``` ### Usage notes When you stop replication on a DB instance, it doesn't affect any other DB instance in the active-active cluster. # 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](mysql-multi-source-replication.md). These stored procedures are only available with RDS for MySQL DB instances running the following engine versions: + All 8.4 versions + 8.0.35 and higher minor versions + 5.7.44 and higher minor 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. **Note** Although this documentation refers to source DB instances as RDS for MySQL DB instances, these procedures also work for MySQL instances running external to Amazon RDS. **Topics** + [ ## mysql.rds\$1next\$1source\$1log\$1for\$1channel ](#mysql_rds_next_source_log_for_channel) + [ ## mysql.rds\$1reset\$1external\$1source\$1for\$1channel ](#mysql_rds_reset_external_source_for_channel) + [ ## mysql.rds\$1set\$1external\$1source\$1for\$1channel ](#mysql_rds_set_external_source_for_channel) + [ ## mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position\$1for\$1channel ](#mysql_rds_set_external_source_with_auto_position_for_channel) + [ ## mysql.rds\$1set\$1external\$1source\$1with\$1delay\$1for\$1channel ](#mysql_rds_set_external_source_with_delay_for_channel) + [ ## mysql.rds\$1set\$1source\$1auto\$1position\$1for\$1channel ](#mysql_rds_set_source_auto_position_for_channel) + [ ## mysql.rds\$1set\$1source\$1delay\$1for\$1channel ](#mysql_rds_set_source_delay_for_channel) + [ ## mysql.rds\$1skip\$1repl\$1error\$1for\$1channel ](#mysql_rds_skip_repl_error_for_channel) + [ ## mysql.rds\$1start\$1replication\$1for\$1channel ](#mysql_rds_start_replication_for_channel) + [ ## mysql.rds\$1start\$1replication\$1until\$1for\$1channel ](#mysql_rds_start_replication_until_for_channel) + [ ## mysql.rds\$1start\$1replication\$1until\$1gtid\$1for\$1channel ](#mysql_rds_start_replication_until_gtid_for_channel) + [ ## mysql.rds\$1stop\$1replication\$1for\$1channel ](#mysql_rds_stop_replication_for_channel) ## mysql.rds\$1next\$1source\$1log\$1for\$1channel 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 *curr\$1master\$1log* 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 FOR CHANNEL 'channel_name'` command and view the `Source_Log_File` field. *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes The master user must run the `mysql.rds_next_source_log_for_channel` procedure. If there is an IO\$1Thread 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\$1reset\$1external\$1source\$1for\$1channel mysql.rds\$1reset\$1external\$1source\$1for\$1channel Stops the replication process on the specified channel, and removes the channel and associated configurations from the multi-source replica. **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](USER_WorkingWithParamGroups.Modifying.md). ### Syntax Syntax ``` CALL mysql.rds_reset_external_source_for_channel (channel_name); ``` ### Parameters *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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\$1set\$1external\$1source\$1for\$1channel mysql.rds\$1set\$1external\$1source\$1for\$1channel Configures a replication channel on an RDS for MySQL DB instance to replicate the data from another RDS for MySQL DB instance. **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](USER_WorkingWithParamGroups.Modifying.md). **Note** You can use the [mysql.rds\$1set\$1external\$1source\$1with\$1delay\$1for\$1channel](#mysql_rds_set_external_source_with_delay_for_channel) stored procedure instead to configure this channel with delayed replication. ### Syntax 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 Parameters *host\$1name* The host name or IP address of the RDS for MySQL source DB instance. *host\$1port* The port used by the RDS for MySQL source DB 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\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the RDS for MySQL source DB instance. We recommend that you provide an account that is used solely for replication with the source DB instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source DB instance that contains the replication information. *mysql\$1binary\$1log\$1file\$1location* 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 BINARY LOG STATUS` on the source DB instance. Previous versions of MySQL used `SHOW MASTER STATUS` instead of `SHOW BINARY LOG STATUS`. If you are using a MySQL version before 8.4, then use `SHOW MASTER STATUS`. *ssl\$1encryption* 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. The `SOURCE_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. *channel\$1name* The name of the replication channel. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 configure a replication user on the source DB instance** 1. Using the MySQL client of your choice, connect to the source DB instance and create a user account to be used for replication. The following is an example. **Important** As a security best practice, specify a password other than the placeholder value shown in the following examples. ``` CREATE USER 'repl_user'@'example.com' IDENTIFIED BY 'password'; ``` 1. On the source DB instance, 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\$1user' user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'example.com'; ``` 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\$1start\$1replication\$1for\$1channel](#mysql_rds_start_replication_for_channel) on the replica to start the replication process on the channel. You can call [mysql.rds\$1reset\$1external\$1source\$1for\$1channel](#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 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\$1set\$1external\$1source\$1with\$1auto\$1position\$1for\$1channel mysql.rds\$1set\$1external\$1source\$1with\$1auto\$1position\$1for\$1channel 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). **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](USER_WorkingWithParamGroups.Modifying.md). ### Syntax 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 Parameters *host\$1name* The host name or IP address of the RDS for MySQL source DB instance. *host\$1port* The port used by the RDS for MySQL source DB 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\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the RDS for MySQL source DB instance. We recommend that you provide an account that is used solely for replication with the source DB instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *ssl\$1encryption* 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. The `SOURCE_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 DB instance. The limit for this parameter is one day (86,400 seconds). *channel\$1name* The name of the replication channel. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 configure a replication user on the source DB instance** 1. Using the MySQL client of your choice, connect to the source DB instance and create a user account to be used for replication. The following is an example. **Important** As a security best practice, specify a password other than the placeholder value shown in the following examples. ``` CREATE USER 'repl_user'@'example.com' IDENTIFIED BY 'password'; ``` 1. On the source DB instance, 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\$1user' user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'example.com'; ``` 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\$1set\$1external\$1source\$1gtid\$1purged](mysql-stored-proc-replicating.md#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\$1start\$1replication\$1for\$1channel](#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\$1start\$1replication\$1for\$1channel](#mysql_rds_start_replication_for_channel) on the replica to start the replication process on the channel. You can call [mysql.rds\$1reset\$1external\$1source\$1for\$1channel](#mysql_rds_reset_external_source_for_channel) to stop replication on the channel and remove the channel configuration from the replica. ### Examples 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\$1set\$1external\$1source\$1with\$1delay\$1for\$1channel mysql.rds\$1set\$1external\$1source\$1with\$1delay\$1for\$1channel Configures a replication channel on an RDS for MySQL DB instance with a specified replication delay. **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](USER_WorkingWithParamGroups.Modifying.md). ### Syntax 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 Parameters *host\$1name* The host name or IP address of the RDS for MySQL source DB instance. *host\$1port* The port used by the RDS for MySQL source DB 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\$1user\$1name* The ID of a user with `REPLICATION CLIENT` and `REPLICATION SLAVE` permissions on the RDS for MySQL source DB instance. We recommend that you provide an account that is used solely for replication with the source DB instance. *replication\$1user\$1password* The password of the user ID specified in `replication_user_name`. *mysql\$1binary\$1log\$1file\$1name* The name of the binary log on the source DB instance contains the replication information. *mysql\$1binary\$1log\$1file\$1location* 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 BINARY LOG STATUS` on the source database instance. Previous versions of MySQL used `SHOW MASTER STATUS` instead of `SHOW BINARY LOG STATUS`. If you are using a MySQL version before 8.4, then use `SHOW MASTER STATUS`. *ssl\$1encryption* 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. The `SOURCE_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 DB instance. The limit for this parameter is one day (86400 seconds). *channel\$1name* The name of the replication channel. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 configure a replication user on the source DB instance** 1. Using the MySQL client of your choice, connect to the source DB instance and create a user account to be used for replication. The following is an example. **Important** As a security best practice, specify a password other than the placeholder value shown in the following examples. ``` CREATE USER 'repl_user'@'example.com' IDENTIFIED BY 'password'; ``` 1. On the source DB instance, 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\$1user' user for your domain. ``` GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'example.com'; ``` 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\$1start\$1replication\$1for\$1channel](#mysql_rds_start_replication_for_channel) on the replica to start the replication process on the channel. You can call [mysql.rds\$1reset\$1external\$1source\$1for\$1channel](#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 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\$1set\$1source\$1auto\$1position\$1for\$1channel mysql.rds\$1set\$1source\$1auto\$1position\$1for\$1channel Sets the replication mode for the specified channel to be based on either binary log file positions or on global transaction identifiers (GTIDs). ### Syntax Syntax ``` CALL mysql.rds_set_source_auto_position_for_channel ( auto_position_mode , channel_name ); ``` ### Parameters Parameters *auto\$1position\$1mode* 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. *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 Examples The following example sets the auto position mode for channel\$11 to use the GTID-based replication method. ``` call mysql.rds_set_source_auto_position_for_channel(1,'channel_1'); ``` ## mysql.rds\$1set\$1source\$1delay\$1for\$1channel mysql.rds\$1set\$1source\$1delay\$1for\$1channel Sets the minimum number of seconds to delay replication from the source database instance to the multi-source replica for the specified channel. ### Syntax Syntax ``` CALL mysql.rds_set_source_delay_for_channel(delay, channel_name); ``` ### Parameters Parameters *delay* The minimum number of seconds to delay replication from the source DB instance. The limit for this parameter is one day (86400 seconds). *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 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\$1skip\$1repl\$1error\$1for\$1channel mysql.rds\$1skip\$1repl\$1error\$1for\$1channel Skips a binary log event and deletes a replication error on a MySQL DB multi-source replica for the specified channel. ### Syntax Syntax ``` CALL mysql.rds_skip_repl_error_for_channel(channel_name); ``` ### Parameters *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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\$1skip\$1repl\$1error procedure](Appendix.MySQL.CommonDBATasks.SkipError.md#Appendix.MySQL.CommonDBATasks.SkipError.procedure). **Note** To skip errors in GTID-based replication, we recommend that you use the procedure [mysql.rds\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) instead. 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](https://dev.mysql.com/doc/refman/8.0/en/show-replica-status.html) in the MySQL documentation. ## mysql.rds\$1start\$1replication\$1for\$1channel mysql.rds\$1start\$1replication\$1for\$1channel Initiates replication from an RDS for MySQL DB instance to a multi-source replica on the specified channel. **Note** You can use the [mysql.rds\$1start\$1replication\$1until\$1for\$1channel](#mysql_rds_start_replication_until_for_channel) or [mysql.rds\$1start\$1replication\$1until\$1gtid\$1for\$1channel](#mysql_rds_start_replication_until_gtid_for_channel) stored procedure to initiate replication from an RDS for MySQL DB instance and stop replication at the specified binary log file location. ### Syntax Syntax ``` CALL mysql.rds_start_replication_for_channel(channel_name); ``` ### Parameters *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 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\$1start\$1replication\$1until\$1for\$1channel mysql.rds\$1start\$1replication\$1until\$1for\$1channel Initiates replication from an RDS for MySQL DB instance on the specified channel and stops replication at the specified binary log file location. ### Syntax Syntax ``` CALL mysql.rds_start_replication_until_for_channel ( replication_log_file , replication_stop_point , channel_name ); ``` ### Parameters Parameters *replication\$1log\$1file* The name of the binary log on the source DB instance contains the replication information. *replication\$1stop\$1point * The location in the `replication_log_file` binary log at which replication will stop. *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 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\$1start\$1replication\$1until\$1gtid\$1for\$1channel mysql.rds\$1start\$1replication\$1until\$1gtid\$1for\$1channel Initiates replication on the specified channel from an RDS for MySQL DB instance and stops replication at the specified global transaction identifier (GTID). ### Syntax Syntax ``` CALL mysql.rds_start_replication_until_gtid_for_channel(gtid,channel_name); ``` ### Parameters Parameters *gtid* The GTID after which to stop replication. *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes 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 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\$1stop\$1replication\$1for\$1channel mysql.rds\$1stop\$1replication\$1for\$1channel Stops replication from a MySQL DB instance on the specified channel. ### Syntax Syntax ``` CALL mysql.rds_stop_replication_for_channel(channel_name); ``` ### Parameters *channel\$1name* The name of the replication channel on the multi-source replica. Each replication channel receives the binary log events from a single source RDS for MySQL DB instance running on a specific host and port. ### Usage notes Usage notes The master user must run the `mysql.rds_stop_replication_for_channel` procedure. ### Examples Examples The following example stops replication on `channel_1` of the multi-source replica. ``` CALL mysql.rds_stop_replication_for_channel('channel_1'); ``` # Replicating transactions using GTIDs The following stored procedures control how transactions are replicated using global transaction identifiers (GTIDs) with RDS for MySQL. For more information about replication based on GTIDs with RDS for MySQL, see [Using GTID-based replication](mysql-replication-gtid.md). When using 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. **Topics** + [ ## mysql.rds\$1skip\$1transaction\$1with\$1gtid ](#mysql_rds_skip_transaction_with_gtid) + [ ## mysql.rds\$1start\$1replication\$1until\$1gtid ](#mysql_rds_start_replication_until_gtid) ## mysql.rds\$1skip\$1transaction\$1with\$1gtid mysql.rds\$1skip\$1transaction\$1with\$1gtid 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 Syntax ``` CALL mysql.rds_skip_transaction_with_gtid ( gtid_to_skip ); ``` ### Parameters Parameters *gtid\$1to\$1skip* The GTID of the replication transaction to skip. ### Usage notes 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, all RDS for MySQL 8.0 versions, and all RDS for MySQL 8.4 versions. ### Examples 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\$1start\$1replication\$1until\$1gtid mysql.rds\$1start\$1replication\$1until\$1gtid Initiates replication from an RDS for MySQL DB instance and stops replication immediately after the specified global transaction identifier (GTID). ### Syntax Syntax ``` CALL mysql.rds_start_replication_until_gtid(gtid); ``` ### Parameters Parameters *gtid* The GTID after which replication is to stop. ### Usage notes 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, all RDS for MySQL 8.0 versions, and all RDS for MySQL 8.4 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](USER_ReadRepl.Promote.md). You can configure delayed replication using the following stored procedures: + [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) + [mysql.rds\$1set\$1external\$1master\$1with\$1delay (RDS for MariaDB and RDS for MySQL major versions 8.0 and lower)](mysql-stored-proc-replicating.md#mysql_rds_set_external_master_with_delay) + [mysql.rds\$1set\$1external\$1source\$1with\$1delay (RDS for MySQL major versions 8.4 and higher)](mysql-stored-proc-replicating.md#mysql_rds_set_external_source_with_delay) + [mysql.rds\$1set\$1source\$1delay](mysql-stored-proc-replicating.md#mysql_rds_set_source_delay) When the `gtid` parameter specifies a transaction that has already been run by the replica, replication is stopped immediately. ### Examples 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'); ``` # Rotating the query logs The following stored procedures rotate MySQL logs to backup tables. For more information, see [MySQL database log files](USER_LogAccess.Concepts.MySQL.md). **Topics** + [ ## mysql.rds\$1rotate\$1general\$1log ](#mysql_rds_rotate_general_log) + [ ## mysql.rds\$1rotate\$1slow\$1log ](#mysql_rds_rotate_slow_log) ## mysql.rds\$1rotate\$1general\$1log mysql.rds\$1rotate\$1general\$1log Rotates the `mysql.general_log` table to a backup table. ### Syntax Syntax ``` CALL mysql.rds_rotate_general_log; ``` ### Usage notes Usage notes You can rotate the `mysql.general_log` table to a backup table by calling the `mysql.rds_rotate_general_log` procedure. When log tables are rotated, the current log table is copied to a backup log table and the entries in the current log table are removed. If a backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the `mysql.general_log` table is named `mysql.general_log_backup`. You can run this procedure only when the `log_output` parameter is set to `TABLE`. ## mysql.rds\$1rotate\$1slow\$1log mysql.rds\$1rotate\$1slow\$1log Rotates the `mysql.slow_log` table to a backup table. ### Syntax Syntax ``` CALL mysql.rds_rotate_slow_log; ``` ### Usage notes Usage notes You can rotate the `mysql.slow_log` table to a backup table by calling the `mysql.rds_rotate_slow_log` procedure. When log tables are rotated, the current log table is copied to a backup log table and the entries in the current log table are removed. If a backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the `mysql.slow_log` table is named `mysql.slow_log_backup`. # Setting and showing binary log configuration The following stored procedures set and show configuration parameters, such as for binary log file retention. **Topics** + [ ## mysql.rds\$1set\$1configuration ](#mysql_rds_set_configuration) + [ ## mysql.rds\$1show\$1configuration ](#mysql_rds_show_configuration) ## mysql.rds\$1set\$1configuration Specifies the number of hours to retain binary logs or the number of seconds to delay replication. ### Syntax Syntax ``` CALL mysql.rds_set_configuration(name,value); ``` ### Parameters Parameters *name* The name of the configuration parameter to set. *value* The value of the configuration parameter. ### Usage notes Usage notes The `mysql.rds_set_configuration` procedure supports the following configuration parameters: + [binlog retention hours](#mysql_rds_set_configuration-usage-notes.binlog-retention-hours) + [source delay](#mysql_rds_set_configuration-usage-notes.source-delay) + [target delay](#mysql_rds_set_configuration-usage-notes.target-delay) The configuration parameters are stored permanently and survive any DB instance reboot or failover. #### binlog retention hours The `binlog retention hours` parameter is used to specify the number of hours to retain binary log files. Amazon RDS normally purges a binary log as soon as possible, but the binary log might still be required for replication with a MySQL database external to RDS. The default value of `binlog retention hours` is `NULL`. For RDS for MySQL, `NULL` means binary logs aren't retained (0 hours). To specify the number of hours to retain binary logs on a DB instance, use the `mysql.rds_set_configuration` stored procedure and specify a period with enough time for replication to occur, as shown in the following example. `call mysql.rds_set_configuration('binlog retention hours', 24);` **Note** You can't use the value `0` for `binlog retention hours`. For MySQL DB instances, the maximum `binlog retention hours` value is 168 (7 days). After you set the retention period, monitor storage usage for the DB instance to make sure that the retained binary logs don't take up too much storage. For Multi-AZ DB cluster deployments, you can only configure binary log retention from the writer DB instance, and the setting is propagated to all reader DB instances asynchronously. If binary logs on the DB cluster exceed half of the total local storage space, Amazon RDS automatically moves stale logs to the EBS volume. However, the newest logs remain in local storage, so they're subject to be lost if there's a failure that requires a host replacement, or if you scale the database up or down. #### source delay Use the `source delay` parameter in a read replica to specify the number of seconds to delay replication from the read replica to its source DB instance. Amazon RDS normally replicates changes as soon as possible, but you might want some environments to delay replication. For example, when replication is delayed, you can roll forward a delayed read replica to the time just before a disaster. If a table is dropped accidentally, you can use delayed replication to quickly recover it. The default value of `target delay` is `0` (don't delay replication). When you use this parameter, it runs [mysql.rds\$1set\$1source\$1delay](mysql-stored-proc-replicating.md#mysql_rds_set_source_delay) and applies CHANGE primary TO MASTER\$1DELAY = input value. If successful, the procedure saves the `source delay` parameter to the `mysql.rds_configuration` table. To specify the number of seconds for Amazon RDS to delay replication to a source DB instance, use the `mysql.rds_set_configuration` stored procedure and specify the number of seconds to delay replication. In the following example, the replication is delayed by at least one hour (3,600 seconds). `call mysql.rds_set_configuration('source delay', 3600);` The procedure then runs `mysql.rds_set_source_delay(3600)`. The limit for the `source delay` parameter is one day (86400 seconds). #### target delay Use the `target delay` parameter to specify the number of seconds to delay replication between a DB instance and any future RDS-managed read replicas created from this instance. This parameter is ignored for non-RDS-managed read replicas. Amazon RDS normally replicates changes as soon as possible, but you might want some environments to delay replication. For example, when replication is delayed, you can roll forward a delayed read replica to the time just before a disaster. If a table is dropped accidentally, you can use delayed replication to recover it quickly. The default value of `target delay` is `0` (don't delay replication). For disaster recovery, you can use this configuration parameter with the [mysql.rds\$1start\$1replication\$1until](mysql-stored-proc-replicating.md#mysql_rds_start_replication_until) stored procedure or the [mysql.rds\$1start\$1replication\$1until\$1gtid](mysql-stored-proc-gtid.md#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_configuration` procedure with this parameter set. 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](USER_ReadRepl.Promote.md). 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\$1skip\$1transaction\$1with\$1gtid](mysql-stored-proc-gtid.md#mysql_rds_skip_transaction_with_gtid) stored procedure. For more information about working with GTID-based replication, see [Using GTID-based replication](mysql-replication-gtid.md). To specify the number of seconds for Amazon RDS to delay replication to a read replica, use the `mysql.rds_set_configuration` stored procedure and specify the number of seconds to delay replication. The following example specifies that replication is delayed by at least one hour (3,600 seconds). `call mysql.rds_set_configuration('target delay', 3600);` The limit for the `target delay` parameter is one day (86400 seconds). ## mysql.rds\$1show\$1configuration The number of hours that binary logs are retained. ### Syntax Syntax ``` CALL mysql.rds_show_configuration; ``` ### Usage notes Usage notes To verify the number of hours that Amazon RDS retains binary logs, use the `mysql.rds_show_configuration` stored procedure. ### Examples Examples The following example displays the retention period: ``` call mysql.rds_show_configuration; name value description binlog retention hours 24 binlog retention hours specifies the duration in hours before binary logs are automatically deleted. ``` # Warming the InnoDB cache The following stored procedures save, load, or cancel loading the InnoDB buffer pool on RDS for MySQL DB instances. For more information, see [InnoDB cache warming for MySQL on Amazon RDS](MySQL.Concepts.FeatureSupport.md#MySQL.Concepts.InnoDBCacheWarming). **Topics** + [ ## mysql.rds\$1innodb\$1buffer\$1pool\$1dump\$1now ](#mysql_rds_innodb_buffer_pool_dump_now) + [ ## mysql.rds\$1innodb\$1buffer\$1pool\$1load\$1abort ](#mysql_rds_innodb_buffer_pool_load_abort) + [ ## mysql.rds\$1innodb\$1buffer\$1pool\$1load\$1now ](#mysql_rds_innodb_buffer_pool_load_now) ## mysql.rds\$1innodb\$1buffer\$1pool\$1dump\$1now mysql.rds\$1innodb\$1buffer\$1pool\$1dump\$1now Dumps the current state of the buffer pool to disk. ### Syntax Syntax ``` CALL mysql.rds_innodb_buffer_pool_dump_now(); ``` ### Usage notes Usage notes The master user must run the `mysql.rds_innodb_buffer_pool_dump_now` procedure. ## mysql.rds\$1innodb\$1buffer\$1pool\$1load\$1abort mysql.rds\$1innodb\$1buffer\$1pool\$1load\$1abort Cancels a load of the saved buffer pool state while in progress. ### Syntax Syntax ``` CALL mysql.rds_innodb_buffer_pool_load_abort(); ``` ### Usage notes Usage notes The master user must run the `mysql.rds_innodb_buffer_pool_load_abort` procedure. ## mysql.rds\$1innodb\$1buffer\$1pool\$1load\$1now mysql.rds\$1innodb\$1buffer\$1pool\$1load\$1now Loads the saved state of the buffer pool from disk. ### Syntax Syntax ``` CALL mysql.rds_innodb_buffer_pool_load_now(); ``` ### Usage notes Usage notes The master user must run the `mysql.rds_innodb_buffer_pool_load_now` procedure.