The procedure below outlines the steps to configure SSL on both the MySQL source and replica.
Before generating a Certificate Authority (CA) certificate, confirm whether an existing CA certificate is already present on the primary server. If a certificate exists, you can use the command below to verify its details:
$ cd /mysql/data
$ openssl x509 -in ca.pem -text -noout
Where ca.pem is the CA certificate.
Use the details below to create a certificate on the source and replica
Step 1:
Generate the CA Certificate
$ openssl genpkey -algorithm RSA -out ca-key.pem
$ openssl req -new -key ca-key.pem -out ca-csr.pem
$ openssl x509 -req -in ca-csr.pem -signkey ca-key.pem -out ca.pem
Step 2:
Generate the Server Certificate and Key
$ openssl genpkey -algorithm RSA -out server-key.pem
$ openssl req -new -key server-key.pem -out server-csr.pem
$ openssl x509 -req -in server-csr.pem -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem
Step 3:
Generate the Client Certificate and Key
$ openssl genpkey -algorithm RSA -out client-key.pem
$ openssl req -new -key client-key.pem -out client-csr.pem
$ openssl x509 -req -in client-csr.pem -CA ca.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem
Step 4:
Edit MySQL Configuration
Add the following entries to the my.cnf file on the source and replica
On the source
vi /etc/mysql/my.cnf
ssl-ca = /mysql/data/ca.pem
ssl-cert = /mysql/data/server-cert.pem
ssl-key = /mysql/data/server-key.pem
On the replica
vi /etc/mysql/my.cnf
ssl-ca = /mysql/data/ca.pem
ssl-cert = /mysql/data/client-cert.pem
ssl-key = /mysql/data/client-key.pem
Restart the replica MySQL instance
Step 5:
Restart MySQL server for changes to take effect
Verify SSL Configuration on the Server
SHOW VARIABLES LIKE '%ssl%';
Step 6:
Configure MySQL Client to Use SSL
$ mysql -u username -p --ssl-ca=/mysql/data/ca.pem --ssl-cert=/mysql/data/client-cert.pem --ssl-key=/mysql/data//client-key.pem -h mysql-server-hostname
mysql> SHOW STATUS LIKE 'Ssl_cipher';
From the output ensure that SSL is enabled
Step 7:
Enable replication
mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST = 'source IP address',
SOURCE_PORT = 3306,
SOURCE_USER = 'repltest',
SOURCE_PASSWORD = 'xxxxxxxxx',
SOURCE_AUTO_POSITION = 1;