概述Mysql 8 默認啟用SSL安全連接也可以強制必須啟用。建議手動生成自簽名證書和key并配置到服務(wù)端和客戶端路徑里也可以直接使用mysql默認生成的。前提條件https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration使用OpenSSL編譯的MySQL服務(wù)器官網(wǎng)mysql安裝包已經(jīng)集成了支持ssl自簽名證書自簽名證書文件系統(tǒng)自動生成默認在數(shù)據(jù)存儲目錄下[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]# 強制所有網(wǎng)絡(luò)連接使用ssl。socket不受限制默認就是安全狀態(tài)。如果客戶端強制使用SSL連接允許登錄但會提示建議取消SSL的信息require_secure_transportON修改運行參數(shù)進入數(shù)據(jù)庫手動設(shè)置正在運行的數(shù)據(jù)庫SET PERSISTrequire_secure_transportON;設(shè)置成功后會寫入數(shù)據(jù)目錄下的 mysqld-auto.cnf文件mysql重啟后會自動讀取這個文件實現(xiàn)持久化修改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服務(wù)器端配置SSL安全連接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration[mysqld]# 可以自行創(chuàng)建ca證書、服務(wù)端證書和KEYssl_caca.pemssl_certserver-cert.pemssl_keyserver-key.pem# 如果開啟那么強制客戶端必須使用SSL連接如果關(guān)閉可以指定賬號使用SSL連接require_secure_transportON以下示例簡化生成自簽名證書# 創(chuàng)建目錄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# 賦權(quán)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連接如果關(guān)閉可以指定賬號使用SSL連接# 如果不配置上述自定義證書默認使用mysql系統(tǒng)生成的自簽名證書在/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加密所有通信都以明文傳輸安全性最低性能最好適用于測試環(huán)境或內(nèi)部安全網(wǎng)絡(luò)PREFERRED默認模式嘗試使用SSL失敗則回退到非SSL對于Unix套接字連接不會加密平衡了安全性和兼容性REQUIRED強制要求SSL加密如果服務(wù)器不支持SSL則連接失敗不驗證證書的有效性適用于需要加密但不關(guān)心證書來源的環(huán)境VERIFY_CA要求SSL加密驗證服務(wù)器證書是否由受信任的CA簽發(fā)不驗證主機名是否匹配比REQUIRED更安全VERIFY_IDENTITY最嚴格的模式要求SSL加密驗證CA證書驗證主機名是否與證書匹配防止中間人攻擊安全性最高配置也最復(fù)雜Mysql 8客戶端連接驗證服務(wù)端開啟了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.服務(wù)端開啟了require_secure_transportON客戶端啟用SSL連接查看連接狀態(tài)[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 創(chuàng)建使用SSL安全連接的賬號# require ssl 強制賬號使用證書認證CREATEUSER用戶名%IDENTIFIED BYpasswordrequire ssl;# require x509 強制賬號使用證書認證CREATEUSER用戶名%IDENTIFIED BYpasswordrequire x509;# 修改賬號使用證書認證ALTERUSER用戶名%require ssl;Mysql 8 服務(wù)端查看SSL狀態(tài)# 查看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)# 查看強制開啟狀態(tài)mysqlSHOW VARIABLES LIKErequire_secure_transport;---------------------------------|Variable_name|Value|---------------------------------|require_secure_transport|ON|---------------------------------1rowinset(0.00sec) Mysql8 啟用SSL安全連接
概述Mysql 8 默認啟用SSL安全連接也可以強制必須啟用。建議手動生成自簽名證書和key并配置到服務(wù)端和客戶端路徑里也可以直接使用mysql默認生成的。前提條件https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration使用OpenSSL編譯的MySQL服務(wù)器官網(wǎng)mysql安裝包已經(jīng)集成了支持ssl自簽名證書自簽名證書文件系統(tǒng)自動生成默認在數(shù)據(jù)存儲目錄下[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]# 強制所有網(wǎng)絡(luò)連接使用ssl。socket不受限制默認就是安全狀態(tài)。如果客戶端強制使用SSL連接允許登錄但會提示建議取消SSL的信息require_secure_transportON修改運行參數(shù)進入數(shù)據(jù)庫手動設(shè)置正在運行的數(shù)據(jù)庫SET PERSISTrequire_secure_transportON;設(shè)置成功后會寫入數(shù)據(jù)目錄下的 mysqld-auto.cnf文件mysql重啟后會自動讀取這個文件實現(xiàn)持久化修改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服務(wù)器端配置SSL安全連接https://dev.mysql.com/doc/refman/8.4/en/using-encrypted-connections.html#using-encrypted-connections-server-side-startup-configuration[mysqld]# 可以自行創(chuàng)建ca證書、服務(wù)端證書和KEYssl_caca.pemssl_certserver-cert.pemssl_keyserver-key.pem# 如果開啟那么強制客戶端必須使用SSL連接如果關(guān)閉可以指定賬號使用SSL連接require_secure_transportON以下示例簡化生成自簽名證書# 創(chuàng)建目錄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# 賦權(quán)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連接如果關(guān)閉可以指定賬號使用SSL連接# 如果不配置上述自定義證書默認使用mysql系統(tǒng)生成的自簽名證書在/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加密所有通信都以明文傳輸安全性最低性能最好適用于測試環(huán)境或內(nèi)部安全網(wǎng)絡(luò)PREFERRED默認模式嘗試使用SSL失敗則回退到非SSL對于Unix套接字連接不會加密平衡了安全性和兼容性REQUIRED強制要求SSL加密如果服務(wù)器不支持SSL則連接失敗不驗證證書的有效性適用于需要加密但不關(guān)心證書來源的環(huán)境VERIFY_CA要求SSL加密驗證服務(wù)器證書是否由受信任的CA簽發(fā)不驗證主機名是否匹配比REQUIRED更安全VERIFY_IDENTITY最嚴格的模式要求SSL加密驗證CA證書驗證主機名是否與證書匹配防止中間人攻擊安全性最高配置也最復(fù)雜Mysql 8客戶端連接驗證服務(wù)端開啟了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.服務(wù)端開啟了require_secure_transportON客戶端啟用SSL連接查看連接狀態(tài)[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 創(chuàng)建使用SSL安全連接的賬號# require ssl 強制賬號使用證書認證CREATEUSER用戶名%IDENTIFIED BYpasswordrequire ssl;# require x509 強制賬號使用證書認證CREATEUSER用戶名%IDENTIFIED BYpasswordrequire x509;# 修改賬號使用證書認證ALTERUSER用戶名%require ssl;Mysql 8 服務(wù)端查看SSL狀態(tài)# 查看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)# 查看強制開啟狀態(tài)mysqlSHOW VARIABLES LIKErequire_secure_transport;---------------------------------|Variable_name|Value|---------------------------------|require_secure_transport|ON|---------------------------------1rowinset(0.00sec)