mysql.rds_set_external_master
Configures a MySQL DB instance to be a read replica of an instance of MySQL running external to Amazon RDS.
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.
You can use the mysql.rds_set_external_master_with_delay stored procedure to configure an external source database instance and delayed replication.
Syntax
CALL mysql.rds_set_external_master ( host_name , host_port , replication_user_name , replication_user_password , mysql_binary_log_file_name , mysql_binary_log_file_location , ssl_encryption );
Parameters
- host_name
-
The host name or IP address of the MySQL instance running external to Amazon RDS to become the source database instance.
- host_port
-
The port used by the MySQL instance running external to Amazon RDS to be configured as the source database instance. If your network configuration includes Secure Shell (SSH) port replication that converts the port number, specify the port number that is exposed by SSH.
- replication_user_name
-
The ID of a user with
REPLICATION CLIENT
andREPLICATION SLAVE
permissions on the MySQL instance running external to Amazon RDS. We recommend that you provide an account that is used solely for replication with the external instance. - replication_user_password
-
The password of the user ID specified in
replication_user_name
. - mysql_binary_log_file_name
-
The name of the binary log on the source database instance that contains the replication information.
- mysql_binary_log_file_location
-
The location in the
mysql_binary_log_file_name
binary log at which replication starts reading the replication information.You can determine the binlog file name and location by running
SHOW MASTER STATUS
on the source database instance. - ssl_encryption
-
A value that specifies whether Secure Socket Layer (SSL) encryption is used on the replication connection. 1 specifies to use SSL encryption, 0 specifies to not use encryption. The default is 0.
Note The
MASTER_SSL_VERIFY_SERVER_CERT
option isn't supported. This option is set to 0, which means that the connection is encrypted, but the certificates aren't verified.
Usage notes
The master user must run the mysql.rds_set_external_master
procedure. This procedure must be run on the MySQL DB instance to be configured as
the read replica of a MySQL instance running external to Amazon RDS.
Before you run mysql.rds_set_external_master
, you must configure the instance of MySQL running external
to Amazon RDS to be a source database instance. To connect to the MySQL instance running external to Amazon RDS,
you must specify replication_user_name
and replication_user_password
values that indicate a replication user that has
REPLICATION CLIENT
and REPLICATION SLAVE
permissions on the external instance of MySQL.
To configure an external instance of MySQL as a source database instance
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
';-
On the external instance of MySQL, grant
REPLICATION CLIENT
andREPLICATION SLAVE
privileges to your replication user. The following example grantsREPLICATION CLIENT
andREPLICATION SLAVE
privileges on all databases for the 'repl_user' user for your domain.MySQL 5.7
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY '
password
';MySQL 8.0
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com';
To use encrypted replication, configure source database instance to use SSL connections. Also, import the certificate authority certificate, client certificate, and client key into the DB instance or DB cluster using the mysql.rds_import_binlog_ssl_material procedure.
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 read replicas.
After calling mysql.rds_set_external_master
to configure an Amazon RDS DB
instance as a read replica, you can call mysql.rds_start_replication on the read replica to start the
replication process. You can call mysql.rds_reset_external_master to remove the read replica
configuration.
When mysql.rds_set_external_master
is called, Amazon RDS records
the time, user, and an action of set master
in the mysql.rds_history
and
mysql.rds_replication_status
tables.
Examples
When run on a MySQL DB instance, the following example configures the DB instance to be a read replica of an instance of MySQL running external to Amazon RDS.
call mysql.rds_set_external_master( 'Externaldb.some.com', 3306, 'repl_user', '
password
', 'mysql-bin-changelog.0777', 120, 0);