MySQL数据库SSL连接测试

环境信息：Centos7 + MySQL 5.7.21

在该环境上进行SSL连接测试。

MySQL Server 配置：

vi /etc/my.cnf

[mysql]
default-character-set = utf8mb4
 
[client]
default-character-set = utf8mb4
 
# ssl_mode = [ DISABLED | PREFERRED(default if not set) | REQUIRED | VERIFY_CA | VERIFY_IDENTITY ]
# ssl_mode 为客户端可选参数，如果客户端连接时指定了则生效的是客户端连接时指定的参数，如果客户端连接未指定则使用服务端的[client]中的配置参数，如果未配置默认为PREFRED。
#   DISABLED: 非SSL连接。
#   PREFERRED: SSL/no-ssl。如果不配置、默认就是该行为。先尝试SSL连接、如果失败则尝试非SSL连接。
#   REQUIRED: SSL连接。仅尝试SSL连接，SSL连接失败则失败、不会尝试非SSL连接。
#   VERIFY_CA: SSL连接，双向认证。校验ca证书、但不校验证书中的server host name。
#   VERIFY_IDENTITY: SSL连接，双向认证。校验ca证书、同时校验证书中的server host name。
#ssl_mode = PREFERRED
ssl_mode = VERIFY_CA
 
[mysqld]
 
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
 
validate_password=off
 
# for ssl enable
# 服务器端配置强制SSL连接、禁止非SSL连接
require_secure_transport = ON
# 下面3个参数如果不配置或者值配置成ca.pem, server-cert.pem, server-key.pem，不加路径，则MySQL默认行为是读取数据目录下的三个文件
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem
 
#for mysqlbinlog enable
server-id=1
log-bin=/var/lib/mysql/mysql-bin
binlog_format=row
expire_logs_days=15
max_binlog_size=500m
 
#utf8mb4
character-set-client-handshake=false
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
 
# for blob input, default value is 4194304 (4M) if not set
#max_allowed_packet = 100M
 
# for other performance settings
max_connections = 2000
max_connect_errors = 500
back_log = 1000
thread_cache_size = 64
table_open_cache_instances = 500
###show global status like '%open%table%'; Open_tables / Opened_tables 85%~95%
table_open_cache = 10000
table_definition_cache = 2000
innodb_thread_concurrency = 0
max_tmp_tables = 100
 
## common sql commands
## -- show engine innodb status \G;
## -- show global variables like '%thread%';
## -- show global status like '%thread%';
## -- show global status like '%open%table%';
## -- show variables like '%table%';
 
# Disablinr symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
 
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

编写测试脚本：

vi cmd.sh

#!/bin/sh
 
# ssl-test-qftools
 
SQL_SSL="
# check if server start with --ssl:\n
# -- show variables like '%have_ssl%';\n
\n
# check if server force accpet ssl session request ( deny no-ssl requests ):\n
# -- show variables like 'require_secure_transport';\n
\n
# client connected and check current session ssl status:\n
# -- show session status like 'Ssl_cipher';\n
\n
# check ssl configs:\n
# -- show variables like '%ssl%';\n
"
# java keystore.jks generate cmd: keytool -importcert -trustcacerts -file ca.pem -keystore keystore216.jks -storepass 1q2w3e
 
HOST=192.168.1.216
PORT=3306
USER=root
DIR=./ssl_1.216
 
# for MySQL old Versin
#$SSLOPT=--ssl
#$SSLOPT="--ssl --ssl-verify-server-cert"
 
# for MySQL new Version
SSLOPT_DISABLED="--ssl-mode=DISABLED"
SSLOPT_PREFERRED="--ssl-mode=PREFERRED"
SSLOPT_REQUIRED="--ssl-mode=REQUIRED"
# ssl && also perform verification against the server CA certificate but no against the server host name in its certificate.
SSLOPT_CA="--ssl-mode=VERIFY_CA"
# ssl && also perform verification against the server CA certificate and (with VERIFY_IDENTITY) against the server host name in its certificate.
SSLOPT_IDENTITY="--ssl-mode=VERIFY_IDENTITY"
 
