In this blog post, I’ll walk you through setting up encrypted replication on MySQL 5.7 with GTID enabled. I will walk you through how to create sample certificates and keys, and then configure MySQL to only use replication via an encrypted SSL tunnel.
For simplicity, the credentials and certificates I used in this tutorial are very basic. I would suggest, of course, you use stronger passwords and accounts.
Let’s get started.
Create a folder where you will keep the certificates and keys
mkdir /etc/newcerts/
cd /etc/newcerts/
Create CA certificate
[[email protected] newcerts]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
.............+++
..................+++
e is 65537 (0x10001)
[[email protected] newcerts]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.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) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Create server certificate
server-cert.pem = public key, server-key.pem = private key
NOTE: The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate otherwise the certificate and key files will not work for servers compiled using OpenSSL.
[[email protected] newcerts]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
....................................................................+++
.+++
writing new private key to 'server-key.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) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:server
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[[email protected] newcerts]# openssl rsa -in server-key.pem -out server-key.pem
writing RSA key
[[email protected] newcerts]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=server
Getting CA Private Key
Create client certificate
client-cert.pem = public key, client-key.pem = private key
NOTE: The Common Name value used for the server and client certificates/keys must each differ from the Common Name value used for the CA certificate otherwise the certificate and key files will not work for servers compiled using OpenSSL.
[[email protected] newcerts]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
.....................+++
....................................................................................+++
writing new private key to 'client-key.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) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:client
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[[email protected] newcerts]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
[[email protected] newcerts]# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd/CN=client
Getting CA Private Key
Verify both client and server certificates
[[email protected] newcerts]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK
Copy certificates, adjust permissions and restart MySQL
Add the server cert files and key to all hosts.
Add the entry below to my.cnf on all hosts.
Make sure the folder and files are owned by MySQL user and group.
Restart MySQL.
scp *.pem master:/etc/newcerts/
scp *.pem slave:/etc/newcerts/
chown -R mysql:mysql /etc/newcerts/
[mysqld]
ssl-ca=/etc/newcerts/ca.pem
ssl-cert=/etc/newcerts/server-cert.pem
ssl-key=/etc/newcerts/server-key.pem
service mysql restart
Verify SSL is enabled and key and certs are shown (check both master and slave)
([email protected]) [(none)]>SHOW VARIABLES LIKE '%ssl%';
+---------------+-------------------------------+
| Variable_name | Value |
+---------------+-------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/newcerts/ca.pem |
| ssl_capath | |
| ssl_cert | /etc/newcerts/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /etc/newcerts/server-key.pem |
+---------------+-------------------------------+
9 rows in set (0.01 sec)
Verify you are able to connect from slave to master
From command line, issue the following commands and look for this output:
“SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256”
[[email protected] ~]# mysql -urepluser -p -P53306 --host po-mysql1 --ssl-cert=/etc/newcerts/client-cert.pem --ssl-key=/etc/newcerts/client-key.pem -e '\s'
Enter password:
--------------
mysql Ver 14.14 Distrib 5.7.21-20, for Linux (x86_64) using 6.2
Connection id: 421
Current database:
Current user: [email protected]
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.21-21-log Percona Server (GPL), Release 21, Revision 2a37e4e
Protocol version: 10
Connection: po-mysql1 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
TCP port: 53306
Uptime: 13 min 38 sec
Threads: 6 Questions: 6138 Slow queries: 4 Opens: 112 Flush tables: 1 Open tables: 106 Queries per second avg: 7.503
--------------
Enable encrypted replication.
We are using GTID in this example, so adjust the command below if you are not using GTID based replication.
Go to the slave host and run the following: (details below)
stop slave
change master
start slave
verify replication is working and using an encrypted connection
([email protected]) [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| po-mysql2 |
+------------+
1 row in set (0.00 sec)
([email protected]) [(none)]>STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
([email protected]) [(none)]>CHANGE MASTER TO MASTER_HOST="po-mysql1", MASTER_PORT=53306, MASTER_USER="repluser", MASTER_AUTO_POSITION = 1, MASTER_PASSWORD='replpassword',
-> MASTER_SSL=1, MASTER_SSL_CA = '/etc/newcerts/ca.pem', MASTER_SSL_CERT = '/etc/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/newcerts/client-key.pem';
Query OK, 0 rows affected, 2 warnings (0.16 sec)
([email protected]) [(none)]>START SLAVE;
Query OK, 0 rows affected (0.01 sec)
([email protected]) [(none)]>SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: po-mysql1
Master_User: repluser
Master_Port: 53306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 491351
Relay_Log_File: relay.000002
Relay_Log_Pos: 208950
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 257004
Relay_Log_Space: 443534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/newcerts/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/newcerts/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/newcerts/client-key.pem
Seconds_Behind_Master: 0
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: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:82150-83149
Executed_Gtid_Set: 3a19f03e-5f76-11e8-b99e-0800275ae9e7:1-2842,
7f0b0f43-d45c-11e7-80f7-0800275ae9e7:1-82620,
85209bfc-d45c-11e7-80f7-0800275ae9e7:1-3,
cc1d9186-5f6b-11e8-b061-0800275ae9e7:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Congratulations, you have configured encrypted replication
This process was only to enable SSL replication; however, if you wish to limit replication to only use SSL connections, you’ll need to alter the replication account accordingly, as shown below.
Go to the master and alter the replication user.
NOTE: For some reason, the SHOW GRANTS command does not show REQUIRE SSL as part of the output, even after changing the account
([email protected]) [(none)]>SHOW GRANTS FOR 'repluser'@'%';
+----------------------------------------------+
| Grants for [email protected]% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
([email protected]) [(none)]>ALTER USER 'repluser'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.04 sec)
([email protected]) [(none)]>SHOW GRANTS FOR 'repluser'@'%';
+----------------------------------------------+
| Grants for [email protected]% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
Test from a slave which has not yet been configured to use encrypted replication.
Notice the error below from this slave, so we know for sure, we can only connect via SSL and replication will not work until we make the required changes:
Last_IO_Error: error connecting to master ‘[email protected]:53306’ – retry-time: 60 retries: 1
([email protected]) [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| po-mysql3 |
+------------+
1 row in set (0.00 sec)
([email protected]) [(none)]>stop slave;
Query OK, 0 rows affected (0.00 sec)
([email protected]) [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
([email protected]) [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: po-mysql1
Master_User: repluser
Master_Port: 53306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 730732
Relay_Log_File: relay.000003
Relay_Log_Pos: 730825
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
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: 730732
Relay_Log_Space: 7465275
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master '[email protected]:53306' - retry-time: 60 retries: 1
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 180719 23:29:07
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:66868-83690
Executed_Gtid_Set: 3a19f03e-5f76-11e8-b99e-0800275ae9e7:1-2842,
7f0b0f43-d45c-11e7-80f7-0800275ae9e7:1-83690,
85209bfc-d45c-11e7-80f7-0800275ae9e7:1-3,
cc1d9186-5f6b-11e8-b061-0800275ae9e7:1-134
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Setup encrypted replication on another slave
Now we just need to follow the same steps as documented above to copy the certs and keys. We restart MySQL, stop slave and reset replication and then replication will work again, this time using SSL.
([email protected]) [(none)]>SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| po-mysql3 |
+------------+
1 row in set (0.00 sec)
([email protected]) [(none)]>STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)
([email protected]) [(none)]>CHANGE MASTER TO MASTER_HOST="po-mysql1", MASTER_PORT=53306, MASTER_USER="repluser", MASTER_AUTO_POSITION = 1, MASTER_PASSWORD='r3pl',
-> MASTER_SSL=1, MASTER_SSL_CA = '/etc/newcerts/ca.pem', MASTER_SSL_CERT = '/etc/newcerts/client-cert.pem', MASTER_SSL_KEY = '/etc/newcerts/client-key.pem';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
([email protected]) [(none)]>START SLAVE;
Query OK, 0 rows affected (0.04 sec)
([email protected]) [(none)]>SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: po-mysql1
Master_User: repluser
Master_Port: 53306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 1128836
Relay_Log_File: relay.000002
Relay_Log_Pos: 398518
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
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: 1128836
Relay_Log_Space: 398755
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /etc/newcerts/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /etc/newcerts/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /etc/newcerts/client-key.pem
Seconds_Behind_Master: 0
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: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7f0b0f43-d45c-11e7-80f7-0800275ae9e7:83691-84588
Executed_Gtid_Set: 3a19f03e-5f76-11e8-b99e-0800275ae9e7:1-2842,
7f0b0f43-d45c-11e7-80f7-0800275ae9e7:1-84588,
85209bfc-d45c-11e7-80f7-0800275ae9e7:1-3,
cc1d9186-5f6b-11e8-b061-0800275ae9e7:1-134
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Congratulations, you now have SSL replication enabled. MySQL replication will now only work with encryption.
1 Comment. Leave new
Thanks for this article. This is helpful.
I think there is no need to copy server certs/keys to client.