MySQL 5.7配置SSL連線
MySQL 5.7配置SSL連線
如果想服務能夠部署自動支援安全連線，使用mysql_ssl_rsa_setup工具來建立預設SSL與RSA檔案
[root@cs2 bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/mysql Generating a 2048 bit RSA private key ......................................................................+++ ..............................................................+++ writing new private key to 'ca-key.pem' ----- Generating a 2048 bit RSA private key .............+++ ..............+++ writing new private key to 'server-key.pem' ----- Generating a 2048 bit RSA private key .....................................+++ ................................................+++ writing new private key to 'client-key.pem' -----
啟動mysql
[root@cs2 ~]# service mysqld start Starting MySQL.. SUCCESS!
測試遠端登入
-bash-4.2$ mysql -h 10.11.13.19 -P 3306 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT DISTINCT CONCAT('User: [', user, '''@''', host, '];') AS USER_HOST FROM user;
+------------------------------------+
| USER_HOST |
+------------------------------------+
| User: [root'@'%]; |
| User: [mysql.session'@'localhost]; |
| User: [mysql.sys'@'localhost]; |
+------------------------------------+
3 rows in set (0.05 sec)
透過ssl登入mysql伺服器
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u root -pabcd123 --ssl-cert=client-cert.pem --ssl-key=client-key.pem mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper Connection id: 7 Current database: Current user: root@10.11.13.19 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.26-log Source distribution Protocol version: 10 Connection: 10.11.13.19 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: gb2312 Conn. characterset: gb2312 TCP port: 3306 Uptime: 14 min 11 sec Threads: 2 Questions: 13 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.015 -------------- mysql> show variables like 'have_ssl'; +---------------+----------+ | Variable_name | Value | +---------------+----------+ | have_ssl | DISABLED | +---------------+----------+ 1 row in set (0.01 sec) mysql> show variables like 'require_secure_transport'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | require_secure_transport | OFF | +--------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like '%ssl%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | have_openssl | DISABLED | | have_ssl | DISABLED | | ssl_ca | ca.pem | | ssl_capath | | | ssl_cert | server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | server-key.pem | +---------------+-----------------+
從上面的查詢結果可以看到SSL: Not in use，have_ssl和have_openssl為DISABLED，這說明實際上是沒有啟用ssl。
這是因為透過執行mysql_ssl_rsa_setup命令產生的pem檔案的許可權為root使用者而不是mysql使用者造成的，將這些pem檔案的許可權修改為mysql使用者與組 許可權不要太大，只使用者許可權為rw，組與其它使用者需要r許可權
[root@localhost mysql]# ls -lrt 30172 -rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile1 -rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile2 drwxr-x---. 2 mysql mysql 48 321 16:25 undo -rw-r-----. 1 mysql mysql 56 321 16:25 auto.cnf drwxr-x---. 2 mysql mysql 8192 321 16:25 performance_schema drwxr-x---. 2 mysql mysql 4096 321 16:25 mysql drwxr-x---. 2 mysql mysql 8192 321 16:25 sys -rw-r-----. 1 mysql mysql 177 321 16:25 binlog.000001 -rw-r--r--. 1 mysql mysql 1679 321 16:26 ca-key.pem -rw-r--r--. 1 mysql mysql 1107 321 16:26 ca.pem -rw-r--r--. 1 root root 1679 321 16:26 server-key.pem -rw-r--r--. 1 root root 1107 321 16:26 server-cert.pem -rw-r--r--. 1 root root 1675 321 16:26 client-key.pem -rw-r--r--. 1 root root 1107 321 16:26 client-cert.pem -rw-r--r--. 1 root root 1675 321 16:26 private_key.pem -rw-r--r--. 1 root root 451 321 16:26 public_key.pem -rw-r-----. 1 mysql mysql 114688 524 17:00 ts2.ibd -rw-r-----. 1 mysql mysql 98304 524 17:01 ts1.ibd drwxr-x---. 2 mysql mysql 32 524 17:11 test -rw-r-----. 1 mysql mysql 9352 621 10:46 binlog.000002 -rw-r-----. 1 mysql mysql 177 621 10:46 binlog.000003 -rw-r-----. 1 mysql mysql 154 9 9 19:55 binlog.000004 -rw-r-----. 1 mysql mysql 177 9 9 20:23 binlog.000005 -rw-r-----. 1 mysql mysql 177 9 9 20:38 binlog.000006 -rw-r-----. 1 mysql mysql 177 9 9 20:44 binlog.000007 -rw-r-----. 1 mysql mysql 122 9 9 20:44 ib_buffer_pool -rw-r-----. 1 mysql mysql 248 9 9 20:44 binlog.index -rw-r-----. 1 mysql mysql 154 9 9 20:44 binlog.000008 -rw-r-----. 1 mysql mysql 6 9 9 20:44 mysqld.pid -rw-r-----. 1 mysql mysql 12582912 9 9 20:44 ibtmp1 -rw-r-----. 1 mysql mysql 10485760 9 9 20:44 ibdata1 -rw-r-----. 1 mysql mysql 104857600 9 9 20:44 ib_logfile0 -rw-r-----. 1 mysql mysql 107011 9 9 20:54 mysql.err [root@localhost mysql]# chown -R mysql:mysql *.pem [root@localhost mysql]# ls -lrt 30172 -rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile1 -rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile2 drwxr-x---. 2 mysql mysql 48 321 16:25 undo -rw-r-----. 1 mysql mysql 56 321 16:25 auto.cnf drwxr-x---. 2 mysql mysql 8192 321 16:25 performance_schema drwxr-x---. 2 mysql mysql 4096 321 16:25 mysql drwxr-x---. 2 mysql mysql 8192 321 16:25 sys -rw-r-----. 1 mysql mysql 177 321 16:25 binlog.000001 -rw-r--r--. 1 mysql mysql 1679 321 16:26 ca-key.pem -rw-r--r--. 1 mysql mysql 1107 321 16:26 ca.pem -rw-r--r--. 1 mysql mysql 1679 321 16:26 server-key.pem -rw-r--r--. 1 mysql mysql 1107 321 16:26 server-cert.pem -rw-r--r--. 1 mysql mysql 1675 321 16:26 client-key.pem -rw-r--r--. 1 mysql mysql 1107 321 16:26 client-cert.pem -rw-r--r--. 1 mysql mysql 1675 321 16:26 private_key.pem -rw-r--r--. 1 mysql mysql 451 321 16:26 public_key.pem -rw-r-----. 1 mysql mysql 114688 524 17:00 ts2.ibd -rw-r-----. 1 mysql mysql 98304 524 17:01 ts1.ibd drwxr-x---. 2 mysql mysql 32 524 17:11 test -rw-r-----. 1 mysql mysql 9352 621 10:46 binlog.000002 -rw-r-----. 1 mysql mysql 177 621 10:46 binlog.000003 -rw-r-----. 1 mysql mysql 154 9 9 19:55 binlog.000004 -rw-r-----. 1 mysql mysql 177 9 9 20:23 binlog.000005 -rw-r-----. 1 mysql mysql 177 9 9 20:38 binlog.000006 -rw-r-----. 1 mysql mysql 177 9 9 20:44 binlog.000007 -rw-r-----. 1 mysql mysql 122 9 9 20:44 ib_buffer_pool -rw-r-----. 1 mysql mysql 248 9 9 20:44 binlog.index -rw-r-----. 1 mysql mysql 154 9 9 20:44 binlog.000008 -rw-r-----. 1 mysql mysql 6 9 9 20:44 mysqld.pid -rw-r-----. 1 mysql mysql 12582912 9 9 20:44 ibtmp1 -rw-r-----. 1 mysql mysql 10485760 9 9 20:44 ibdata1 -rw-r-----. 1 mysql mysql 104857600 9 9 20:44 ib_logfile0 -rw-r-----. 1 mysql mysql 107011 9 9 20:54 mysql.err
pem檔案許可權太多可能會出現如下錯誤
[mysql@localhost mysql]$ mysql -h 10.11.13.19 -P 3306 -u root -pabcd123 --ssl-cert=/mysqldata/mysql/client-cert.pem --ssl-key=/mysqldata/mysql/client-key.pem mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed [root@localhost mysql]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS! [mysql@localhost mysql]$ mysql -h 10.11.13.19 -P 3306 -u root -pabcd123 --ssl-cert=/mysqldata/mysql/client-cert.pem --ssl-key=/mysqldata/mysql/client-key.pem mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper Connection id: 4 Current database: Current user: root@10.11.13.19 SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.26-log Source distribution Protocol version: 10 Connection: 10.11.13.19 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: gb2312 Conn. characterset: gb2312 TCP port: 3306 Uptime: 59 sec Threads: 2 Questions: 11 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.186 --------------
建立使用者限制使用者必須用ssl登入
mysql> create user 'jy'@'%' identified by "123"; Query OK, 0 rows affected (0.02 sec) mysql> grant all on *.* to 'jy'@'%' require ssl; Query OK, 0 rows affected, 1 warning (0.02 sec)
雖然要求用ssl但是還是可以使用密碼登入，是因為mysql.user表中的ssl_type=ANY
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u jy -p123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.26, for Linux (x86_64) using EditLine wrapper Connection id: 7 Current database: Current user: jy@10.11.13.19 SSL: Cipher in use is DHE-RSA-AES256-SHA Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.26-log Source distribution Protocol version: 10 Connection: 10.11.13.19 via TCP/IP Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: gb2312 Conn. characterset: gb2312 TCP port: 3306 Uptime: 50 min 18 sec Threads: 2 Questions: 69 Slow queries: 0 Opens: 139 Flush tables: 1 Open tables: 132 Queries per second avg: 0.022 -------------- mysql> select * from user where user='jy'\G ERROR 1046 (3D000): No database selected mysql> select * from mysql.user where user='root'\G *************************** 1. row *************************** Host: % User: jy Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ANY ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *1AA476D99C1600C9D984E248FBF2FDE3A0BB256E password_expired: N password_last_changed: 2022-03-21 16:27:48 password_lifetime: NULL account_locked: N 1 row in set (0.00 sec)
修改使用者jy的ssl型別為x509
mysql> alter user 'jy'@'%' require x509 -> ; Query OK, 0 rows affected (0.01 sec)
在不指定ssl金鑰時就不能登入了
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u jy -p123 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'jy'@'10.11.13.19' (using password: YES)
或
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u jy -p123 --ssl mysql: [Warning] Using a password on the command line interface can be insecure. WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead. ERROR 1045 (28000): Access denied for user 'jy'@'10.11.13.19' (using password: YES)
指定ssl金鑰進行登入
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u jy -p123 --ssl-cert=/mysqldata/mysql/client-cert.pem --ssl-key=/mysqldata/mysql/client-key.pem mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit
如果指定ssl金鑰進行登入時不指定金鑰完整路徑時會出現如下錯誤
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u jy -p123 --ssl-cert=client-cert.pem --ssl-key=client-key.pem mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [ERROR] SSL error: Unable to get certificate from 'client-cert.pem' ERROR 2026 (HY000): SSL connection error: Unable to get certificate
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2914898/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql5.7 ssl加密連線MySql加密
- mysql建立ssl安全連線的配置MySql
- MySQL SSL連線問題MySql
- mysql之使用SSL連線MySql
- 轉-配置openldap使用SSL連線LDA
- MySQL 使用 SSL 連線(附 Docker 例子)MySqlDocker
- 技術分享 | MySQL : SSL 連線淺析MySql
- JPA配置mysql連線MySql
- 使用 ProxySQL 改進 MySQL SSL 的連線效能MySql
- [Oracle-> MySQL] Oracle通過dblink連線MySQL--Oracle 19c連線到MySQL 5.7OracleMySql
- Loopback 4配置連線MySQLOOPMySql
- MYSQL遠端連線配置MySql
- 配置 MySQL 遠端連線MySql
- MYSQL配置遠端連線MySql
- 配置postfix和dovecot啟用SSL以加密連線加密
- mysql5.7MHA配置MySql
- 基於 Apaache 的 laravel-websocket SSL配置（wss連線）LaravelWeb
- proxool連線池如何使用SSL方式連線？
- CentOS 7 系統安裝與遠端連線 MySQL 5.7CentOSMySql
- 安全連線方式ssl(轉)
- MySQL5.7 新增配置MySql
- 配置Oracle DBlink連線MySQL庫OracleMySql
- 配置SQL Developer連線MySQL的方法DeveloperMySql
- linux下配置ODBC連線MySqlLinuxMySql
- 使用 Apache 反向代理來配置 Laravel-Websocket SSL（WSS 連線）ApacheLaravelWeb
- ECMall如何支援SSL連線郵件伺服器的配置伺服器
- MySQL基礎之MySQL 5.7 新增配置MySql
- mysql資料庫連線池配置教程MySql資料庫
- centos無法建立ssl連線CentOS
- RDSSQLServer設定SSL加密連線SQLServer加密
- Mysql 5.7憂化配置詳解MySql
- mysql5.7 慢日誌配置MySql
- mysql 5.7 GTID主從配置MySql
- MySQL 5.7 延遲複製配置MySql
- 關於MYSQL 5.7 新連線建立流程原始碼介面(和5.6不同)MySql原始碼
- 配置檔案讀取——MySQL 多個連線MySql
- 配置 MySQL 允許遠端連線的方法MySql
- 配置mysql允許遠端連線的方法MySql