Example tutorial of SSL connection-Mysql Tutorial-php.cn

. Background

* In a production environment, security is always an issue that cannot be ignored, and database security is a top priority, because all data is stored in the database

When using a non-encrypted method to connect to the MySQL database, all information transmitted on the network is in clear text and can be intercepted by everyone on the network, and sensitive information may be leaked. When transmitting sensitive information (such as passwords), you can use SSL connection.

* * When the version is less than 5.7.6, follow the MySQL 5.6 SSL configuration

method.

MySQL connection method

* socket connection

* TCP non-SSL connection

* SSL secure connection

* SSL + password connection [version > MySQL 5.7.5]

* SSL + password + key connection

3. Introduction to SSL

* SSL refers to SSL/TLS, which is a Encryption protocol for secure communication in computer networks. Assuming that the user's transmission is not through SSL, it will be transmitted in clear text on the network, which will give people with ulterior motives an opportunity to take advantage of it. Therefore, many websites now actually have SSL enabled by default, such as Facebook, Twitter, YouTube, Taobao, etc.

4. Environment [Close SeLinux]

* system environment

[root@MySQL ~]# cat /etc/redhat-release 
CentOS release 6.9 (Final)

[root@MySQL ~]# uname -r
 
2.6.32-696.3.2.el6.x86_64
 

[root@MySQL ~]# getenforce 
Disabled

* MySQL 环境 [ MySQL 5.7安装前面篇章已做详细介绍 ]

have_openssl 与 have_ssl 值都为DISABLED表示ssl未开启

[root@MySQL ~]# mysql -p'123'

mysql: [Warning] Using a password on the command line interface can be insecure.
 
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6
Server version: 5.7.18 MySQL Community Server (GPL)
 

Copyright (c) 2000, 2017, 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> select version();
+-----------+
| version() |
+-----------+
| 5.7.18    |
+-----------+

1 row in set (0.00 sec)
 

mysql> show variables like 'have%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
+---------------+----------+

2 rows in set (0.02 sec)
 

mysql> show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+

1 row in set (0.01 sec)
 

mysql> show variables like 'datadir';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+

| datadir       | /data/mysql_data/ |
+---------------+-------------------+

1 row in set (0.01 sec)
5. SSL配置
   *  利用自带工具生成SSL相关文件 
 

[root@MySQL ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql_data
Generating a 2048 bit RSA private key
..........................................................................+++
.....+++

writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.......................................................................................................................................................................+++
...+++

writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
.....................+++
...........................................+++

writing new private key to 'client-key.pem'
-----
 * 查看生成的SSL文件

[root@MySQL ~]# ls -l /data/mysql_data/*.pem

-rw------- 1 root root 1679 Jun 24 20:54 /data/mysql_data/ca-key.pem

-rw-r--r-- 1 root root 1074 Jun 24 20:54 /data/mysql_data/ca.pem

-rw-r--r-- 1 root root 1078 Jun 24 20:54 /data/mysql_data/client-cert.pem

-rw------- 1 root root 1675 Jun 24 20:54 /data/mysql_data/client-key.pem

-rw------- 1 root root 1675 Jun 24 20:54 /data/mysql_data/private_key.pem

-rw-r--r-- 1 root root  451 Jun 24 20:54 /data/mysql_data/public_key.pem

-rw-r--r-- 1 root root 1078 Jun 24 20:54 /data/mysql_data/server-cert.pem

-rw------- 1 root root 1675 Jun 24 20:54 /data/mysql_data/server-key.pem
* 重启 MySQL 服务

[root@MySQL ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!
  * 连接MySQL 查看SSL开启状态     have_openssl 与 have_ssl 值都为YES表示ssl开启成功
 

mysql> show variables like 'have%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
+---------------+-------+

2 rows in set (0.03 sec)
6. SSL + Password Connection Test
 * Create a user and specify an SSL connection [ After MySQL 5.7, it is recommended to use the create user method to create a user ]
##mysql> create user 'ssl_test'@'%' identified by '123' require SSL;
Query OK, 0 rows affected (0.00 sec)
## *  Password connection test [  The default is to use SSL connection, you need to specify not to use SSL connection  ] 
[root@MySQL ~]
# mysql -h 192.168.60.129 -ussl_test -p'123' --ssl=0
mysql: [Warning] Using a password on the 
command line interface can be insecure.##ERROR 1045 (28000): Access denied 
for user 'ssl_test'@'192.168.60.129' (using password: YES)
 * Connect via SSL + password test SSL: Cipher in use is DHE-RSA-AES256-SHA means connecting through SSL 
[root@MySQL ~] # mysql -h 192.168.60.129 -ussl_test -p'123' --ssl
mysql: [Warning] Using a password on the command line interface can be insecure.

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 12
Server version: 5.7.18 MySQL Community Server (GPL)
 

Copyright (c) 2000, 2017, 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> \s
--------------

mysql  Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using  EditLine wrapper
 

Connection id:     12
Current database:  
Current user:      ssl_test@192.168.60.129

SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:     stdout

Using outfile:     ''
Using delimiter:   ;
Server version:        5.7.18 MySQL Community Server (GPL)
Protocol version:  10

Connection:     192.168.60.129 via TCP/IP
Server characterset:   latin1
Db characterset: latin1
##Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
##Uptime: 7 min 34 sec
 
Threads: 1 Questions: 29 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.063
--------------
7. SSL + password + key connection 
 * Create a user and specify X509 [SSL+key] to connect [Recommended after MySQL 5.7 create user method ]
##mysql> create user 'X509_test'@'%' identified by '123' require X509; Query OK, 0 rows affected (0.00 sec)

* 
Connect via password test
##[root@MySQL ~]# mysql -h 192.168.60.129 -uX509_test -p'123' --ssl=0
 mysql: [Warning] Using a password on the 
command line interface can be insecure.##ERROR 1045 ( 28000): Access denied for
user 'X509_test'@'192.168.60.129' (using password: YES)* 通过 SSL +密码 连接测试

[root@MySQL ~]# mysql -h 192.168.60.129 -uX509_test -p'123' --ssl

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'X509_test'@'192.168.60.129' (using password: YES)
   * 通过 SSL + 密码＋密钥连接测试　   SSL: Cipher in use is DHE-RSA-AES256-SHA 表示通过SSL连接
 

[root@MySQL ~]# mysql -h 192.168.60.129 -uX509_test -p'123' --ssl-cert=/data/mysql_data/client-cert.pem --ssl-key=/data/mysql_data/client-key.pem 

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 21
Server version: 5.7.18 MySQL Community Server (GPL)
 

Copyright (c) 2000, 2017, 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> \s
--------------

mysql  Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using  EditLine wrapper
 

Connection id:     21
Current database:  
Current user:      X509_test@192.168.60.129

SSL:            Cipher in use is DHE-RSA-AES256-SHA
Current pager:     stdout

Using outfile:     ''
Using delimiter:   ;
Server version:        5.7.18 MySQL Community Server (GPL)
Protocol version:  10

Connection:     192.168.60.129 via TCP/IP
Server characterset:   latin1
Db     characterset:   latin1
Client characterset:   utf8
Conn.  characterset:  utf8
TCP port:      3306
Uptime:         18 min 27 sec
 
Threads: 1  Questions: 40  Slow queries: 0  Opens: 118  Flush tables: 1  Open tables: 111  Queries per second avg: 0.036
--------------

The above is the detailed content of Example tutorial of SSL connection. For more information, please follow other related articles on the PHP Chinese website!