These are the steps I used to create a MySQL replication slave using SSL, and were performed using MySQL 5.5 and Ubuntu 15.10.

The highlighted items should be replaced with your own items. I hope someone else finds this useful!


1. On the main database server, set up the replication user from the MySQL command line:

# create the user and require SSL
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replication_password';

# set the permissions
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replication_user'@'%' REQUIRE SSL;

# reload the user privileges
FLUSH PRIVILEGES;


2. On the main database server, create the SSL keys:

sudo mkdir /etc/ssl/certs/mysql
sudo chown mysql /etc/ssl/certs/mysql
cd /etc/ssl/certs/mysql
sudo openssl req -x509 -newkey rsa:2048 -keyout master-private.pem -out master-public.pem -nodes -days 3650

# convert the key to the correct format for mysql
sudo openssl rsa -in master-private.pem -out master-private.pem

sudo cp master-public.pem ca-cert.pem
sudo chown mysql:mysql *


3. On the slave database server, add the SSL keys:

sudo mkdir /etc/ssl/certs/mysql
sudo chown SLAVE_USER /etc/ssl/certs/mysql
cd /etc/ssl/certs/mysql

# transfer the certificate from the master database server to the slave database server
scp MASTER_USER@MASTER_IP_ADDRESS:/etc/ssl/certs/mysql/ca-cert.pem .

# create the slave's ssl keys
sudo openssl req -x509 -newkey rsa:2048 -keyout slave-private.pem -out slave-public.pem -nodes -days 3650

# convert the key to the correct format for mysql
sudo openssl rsa -in slave-private.pem -out slave-private.pem

# append the public key to the certificate
sudo bash -c "cat slave-public.pem >> ca-cert.pem"

# change file ownerships back to mysql
sudo chown mysql:mysql /etc/ssl/certs/mysql -R


4. On the main database server, get the new certificate from the slave:

cd /etc/ssl/certs/mysql
sudo chown MASTER_USER ca-cert.pem
scp SLAVE_USER@SLAVE_IP_ADDRESS:/etc/ssl/certs/mysql/ca-cert.pem .

Set the file permissions back:

sudo chown mysql ca-cert.pem


5. On the main database server, modify the MySQL configuration:

sudo nano /etc/mysql/my.cnf

[mysqld]
ssl
ssl-ca=/etc/ssl/certs/mysql/ca-cert.pem
ssl-cert=/etc/ssl/certs/mysql/master-public.pem
ssl-key=/etc/ssl/certs/mysql/master-private.pem

log-bin=mysql-bin
server-id=2
expire_logs_days=30

Restart MySQL:

sudo service mysql restart

Verify that SSL is enabled via the MySQL command line:

SHOW VARIABLES LIKE '%ssl%';

You can test the connection at this point from the slave server, using:

mysql -ureplication -p -hMASTER_IP_ADDRESS --ssl-ca /etc/ssl/certs/mysql/ca-cert.pem


6. On the main database server, get the log position and dump the database.

From the MySQL command line (session 1):

# prevent any writes from occurring on the database
# the web app will return errors until the lock is released
FLUSH TABLES WITH READ LOCK;

From another MySQL command line (session 2):

# write down the output from this command
SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000055 |   555555 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Close session 2, and dump the database:

cd ~/backups
mysqldump -uroot -p --databases DATABASE_NAME | gzip > 20151231.sql.gz

Back to session 1:

# release the lock that is preventing writes to the database
UNLOCK TABLES;


7. On the slave database server, allow AppArmor to access the ca-cert.pem file:

sudo nano /etc/apparmor.d/usr.sbin.mysqld

Add the line:

/etc/ssl/certs/mysql/*.pem r,


8. On the slave database server, configure MySQL:

sudo nano /etc/mysql/my.cnf

[mysqld]
ssl
ssl-ca=/etc/ssl/certs/mysql/ca-cert.pem
ssl-cert=/etc/ssl/certs/mysql/slave-public.pem
ssl-key=/etc/ssl/certs/mysql/slave-private.pem

server-id=3
relay-log=mysql-relay-bin.log
log-bin=mysql-bin

# also log updates to the slave server (optional)
log_slave_updates=true

Restart MySQL:

sudo service mysql restart

Verify that SSL is enabled via the MySQL command line:

SHOW VARIABLES LIKE '%ssl%';


9. On the slave database server, import the database:

cd ~/backups
scp MASTER_USER@MASTER_IP_ADDRESS:/home/SLAVE_USER/backups/20151231.sql.gz .
gunzip 20151231.sql.gz
mysql -uroot -p < 20151231.sql


10. On the slave database, start replication from the MySQL command line:

# set the master credentials
CHANGE MASTER TO MASTER_HOST='MASTER_IP_ADDRESS', MASTER_USER='replication', MASTER_PASSWORD='replication_password', MASTER_SSL=1, MASTER_SSL_CA='/etc/ssl/certs/mysql/ca-cert.pem', MASTER_LOG_FILE='mysql-bin.000055', MASTER_LOG_POS=555555;

# start the slave
START SLAVE;

# check the status
SHOW SLAVE STATUS\G