一、用MySQL镜像安装MySQL
# docker pull mysql:8.0
# mkdir -p /opt/mysqldata /opt/mysqlconfig /opt/sql
# vim /opt/mysqlconfig/mysqld.cnf
#######################################################
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links = 0
max_connections = 2000
user = mysql
skip_name_resolve
skip-host-cache
skip-log-bin
character-set-client-handshake = FALSE
lower_case_table_names = 1
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = "SET NAMES 'utf8'"
default_authentication_plugin = mysql_native_password
general_log = on
general_log_file = /var/lib/mysql/general.log
log_timestamps = SYSTEM
[mysql]
default-character-set = utf8
[client]
default-character-set = utf8
#######################################################
二、初始化MySQL
# vim mysql8.x_init.sh
###################################################
#!/bin/bash
mysql_ver="8.0"
mysql_datadir="/opt/mysqldata"
mysql_root_pwd="MySQL@123"
registry_addr=""
docker run -itd \
--name mysql \
-p 3306:3306 \
-e UMASK=0600 \
-e UMASK_DIR=0700 \
-e MYSQL_HISTFILE=/dev/null \
-v ${mysql_datadir}:/var/lib/mysql \
-v /opt/mysqlconfig/mysqld.cnf:/etc/mysql/conf.d//mysqld.cnf \
-e MYSQL_ROOT_PASSWORD="${mysql_root_pwd}" \
mysql:${mysql_ver}
sleep 30
if ss -tan | grep -w "3306" > /dev/null 2>&1; then
docker stop mysql
docker rm mysql
echo "Mysql init successfully!"
else
echo "Mysql init failed!"
fi
###################################################
# sh mysql8.x_init.sh
将MySQL服务注册成系统服务
# cat /etc/systemd/system/mysqld.service
####################################################
[Unit]
Description=MySQL Server
After=network-online.target docker.service
Requires=docker.service
[Service]
Type=simple
ExecStartPre=-/usr/bin/docker rm -f mysql
ExecStart=/usr/bin/docker run \
--name mysql \
-p 3306:3306 \
-e UMASK=0600 \
-e UMASK_DIR=0700 \
-e MYSQL_HISTFILE=/dev/null \
-v /opt/mysqldata:/var/lib/mysql \
-v /opt/mysqlconfig/mysqld.cnf:/etc/mysql/conf.d//mysqld.cnf \
-v /opt/sql:/opt/sql \
-v /etc/localtime:/etc/localtime \
--security-opt seccomp=unconfined \
mysql:8.0
ExecStop=/usr/bin/docker stop mysql
LimitNOFILE=65535
Restart=on-failure
StartLimitBurst=3
StartLimitInterval=60s
[Install]
WantedBy=multi-user.target
####################################################
# systemctl daemon-reload
# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld
二、MySQL 不开启SSL
# cat /opt/mysqlconfig/mysqld.cnf
#######################################################
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links = 0
max_connections = 2000
user = mysql
skip_name_resolve
skip-host-cache
skip-log-bin
character-set-client-handshake = FALSE
lower_case_table_names = 1
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = "SET NAMES 'utf8'"
default_authentication_plugin = mysql_native_password
general_log = on
general_log_file = /var/lib/mysql/general.log
log_timestamps = SYSTEM
[mysql]
default-character-set = utf8
[client]
default-character-set = utf8
########################################################
后台通过socket登录
# docker exec -it mysql mysql -u root -p"MySQL@123"
可以看出，后台通过socket登录，是没有走加密通道的。
后台通过127.0.0.1登录
# docker exec -it mysql mysql -h 127.0.0.1 -u root -p"MySQL@123"
可以看出，后台通过127.0.0.1登录，是加密的。
客户端远程登录 192.18.1.104 -----> 192.168.1.100:3306
# tail -f /opt/mysqldata/general.log
可以看出，在不开启MySQL SSL 的情况下，客户端连接服务端，是走TCP/IP，没有加密通道。
MySQL不开启SSL 的jdbc
MySQL驱动
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&useSSL=false
username=root
password=MySQL@123
MariaDB驱动
driverClassName=org.mariadb.jdbc.Driver
url=jdbc:mariadb://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&useSSL=false
username=root
password=MySQL@123
三、MySQL 开启SSL
# vim /opt/mysqlconfig/mysqld.cnf
#############################################################
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
symbolic-links = 0
max_connections = 2000
user = mysql
skip_name_resolve
skip-host-cache
skip-log-bin
character-set-client-handshake = FALSE
lower_case_table_names = 1
character-set-server = utf8
collation-server = utf8_general_ci
init_connect = "SET NAMES 'utf8'"
default_authentication_plugin = mysql_native_password
general_log = on
general_log_file = /var/lib/mysql/general.log
log_timestamps = SYSTEM
require_secure_transport = ON
ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key = /var/lib/mysql/server-key.pem
[mysql]
default-character-set = utf8
[client]
default-character-set = utf8
#############################################################
# systemctl restart mysqld
# ll /opt/mysqldata/*.pem
后台通过socket登录
# docker exec -it mysql mysql -u root -p"MySQL@123"
可以看出，后台通过socket登录，是没有走加密通道的。
后台通过127.0.0.1登录
# docker exec -it mysql mysql -h 127.0.0.1 -u root -p"MySQL@123"
可以看出，后台通过127.0.0.1登录，是加密通的，但是没有启动SSL。
客户端远程登录 192.18.1.104 -----> 192.168.1.100:3306
# tail -f /opt/mysqldata/general.log
可以看出，在开启MySQL SSL 的情况下，客户端连接服务端，走加密通道。
四、MySQL 开启SSL，客户端jdbc的几种设置
关于开启 MySQL SSL，客户端dbc的设置分以下几种情况：
1. 服务端开启SSL，配置证书，客户端连服务端，直接信任证书，不用配置证书
2. 服务端开启SSL，配置证书，客户端连服务端，配置单向验证客户端或者服务端证书
3. 服务端开启SSL，配置证书，客户端连服务端，配置双向验证服务端证书和客户端证书
1. 服务端开启SSL，配置证书，客户端连服务端，直接信任证书，不用配置证书
MySQL驱动
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&useSSL=true&verifyServerCertificate=false&requireSSL=true
username=root
password=MySQL@123
MariaDB驱动
driverClassName=org.mariadb.jdbc.Driver
url=jdbc:mariadb://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&useSSL=true&trustServerCertificate=true&requireSSL=true
username=root
password=MySQL@123
关于证书信任设置
MySQL: verifyServerCertificate=false
MariaDB: trustServerCertificate=true
当启用SSL加密并设置连接字符串属性trustServerCertificate=false 时需要做些什么，与设置trustServerCertificate=true 有什么区别？
如果当使用安全套接字层 (SSL) 对通信层加密时应自动信任服务器安全套接字层证书，则为“true” ， 否则为 false。
此处，我们服务端，不设置证书验证，为自动信任服务器安全套接字层证书。
2. 服务端开启SSL，配置证书，客户端连服务端，配置单向验证客户端或者服务端证书
在 MySQL Server服务器上
# mkdir /root/mysqlSSL
# cp /opt/mysqldata/*.pem /root/mysqlSSL/
# keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore.jks -storepass Truststore@123 -noprompt
# keytool -v -list -keystore truststore.jks -storepass "Truststore@123"
将 truststore.jks拷贝到客户端 ，假设文件路径为 /opt/cert/truststore.jks
MySQL驱动
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&useSSL=true&verifyServerCertificate=true&requireSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/truststore.jks&clientCertificateKeyStorePassword="Truststore@123"
username=root
password=MySQL@123
MariaDB驱动
driverClassName=org.mariadb.jdbc.Driver
url=jdbc:mariadb://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&useSSL=true&trustServerCertificate=false&requireSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/truststore.jks&clientCertificateKeyStorePassword="Truststore@123"
username=root
password=MySQL@123
当启用SSL加密并设置连接字符串属性trustServerCertificate=false 时需要做些什么，与设置trustServerCertificate=true 有什么区别？
如果当使用安全套接字层 (SSL) 对通信层加密时应自动信任服务器安全套接字层证书，则为“true” ， 否则为 false。
此处，客户端务端设置了证书验证，通过连接字符串属性 clientCertificateKeyStoreUrl=file:/opt/cert/ truststore.jks 和 clientCertificateKeyStorePassword= "Truststore@123" 验证服务器安全套接字层证书。
3. 服务端开启SSL，配置证书，客户端连服务端，配置双向验证服务端证书和客户端证书
# keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore.jks -storepass Truststore@123 -noprompt
# openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -name "mysqlclient" -passout pass:Keystore@123 -out client-keystore.p12
# keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass "Keystore@123" -destkeystore keystore.jks -deststoretype JKS -deststorepass "Truststore@123"
# keytool -v -list -keystore truststore.jks -storepass "Truststore@123"
# keytool -v -list -keystore keystore.jks -storepass "Truststore@123"
# cat ca.pem
# keytool -export -alias mysqlcacert -keystore truststore.jks --storepass "Truststore@123" -rfc
可以看出 ，实际上事将CA证书 ca.pem 导入到了 truststore.jks中。
# cat client-cert.pem
# keytool -export -alias mysqlclient -keystore keystore.jks --storepass "Truststore@123" -rfc
可以看出，实际是将 客户端证书 client-cert.pem 导入到了 keystore.jks中，当然也将 客户端私钥 client-key.pem 导入到了keystore.jks中，只是我们没法直接从 keystore.jks 获取私钥内容。
注意： 此处 file:///path/to/file 等价于 file:/path/to/file
将 truststore.jks 和 keystore.jks 拷贝到客户端 ，假设文件路径为 /opt/cert/truststore.jks /opt/cert/keystore.jks
MySQL驱动
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&verifyServerCertificate=true&useSSL=true&requireSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/keystore.jks&clientCertificateKeyStorePassword="Keystore@123"&trustCertificateKeyStoreUrl=file:/opt/cert/truststore.jks&trustCertificateKeyStorePassword="Truststore@123"
username=root
password=MySQL@123
MariaDB驱动
driverClassName=org.mariadb.jdbc.Driver
url=jdbc:mariadb://xx.xx.xx.xx:3306/test?allowMultiQueries=true&useUnicode&characterEncoding=UTF-8&autoReconnect=true&trustServerCertificate=false&useSSL=true&requireSSL=true&clientCertificateKeyStoreUrl=file:/opt/cert/keystore.jks&clientCertificateKeyStorePassword="Keystore@123"&trustCertificateKeyStoreUrl=file:/opt/cert/truststore.jks&trustCertificateKeyStorePassword="Truststore@123"
username=root
password=MySQL@123
综上，我们可以看到：
客户端不做认证 ，直接信任证书，客户端无需配置证书
单向认证（验证客户端或者服务端），客户端只需要配置 truststore.jks
双向认证（验证客户端和者服务端）， 客户端需要配置truststore.jks 和keystore.jks
