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-ge5];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"Createserverrequestandkey"
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)[]:MySQLserver

#EmailAddress[]:

#

#Pleaseenterthefollowingextraattributes

#tobesentwithyourcertificaterequest

#Achallengepassword[]:

#Anoptionalcompanyname[]:


#

#Removethepassphrasefromthekey(optional)

#


opensslrsa-in$DIR/server-key.pem-out$DIR/server-key.pem

echo""
echo"Signservercert"
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
serverversion: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-mtcp-ptcp--dport3309-jACCEPT
-ARH-Firewall-1-INPUT-mstate--stateNEW-mtcp-ptcp--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
serverversion: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:;
serverversion:5.1.30-logSourcedistribution
Protocolversion:10
Connection:192.168.2.41viatcp/IP
servercharacterset:latin1
Dbcharacterset:latin1
Clientcharacterset:utf8
Conn.characterset:utf8
tcpport: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,1DBaaS解决方案既可以解决这些问题，又能为客户节约资金。相反作为解决方案提供商，采用DBaaS模式似乎就并不那么有吸引力了，因为与企业内部署软件的解决方案相比，DBaaS意味着更低的利润。

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

从底层原理到表层引用，书籍多的很。个人认为没有什么那本书好？这样的说法。主要看和个人的学习方法是否适合。

不过话说回来了，绝大多数的性能优化准则与对sqlserver存储的结构理解息息相关

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书