MySQL Amazon EC2 Cross region Master-Slave Replication using SSL

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:

1. http://harish11g.blogspot.com/2013/05/Amazon-EC2-AMI-Copy-between-Multiple-Amazon-EC2-regions-performance-tips-how-to.html
2. http://harish11g.blogspot.com/2013/05/Amazon-EBS-Snapshot-Copy-Multiple-Amazon-EC2-regions-howto-performance-tips-practices.html
3. 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

Restart MySQL to load the SSL configurations

Step 3.2 : Configure the certificates on MySQL Slave EC2 

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

Execute the following in MySQL EC2 shell terminal of Slave:     

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