SSL encryption method for MySQL 5.7

SSL encryption method for MySQL 5.7

MySQL 5.7.6 or later

(1) Create a certificate to turn on SSL authentication
--Installing OpenSSL
Yum Install-y OpenSSL
OpenSSL version
OpenSSL 1.0.1e-fips 2013

--Installation Certificate
/usr/local/mysql/bin/mysql_ssl_rsa_setup--datadir=/data/mysql/mysql3306/data


--Modify Permissions
Chown-r Mysql:mysql/data/mysql/mysql3306/data


Pwd
/data/mysql/mysql3306/data
[Email protected]_45_133_centos Wed June 10:51:22 data]# ll
Total 1024072
-rw-r-----1 mysql mysql, 17:56 auto.cnf
-RW-------1 root root 1679 June 10:48 Ca-key.pem
-rw-r--r--1 root root 1074 June 10:48 Ca.pem
-rw-r--r--1 root root 1078 June 10:48 Client-cert.pem
-RW-------1 root root 1679 June 10:48 Client-key.pem
-rw-r-----1 mysql mysql 672 June 10:47 Ib_buffer_pool
-rw-r-----1 mysql mysql 1048576000 June 10:47 ibdata1
Drwxr-x---2 mysql mysql 4096 June 17:57 MySQL
Drwxr-x---2 mysql mysql 4096 June 17:57 Performance_schema
-RW-------1 root root 1679 June 10:48 Private_key.pem
-rw-r--r--1 root root 451 June 10:48 Public_key.pem
Drwxr-x---2 mysql mysql 4096 June 10:48 School
-rw-r--r--1 root root 1078 June 10:48 Server-cert.pem
-RW-------1 root root 1675 June 10:48 Server-key.pem
Drwxr-x---2 mysql mysql 12288 June 17:57 Sys
-rw-r-----1 mysql mysql 418 June 14:14 Vm_45_133_centos.log

Certificate required for client connection, of course, no certificate is allowed
-rw-r--r--1 root root 1074 June 10:48 Ca.pem
-rw-r--r--1 root root 1078 June 10:48 Client-cert.pem
-RW-------1 root root 1679 June 10:48 Client-key.pem
-RW-------1 root root 1679 June 10:48 Private_key.pem

Certificates on the server
-rw-r--r--1 root root 1074 June 10:48 Ca.pem
-RW-------1 root root 1679 June 10:48 Ca-key.pem
-rw-r--r--1 root root 451 June 10:48 Public_key.pem
-rw-r--r--1 root root 1078 June 10:48 Server-cert.pem
-RW-------1 root root 1675 June 10:48 Server-key.pem

--Modify MY.CNF
######## #SSL #############
Ssl-ca =/data/mysql/mysql3306/data/ca.pem
Ssl-cert =/data/mysql/mysql3306/data/server-cert.pem
Ssl-key =/data/mysql/mysql3306/data/server-key.pem



(2) Restart MySQL
/etc/init.d/mysql stop
/etc/init.d/mysql start



--View SSL parameter status, view Have_ssl, yes, this means that SSL is already supported
Show global variables like '%ssl% ';
+---------------+--------------------------------------------+
| variable_name | Value |
+---------------+--------------------------------------------+
| Have_openssl | YES |
| Have_ssl | YES |
| Ssl_ca | /data/mysql/mysql3306/data/ca.pem |
|                                            Ssl_capath | |
| Ssl_cert | /data/mysql/mysql3306/data/server-cert.pem |
|                                            Ssl_cipher | |
|                                            SSL_CRL | |
|                                            Ssl_crlpath | |
| Ssl_key | /data/mysql/mysql3306/data/server-key.pem |
+---------------+--------------------------------------------+



