快速业务通道

用SSL来连接MySQL数据库

作者 佚名技术 来源 NET编程 浏览 发布时间 2012-07-03
    这里测试的环境是MySQL5.1.30,单核CPU,2G内存。
如果你下载的是源码,那么用内置的yaSSL或者用第三方的OpenSSL来编译MySQL.
OpenSSL下载地址:http://www.openssl.org/
关于SSL加密传输的原理可以随便GOOGLE一下。

要注意的事项见这里:
 http://dev.mysql.com/doc/refman/5.0/en/secure-using-ssl.html

不过用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到文件里面然后执行。
具体解释:
http://dev.mysql.com/doc/refman/5.0/en/secure-create-certs.html

#-------------------------------------------------------------

#------------------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
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

参考文档:
https://support.eapps.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=122&nav=0,1

凌众科技专业提供服务器租用、服务器托管、企业邮局、虚拟主机等服务,公司网站:http://www.lingzhong.cn 为了给广大客户了解更多的技术信息,本技术文章收集来源于网络,凌众科技尊重文章作者的版权,如果有涉及你的版权有必要删除你的文章,请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息,谢谢!

分享到: 更多

Copyright ©1999-2011 厦门凌众科技有限公司 厦门优通互联科技开发有限公司 All rights reserved

地址(ADD):厦门软件园二期望海路63号701E(东南融通旁) 邮编(ZIP):361008

电话:0592-5908028 传真:0592-5908039 咨询信箱:web@lingzhong.cn 咨询OICQ:173723134

《中华人民共和国增值电信业务经营许可证》闽B2-20100024  ICP备案:闽ICP备05037997号