SSL encryption method for MySQL 5.7

Source: Internet
Author: User
Tags openssl openssl version

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.