Show global status like '%ssl% ';
+--------------------------------+--------------------------+
| variable_name | Value |
+--------------------------------+--------------------------+
| Com_show_processlist | 0 |
| Ssl_accept_renegotiates | 0 |
| ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
|                          Ssl_cipher | |
|                          Ssl_cipher_list | |
| ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| ssl_finished_accepts | 0 |
| ssl_finished_connects | 0 |
| Ssl_server_not_after | June 02:48:05 2027 GMT |
| Ssl_server_not_before | June 02:48:05 GMT |
| Ssl_session_cache_hits | 0 |
| ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | Unknown |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| ssl_session_cache_timeouts | 0 |
| ssl_sessions_reused | 0 |
| ssl_used_session_cache_entries | 0 |
| ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
|                          ssl_version | |
+--------------------------------+--------------------------+



See how SSL is encrypted
Show global variables like ' tls_version ';
+---------------+---------------+
| variable_name | Value |
+---------------+---------------+
| tls_version | tlsv1,tlsv1.1 |
+---------------+---------------+




(3) Configuring SSL Users
Canceling SSL authentication
Grant all privileges on * * to [email protected] '% ' identified by ' 123456 ' require none;
alter user [email protected] '% ' require none;
--Mandatory SSL authentication, even if mandatory SSL is set, the use of--ssl-mode=disable can still avoid SSL authentication at login time
Grant all privileges on * * to [email protected] '% ' identified by ' 123465 ' require SSL;
alter user [email protected] '% ' require SSL;



See if forcing users to use SSL is turned on
Select User,host,ssl_type,ssl_cipher from Mysql.user;
+-----------+-----------+----------+------------+
| user | Host | Ssl_type | Ssl_cipher |
+-----------+-----------+----------+------------+
| Root |          %         |            | |
| Mysql.sys |          localhost |            | |
| Abcssl | %         |            any | |
+-----------+-----------+----------+------------+


(4) When connecting to the database, bring SSL
Do not specify the client certificate method
5.6
--ssl 、--Disable-ssl 、--skip-ssl: In mysql5.7 is the option to be discarded, the future version is no longer supported, the--ssl-mode option is recommended,
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--ssl defaults to 1
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--ssl=0
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--ssl=1 defaults to 1
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--disable-ssl
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--skip-ssl

5.7
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--ssl-mode=disable
/usr/local/mysql/bin/mysql-uroot-p-h127.0.0.1--ssl-mode=required Default required

Connecting from another machine can also be SSL encrypted, indicating that you do not need to install the client certificate
/usr/local/mysql/bin/mysql-uroot-p-h10.105.45.133--ssl-mode=required



Specify the client certificate method, 5.6 of the way, 5.7 can also be used
/usr/local/mysql/bin/mysql--SSL-CA=/DATA/MYSQL/MYSQL3306/DATA/CA.PEM \
--SSL-CERT=/DATA/MYSQL/MYSQL3306/DATA/CLIENT-CERT.PEM \
--SSL-KEY=/DATA/MYSQL/MYSQL3306/DATA/CLIENT-KEY.PEM \
-uroot-p-h127.0.0.1



(5) Connect to verify that the connection is using SSL
\s = = Status
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using Editline Wrapper client version

Connection id:69
Current database:
Current User: [email protected]
Ssl:cipher in use is Dhe-rsa-aes256-sha
Current Pager:stdout
Using outfile: '
Using delimiter:;
Server Version:5.7.18-log MySQL Community Server (GPL)
Protocol version:10
connection:127.0.0.1 via TCP/IP
Server CHARACTERSET:UTF8MB4
Db CHARACTERSET:UTF8MB4
Client Characterset:utf8
Conn. Characterset:utf8
TCP port:3306
uptime:28 min sec

Threads:2 questions:1755 Slow queries:0 opens:114 Flush tables:1 Open tables:102 queries per second avg:1.036
--------------











Workarounds for JDBC Clients
Add Ssl=true or False to the connection string URL:
Url=jdbc:mysql://127.0.0.1:3306/framework?characterencoding=utf8&usessl=true

SSL encryption method for MySQL 5.7