MySQL Replication to AWS RDS with SSL

you currently have a MySQL database

and you're thinking about moving your database

if your dataset is small, like maybe under a gigabyte or 2,

but if your dataset is bigger, say a few 100 gigabytes, it's going to take hours to transfer and load your data

luckily, there's this thing called replication

replication is nothing new, it has been around for decades

you can setup your new database ahead of time and continuously keep it updated

so your search for a cloud provider brought you to

where they have this great Relational Database Service

you pick the MySQL option, and set about configuring it so that you can replicate your 100's of gigabytes of data

ssl_encryption, yes please

until you look at the documentation...

apparently, amazon expects you to not worry about transfering your data in the clear over the internet

this is usually not good

so, what are your options?

we came up with 3...

By Mathew Brady - ID:HD-SN-99-01789This media is available in the holdings of the National Archives and Records Administration, cataloged under the ARC Identifier (National Archives Identifier) 529975.This tag does not indicate the copyright status of the attached work. A normal copyright tag is still required. See Commons:Licensing for more information.English | Español | Français | Italiano | Македонски | മലയാളം | Nederlands | Polski | Português | Русский | Slovenščina | Türkçe | Українська | Tiếng Việt | 中文（简体） | 中文（繁體） | +/−, Public Domain, https://commons.wikimedia.org/w/index.php?curid=1294425

this is usually not a good option

quite easy to setup a tunnel on an intermediate server

            
              ssh -n -N -T -L 3306:localhost:3306 tunneluser@192.168.10.1

redirect stdin

do not execute a remote command

disable pseudo-terminal allocation

forward local port to remote host:port

the server hosting the tunnel should

be an ec2 instance

be in the same security group as your rds instance

have a public ip address

not allow inbound traffic from the internet

this setup

is relatively easy and straight-forward

can be run with minimal hardware/resources

also assumes that you're able to load the rds instance using a mysqldump file

there is no special configuration for securing your data. it's all handled via the ssh tunnel

so what if you don't have a mysqldump?

or want finer control over the data being replicated?

a relay server is another database instance that acts as an intermediary

it is a fully functional database instance

with replication filters for specific databases and tables

blackhole storage engines do not store any data, they just log inserts, updates and deletes, and pass data along

relay servers are more complicated to setup, however they provide more flexibility

before setting up your relay, best to verify that your existing database supports ssl

            
              show global variables like "have%ssl%";
              +---------------+----------+
              | Variable_name | Value    |
              +---------------+----------+
              | have_openssl  | DISABLED |
              | have_ssl      | DISABLED |
              +---------------+----------+

DISABLED or YES is what you're looking for,

if no, then SSL with this server is not possible

just like the server hosting the tunnel, the relay should

be an ec2 instance

be in the same security group as your rds instance

have a public ip address

not allow inbound traffic from the internet

you'll need some ssl certs

one for the original master server

              
openssl req -new -x509 -nodes -days 360 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 360 -nodes -keyout \
  server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 360 -CA ca.pem -CAkey \
  ca-key.pem -set_serial 01 -out server-cert.pem

reference these in the master server's my.cnf

          
ssl-ca=/etc/mysql/certs/ca-cert.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-key=/etc/mysql/certs/server-key.pem

and one for the relay acting as a client

                
openssl req -newkey rsa:2048 -days 360 -nodes -keyout \
  client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 360 -CA ca.pem -CAkey \
  ca-key.pem -set_serial 01 -out client-cert.pem

configure the relay to use the client cert

                
  CHANGE MASTER TO
    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'

configure the rds instance to replicate from the relay

            
CALL mysql.rds_set_external_master (
  'sum.tin.pri.v8',
  3306,
  'replication_user',
  'replication_user_password',
  'mysql-binlogs.000001',
  1,
  0
);

make sure that the relay and rds have the same innodb settings as the master

ask me for a more complete set of instructions