用SSL 来连接MySQL数据库

た入场券 2022-01-15 02:41 319阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

这里测试的环境是MySQL5.1.30,单核CPU，2G内存。  
如果你下载的是源码，那么用内置的yaSSL或者用第三方的OpenSSL来编译MySQL.  
OpenSSL下载地址：\[url\]http://www.openssl.org/\[/url\]  
关于SSL加密传输的原理可以随便GOOGLE一下。  
  
要注意的事项见这里：  
 \[url\]http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html\[/url\]  
  
不过用SSL之前要想清楚，因为客户端和服务器端的连接以及传输速度会降低。  
  
**1. 下面我们来看安装过程。**  
**先看一下你自己的mysqld支持SSL与否。**  
**mysql> select @@have\_ssl;**  
**+------------+**  
**| @@have\_ssl |**  
**+------------+**  
**| NO         |**   
**+------------+**  
**1 row in set (0.01 sec)**  
**如果不支持，我们来看看安装过程。**  
  
**tar zxf mysql-5.1.30.tar.gz**   
**./configure --with-ssl --prefix=/usr/local/mysql-ytt**  
  
**configure 过程中有什么问题，见自己的config.log。**  
  
**如果没有问题，欢迎页面就会出现：**  
**...**  
**Thank you for choosing MySQL!**  
  
**然后**  
**make**  
**make install;**   
**这个时间比较长，我只有一个核的CPU。半个小时左右才搞完。**  
  
**COPY一个配置文件。**  
**\[root@ytt2 support-files\]\# cp my-medium.cnf /usr/local/mysql-ytt/my.cnf**  
  
**添加如下信息。**  
**port            = 3309**  
**socket          = /tmp/mysql3309.sock**  
**basedir=/usr/local/mysql-ytt**  
**datadir=/data/mysql-ytt**  
  
  
**建立MySQL的DATA目录来存放数据。**  
  
**\[root@ytt2 mysql-ytt\]\# cd /data/**  
**\[root@ytt2 data\]\# mkdir mysql-ytt**  
**\[root@ytt2 data\]\# chown -R mysql.mysql mysql-ytt/**  
  
**下来初始化数据库。**  
  
**\[root@ytt2 bin\]\# ./mysql\_install\_db --defaults-file=/usr/local/mysql-ytt/my.cnf**   
  
  
**2. 添加SSL认证过程。**  
**这个脚本COPY到文件里面然后执行。**  
**具体解释：**  
\[url\]http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html\[/url\]

