用SSL来连接MySQL数据库

这里测试的环境是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-yttconfigure 过程中有什么问题，见自己的config.log。如果没有问题，欢迎页面就会出现：...Thank you for choosing MySQL!然后makemake install;这个时间比较长，我只有一个核的CPU。半个小时左右才搞完。COPY一个配置文件。[root@ytt2 support-files]# cp my-medium.cnf /usr/local/mysql-ytt/my.cnf添加如下信息。port;= 3309socket; = /tmp/mysql3309.sockbasedir=/usr/local/mysql-yttdatadir=/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`/opensslPRIV=$DIR/privatemkdir $DIR $PRIV $DIR/newcerts#check if centos4 or centos5VER=$(awk '{printf '%d', $3}' /etc/redhat-release)if [ $VER -ge 5 ] thencp /etc/pki/tls/openssl.cnf $DIRreplace ../../CA $DIR -- $DIR/openssl.cnfelsecp /usr/share/ssl/openssl.cnf $DIRreplace ./demoCA $DIR -- $DIR/openssl.cnffi# Create necessary files: $database, $serial and $new_certs_dir# directory (optional)touch $DIR/index.txtecho '01' > $DIR/serialecho ''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.pemecho ''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 Updatedecho ''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.pemecho ''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 Updatedecho ''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.cnfecho '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.pemssl-cert=/home/david_yeung/ssl/openssl/client-cert.pemssl-key=/home/david_yeung/ssl/openssl/client-key.pem[mysqld]ssl-ca=/home/david_yeung/ssl/openssl/cacert.pemssl-cert=/home/david_yeung/ssl/openssl/server-cert.pemssl-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] 242393. 授权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 11server version: 5.1.30-log Source distributionType '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> qBye[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 restartFlushing 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=3306ssl-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 mysql5The MySQL5 service is stopping..The MySQL5 service was stopped successfully.C:>net start mysql5The MySQL5 service is starting.The MySQL5 service was started successfully.测试连接：C:>mysql -uroot -p -h192.168.2.41; -P3309Enter password: *********Welcome to the MySQL monitor.; Commands end with ; or g.Your MySQL connection id is 13server version: 5.1.30-log Source distributionType '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:; 13Current database:Current user:root@wh88.wswtek.comSSL:;Cipher in use is DHE-RSA-AES256-SHAUsing delimiter:;;;;;;server version: 5.1.30-log Source distributionProtocol version:;;;;10Connection:;;192.168.2.41 via tcp/IPserver characterset:;latin1Db;;characterset:;latin1Client characterset:;utf8Conn.; characterset:;utf8tcp port:;;;;3309Uptime: 20 min 43 secThreads: 1; Questions: 27; Slow queries: 0; Opens: 22; Flush tables: 2; Open tables: 7; Queries per second avg: 0.21--------------mysql> q参考文档：https://support.eapps.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=122&nav=0,1