MySQL with SSL Encryption

2016-12-13 14:08:36
Check MySQL has SSL Support
1
2
3
4
5
6
7
8
9
[root@localhost ~]# /usr/local/mysql/bin/mysql --ssl --help|grep ssl
                ssl           TRUE
				
mysql> show variables like '%ssl%'; 
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| have_openssl  | YES                    |
| have_ssl      | YES                    |
Generate SSL Certificates
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mkdir /etc/mysql-ssl
cd /etc/mysql-ssl

#CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 9000 -key ca-key.pem > ca-cert.pem

#Server
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 9000  -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
 
#Client
openssl req -newkey rsa:2048 -days 9000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 9000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

备注：这里需要注意的是CA的Common Name和server、client的Common Name不要一致，其他信息一致即可，默认回车就行。

Configure the MySQL Server to use SSL Encryption
1
2
3
4
[mysqld]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem
Configure the MySQL Clients to use SSL Encryption
1
2
3
4
[client]
ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/client-cert.pem
ssl-key=/etc/mysql-ssl/client-key.pem
Create MySQL User that is Required to use SSL
1
2
grant select,insert,update,delete  on db.* to 'ssluser'@'host' identified by 'pass' require ssl;
flush privileges;
Login to MySQL using SSL Encryption
1
2
3
4
5
#Using command line parameters without /etc/my.cnf [client] section
mysql--ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem –ussluser –p

#Using the [client] section in /etc/my.cnf
mysql –ussluser –p
MySQL Replication
1
2
3
4
5
6
7
8
stop slave;
change master to
master_ssl=1,
master_ssl_ca='/etc/mysql-ssl/ca-cert.pem',
master_ssl_cert='/etc/mysql/ssl/client-cert.pem',
master_ssl_key='/etc/mysql/ssl/client-key.pem';
start slave;
show slave status;
Check SSL Encryption is Working
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
mysql> show status like '%ssl%'\G;
*************************** 1. row ***************************
Variable_name: Com_show_processlist
        Value: 0
*************************** 2. row ***************************
Variable_name: Ssl_accept_renegotiates
        Value: 0
*************************** 3. row ***************************
Variable_name: Ssl_accepts
        Value: 93
*************************** 4. row ***************************
Variable_name: Ssl_callback_cache_hits
        Value: 0
*************************** 5. row ***************************
Variable_name: Ssl_cipher
        Value: AES256-SHA

mysql> \s
--------------
/usr/local/mysql/bin/mysql  Ver 14.14 Distrib 5.5.18, for Linux (x86_64) using  EditLine wrapper

Connection id:          570
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.18-log Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 14 hours 50 min 34 sec

Threads: 3  Questions: 4002  Slow queries: 0  Opens: 1013  Flush tables: 1  Open tables: 256  Queries per second avg: 0.074

php环境测试代码

mysqli

1
2
3
4
5
6
7
8
9
10
11
12
<?php
$conn=mysqli_init();
if(!conn){
        echo "mysqli_init error";
        exit(0);
}
mysqli_ssl_set($conn, '/etc/mysql-ssl/client-key.pem', '/etc/mysql-ssl/client-cert.pem', NULL, NULL,'AES256-SHA');
if (!mysqli_real_connect($conn, 'ip', 'ssluser', 'pass')) { die(); }
$res = mysqli_query($conn, 'SHOW STATUS like "Ssl_cipher"');
print_r(mysqli_fetch_row($res));
mysqli_close($conn);
?>

pdo_mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
$pdo = new PDO('mysql:host=ip;dbname=db', 'ssluser', 'pass', array(
            PDO::MYSQL_ATTR_SSL_KEY=>'/etc/mysql-ssl/client-key.pem',
            PDO::MYSQL_ATTR_SSL_CERT=>'/etc/mysql-ssl/client-cert.pem',
            PDO::MYSQL_ATTR_SSL_CA=>'/etc/mysql-ssl/ssl.ca',
			PDO::MYSQL_ATTR_SSL_CIPHER=>'AES128-SHA'
            )
                );
$statement = $pdo->query('SHOW STATUS like "Ssl_cipher"');
$row = $statement->fetch(PDO::FETCH_ASSOC);
 print_r($row);
//echo htmlentities($row['_message']);
?>

ref
Newest RHEL/CentOS openssl update breaks mysql DHE ciphers
How to fix Logjam vulnerability with MySQL
mysqli_ssl_set — Used for establishing secure connections using SSL

您的鼓励是我写作最大的动力

俗话说，投资效率是最好的投资。 如果您感觉我的文章质量不错，读后收获很大，预计能为您提高 10% 的工作效率，不妨小额捐助我一下，让我有动力继续写出更多好文章。