仓酷云
标题:
MYSQL教程之用SSL来毗连MySQL数据库
[打印本页]
作者:
海妖
时间:
2015-1-16 20:12
标题:
MYSQL教程之用SSL来毗连MySQL数据库
客户还是可以使用DBaaS系统所能提供的所有能力。数据库云服务消除了组织对专职人员、本地数据库存储设备的需要。他们不必安装、配置和维护任何软硬件。
这里测试的情况是MySQL5.1.30,单核CPU,2G内存。
假如你下载的是源码,那末用内置的yaSSL大概用第三方的OpenSSL来编译MySQL.
OpenSSL下载地点:
http://www.ckuyun.com/
关于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|
+------------+
1rowinset(0.01sec)
假如不撑持,我们来看看安装历程。
tarzxfmysql-5.1.30.tar.gz
./configure--with-ssl--PRefix=/usr/local/mysql-ytt
configure过程当中有甚么成绩,见本人的
config.log
。
假如没有成绩,接待页面就会呈现:
...
ThankyouforchoosingMySQL!
然后
make
makeinstall;
这个工夫对照长,我只要一个核的CPU。半个小时摆布才弄完。
COPY一个设置文件。
[root@ytt2support-files]#cpmy-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@ytt2mysql-ytt]#cd/data/
[root@ytt2data]#mkdirmysql-ytt
[root@ytt2data]#chown-Rmysql.mysqlmysql-ytt/
上去初始化数据库。
[root@ytt2bin]#./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
#-------------------------------------------------------------
#------------------STARTSCRipT-------------------
#-------------------------------------------------------------
DIR
=
`
pwd`
/
openssl
PRIV
=
$
DIR
/
private
mkdir
$
DIR
$
PRIV
$
DIR
/
newcerts
#checkifcentos4orcentos5
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
#Createnecessaryfiles:$database,$serialand$new_certs_dir
#directory(optional)
touch
$
DIR
/
index
.
txt
echo
"01"
>
$
DIR
/
serial
echo
""
echo
"GenerationofCertificateAuthority(CA):"
echo
""
opensslreq
-
new
-
x509
-
keyout
$
PRIV
/
cakey
.
pem
-
out
$
DIR
/
cacert
.
pem
-
config
$
DIR
/
openssl
.
cnf
#Sampleoutput:
#Usingconfigurationfrom/home/monty/openssl/openssl.cnf
#Generatinga1024bitRSAprivatekey
#................++++++
#.........++++++
#writingnewprivatekeyto/home/monty/openssl/private/cakey.pem
#EnterPEMpassphrase:
#VerifyingpassWord-EnterPEMpassphrase:
#-----
#Youareabouttobeaskedtoenterinformationthatwillbe
#incorporatedintoyourcertificaterequest.
#WhatyouareabouttoenteriswhatiscalledaDistinguishedName
#oraDN.
#Therearequiteafewfieldsbutyoucanleavesomeblank
#Forsomefieldstherewillbeadefaultvalue,
#Ifyouenter.,thefieldwillbeleftblank.
#-----
#CountryName(2lettercode)[AU]:FI
#StateorProvinceName(fullname)[Some-State]:.
#LocalityName(eg,city)[]:
#OrganizationName(eg,company)[InternetWidgitsPtyLtd]:MySQLAB
#OrganizationalUnitName(eg,section)[]:
#CommonName(eg,YOURname)[]:MySQLadmin
#EmailAddress[]:
echo
""
echo
"Create
server
requestandkey"
echo
""
opensslreq
-
new
-
keyout
$
DIR
/
server
-
key
.
pem
-
out
$
DIR
/
server
-
req
.
pem
-
days3600
-
config
$
DIR
/
openssl
.
cnf
#Sampleoutput:
#Usingconfigurationfrom/home/monty/openssl/openssl.cnf
#Generatinga1024bitRSAprivatekey
#..++++++
#..........++++++
#writingnewprivatekeyto/home/monty/openssl/server-key.pem
#EnterPEMpassphrase:
#Verifyingpassword-EnterPEMpassphrase:
#-----
#Youareabouttobeaskedtoenterinformationthatwillbe
#incorporatedintoyourcertificaterequest.
#WhatyouareabouttoenteriswhatiscalledaDistinguishedName
#oraDN.
#Therearequiteafewfieldsbutyoucanleavesomeblank
#Forsomefieldstherewillbeadefaultvalue,
#Ifyouenter.,thefieldwillbeleftblank.
#-----
#CountryName(2lettercode)[AU]:FI
#StateorProvinceName(fullname)[Some-State]:.
#LocalityName(eg,city)[]:
#OrganizationName(eg,company)[InternetWidgitsPtyLtd]:MySQLAB
#OrganizationalUnitName(eg,section)[]:
#CommonName(eg,YOURname)[]:MySQL
server
#EmailAddress[]:
#
#Pleaseenterthefollowingextraattributes
#tobesentwithyourcertificaterequest
#Achallengepassword[]:
#Anoptionalcompanyname[]:
#
#Removethepassphrasefromthekey(optional)
#
opensslrsa
-
in
$
DIR
/
server
-
key
.
pem
-
out
$
DIR
/
server
-
key
.
pem
echo
""
echo
"Sign
server
cert"
echo
""
opensslca
-
policypolicy_anything
-
out
$
DIR
/
server
-
cert
.
pem
-
config
$
DIR
/
openssl
.
cnf
-
infiles
$
DIR
/
server
-
req
.
pem
#Sampleoutput:
#Usingconfigurationfrom/home/monty/openssl/openssl.cnf
#EnterPEMpassphrase:
#Checkthattherequestmatchesthesignature
#Signatureok
#TheSubjectsDistinguishedNameisasfollows
#countryName:PRINTABLE:FI
#organizationName:PRINTABLE:MySQLAB
#commonName:PRINTABLE:MySQLadmin
#CertificateistobecertifieduntilSep1314:22:462003GMT
#(365days)
#Signthecertificate?[y/n]:y
#
#
#1outof1certificaterequestscertified,commit?[y/n]y
#Writeoutdatabasewith1newentries
#DataBaseUpdated
echo
""
echo
"Createclientrequestandkey"
echo
""
echo
"RemembertouseadifferentcommonName(CN)thanfromabove"
echo
""
opensslreq
-
new
-
keyout
$
DIR
/
client
-
key
.
pem
-
out
$
DIR
/
client
-
req
.
pem
-
days3600
-
config
$
DIR
/
openssl
.
cnf
#Sampleoutput:
#Usingconfigurationfrom/home/monty/openssl/openssl.cnf
#Generatinga1024bitRSAprivatekey
#.....................................++++++
#.............................................++++++
#writingnewprivatekeyto/home/monty/openssl/client-key.pem
#EnterPEMpassphrase:
#Verifyingpassword-EnterPEMpassphrase:
#-----
#Youareabouttobeaskedtoenterinformationthatwillbe
#incorporatedintoyourcertificaterequest.
#WhatyouareabouttoenteriswhatiscalledaDistinguishedName
#oraDN.
#Therearequiteafewfieldsbutyoucanleavesomeblank
#Forsomefieldstherewillbeadefaultvalue,
#Ifyouenter.,thefieldwillbeleftblank.
#-----
#CountryName(2lettercode)[AU]:FI
#StateorProvinceName(fullname)[Some-State]:.
#LocalityName(eg,city)[]:
#OrganizationName(eg,company)[InternetWidgitsPtyLtd]:MySQLAB
#OrganizationalUnitName(eg,section)[]:
#CommonName(eg,YOURname)[]:MySQLuser
#EmailAddress[]:
#
#Pleaseenterthefollowingextraattributes
#tobesentwithyourcertificaterequest
#Achallengepassword[]:
#Anoptionalcompanyname[]:
#
#Removeapassphrasefromthekey(optional)
#
opensslrsa
-
in
$
DIR
/
client
-
key
.
pem
-
out
$
DIR
/
client
-
key
.
pem
echo
""
echo
"Signclientcert"
echo
""
opensslca
-
policypolicy_anything
-
out
$
DIR
/
client
-
cert
.
pem
-
config
$
DIR
/
openssl
.
cnf
-
infiles
$
DIR
/
client
-
req
.
pem
#Sampleoutput:
#Usingconfigurationfrom/home/monty/openssl/openssl.cnf
#EnterPEMpassphrase:
#Checkthattherequestmatchesthesignature
#Signatureok
#TheSubjectsDistinguishedNameisasfollows
#countryName:PRINTABLE:FI
#organizationName:PRINTABLE:MySQLAB
#commonName:PRINTABLE:MySQLuser
#CertificateistobecertifieduntilSep1316:45:172003GMT
#(365days)
#Signthecertificate?[y/n]:y
#
#
#1outof1certificaterequestscertified,commit?[y/n]y
#Writeoutdatabasewith1newentries
#DataBaseUpdated
echo
""
echo
"Creatingamy.cnffilethatyoucanusetotestthecertificates"
echo
""
cnf
=
""
cnf
=
"$cnf[client]"
cnf
=
"$cnfssl-ca=$DIR/cacert.pem"
cnf
=
"$cnfssl-cert=$DIR/client-cert.pem"
cnf
=
"$cnfssl-key=$DIR/client-key.pem"
cnf
=
"$cnf[mysqld]"
cnf
=
"$cnfssl-ca=$DIR/cacert.pem"
cnf
=
"$cnfssl-cert=$DIR/server-cert.pem"
cnf
=
"$cnfssl-key=$DIR/server-key.pem"
echo
$
cnf
|
replace
""
>
$
DIR
/
my
.
cnf
echo
"DONE!"
#------------------------------------------------------------
#-------------------ENDSCRIPT--------------------
#------------------------------------------------------------
然后实行:
[root@ytt2ssl]#chmod755ssl_script
[root@ytt2ssl]#./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@ytt2mysql-ytt]#/usr/local/mysql-ytt/bin/mysqld_safe--defaults-file=/usr/local/mysql-ytt/my.cnf&
[1]24239
3.受权SSL测试用户:
[root@ytt2ssl]#/usr/local/mysql-ytt/bin/mysql--defaults-file=/usr/local/mysql-ytt/my.cnf
WelcometotheMySQLmonitor.Commandsendwith;org.
YourMySQLconnectionidis11
server
version:5.1.30-logSourcedistribution
Typehelp;orhforhelp.Typectoclearthebuffer.
mysql>grantallprivilegeson*.*toroot@192.168.2.88identifiedbylove_rootrequiressl;
QueryOK,0rowsaffected(0.00sec)
mysql>flushprivileges;
QueryOK,0rowsaffected(0.00sec)
mysql>q
Bye
[root@ytt2ssl]#
增加iptables划定规矩
-ARH-Firewall-1-INPUT-mstate--stateNEW-m
tcp
-p
tcp
--dport3309-jACCEPT
-ARH-Firewall-1-INPUT-mstate--stateNEW-m
tcp
-p
tcp
--dport443-jACCEPT
重启iptables.
[root@ytt2ssl]#/etc/init.d/iptablesrestart
Flushingfirewallrules:[OK]
SettingchainstopolicyACCEPT:filter[OK]
Unloadingiptablesmodules:[OK]
Applyingiptablesfirewallrules:[OK]
Loadingadditionaliptablesmodules: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:>netstopmysql5
TheMySQL5serviceisstopping..
TheMySQL5servicewasstoppedsuccessfully.
C:>netstartmysql5
TheMySQL5serviceisstarting.
TheMySQL5servicewasstartedsuccessfully.
测试毗连:
C:>mysql-uroot-p-h192.168.2.41-P3309
Enterpassword:*********
WelcometotheMySQLmonitor.Commandsendwith;org.
YourMySQLconnectionidis13
server
version:5.1.30-logSourcedistribution
Typehelp;orhforhelp.Typectoclearthebuffer.
mysql>status;
--------------
mysqlVer14.12Distrib5.0.45,forWin32(ia32)
Connectionid:13
Currentdatabase:
Currentuser:
root@wh88.wswtek.com
SSL:CipherinuseisDHE-RSA-AES256-SHA
Usingdelimiter:;
server
version:5.1.30-logSourcedistribution
Protocolversion:10
Connection:192.168.2.41via
tcp
/IP
server
characterset:latin1
Dbcharacterset:latin1
Clientcharacterset:utf8
Conn.characterset:utf8
tcp
port:3309
Uptime:20min43sec
Threads:1Questions:27Slowqueries:0Opens:22Flushtables:2Opentab
les:7Queriespersecondavg:0.21
--------------
mysql>q
参考文档:
https://support.eapps.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=122&nav=0
,1
DBaaS解决方案既可以解决这些问题,又能为客户节约资金。相反作为解决方案提供商,采用DBaaS模式似乎就并不那么有吸引力了,因为与企业内部署软件的解决方案相比,DBaaS意味着更低的利润。
作者:
再见西城
时间:
2015-1-22 06:53
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高,但是相对映射到数据表中效率还是提高了很多。我做过试验,在我的实验环境中会比映射到表中提高30%左右的效率。
作者:
admin
时间:
2015-1-30 23:34
从底层原理到表层引用,书籍多的很。个人认为没有什么那本书好?这样的说法。主要看和个人的学习方法是否适合。
作者:
谁可相欹
时间:
2015-2-6 17:02
不过话说回来了,绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关
作者:
简单生活
时间:
2015-2-17 12:55
微软对CLR作了大篇幅的宣传,这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜,感觉对象数据库的一些概念可以实现了。
作者:
若天明
时间:
2015-3-5 19:54
你觉得我的非分区索引无法对起子分区,你可以提醒我一下呀!没有任何的提醒,直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。
作者:
愤怒的大鸟
时间:
2015-3-12 14:00
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已,性能方面提高不多。但是SQL2005提供了计算字段的持久化,这就提高了查询的性能,但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。
作者:
山那边是海
时间:
2015-3-19 22:59
两个月啃那本sqlserver2005技术内部-存储引擎,花了几个月啃四本书
欢迎光临 仓酷云 (http://ckuyun.com/)
Powered by Discuz! X3.2