``#------------------------------------------------------------- #------------------START SCRIPT------------------- #------------------------------------------------------------- DIR=`pwd`/openssl PRIV=$DIR/private mkdir $DIR $PRIV $DIR/newcerts #check if centos4 or centos5 VER=$(awk '{printf "%d", $3}' /etc/redhat-release); if [ $VER -ge 5 ]; then         cp /etc/pki/tls/openssl.cnf $DIR         replace ../../CA $DIR -- $DIR/openssl.cnf else         cp /usr/share/ssl/openssl.cnf $DIR         replace ./demoCA $DIR -- $DIR/openssl.cnf fi # Create necessary files: $database, $serial and $new_certs_dir # directory (optional) touch $DIR/index.txt echo "01" > $DIR/serial echo "" echo "Generation of Certificate Authority(CA):" echo "" openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ................++++++ # .........++++++ # writing new private key to '/home/monty/openssl/private/cakey.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL admin # Email Address []: echo "" echo "Create server request and key" echo "" openssl req -new -keyout $DIR/server-key.pem -out $DIR/server-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # ..++++++ # ..........++++++ # writing new private key to '/home/monty/openssl/server-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL server # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove the passphrase from the key (optional) # openssl rsa -in $DIR/server-key.pem -out $DIR/server-key.pem echo "" echo "Sign server cert" echo "" openssl ca -policy policy_anything -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles $DIR/server-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL admin' # Certificate is to be certified until Sep 13 14:22:46 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated echo "" echo "Create client request and key" echo "" echo "Remember to use a different commonName (CN) than from above" echo "" openssl req -new -keyout $DIR/client-key.pem -out $DIR/client-req.pem -days 3600 -config $DIR/openssl.cnf # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Generating a 1024 bit RSA private key # .....................................++++++ # .............................................++++++ # writing new private key to '/home/monty/openssl/client-key.pem' # Enter PEM pass phrase: # Verifying password - Enter PEM pass phrase: # ----- # You are about to be asked to enter information that will be # incorporated into your certificate request. # What you are about to enter is what is called a Distinguished Name # or a DN. # There are quite a few fields but you can leave some blank # For some fields there will be a default value, # If you enter '.', the field will be left blank. # ----- # Country Name (2 letter code) [AU]:FI # State or Province Name (full name) [Some-State]:. # Locality Name (eg, city) []: # Organization Name (eg, company) [Internet Widgits Pty Ltd]:MySQL AB # Organizational Unit Name (eg, section) []: # Common Name (eg, YOUR name) []:MySQL user # Email Address []: # # Please enter the following 'extra' attributes # to be sent with your certificate request # A challenge password []: # An optional company name []: # # Remove a passphrase from the key (optional) # openssl rsa -in $DIR/client-key.pem -out $DIR/client-key.pem echo "" echo "Sign client cert" echo "" openssl ca -policy policy_anything -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles $DIR/client-req.pem # Sample output: # Using configuration from /home/monty/openssl/openssl.cnf # Enter PEM pass phrase: # Check that the request matches the signature # Signature ok # The Subjects Distinguished Name is as follows # countryName :PRINTABLE:'FI' # organizationName :PRINTABLE:'MySQL AB' # commonName :PRINTABLE:'MySQL user' # Certificate is to be certified until Sep 13 16:45:17 2003 GMT # (365 days) # Sign the certificate? [y/n]:y # # # 1 out of 1 certificate requests certified, commit? [y/n]y # Write out database with 1 new entries # Data Base Updated echo "" echo "Creating a my.cnf file that you can use to test the certificates" echo "" cnf="" cnf="$cnf [client]" cnf="$cnf ssl-ca=$DIR/cacert.pem" cnf="$cnf ssl-cert=$DIR/client-cert.pem" cnf="$cnf ssl-key=$DIR/client-key.pem" cnf="$cnf [mysqld]" cnf="$cnf ssl-ca=$DIR/cacert.pem" cnf="$cnf ssl-cert=$DIR/server-cert.pem" cnf="$cnf ssl-key=$DIR/server-key.pem" echo $cnf | replace " " ' ' > $DIR/my.cnf echo "DONE!" #------------------------------------------------------------ #-------------------END SCRIPT-------------------- #------------------------------------------------------------``  
**然后执行：**  
**\[root@ytt2 ssl\]\# chmod 755 ssl\_script**   
**\[root@ytt2 ssl\]\# ./ssl\_script**   
**完了后**  
**然后在MySQL配置文件里面添加如下信息：**  
  
**\[client\]**  
**ssl-ca=/home/david\_yeung/ssl/openssl/cacert.pem**  
**ssl-cert=/home/david\_yeung/ssl/openssl/client-cert.pem**  
**ssl-key=/home/david\_yeung/ssl/openssl/client-key.pem**  
**\[mysqld\]**  
**ssl-ca=/home/david\_yeung/ssl/openssl/cacert.pem**  
**ssl-cert=/home/david\_yeung/ssl/openssl/server-cert.pem**  
**ssl-key=/home/david\_yeung/ssl/openssl/server-key.pem**  
  
  
**启动mysqld**  
  
**\[root@ytt2 mysql-ytt\]\# /usr/local/mysql-ytt/bin/mysqld\_safe --defaults-file=/usr/local/mysql-ytt/my.cnf &**  
\[1\] 24239  
  
**3. 授权SSL 测试用户：**  
  
