尧图网站建设 
概述Mysql 8 默认启用SSL安全连接也可以强制必须启用。建议手动生成自签名证书和key并配置到服务端和客户端路径里也可以直接使用mysql默认生成的。前提条件https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration使用OpenSSL编译的MySQL服务器官网mysql安装包已经集成了支持ssl自签名证书自签名证书文件系统自动生成默认在数据存储目录下[rootcentos7-172-028-002-001 mysql]# ll /var/lib/mysql | grep pem-rw-------1mysql mysql16803月1822:03 ca-key.pem -rw-r--r--1mysql mysql11083月1822:03 ca.pem -rw-r--r--1mysql mysql11083月1822:03 client-cert.pem -rw-------1mysql mysql16763月1822:03 client-key.pem -rw-------1mysql mysql16803月1822:03 private_key.pem -rw-r--r--1mysql mysql4523月1822:03 public_key.pem -rw-r--r--1mysql mysql11083月1822:03 server-cert.pem -rw-------1mysql mysql16803月1822:03 server-key.pem因为是自签名证书启动日志中会报一条警告信息[Warning][MY-010068][Server]CA certificate ca.pem is self signed.Mysql 8手动配置SSL安全连接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#mandatory-encrypted-connections修改配置文件[mysqld]# 强制所有网络连接使用ssl。socket不受限制默认就是安全状态。如果客户端强制使用SSL连接允许登录但会提示建议取消SSL的信息require_secure_transportON修改运行参数进入数据库手动设置正在运行的数据库SET PERSISTrequire_secure_transportON;设置成功后会写入数据目录下的 mysqld-auto.cnf文件mysql重启后会自动读取这个文件实现持久化修改catmysqld-auto.cnf{Version:2,mysql_dynamic_variables:{require_secure_transport:{Value:ON,Metadata:{Host:,User:dba,Timestamp:1788524612742190}}}}[rootvm172-16-0-84 mysql_data]#Mysql 8服务器端配置SSL安全连接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration[mysqld]# 可以自行创建ca证书、服务端证书和KEYssl_caca.pemssl_certserver-cert.pemssl_keyserver-key.pem# 如果开启那么强制客户端必须使用SSL连接如果关闭可以指定账号使用SSL连接require_secure_transportON以下示例简化生成自签名证书# 创建目录mkdir-p/var/lib/mysql/sslcd/var/lib/mysql/ssl# 生成KEYopenssl genrsa-outmysql-key.pem2048# 生成自签名证书openssl req-x509-new-keymysql-key.pem-days3650-outmysql-cert.pem# 赋权chmod600/var/lib/mysql/ssl/*chown-Rmysql:mysql /var/lib/mysql/ssl查看证书有效期openssl x509-inmysql-cert.pem-noout-dates配置my.cnf# 配置自定义证书和key。使用简化自签名证书ssl_ca与ssl_cert一样ssl_ca/var/lib/mysql/ssl/mysql-cert.pemssl_cert/var/lib/mysql/ssl/mysql-cert.pemssl_key/var/lib/mysql/ssl/mysql-key.pem# 如果开启那么强制客户端必须使用SSL连接如果关闭可以指定账号使用SSL连接# 如果不配置上述自定义证书默认使用mysql系统生成的自签名证书在/var/lib/mysql/下require_secure_transportONMysql 8客户端配置SSL安全连接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-client-side-configuration--ssl-modeDISABLED|PREFERRED|REQUIRED|VERIFY_CA|VERIFY_IDENTITYDISABLED完全不使用SSL加密所有通信都以明文传输安全性最低性能最好适用于测试环境或内部安全网络PREFERRED默认模式尝试使用SSL失败则回退到非SSL对于Unix套接字连接不会加密平衡了安全性和兼容性REQUIRED强制要求SSL加密如果服务器不支持SSL则连接失败不验证证书的有效性适用于需要加密但不关心证书来源的环境VERIFY_CA要求SSL加密验证服务器证书是否由受信任的CA签发不验证主机名是否匹配比REQUIRED更安全VERIFY_IDENTITY最严格的模式要求SSL加密验证CA证书验证主机名是否与证书匹配防止中间人攻击安全性最高配置也最复杂Mysql 8客户端连接验证服务端开启了require_secure_transportON客户端禁用SSL连接被拒绝[rootcentos7-05145 ~]# mysql -h 172.28.2.1 -u root -p --ssl-modeDISABLEDEnter password: ERROR3159(HY000): Connections using insecure transport are prohibitedwhile--require_secure_transportON.服务端开启了require_secure_transportON客户端启用SSL连接查看连接状态[rootcentos7-05145 ~]# mysql -h 172.28.2.1 -u root -p --ssl-modeREQUIREDEnter password: Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis10Server version:8.4.4 MySQL Community Server - GPL Copyright(c)2000,2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Typehelp;or\hforhelp. Type\ctoclearthe current input statement. mysqlstatus -------------- mysql Ver8.4.2forLinux on x86_64(MySQL Community Server - GPL)Connection id:8Current database: Current user: root192.168.5.145 SSL: Cipherinuse is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile:Using delimiter:;Server version:8.4.4 MySQL Community Server - GPL Protocol version:10Connection:172.28.2.1 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port:3306Binary data as: Hexadecimal Uptime:2min44sec Threads:2Questions:6Slow queries:0Opens:512Flush tables:3Open tables:26Queries per second avg:0.036--------------Mysql 8 创建使用SSL安全连接的账号# require ssl 强制账号使用证书认证CREATEUSER用户名%IDENTIFIED BYpasswordrequire ssl;# require x509 强制账号使用证书认证CREATEUSER用户名%IDENTIFIED BYpasswordrequire x509;# 修改账号使用证书认证ALTERUSER用户名%require ssl;Mysql 8 服务端查看SSL状态# 查看ssl 配置mysqlSHOW VARIABLES LIKE%ssl%;------------------------------------------------------|Variable_name|Value|------------------------------------------------------|admin_ssl_ca|||admin_ssl_capath|||admin_ssl_cert|||admin_ssl_cipher|||admin_ssl_crl|||admin_ssl_crlpath|||admin_ssl_key|||mysqlx_ssl_ca|||mysqlx_ssl_capath|||mysqlx_ssl_cert|||mysqlx_ssl_cipher|||mysqlx_ssl_crl|||mysqlx_ssl_crlpath|||mysqlx_ssl_key|||performance_schema_show_processlist|OFF||ssl_ca|ca.pem||ssl_capath|||ssl_cert|server-cert.pem||ssl_cipher|||ssl_crl|||ssl_crlpath|||ssl_fips_mode|OFF||ssl_key|server-key.pem||ssl_session_cache_mode|ON||ssl_session_cache_timeout|300|------------------------------------------------------25rowsinset(0.01sec)# 查看 TLS 版本mysqlSHOW VARIABLES LIKEtls_version;--------------------------------|Variable_name|Value|--------------------------------|tls_version|TLSv1.2,TLSv1.3|--------------------------------1rowinset(0.01sec)# 查看强制开启状态mysqlSHOW VARIABLES LIKErequire_secure_transport;---------------------------------|Variable_name|Value|---------------------------------|require_secure_transport|ON|---------------------------------1rowinset(0.00sec) Mysql8 启用SSL安全连接
概述Mysql 8 默认启用SSL安全连接也可以强制必须启用。建议手动生成自签名证书和key并配置到服务端和客户端路径里也可以直接使用mysql默认生成的。前提条件https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration使用OpenSSL编译的MySQL服务器官网mysql安装包已经集成了支持ssl自签名证书自签名证书文件系统自动生成默认在数据存储目录下[rootcentos7-172-028-002-001 mysql]# ll /var/lib/mysql | grep pem-rw-------1mysql mysql16803月1822:03 ca-key.pem -rw-r--r--1mysql mysql11083月1822:03 ca.pem -rw-r--r--1mysql mysql11083月1822:03 client-cert.pem -rw-------1mysql mysql16763月1822:03 client-key.pem -rw-------1mysql mysql16803月1822:03 private_key.pem -rw-r--r--1mysql mysql4523月1822:03 public_key.pem -rw-r--r--1mysql mysql11083月1822:03 server-cert.pem -rw-------1mysql mysql16803月1822:03 server-key.pem因为是自签名证书启动日志中会报一条警告信息[Warning][MY-010068][Server]CA certificate ca.pem is self signed.Mysql 8手动配置SSL安全连接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#mandatory-encrypted-connections修改配置文件[mysqld]# 强制所有网络连接使用ssl。socket不受限制默认就是安全状态。如果客户端强制使用SSL连接允许登录但会提示建议取消SSL的信息require_secure_transportON修改运行参数进入数据库手动设置正在运行的数据库SET PERSISTrequire_secure_transportON;设置成功后会写入数据目录下的 mysqld-auto.cnf文件mysql重启后会自动读取这个文件实现持久化修改catmysqld-auto.cnf{Version:2,mysql_dynamic_variables:{require_secure_transport:{Value:ON,Metadata:{Host:,User:dba,Timestamp:1788524612742190}}}}[rootvm172-16-0-84 mysql_data]#Mysql 8服务器端配置SSL安全连接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration[mysqld]# 可以自行创建ca证书、服务端证书和KEYssl_caca.pemssl_certserver-cert.pemssl_keyserver-key.pem# 如果开启那么强制客户端必须使用SSL连接如果关闭可以指定账号使用SSL连接require_secure_transportON以下示例简化生成自签名证书# 创建目录mkdir-p/var/lib/mysql/sslcd/var/lib/mysql/ssl# 生成KEYopenssl genrsa-outmysql-key.pem2048# 生成自签名证书openssl req-x509-new-keymysql-key.pem-days3650-outmysql-cert.pem# 赋权chmod600/var/lib/mysql/ssl/*chown-Rmysql:mysql /var/lib/mysql/ssl查看证书有效期openssl x509-inmysql-cert.pem-noout-dates配置my.cnf# 配置自定义证书和key。使用简化自签名证书ssl_ca与ssl_cert一样ssl_ca/var/lib/mysql/ssl/mysql-cert.pemssl_cert/var/lib/mysql/ssl/mysql-cert.pemssl_key/var/lib/mysql/ssl/mysql-key.pem# 如果开启那么强制客户端必须使用SSL连接如果关闭可以指定账号使用SSL连接# 如果不配置上述自定义证书默认使用mysql系统生成的自签名证书在/var/lib/mysql/下require_secure_transportONMysql 8客户端配置SSL安全连接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-client-side-configuration--ssl-modeDISABLED|PREFERRED|REQUIRED|VERIFY_CA|VERIFY_IDENTITYDISABLED完全不使用SSL加密所有通信都以明文传输安全性最低性能最好适用于测试环境或内部安全网络PREFERRED默认模式尝试使用SSL失败则回退到非SSL对于Unix套接字连接不会加密平衡了安全性和兼容性REQUIRED强制要求SSL加密如果服务器不支持SSL则连接失败不验证证书的有效性适用于需要加密但不关心证书来源的环境VERIFY_CA要求SSL加密验证服务器证书是否由受信任的CA签发不验证主机名是否匹配比REQUIRED更安全VERIFY_IDENTITY最严格的模式要求SSL加密验证CA证书验证主机名是否与证书匹配防止中间人攻击安全性最高配置也最复杂Mysql 8客户端连接验证服务端开启了require_secure_transportON客户端禁用SSL连接被拒绝[rootcentos7-05145 ~]# mysql -h 172.28.2.1 -u root -p --ssl-modeDISABLEDEnter password: ERROR3159(HY000): Connections using insecure transport are prohibitedwhile--require_secure_transportON.服务端开启了require_secure_transportON客户端启用SSL连接查看连接状态[rootcentos7-05145 ~]# mysql -h 172.28.2.1 -u root -p --ssl-modeREQUIREDEnter password: Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis10Server version:8.4.4 MySQL Community Server - GPL Copyright(c)2000,2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Typehelp;or\hforhelp. Type\ctoclearthe current input statement. mysqlstatus -------------- mysql Ver8.4.2forLinux on x86_64(MySQL Community Server - GPL)Connection id:8Current database: Current user: root192.168.5.145 SSL: Cipherinuse is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile:Using delimiter:;Server version:8.4.4 MySQL Community Server - GPL Protocol version:10Connection:172.28.2.1 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 TCP port:3306Binary data as: Hexadecimal Uptime:2min44sec Threads:2Questions:6Slow queries:0Opens:512Flush tables:3Open tables:26Queries per second avg:0.036--------------Mysql 8 创建使用SSL安全连接的账号# require ssl 强制账号使用证书认证CREATEUSER用户名%IDENTIFIED BYpasswordrequire ssl;# require x509 强制账号使用证书认证CREATEUSER用户名%IDENTIFIED BYpasswordrequire x509;# 修改账号使用证书认证ALTERUSER用户名%require ssl;Mysql 8 服务端查看SSL状态# 查看ssl 配置mysqlSHOW VARIABLES LIKE%ssl%;------------------------------------------------------|Variable_name|Value|------------------------------------------------------|admin_ssl_ca|||admin_ssl_capath|||admin_ssl_cert|||admin_ssl_cipher|||admin_ssl_crl|||admin_ssl_crlpath|||admin_ssl_key|||mysqlx_ssl_ca|||mysqlx_ssl_capath|||mysqlx_ssl_cert|||mysqlx_ssl_cipher|||mysqlx_ssl_crl|||mysqlx_ssl_crlpath|||mysqlx_ssl_key|||performance_schema_show_processlist|OFF||ssl_ca|ca.pem||ssl_capath|||ssl_cert|server-cert.pem||ssl_cipher|||ssl_crl|||ssl_crlpath|||ssl_fips_mode|OFF||ssl_key|server-key.pem||ssl_session_cache_mode|ON||ssl_session_cache_timeout|300|------------------------------------------------------25rowsinset(0.01sec)# 查看 TLS 版本mysqlSHOW VARIABLES LIKEtls_version;--------------------------------|Variable_name|Value|--------------------------------|tls_version|TLSv1.2,TLSv1.3|--------------------------------1rowinset(0.01sec)# 查看强制开启状态mysqlSHOW VARIABLES LIKErequire_secure_transport;---------------------------------|Variable_name|Value|---------------------------------|require_secure_transport|ON|---------------------------------1rowinset(0.00sec)