Secure MySQL/MariaDB Master‑Slave Replication with SSL: Step‑by‑Step Guide
This tutorial walks through setting up secure MySQL/MariaDB master‑slave replication over a network using SSL encryption, covering environment preparation, installation, configuration of both master and slave servers, certificate generation, SSL integration, troubleshooting, and verification of data synchronization.
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 synchronizedConfigure 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 mysqlProvide 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 slaveMaster server configuration:
[root@node1 mysql]# mkdir /mydata/binlogs[root@node1 mysql]# chown -R mysql.mysql /mydata[root@node1 mysql]# vim /etc/mysql/my.cnfdatadir = /mydata/datalog-bin = /mydata/binlogs/master-binbinlog_format = mixedserver-id = 1 # must differ from slave[root@node1 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data[root@node1 mysql]# service mysqld startAuthorize 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.cnfdatadir = /mydata/data#log-bin = mysql-bin#binlog_format = mixed#log-slave-updates = 1 # enable when slave is also a masterserver-id = 2 # must differ from masterrelay-log = /mydata/relaylogs/relay-bin # relay log locationread_only = 1 # set slave to read‑only[root@node2 mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data[root@node2 mysql]# service mysqld startConnect 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 3650touch {index.txt,serial}echo 01 > serialGenerate 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.csropenssl ca -in master.csr -out master.crt -days 3650Slave:
[root@node2 ssl]# (umask 077; openssl genrsa -out slave.key 2048)openssl req -new -key slave.key -out slave.csrSign 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/sslCopy 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]sslssl-ca = /etc/mysql/ssl/cacert.pemssl-cert = /etc/mysql/ssl/master.crtssl-key = /etc/mysql/ssl/master.key[root@node1 ~]# service mysqld restartVerify 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.
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