**\[root@ytt2 ssl\]\# /usr/local/mysql-ytt/bin/mysql --defaults-file=/usr/local/mysql-ytt/my.cnf**   
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 11  
Server version: 5.1.30-log Source distribution  
  
Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.  
  
**mysql> grant all privileges on \*.\* to root@'192.168.2.88' identified by 'love\_root' require ssl;**  
Query OK, 0 rows affected (0.00 sec)  
  
**mysql> flush privileges;**  
Query OK, 0 rows affected (0.00 sec)  
  
**mysql> \\q**  
**Bye**  
**\[root@ytt2 ssl\]\#**   
  
**添加iptables 规则**  
  
**-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3309 -j ACCEPT**  
**-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT**  
**重启iptables.**  
**\[root@ytt2 ssl\]\# /etc/init.d/iptables restart**  
Flushing firewall rules:                                   \[  OK  \]  
Setting chains to policy ACCEPT: filter                    \[  OK  \]  
Unloading iptables modules:                                \[  OK  \]  
Applying iptables firewall rules:                          \[  OK  \]  
Loading additional iptables modules: ip\_conntrack\_netbios\_n\[  OK  \]  
  
**4.测试一下效果。**  
  
**把客户端的认证传到192.168.2.88的windows机器上。**  
**然后添加my.ini.**  
**比如我的：**  
**\[client\]**  
  
**port=3306**  
**ssl-ca="D:/LAMP/MySQL5.0/SSL\_key/cacert.pem"**  
**ssl-cert="D:/LAMP/MySQL5.0/SSL\_key/client-cert.pem"**  
**ssl-key="D:/LAMP/MySQL5.0/SSL\_key/client-key.pem"**  
重启MySQL服务器。  
  
**C:\\>net stop mysql5**  
The MySQL5 service is stopping..  
The MySQL5 service was stopped successfully.  
  
  
**C:\\>net start mysql5**  
The MySQL5 service is starting.  
The MySQL5 service was started successfully.  
  
**测试连接：**  
**C:\\>mysql -uroot -p -h192.168.2.41  -P3309**  
Enter password: \*\*\*\*\*\*\*\*\*  
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 13  
Server version: 5.1.30-log Source distribution  
  
Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.  
  
**mysql> status;**  
\--------------  
mysql  Ver 14.12 Distrib 5.0.45, for Win32 (ia32)  
  
Connection id:          13  
Current database:  
Current user:           [root@wh88.wswtek.com][root_wh88.wswtek.com]  
SSL:                    Cipher in use is DHE-RSA-AES256-SHA  
Using delimiter:        ;  
Server version:         5.1.30-log Source distribution  
Protocol version:       10  
Connection:             192.168.2.41 via TCP/IP  
Server characterset:    latin1  
Db     characterset:    latin1  
Client characterset:    utf8  
Conn.  characterset:    utf8  
TCP port:               3309  
Uptime:                 20 min 43 sec  
  
Threads: 1  Questions: 27  Slow queries: 0  Opens: 22  Flush tables: 2  Open tab  
les: 7  Queries per second avg: 0.21  
\--------------  
  
**mysql> \\q**  
  
**参考文档：**  
\[url\]https://support.eapps.com/index.php?\_m=knowledgebase&\_a=viewarticle&kbarticleid=122&nav=0\[/url\],1

本文出自 “[上帝，咱们不见不散！][Link 1]” 博客，请务必保留此出处[http://yueliangdao0608.blog.51cto.com/397025/119064][http_yueliangdao0608.blog.51cto.com_397025_119064]

转载于:https://my.oschina.net/u/585111/blog/219496

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/6933d40af58f40b09ba09f33ff67e23d.png
[root_wh88.wswtek.com]: mailto:root@wh88.wswtek.com
[Link 1]: http://yueliangdao0608.blog.51cto.com
[http_yueliangdao0608.blog.51cto.com_397025_119064]: http://yueliangdao0608.blog.51cto.com/397025/119064