Configuring GTID-Based Replication into an Amazon RDS MariaDB DB instance
You can set up GTID-based replication from an external MariaDB instance of version 10.0.24 or greater into an Amazon RDS MariaDB DB instance. Be sure to follow these guidelines when you set up an external replication master and a replica on Amazon RDS:
Monitor failover events for the Amazon RDS MariaDB DB instance that is your replica. If a failover occurs, then the DB instance that is your replica might be recreated on a new host with a different network address. For information on how to monitor failover events, see Using Amazon RDS Event Notification.
Maintain the binlogs on your master instance until you have verified that they have been applied to the replica. This maintenance ensures that you can restore your master instance in the event of a failure.
Turn on automated backups on your MariaDB DB instance on Amazon RDS. Turning on automated backups ensures that you can restore your replica to a particular point in time if you need to re-synchronize your master and replica. For information on backups and Point-In-Time Restore, see Backing Up and Restoring Amazon RDS DB Instances.
The permissions required to start replication on an Amazon RDS MariaDB DB instance are restricted and not available to your Amazon RDS master user. Because of this, you must use the Amazon RDS mysql.rds_set_external_master_gtid and mysql.rds_start_replication commands to set up replication between your live database and your Amazon RDS MariaDB database.
To Start Replication Between an External Master Instance and a MariaDB DB Instance on Amazon RDS
Make the source MariaDB instance read-only:Copy to clipboard
mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = ON;
Get the current GTID of the external MariaDB instance. You can do this by using
mysqlor the query editor of your choice to run
The GTID is formatted as
<domain-id>-<server-id>-<sequence-id>. A typical GTID looks something like
0-1234510749-1728. For more information about GTIDs and their component parts, go to Global Transaction ID in the MariaDB documentation.
Copy the database from the external MariaDB instance to the Amazon RDS MariaDB DB instance using
mysqldump. For very large databases, you might want to use the procedure in Importing Data to an Amazon RDS MySQL or MariaDB DB Instance with Reduced Downtime.
For Linux, OS X, or Unix:Copy to clipboard
mysqldump \ --databases <database_name> \ --single-transaction \ --compress \ --order-by-primary \ -u <local_user> \ -p<local_password> | mysql \ --host=hostname \ --port=3306 \ -u <RDS_user_name> \ -p <RDS_password>
For Windows:Copy to clipboard
mysqldump ^ --databases <database_name> ^ --single-transaction ^ --compress ^ --order-by-primary \ -u <local_user> \ -p<local_password> | mysql ^ --host=hostname ^ --port=3306 ^ -u <RDS_user_name> ^ -p <RDS_password>
Make sure there is not a space between the
-poption and the entered password.
-poptions in the
mysqlcommand to specify the host name, user name, port, and password to connect to your Amazon RDS MariaDB DB instance. The host name is the DNS name from the Amazon RDS MariaDB DB instance endpoint, for example
myinstance.123456789012.us-east-1.rds.amazonaws.com. You can find the endpoint value in the instance details in the Amazon RDS Management Console.
Make the source MariaDB instance writeable again:Copy to clipboard
mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES;
In the Amazon RDS Management Console, add the IP address of the server that hosts the external MariaDB database to the VPC security group for the Amazon RDS MariaDB DB instance. For more information on modifying a VPC security group, go to Security Groups for Your VPC in the Amazon Virtual Private Cloud User Guide.
You might also need to configure your local network to permit connections from the IP address of your Amazon RDS MariaDB DB instance, so that it can communicate with your external MariaDB instance. To find the IP address of the Amazon RDS MariaDB DB instance, use the
hostcommand:Copy to clipboard
The host name is the DNS name from the Amazon RDS MariaDB DB instance endpoint.
Using the client of your choice, connect to the external MariaDB instance and create a MariaDB user to be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following is an example:Copy to clipboard
CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY '<password>';
For the external MariaDB instance, grant
REPLICATION SLAVEprivileges to your replication user. For example, to grant the
REPLICATION SLAVEprivileges on all databases for the '
repl_user' user for your domain, issue the following command:Copy to clipboard
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY '<password>';
Make the Amazon RDS MariaDB DB instance the replica. Connect to the Amazon RDS MariaDB DB instance as the master user and identify the external MariaDB database as the replication master by using the mysql.rds_set_external_master_gtid command. Use the GTID that you determined in Step 2. The following is an example:Copy to clipboard
CALL mysql.rds_set_external_master_gtid ('mymasterserver.mydomain.com', 3306, 'repl_user', '<password>', '<GTID>', 0);
On the Amazon RDS MariaDB DB instance, issue the mysql.rds_start_replication command to start replication:Copy to clipboard