MySQL SSL Enable Replication

This blog post illustrates ” How to setup SSL enabled replication”

blog_pic

By default, mysql package installation creates SSL file in the data directory at the time of installation. If you would like to use different self-signed certificates then create them as described here.

Add SSL setting to my.cnf on all servers.


ssl=on
ssl-ca=/etc/sslcerts/ca.pem
ssl-cert=/etc/sslcerts/server-cert.pem
ssl-key=/etc/sslcerts/server-key.pem

Restart mysql server and verify the settings.

Example: client connections using SSL


#mysql -urpluser -p -P22403 --host 127.0.0.1 --ssl-cert=/etc/sslcerts/client-cert.pem --ssl-key=/etc/sslcerts/client-key.pem -e '\s'
Enter password:
--------------

Connection id: 5
Current database:
Current user: rpluser@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.28-31-log Percona Server (GPL), Release 31, Revision d14ef86
Protocol version: 10
Connection: 127.0.0.1 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8
Conn. characterset: utf8
TCP port: 22403
Uptime: 3 min 10 sec

Threads: 4 Questions: 36 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.189

 

Replication Setup:

CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_USER='rpluser',MASTER_PASSWORD='msandbox',MASTER_PORT=22403,MASTER_SSL = 1,MASTER_SSL_CA = '/etc/sslcerts/ca.pem', MASTER_SSL_CERT = '/etc/sslcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/sslcerts/client-key.pem', MASTER_AUTO_POSITION = 1;


slave [localhost] {msandbox} ((none)) > show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 127.0.0.1
Master_User: rpluser
Master_Port: 22403
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 19948
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 20121
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 19948
Relay_Log_Space: 20324
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/sslcerts/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/sslcerts/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/sslcerts/client-key.pem
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 00022403-1111-1111-1111-111111111111
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 00022403-1111-1111-1111-111111111111:62924-63024
Executed_Gtid_Set: 00022403-1111-1111-1111-111111111111:1-63024
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)