MySQL SSL Connection

With an unencrypted connection between the MySQL client and the server, someone with access to the network could watch all your traffic and inspect the data being sent or received between client and server.

MySQL supports encrypted connections between clients and the server. Percona server generates all the SSL certificates by default. You need to just use it.

SSL Certificates

Percona Server keeps all the SSL certificate in it’s default data directory. It can be checked using the below command.

mysql> show global variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.00 sec)

root@mysql01:~# ls -ltrh /var/lib/mysql/*.pem
-rw------- 1 mysql mysql 1.7K Oct 10 11:01 /var/lib/mysql/ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Oct 10 11:01 /var/lib/mysql/ca.pem
-rw------- 1 mysql mysql 1.7K Oct 10 11:01 /var/lib/mysql/server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Oct 10 11:01 /var/lib/mysql/server-cert.pem
-rw------- 1 mysql mysql 1.7K Oct 10 11:01 /var/lib/mysql/client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Oct 10 11:01 /var/lib/mysql/client-cert.pem
-rw-r--r-- 1 mysql mysql  452 Oct 10 11:01 /var/lib/mysql/public_key.pem
-rw------- 1 mysql mysql 1.7K Oct 10 11:01 /var/lib/mysql/private_key.pem

SSL Connection

Let’s create a SSL user to permit SSL encrypted connection

GRANT ALL PRIVILEGES ON *.* TO 'ssl_user'@'%' IDENTIFIED BY 'ssl_pass' REQUIRE SSL;

mysql> select * from mysql.user where user='ssl_user'\G
*************************** 1. row ***************************
                  Host: %
                  User: ssl_user
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           ..
           ..
           ..   
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: ANY
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *563144F6BF6A0AF5CC390B3BFE6CAD44BE3FE9F6
      password_expired: N
 password_last_changed: 2018-10-11 11:12:34
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

From above it’s quite clear that connection is of SSL type.

Secure Connection

Copy client certificate (client-cert.pem, client-key.pem) from database server to web-server (from where you need to connect the db server)

Add the following lines to mysql configuration file /etc/my.cnf under [client]

[client]
# SSL
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem

Secure connection example below.

mysql -h 192.168.111.156 -ussl_user -p --ssl-cert=/etc/mysql-ssl/client-cert.pem --ssl-key=/etc/mysql-ssl/client-key.pem 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 148
Server version: 5.7.23-23-log Percona Server (GPL), Release '23', Revision '500fcf5'

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Connection id:		148
Current database:	
Current user:		ssl_user@192.168.110.242
SSL:			Cipher in use is DHE-RSA-AES256-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.23-23-log Percona Server (GPL), Release '23', Revision '500fcf5'
Protocol version:	10
Connection:		192.168.111.156 via TCP/IP
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			7 hours 50 min 54 sec

Threads: 4  Questions: 480  Slow queries: 0  Opens: 181  Flush tables: 1  Open tables: 59  Queries per second avg: 0.016
--------------

mysql> 

Conclusion