Setting up MySQL encrypted replication on MySQL 5.7 with GTID
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
[root@po-mysql2 newcerts]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
.............+++
..................+++
e is 65537 (0x10001)
[root@po-mysql2 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.
[root@po-mysql2 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 []:
[root@po-mysql2 newcerts]# openssl rsa -in server-key.pem -out server-key.pem
writing RSA key
[root@po-mysql2 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.
[root@po-mysql2 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 []:
[root@po-mysql2 newcerts]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
[root@po-mysql2 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
[root@po-mysql2 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)
(root@localhost) [(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"
[root@po-mysql2 ~]# 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: repluser@192.168.56.101
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
(root@localhost) [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| po-mysql2 |
+------------+
1 row in set (0.00 sec)
(root@localhost) [(none)]>STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [(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)
(root@localhost) [(none)]>START SLAVE;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(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
(root@localhost) [(none)]>SHOW GRANTS FOR 'repluser'@'%';
+----------------------------------------------+
| Grants for repluser@% |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [(none)]>ALTER USER 'repluser'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [(none)]>SHOW GRANTS FOR 'repluser'@'%';
+----------------------------------------------+
| Grants for repl@% |
+----------------------------------------------+
| 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 'repluser@po-mysql1:53306' - retry-time: 60 retries: 1
(root@localhost) [(none)]>select @@hostname;
+------------+
| @@hostname |
+------------+
| po-mysql3 |
+------------+
1 row in set (0.00 sec)
(root@localhost) [(none)]>stop slave;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]>start slave;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(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 'repluser@po-mysql1: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.
(root@localhost) [(none)]>SELECT @@hostname;
+------------+
| @@hostname |
+------------+
| po-mysql3 |
+------------+
1 row in set (0.00 sec)
(root@localhost) [(none)]>STOP SLAVE;
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(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)
(root@localhost) [(none)]>START SLAVE;
Query OK, 0 rows affected (0.04 sec)
(root@localhost) [(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.
