mkdir /etc/newcerts/ cd /etc/newcerts/
[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
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
[root@po-mysql2 newcerts]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OKCopy 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 restartVerify 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 --------------
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)
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.
Looking to optimize your MySQL use?