Setting up MySQL encrypted replication on MySQL 5.7 with GTID

Posted in: Articles, DBA Lounge, MySQL, Open Source, Technical Track

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.

email

Interested in working with Daniel? Schedule a tech call.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *