Setting up AWS RDS MySQL replication offsite
Recently I worked on setting up replication between our AWS RDS instance and a server running as a MySQL slave in our location. Although the task was not difficult, there are quite a few areas that one needs to pay attention to.
In this blog post, I am going to outline the process step by step, so that others can benefit and not lose time trying to discover what went wrong.
Disclaimer: Google, DuckDuckGo, the AWS forums and this blog post have been invaluable guides to help me do what I needed to do.
Setup
- One RDS MySQL or Aurora instance (Master)
- One server running MySQL in your premises (or wherever you want to put it) (Slave)
- Appropriate access to IP of the Slave on the master.
Master Setup
There is little to do on our master (RDS). Depending on the database size and update frequency, we will need to set up the maximum retention time for the bin logs. For a very large database we need to set a high number, so that we are able to export the database from the master, import it in the slave and start replication.
Connect to your database and run the following command:
MySQL [(none)]> call mysql.rds_set_configuration('binlog retention hours', 24);
You can use a different number of hours; I am using 24 for this example.
Slave Setup
I am assuming that MySQL is installed on the machine that has been designated as the slave, and also that that machine has ample space for the actual data as well as the binary logs that will be created for the replication.
Edit my.cnf
The location of this file is usually under /etc or /etc/mysql. Depending on your distribution it might be located elsewhere.
[mysqld]
...
#bind-address = 0.0.0.0
# Logging and Replication
general_log_file = /logs/mysql.log
general_log = 1
log_error = /logs/mysql_safe.log
log_slow_queries = /logs/mysql-slow.log
long_query_time = 2
slave-skip-errors = 1062
log-queries-not-using-indexes
server-id = 1234567
log_bin = /logs/mysql-bin.log
expire_logs_days = 2
max_binlog_size = 100M
Note: The configuration file will contain a lot more entries but the ones above are the ones you need to pay attention to.
bind-address: We need to comment this line so that we can connect to the instance from somewhere else in the network. Keep this line if you are going to work only on the slave machine and allow no connections from elsewhere.general_log_file: The location of your query log file. You can disable this (see next entry) but it is always good to keep it on at least at the start, to ensure that replication is moving smoothly. Tailing that log will give you a nice indicator of the activity in your database.general_log: Enable or disable the general loglog_error: Where to store the errors loglog_slow_queries: Where to store the slow queries log. Especially helpful in identifying bottlenecks in your applicationlong_query_time: Time to specify what a slow query is-
slave-skip-errors: 1062 is the *“1062Error ‘Duplicate entry ‘xyz’ for key 1’ on query. Default database: ‘db’. Query: ‘INSERT INTO …’”* error. Helpful especially when the replication starts. log-queries-not-using-indexes: We want this because it can help identifying potential bottlenecks in the applicationserver-id: A unique ID for your slave instance.log_bin: Where the binary replication logs are keptexpire_logs_days: How long to keep the replication logs formax_binlog_size: Maximum replication log size (per file)
Once you set these up, restart your MySQL instance
/etc/init.d/mysql restart
Download the SSH Public Key for RDS
In your slave server, navigate to /etc/mysql and download the rds-combined-ca-bundle.pem file. This file will be used by the slave to ensure that all the replication traffic is done using SSL and nobody can eavesdrop on your data in transit.
cd /etc/mysql
wget https://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem
NOTE You can put the rds-combined-ca-bundle.pem anywhere on your slave. If you change the path, you will have to modify the command to connect the slave to the master (shown further below) to specify the exact location of the key.
Import timezone data
This step might not be necessary depending on your MySQL installation. However since RDS works with UTC, you might find your replication breaking because your slave MySQL instance cannot understand the UTC timezone. The shell command you need to run on your slave machine to fix this is:
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Creating the RDS related tables
RDS uses its own tables to keep track of the replication status and other related data such as the replication heartbeat, configuration etc. Those tables need to be present in the mysql database of your slave in order for the replication to work.
DROP TABLE IF EXISTS `rds_configuration`;
CREATE TABLE `rds_configuration` (
`name` varchar(100) NOT NULL,
`value` varchar(100) DEFAULT NULL,
`description` varchar(300) NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `rds_global_status_history`;
CREATE TABLE `rds_global_status_history` (
`collection_end` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`collection_start` timestamp NULL DEFAULT NULL,
`variable_name` varchar(64) NOT NULL,
`variable_value` varchar(1024) NOT NULL,
`variable_delta` int(20) NOT NULL,
PRIMARY KEY (`collection_end`,`variable_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `rds_global_status_history_old`;
CREATE TABLE `rds_global_status_history_old` (
`collection_end` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`collection_start` timestamp NULL DEFAULT NULL,
`variable_name` varchar(64) NOT NULL,
`variable_value` varchar(1024) NOT NULL,
`variable_delta` int(20) NOT NULL,
PRIMARY KEY (`collection_end`,`variable_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `rds_heartbeat2`;
CREATE TABLE `rds_heartbeat2` (
`id` int(11) NOT NULL,
`value` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `rds_history`;
CREATE TABLE `rds_history` (
`action_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`called_by_user` varchar(50) NOT NULL,
`action` varchar(20) NOT NULL,
`mysql_version` varchar(50) NOT NULL,
`master_host` varchar(255) DEFAULT NULL,
`master_port` int(11) DEFAULT NULL,
`master_user` varchar(16) DEFAULT NULL,
`master_log_file` varchar(50) DEFAULT NULL,
`master_log_pos` mediumtext,
`master_ssl` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `rds_replication_status`;
CREATE TABLE `rds_replication_status` (
`action_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`called_by_user` varchar(50) NOT NULL,
`action` varchar(20) NOT NULL,
`mysql_version` varchar(50) NOT NULL,
`master_host` varchar(255) DEFAULT NULL,
`master_port` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `rds_sysinfo`;
CREATE TABLE `rds_sysinfo` (
`name` varchar(25) DEFAULT NULL,
`value` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
NOTE I am not 100% that all of these tables are needed. I have seen only the rds_heartbeat2 and rds_replication_status used. You can experiment with these when you enable replication and add each table in turn if needed. You can confirm whether the above are correct for your instance by connecting to the master and taking a mysqldump of the mysql database.
Replication
Replication user
We need to create a user in our master database that will have the appropriate rights to perform all the replication related actions. We need these commands to be run on the master. For this example I am creating a user called rpluser with the password 424242:
MySQL [(none)]> CREATE USER 'rpluser'@'%' IDENTIFIED BY '424242';
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'rpluser'@'%';
Master Status
Connect to your master and issue this command:
MySQL [(none)]> show master status;
The output will be something like this:
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| mysql-bin-changelog.000123 | 171819 | | | |
+----------------------------+----------+--------------+------------------+-------------------+
Keep those values handy (File and Position) since we will use them to instruct the slave where to start requesting data from the master (binlog file and position).
mysqldump
Take a database dump of all the databases in RDS (exclude information_schema and mysql). If your database can afford a bit of downtime you can use the --opt flag in mysqldump, which will lock all tables until the backup completes. If not, you can use the --skip-add-locks flag. More information about mysqldump options can be found here
mysqldump --host=192.168.1.2 --user='root' --password my_db > /backups/my_db.sql
Adjust the above command to fit your needs. Once all databases have been dumped, we need to import them in the slave.
Importing data in the slave
Navigate to the folder you have all the *.sql dump files, connect to the slave database and start sourcing them.
cd /backups
mysql --host=192.168.1.2 --user='root' --password
MySQL [(none)]> create database my_db;
MySQL [(none)]> use my_db;
MySQL [my_db]> source my_db.sql;
Repeat the process of creating the database, using it and sourcing the dump file until all your databases have been imported.
NOTE There are other ways of doing the above, piping the results directly to the database or even using RDS to get the data straight from it without a mysqldump. Whichever way you choose is up to you. In my experience, the direct import worked for a bit until our database grew to a point that it was timing out or breaking while importing, so I opted for the multi step approach. Have a look at this section in the AWS RDS documentation for more options.
Connecting to the master
Once your restore has been completed it is time to connect our slave to the master. In order to connect to the master from the slave we need to verify the following:
- The name of the RDS instance (for the command below I will use
myinstance.rds.amazonaws.com) - The name of the replication user (we chose
rpluser) - The password of the replication user (we chose
424242) - The master log file (see above, we got
mysql-bin-changelog.000123fromshow master status;) - The master log file position (see above, we got
171819) - The location of the SSL certificate (we used
/etc/mysql/rds-combined-ca-bundle.pem)
The command we need to run on the slave MySQL server is (newlines added for readability):
MySQL [(none)]> CHANGE MASTER TO
-> MASTER_HOST='myinstance.rds.amazonaws.com',
-> MASTER_USER='rpluser',
-> MASTER_PASSWORD='424242',
-> MASTER_LOG_FILE='mysql-bin-changelog.000123',
-> MASTER_LOG_POS=171819,
-> MASTER_SSL=1,
-> MASTER_SSL_CERT='',
-> MASTER_SSL_CA='/etc/mysql/rds-combined-ca-bundle.pem',
-> MASTER_SSL_KEY='';
Starting the replication
All we have to do now is to start the slave:
MySQL [(none)]> START SLAVE;
We can check if everything is OK either by using the general log (see my.cnf section) by tailing it from the shell:
tail -f /logs/mysql.log
or by issuing this command on the mysql prompt:
MySQL [(none)]> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: myinstance.rds.amazonaws.com
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-changelog.000123
Read_Master_Log_Pos: 171819
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 123
...
Last_Errno: 0
Last_Error:
...
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/mysql/rds-combined-ca-bundle.pem
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
...
Congratulations on your working RDS slave (offsite) machine :)
Conclusion
This blog post is by no means exhausting all the topics that replication can cover. For additional information please see the references below.
I hope you find this post helpful :)