Well before Amazon VPC many customers had deployed their application on AWS classic public cloud. Still many customers are inside AWS classic cloud and have not yet migrated to Amazon VPC. Keeping them in mind, This post explains the configuration steps to enable database replication between MySQL Master and Slave on two different Amazon EC2 regions (on AWS Classic cloud).
- MySQL Master is setup in US-East and Slave is setup in APAC.
- Asynchronous replication is enabled between Master and Slave over internet. To protect the data in transit SSL encryption is used. It is bad idea to transfer DB data over Amazon EC2 regions in clear without protection.
- This technique is useful only for MySQL on EC2 on AWS Classic cloud. For replication of MySQL in Amazon VPC, i recommend VPN between the Amazon EC2 regions. To know more about this refer Amazon VPC- VPN article.
- Since the Data is encrypted and decrypted by MySQL EC2 instances itself, it adds additional burden on EC2 instance. Usually MySQL is Memory/IO intensive, but adding SSL enc/dec function to it, we need to power it with more CPU as well.
- It is recommended to use larger EC2 instances with very good CPU,Memory and IO bandwidth for such cases. CPU is needed for SSL, Memory for MySQL and IO for replication over internet. Thought it is possible to set this up with small/medium EC2, it will not give good performance.
- Elastic IP is a mandatory recommendation for this replication process
- This setup is usually good for warm Disaster Recovery mode, Where MySQL is already up to date on alternate Amazon EC2 region and you need to bring the Web/App/LB tier using CloudFormation templates in event of primary location outage.
- RDS MySQL does not offer Inter Region DR currently, so it is out of scope of this article.
- Since the replication happens over internet between MySQL Master and Slave EC2, X amount(secs -->mins) of replication lag is natural. Please factor this in your RPO/RTO objectives of your DR.
- This is not a replacement to backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though. So it is recommended to take regular backups and snapshots and transfer them to alternate amazon ec2 region.For copying AMI,Snapshots and Security groups to Alternate Amazon EC2 region, Refer articles:
- http://harish11g.blogspot.com/2013/05/Amazon-EC2-AMI-Copy-between-Multiple-Amazon-EC2-regions-performance-tips-how-to.html
- http://harish11g.blogspot.com/2013/05/Amazon-EBS-Snapshot-Copy-Multiple-Amazon-EC2-regions-howto-performance-tips-practices.html
- http://harish11g.blogspot.com/2013/05/Migrating-Amazon-Security-groups-to-Multi-Region-setup.html
Step 1: Setup MySQL Master/Slave replication with SSL:
Launch Two RHEL 64 bit EC2 instance with MySQL Database on EC2. The entire setup is done inside AWS Classic cloud and not Amazon VPC. Master MySQL Amazon EC2 is in US-EAST (Master US-East-1a) region and One Amazon EC2 for MySQL Slave in Asia Pacific Singapore region (ap-southeast-1a)
Install MySQL on EC2 and configure some basic steps:
yum update
rpm -Uvh http://mirror.nus.edu.sg/Fedora/epel/6/x86_64/epel-release-6-7.noarch.rpm
rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
yum --enablerepo=remi -y install mysql mysql-server
service mysqld start
chkconfig --levels 235 mysqld on
mkdir /var/log/mysql
touch /var/log/mysql/mysql-bin.log
chown -R mysql:mysql /var/log/mysql
Disable IPTABLES:
service iptables stop
service ip6tables stop
Step 2: Configure the following steps on MySQL Master & Slave Amazon EC2's
To enable SSL, Edit the my.cnf file
vim /etc/my.cnf
Add a line with the word ssl to the [mysqld] section:
ssl
Restart MySQL
mysql> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+-------+
7 rows in set (0.00 sec)
The response shows that now SSL is enabled.
Step 3: Steps to create SSL Certificates on MySQL EC2
Now we need to create the CA, server and client certificates that we need for the SSL connections. Create these certificates in the directory /etc/mysql/certs/
Step 3.1 : On MySQL Master EC2 - create CA/Server/Client Certificate:
mkdir -p /etc/mysql/certs
cd /etc/mysql/certs
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem
Create server certificate:
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Create client certificate:
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
NOTE: Please use “Unique Common Names” while creating certificates otherwise SSL will not work.
Add the following ssl-ca, ssl-cert, and ssl-key lines under [mysqld] section in /etc/my.cnf
[mysqld]
ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem
Restart MySQL to load the SSL configurations
Copy the following certificate files to MySQL Slave EC2 using SCP to a new directory called /etc/mysql/certs/
ca-cert.pem,client-cert.pem,client-key.pem,server-cert.pem,server-key.pem
Add the following in MySQL Slave /etc/my.cnf under [mysqld]
[mysqld]
ssl
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/client-cert.pem
ssl-key=/etc/mysql/certs/client-key.pem
Restart MySQL to load the SSL configuration.
Step 4: Configuring replication settings
Step 4.1: On MySQL Master Amazon EC2
Add the following in /etc/my.cnf under [mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
#log_bin_index = /var/log/mysql/mysql-bin.log.index
max_binlog_size = 100M
expire_logs_days = 1
Restart MySQL
service mysqld restart
Execute the following in MySQL EC2 shell terminal of Master:
Note:The REQUIRE SSL string is optional; if you leave it out, slave_user will be allowed to connect through encrypted and also non encrypted connections. If you use REQUIRE SSL, then only encrypted connections are allowed between slave and master.
$ mysql -u root -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user1'@'%' IDENTIFIED BY 'mysql_mslave1' REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 335 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> unlock tables;
mysql> quit
Step 4.2:On MySQL Slave EC2 instance :
Edit the my.cnf file
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
#log_bin_index = /var/log/mysql/mysql-bin.log.index
max_binlog_size = 100M
expire_logs_days = 1
Restart MySQL
service mysqld restart
mysql -u root -p
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='ec2-IP-49.compute-1.amazonaws.com', MASTER_USER='slave_user1', MASTER_PASSWORD='mysql_mslave1', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=107,MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/certs/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/certs/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/certs/client-key.pem';
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;
** there should be a message "Waiting for master to send event"
mysql>quit
Step 5: Test for MySQL AWS Inter Region Replication between MySQL Master & Slave:
On MySQL Master EC2 instance
mysql -u root -pmysql> create database rep_test_US_APAC;
mysql> use rep_test_US_APAC;
mysql> CREATE TABLE t1(c1 int,c2 var(100));
Note: In MySQL Slave Amazon EC2 in APAC You can find that database and the table is created. Next step, Insert data into the Master MySQL EC2, in few seconds/minutes replication lag check you can find that the data is present on the slave. Stop inserting the records to master and verify that the slave is up to date, Check the replication lag status on Slave EC2 by applying following command
$ mysql -u root -p -e 'show slave status\G' | grep -i seconds
Seconds_Behind_Master: 0
SHOW SLAVE STATUS \G
Note:It is important that both Slave_IO_Running and Slave_SQL_Running have the value "Yes" in the output (otherwise something went wrong, and you should check your configuration steps again and /var/log/syslog to find out about any errors); as you're using an SSL connection now, you should also find values in the fields Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_Cert, and Master_SSL_Key.
This article was co-authored with ramprasad. His linkedin handle in.linkedin.com/in/ramprasadguru
No comments:
Post a Comment