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 []: