Secure MySQL/MariaDB Master‑Slave Replication with SSL: Step‑by‑Step Guide

Introduction

Backing up databases is a top priority in production, and when replication must cross network boundaries, MySQL/MariaDB’s plain‑text replication cannot guarantee data security; SSL encryption is required.

Deployment Configuration

Experiment Topology

Experiment Environment

System: CentOS 6.6 Database version: mariadb‑5.5.36

# Note: master and slave DB versions must match; system time must be synchronized

Configure Master‑Slave Replication

Install MariaDB and prepare directories:

[root@node1 ~]# mkdir /mydata/data -pv

[root@node1 ~]# groupadd -r mysql

[root@node1 ~]# useradd -g mysql -r mysql

[root@node1 ~]# chown -R mysql.mysql /mydata/data

[root@node1 ~]# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local

[root@node1 ~]# cd /usr/local

[root@node1 local]# ln -sv mariadb-5.5.36-linux-x86_64 mysql

[root@node1 local]# chown -R root.mysql mysql

Provide configuration and script files:

[root@node1 local]# mkdir /etc/mysql

[root@node1 mysql]# cp /support-files/my-large.cnf /etc/mysql/my.cnf

[root@node1 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

[root@node1 mysql]# chmod +x /etc/rc.d/init.d/mysqld

[root@node1 mysql]# chkconfig --add mysqld

[root@node1 mysql]# chkconfig mysqld on

# Apply the above on both master and slave

Master server configuration:

[root@node1 mysql]# mkdir /mydata/binlogs

[root@node1 mysql]# chown -R mysql.mysql /mydata

[root@node1 mysql]# vim /etc/mysql/my.cnf

datadir = /mydata/data

log-bin = /mydata/binlogs/master-bin

binlog_format = mixed

server-id = 1   # must differ from slave

[root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data

[root@node1 mysql]# service mysqld start

Authorize a replication account on the master (image omitted for brevity).

View binary log position (image omitted).

Slave Server Configuration

[root@node2 mysql]# mkdir /mydata/relaylogs

[root@node2 mysql]# chown -R mysql.mysql /mydata

[root@node2 mysql]# vim /etc/mysql/my.cnf

datadir = /mydata/data

#log-bin = mysql-bin

#binlog_format = mixed

#log-slave-updates = 1   # enable when slave is also a master

server-id = 2   # must differ from master

relay-log = /mydata/relaylogs/relay-bin   # relay log location

read_only = 1   # set slave to read‑only

[root@node2 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data

[root@node2 mysql]# service mysqld start

Connect slave to master (image omitted) and check slave status (image omitted). The status shows full synchronization.

Replication Test

Insert data on the master (image omitted) and verify the same data appears on the slave (images omitted). The replication works.

Implement SSL Secure Transmission

Check SSL status (image omitted).

Configure CA on Master

[root@node1 ~]# cd /etc/pki/CA

(umask 077; openssl genrsa -out private/cakey.pem 2048)

openssl req -new -x509 -key private/cakey.pem -out cacert.pem -days 3650

touch {index.txt,serial}

echo 01 > serial

Generate Certificates

Master:

[root@node1 CA]# mkdir /etc/mysql/ssl

[root@node1 CA]# cd /etc/mysql/ssl

(umask 077; openssl genrsa -out master.key 2048)

openssl req -new -key master.key -out master.csr

openssl ca -in master.csr -out master.crt -days 3650

Slave:

[root@node2 ssl]# (umask 077; openssl genrsa -out slave.key 2048)

openssl req -new -key slave.key -out slave.csr

Sign slave certificate on master and copy:

[root@node2 ssl]# scp slave.csr node1:/tmp

[root@node1 ssl]# openssl ca -in /tmp/slave.csr -out /tmp/slave.crt -days 3650

[root@node1 ssl]# scp /tmp/slave.crt node2:/etc/mysql/ssl

Copy CA certificate to both servers and set permissions:

[root@node1 ssl]# cp /etc/pki/CA/cacert.pem ./

[root@node1 ssl]# scp /etc/pki/CA/cacert.pem node2:/etc/mysql/ssl

[root@node1 ssl]# chown -R mysql.mysql ./

Configure SSL in my.cnf

[root@node1 ~]# vim /etc/mysql/my.cnf

[mysqld]

ssl

ssl-ca = /etc/mysql/ssl/cacert.pem

ssl-cert = /etc/mysql/ssl/master.crt

ssl-key = /etc/mysql/ssl/master.key

[root@node1 ~]# service mysqld restart

Verify SSL status again (image omitted). Restrict the replication user to SSL (image omitted).

Test login from slave (image omitted) and start replication over SSL (image omitted). Check slave status (image omitted) – if Slave_IO_Running shows NO, stop the slave, flush master logs, update slave’s log file/position, and start the slave again. After correction, both Slave_IO_Running and Slave_SQL_Running show YES.

Final replication test: add data on master (image omitted) and confirm the data appears on the slave (image omitted).

The End

MySQL/MariaDB master‑slave replication secured with SSL is now operational; any issues encountered can be resolved by following the troubleshooting steps above.