Step 1: Setup MySQL Master/Slave replication with SSL:
Launch Two boxes 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)
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_seial 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.