CentOS 8
Sponsored Link

MySQL 8.0 : SSL/TLS Setting
2019/11/28
 
Configure SSL/TLS Setting on MySQL.
[1]
[2] Configure MySQL.
# copy certificates gotten in [1]

[root@www ~]#
mkdir /var/lib/mysql/pki

[root@www ~]#
cp /etc/letsencrypt/live/www.srv.world/* /var/lib/mysql/pki/

[root@www ~]#
chown -R mysql. /var/lib/mysql/pki
[root@www ~]#
vi /etc/my.cnf.d/mysql-server.cnf
# add under [mysqld] section

[mysqld]
ssl-ca=/var/lib/mysql/pki/chain.pem
ssl-cert=/var/lib/mysql/pki/cert.pem
ssl-key=/var/lib/mysql/pki/privkey.pem
[root@www ~]#
systemctl restart mysqld
# verify settings

[root@www ~]#
mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, 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.

# OK if status is like follows
mysql> show variables like '%ssl%'; 
+--------------------+--------------------------------+
| Variable_name      | Value                          |
+--------------------+--------------------------------+
| have_openssl       | YES                            |
| have_ssl           | YES                            |
| mysqlx_ssl_ca      |                                |
| mysqlx_ssl_capath  |                                |
| mysqlx_ssl_cert    |                                |
| mysqlx_ssl_cipher  |                                |
| mysqlx_ssl_crl     |                                |
| mysqlx_ssl_crlpath |                                |
| mysqlx_ssl_key     |                                |
| ssl_ca             | /var/lib/mysql/pki/chain.pem   |
| ssl_capath         |                                |
| ssl_cert           | /var/lib/mysql/pki/cert.pem    |
| ssl_cipher         |                                |
| ssl_crl            |                                |
| ssl_crlpath        |                                |
| ssl_fips_mode      | OFF                            |
| ssl_key            | /var/lib/mysql/pki/privkey.pem |
+--------------------+--------------------------------+
17 rows in set (0.01 sec)
[3] To connect with SSL/TLS from Clients, connect with specifying [--ssl-mode] option.
[root@www ~]#
mysql -u root -p --ssl-mode=required

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, 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.

You are enforcing ssl connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.

# show status
mysql> show status like 'ssl_cipher'; 
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.00 sec)

mysql> exit 
Bye

# for no SSL/TLS connection

[root@www ~]#
mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, 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.

# value is empty
mysql> show status like 'ssl_cipher'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.00 sec)
[4] To force require users to connect with SSL/TLS, set like follows.
[root@www ~]#
mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.17 Source distribution

Copyright (c) 2000, 2019, 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.

# create a user who is required SSL/TLS
mysql> create user redhat identified by 'password' require ssl; 
Query OK, 0 rows affected (0.12 sec)

# show status SSL/TLS required users set [ssl_type] [ANY]
MariaDB [(none)]> select user,host,ssl_type from mysql.user; 
+------------------+-----------+----------+
| user             | host      | ssl_type |
+------------------+-----------+----------+
| redhat           | %         | ANY      |
| cent             | %         |          |
| mysql.infoschema | localhost |          |
| mysql.session    | localhost |          |
| mysql.sys        | localhost |          |
| root             | localhost |          |
+------------------+-----------+----------+
5 rows in set (0.00 sec)

# set SSL/TLS required to an existing user
MariaDB [(none)]> alter user 'cent'@'%' require ssl; 
Query OK, 0 rows affected (0.06 sec)

MariaDB [(none)]> select user,host,ssl_type from mysql.user; 
+------------------+-----------+----------+
| user             | host      | ssl_type |
+------------------+-----------+----------+
| redhat           | %         | ANY      |
| cent             | %         | ANY      |
| mysql.infoschema | localhost |          |
| mysql.session    | localhost |          |
| mysql.sys        | localhost |          |
| root             | localhost |          |
+------------------+-----------+----------+
6 rows in set (0.00 sec)
Matched Content