Step 4 — Master Replication over SSL

Check following attributes on both master and replication servers.

sudo nano /etc/mysql/my.cnf

On Master server

[mysqld]

server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

relay-log = /var/lib/mysql/relay-bin
relay-log-index = /var/lib/mysql/relay-bin.index
relay-log-info-file = /var/lib/mysql/relay-log.info

log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
log-bin = /var/lib/mysql/bin

On Replication Server

[mysqld]

server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1

relay-log = /var/lib/mysql/relay-bin
relay-log-index = /var/lib/mysql/relay-bin.index
relay-log-info-file = /var/lib/mysql/relay-log.info

log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
log-bin = /var/lib/mysql/bin

Restart MySQL on both servers

systemctl restart mysql

On Master server create a directory to save certificates

sudo chmod -R 777 /var/lib/
mkdir /var/lib/mysql-certs
cd /var/lib/mysql-certs/

CA cert

openssl genrsa 2048 > MySQLCAKey.pem

Generating RSA private key, 2048 bit long modulus (2 primes)
..........+++++
........................................................................+++++
e is 65537 (0x010001)

openssl req -new -x509 -nodes -days 3600 -key MySQLCAKey.pem -out MySQLCA-Cert.pem

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:MySQL Replication CA
Email Address []: