用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
为了给广大客户了解更多的技术信息，本技术文章收集来源于网络,凌众科技尊重文章作者的版权，如果有涉及你的版权有必要删除你的文章，请和我们联系。以上信息与文章正文是不可分割的一部分,如果您要转载本文章,请保留以上信息，谢谢!
|
|
|
