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