Pythian Blog: Technical Track

Setting up MySQL encrypted replication on MySQL 5.7 with GTID

by Pythian Marketing on Jul 23, 2018 12:00:00 AM

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.

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.