Hi folks, let’s continue talk about replication, you can see the basics in this other post
First of all, lets create the ssl certificates, go to the master server:
Create CA certificate:
openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem
Create server certificate:
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Create client certificate:
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
copy ca-cert.pem, client-req.pem, client-cert.pem to slave server
Adjust the cnf file:
ssl ssl-ca=/etc/mysql/sslcerts/ca-cert.pem ssl-cert=/etc/mysql/sslcerts/server-cert.pem ssl-key=/etc/mysql/sslcerts/server-key.pem
Make sure you restart the MySQL service, and check if the ssl is enabled
mysql> SHOW VARIABLES LIKE '%ssl%'; +---------------+-------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/sslcerts/ca-cert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/sslcerts/server-cert.pem | | ssl_cipher | | | ssl_key | /etc/mysql/sslcerts/server-KEY.pem | +---------------+-------------------------------------+ 7 rows IN SET (0.00 sec)
Then, we will need a replication user, and we will specify that this user require ssl:
GRANT REPLICATION SLAVE ON *.* TO 'user'@'178.77.90.38' IDENTIFIED BY 'PASSWORD' REQUIRE SSL; FLUSH PRIVILEGES;
Ok, we have finish your work on master, let’s work on slave server:
Adjust the cnf file:
ssl ssl-ca=/etc/mysql/sslcerts/ca-cert.pem ssl-cert=/etc/mysql/sslcerts/client-cert.pem ssl-key=/etc/mysql/sslcerts/client-key.pem
Let’s check if is everythink ok with ssl
mysql> SHOW VARIABLES LIKE '%ssl%'; +---------------+-------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/sslcerts/ca-cert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/sslcerts/client-cert.pem | | ssl_cipher | | | ssl_key | /etc/mysql/sslcerts/client-KEY.pem | +---------------+-------------------------------------+ 7 rows IN SET (0.00 sec)
Finally, Start the replica:
CHANGE MASTER TO MASTER_HOST='xxxxxx', MASTER_USER='user', MASTER_PASSWORD='PASSWORD', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=0, MASTER_SSL=1, MASTER_SSL_CA = '/etc/mysql/sslcerts/ca-cert.pem', MASTER_SSL_CERT = '/etc/mysql/sslcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/mysql/sslcerts/client-key.pem'; START SLAVE;