CA=$DIR/ca.pem
CERT=$DIR/client-cert.pem
KEY=$DIR/client-key.pem
 
 
# 客户端连接时不指定相关参数、由服务器配置决定最终行为
CMD0="mysql -h$HOST -P$PORT -u$USER -p "
# 客户端连接指定配置
# no-ssl
CMD1="mysql -h$HOST -P$PORT -u$USER -p $SSLOPT_DISABLED"
# ssl/no-ssl 先尝试SSL连接、如果失败则尝试非SSL连接
CMD2="mysql -h$HOST -P$PORT -u$USER -p $SSLOPT_PREFERRED"
# ssl单向，SSL连接失败则失败、不会尝试非SSL连接
CMD3="mysql -h$HOST -P$PORT -u$USER -p $SSLOPT_REQUIRED"
# ssl单向、虽然多余传ca证书、客户端证书和秘钥(实际上无需传)，实际效果还是单向。MySQL Server my.cnf [client] 中如果配置为 ssl_mode = VERIFY_CA ，连接时客户端如果指定ssl_mode参数则会覆盖，以客户端配置优先
CMD4="mysql -h$HOST -P$PORT -u$USER -p $SSLOPT_REQUIRED --ssl-ca=$CA  --ssl-cert=$CERT --ssl-key=$KEY"
# ssl双向认证,不验证hostname
CMD5="mysql -h$HOST -P$PORT -u$USER -p $SSLOPT_CA --ssl-ca=$CA  --ssl-cert=$CERT --ssl-key=$KEY"
# ssl双向认证 && 验证hostname
CMD6="mysql -h$HOST -P$PORT -u$USER -p $SSLOPT_IDENTITY --ssl-ca=$CA  --ssl-cert=$CERT --ssl-key=$KEY"
 
 
if [ ! $# -eq 1 ] || [ "$1" == "-h" ];then
  echo ""
  echo "Please user: $0 {type}"
  echo "  type: 0 | 1 | 2 | 3 | 4 | 5 | 6"
  echo "  type 0 for run: $CMD0"
  echo "  type 1 for run: $CMD1"
  echo "  type 2 for run: $CMD2"
  echo "  type 3 for run: $CMD3"
  echo "  type 4 for run: $CMD4"
  echo "  type 5 for run: $CMD5"
  echo "  type 6 for run: $CMD6"
  echo -e "\n---------------------\n"
  echo "About sql for show ssl infos after mysql connected: "
  echo -e $SQL_SSL
  exit
fi
 
 
inputType=$1
if [ -z inputType ];then
  inputType=1
fi
 
echo "inputType = $inputType"
 
if [ $inputType -eq 0 ];then
  echo $CMD0
  $CMD0
elif [ $inputType -eq 1 ];then
  echo $CMD1
  $CMD1
elif [ $inputType -eq 2 ];then
  echo $CMD2
  $CMD2
elif [ $inputType -eq 3 ];then
  echo $CMD3
  $CMD3
elif [ $inputType -eq 4 ];then
  echo $CMD4
  $CMD4
elif [ $inputType -eq 5 ];then
  echo $CMD5
  $CMD5
elif [ $inputType -eq 6 ];then
  echo $CMD6
  $CMD6
else
  echo "ERROR: inputType unsupport!"
  exit
fi

从MySQL Server服务器拷贝证书和Key到客户端机器上，然后客户端机器上执行相关测试：
[root@localhost ssl_test]# ls -l
total 4
-rwxr--r-- 1 root root 3165 Apr 25 01:40 cmd.sh
drwxr-xr-x 2 root root   62 Apr 24 21:45 ssl_1.216
[root@localhost ssl_test]# ls -l ssl_1.216/
total 12
-rw-r--r-- 1 root root 1107 Apr 24 21:45 ca.pem
-rw-r--r-- 1 root root 1107 Apr 24 21:45 client-cert.pem
-rw------- 1 root root 1679 Apr 24 21:45 client-key.pem

SSL连接测试失败的情况：
./cmd.sh 0
./cmd.sh 1
./cmd.sh 6

SSL连接失败的执行详细信息：
[root@localhost ssl_test]# ./cmd.sh 0
inputType = 0
mysql -h192.168.1.216 -P3306 -uroot -p
Enter password: 
ERROR 2026 (HY000): SSL connection error: CA certificate is required if ssl-mode is VERIFY_CA or VERIFY_IDENTITY
[root@localhost ssl_test]# 

[root@localhost ssl_test]# ./cmd.sh 1
inputType = 1
mysql -h192.168.1.216 -P3306 -uroot -p --ssl-mode=DISABLED
Enter password: 
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
[root@localhost ssl_test]# 

[root@localhost ssl_test]# ./cmd.sh 6
inputType = 6
mysql -h192.168.1.216 -P3306 -uroot -p --ssl-mode=VERIFY_IDENTITY --ssl-ca=./ssl_1.216/ca.pem --ssl-cert=./ssl_1.216/client-cert.pem --ssl-key=./ssl_1.216/client-key.pem
Enter password: 
ERROR 2026 (HY000): SSL connection error: SSL certificate validation failure
[root@localhost ssl_test]# 

SSL连接测试成功的情况：
./cmd.sh 2
./cmd.sh 3
./cmd.sh 4
./cmd.sh 5