【原创】用SSL来连接MySQL数据库

这里测试的环境是MySQL5.1.30,单核CPU，2G内存。
如果你下载的是源码，那么用内置的yaSSL或者用第三方的OpenSSL来编译MySQL.
OpenSSL下载地址：
关于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:           [e-mail]
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

参考文档：