mySQL replication with SSL

15 May 2011 by Misha Dragojevic

With recent Amazon EC2 outage that spilled over multiple availability zones and lasted (for some) few days, one has to re-think High Availability and Disaster Recovery in the cloud. Typical approach, based on Amazon’s official EC2 “best practice”, was to spread assets of the same type (for example database servers) among multiple availability zones in the same region. Based on a recent experience, that approach might be still valid, but if you were affected with the outage (even if you followed the guidelines), you might be looking into different solutions.

Objective is to be be more resilient to cloud outages, to be highly available and to have a disaster recovery plan. To reach that objective, you need to prepare suitable cloud architecture. In this article, I will focus on one segment of the problem: redundant database infrastructure and offer one possible solution.

Cloud architecture for highly available database needs to have at least the following components:

  1. master DB server,
  2. slave database server in the same region as master but in different availability zone,
  3. slave database server in a different region for the purpose of disaster recovery

Assuming you already have first two components, I will explain how to add the third and how to establish mySQL database replication between different Amazon EC2 regions.

If you have servers replicating between different EC2 regions, data goes over the Internet and as such it is open to snooping while servers are open to intrusion and spoofing attacks. Therefore, you need to take extra care about security (unlike when you replicate with one EC2 region where Amazon handles your traffic). To address these you need to carefully configure Amazon firewall and you need to encrypt the traffic between servers. For traffic encryption you could use VPN of some sort, that depends on your other needs. If you only need to encrypt the traffic between DB servers, then using mySQL built-in support for SSL makes the most sense.

With architecture and scope out of the way, let’s get to work. Explanation that follows was tried on Ubuntu 10.04 LTS with mySQL 5.1.41, although it might work on different platforms / versions; principle is the same, syntax might be slightly different.

Before starting, please read official mySQL documentation and make sure you understand the topic.

In one of my previous blogs, I have covered the following:

  1. Generate SSL certificates on mySQL server
  2. Configure mySQL server for SSL
  3. Create SSL test user

See more in my blog post Configure mySQL server to use SSL

Step 1: Prepare slave mySQL DB server

First step requires you to create new EC2 instance that will become slave DB server. That new instance needs to be in a different region then master, per our plan above. I will not detail here how to create new EC2 instance and install mySQL server, but have the following in mind:

  1. try to match OS type and version with master
  2. try to match mySQL version with master
  3. open SSH access from master: methods can be different, but we need to be able to SCP from master. I would suggest private/public key as the only SSH method.
  4. consider using static IP (Elastic IP) for new server. This would simplify Firewall rule (see below)

Make changes to your Amazon Firewall: you need to allow new server to access master DB server on port 3306. Assuming you associated static IP for new server, rule can be established once and never changed. Even if you create new instance, associate it to the same IP and you are good to go.

Screen shot of Amazon EC2 console shows proper configuration for firewall: security group “database” (which should contain master mySQL server) allows incoming traffic on port 3306 from IP address 11.12.13.14 (/32 limits to single IP). In this example IP address 11.12.13.14 represents new slave server.

To check if Firewall is set properly, from new server try to telnet to master on port 3306:

"master.mybigstartup.com" is external name of your Master DB server. You should see something like:

Make sure you pass above tests before you continue.

Step 2: Configure Slave for SSL

  1. Stop mysql on slave: service mysql stop
  2. Push SSL keys and certificates from master. If you followed my previous article, you need to copy /etc/mysql/openssl/* from master to slave.

For example (run this on slave):

 Check if files are copied, make symbolic links:

edit /etc/mysl/my.cnf and add the following lines in “mysqld” section (master should have the same configuration):

 Restart mySQL with: "service mysql restart" In my previous article, I used test user "rep2" to try SSL encrypted connection. Make final test with test user "rep2"

Make sure you pass above tests before you continue.

Step 3: Establish Replication

This is the final step of the process. Before you can establish replication, you need to have mysqldump from the master and you need to know binary log name and position. I will not go over those details here, since that applies to ANY master/slave replication. I will assume you obtained mysqldump from the master and I will assume you loaded the data on slave.

The only thing left then is to start the slave. Start mysql client as root an type the following (replace file and position accordingly):

 Finally, start the slave:

Check replication:

 You should see something like:

Make sure that “Slave_IO_Running” and “Slave_SQL_Running” are “Yes”. Check other fields per your configuration.

Once “Seconds_Behind_Master” goes to zero you are in business.

blog comments powered by Disqus