MYSQL编程：基于MySQL的数据库集群体系的完成

下面我将描述五个不使用MySQL的响亮理由。mysql|数据|数据库您的WebApp体系是不是正在利用一个MySQL的数据库体系？您的客户是否是老是埋怨页面了局反应的十分慢？您的MySQL体系的负载是否是老是保持在一个十分高的形态下？本文将为您供应一个分管MySQL体系的负载的办法，和由此派生出来的一个MySQL-HA-Proxy的开辟项目。利用本文供应的办法，您将以最小的源代码修改，取得MySQL体系的高效运转。

第一节数据库集群手艺的近况

今朝数据库集群体系使用得对照乐成，使用局限对照普遍的是：Oracle公司的Oracle9与IBM公司DB2。Oracle9接纳Shared-storage的手艺，DB2选择了Shared-nothing的手艺，两者各有是非。

最新的数据库集群体系的实际基本是散布式盘算，将数据散布到每一个节点，一切的盘算节点并行处置数据，将了局汇总。如许的体例无疑是最完善的。可是今朝仍旧不克不及完成全体的功效。

关于Shared-storage和Shared-nothing的手艺请参考Oracle和IBM网站上的相干材料。

第二节今朝数据库使用情况

今朝数据库使用情况大抵分为两类，第一类是数据量在100G以下，数据库会见频仍，哀求麋集。次要是WebAPP范例的使用，比方：网站，论坛等。这些WebAPP范例的使用会见数据库的特性是：会见频仍，数据库每秒钟要承受几千次以上的查询，必要常常追加数据，同时对数据的呼应速率请求对照高。另外一类是用于迷信盘算、存储汗青数据的使用，数据量常常到达几百G。这些使用会见数据库的特性是：多为查询操纵，数据都是分批、准时、会合倒进数据库，数据库的纪录十分多，堆集了大批的数据，对数据库的呼应速率没有太高请求。

第三节表露出来的成绩

第一类使用，因为会见对照频仍，并且为了撑持更多的会见，WebServer一样平常都利用了负载平衡的集群，可是关于数据库来讲，因为没法完成集群操纵，每秒钟的哀求不休增添，跟着服务器负载的增添，呼应单个哀求的速率愈来愈慢，假如库文件对照年夜，呈现写操纵的时分还会呈现锁表工夫太长等影响会见效力的事变。

第二类使用，次要是数据文件太年夜，每次处置数据都必要大批的工夫，假如写错一个语句就必要花几个小时来重做查询。

第四节怎样办理

起首应该从硬件、软件、程序、索引、SQL语句这几个方面举行优化，假如仍旧不克不及办理成绩，我们就要思索数据库体系的集群（并行处置）了。

关于第一类的使用，在数据库服务器一般运转，负载不高的情形下，使用对数据库体系的情况仍是中意的。可是数据库体系负载太高以后，就会呈现完成哀求的工夫加长，达不到体系的请求工夫。既然负载是因为过量的哀求酿成的，我们就接纳分管哀求的体例，让一部分的哀求往会见别的一台服务器，让单台服务器的负载下降，从而办理成绩。

关于第二类的使用，就必要散布式盘算的体系来办理了，一样平常的体系是力所不及了。

第五节针关于"Linux+Apache+PHP+MySQL"的第一类使用成绩的办理体例

一个实践案例的办理：

我在事情傍边碰到了如许的成绩，我们的WebServer是Linux+Apache+Php的三台呆板构成的集群，MySQL运转在SUN450，2G内存的平台上。因为WEB的会见量在岑岭的时分几近满负荷运转，LoadAvg(就是一分钟以内处于Running形态的历程数目)都在10-20之间，反应出来就是大批的哀求都在会见数据库的时分被挂住了，招致一个哀求没有完成，下一个哀求又出去，最初恶性轮回。LoadAvg会在刹时飙升至800以上。数据库何处就更糟了，LoadAvg到达300多，数据库的线程十分多，CPU忙于切换线程形态，这个时分除非RestartMySQL，不然怎样都不会好。在对SQL语句优化完成后仍是不克不及很好的办理成绩，我们增添了一台数据库服务器，经由过程MySQL的数据同步机制，让两台数据库上的数据坚持同步，修正了一部分只会产生读取操纵的php程序，让这些程序毗连别的一台数据库，算是把负载分别进来一部分，成绩失掉了开端的办理。可是厥后营业做年夜，我们又增添了多台服务器，修正了良多程序，分别他们对数据库的读取操纵，会见分歧的服务器。

第六节MySQL-HA-Proxy计划的提出

经由过程修正程序的体例完成将体系的负载分别，是件很疾苦的事变，工程浩荡，并且不克不及弄错，由于除主服务器能够写进、修正数据，而别的的服务器只能经由过程数据同步更新本身的数据，以是假如你对那些数据库举行了写操纵，了局将是劫难性的。

假如我们可以有一个程序分拣SQL语句，依据他的范例（读取/写进），分离传送给分歧的服务器，然后再将了局前往。接纳一品种似HTTP的PROXY的体例，如许我们就不必要经由过程修正源程序的体例来分管负载了，假如再可以依据服务器的负载情况，大概是表的形态（可用/锁定），来判别应当将这个哀求分派到哪台服务器，那就比我们修正源程序所能到达的效果还要好。

第七节MySQLClient与Server之间怎样通讯

到处寻觅，也没有找到一篇关于Mysql通信协定的文章，看来只要剖析Mysql的源程序了。因而找来mysql3.23.49的代码，翻开sniffer工具。MySQL的通信协定大概变动过量次，在3.23.49的版本内里，通信协定的版本居然是10。

复杂的剖析了一下通信协定，如今规整以下，有些中央还不是很完美，因为我其实没有太多的工夫细心研读mysql的代码，今朝我只懂得到了这些。

偏移地区范例长度(byte)申明0HEADDataLength3123FLAG1=0一般信息
=1多段信息
=2认证前往
>2段停止字4DATACMDCode15MessageDataLength-1

当FLAG=0,2的时分CMDCode与Message的界说

CMDCode范例Message的布局00形态码偏移范例Length(byte)0Affectrows20A服务器版本号偏移范例Length(byte)只要在方才毗连上Server的时分无效，Server会即刻前往一个数据节段的信息0VersionString8endof8SessionID432bits12UnKnown11FF当呈现毛病的时分前往信息偏移范例Length(byte)0ErrCode22ErrMsgENDFE多段信息传输的停止空

Client对Server提交数据的格局：

偏移地区范例Length(byte)0HEADDataLength3123Compressed14DATACommandID15CommandDataDataLength-1

CommandID与CommandData的申明：

ID范例数据格局0COM_SLEEP1COM_QUITNULL2COM_INIT_DBDatabasename3COM_QUERYstandquerystring4COM_FIELD_LISTtablename[128]wildcard[128]5COM_CREATE_DBDatabasename6COM_DROP_DBDatabasename7COM_REFRESHoptions(bits)8COM_SHUTDOWNNULL9COM_STATISTICSNULL10COM_PROCESS_INFONULL11COM_CONNECT12COM_PROCESS_KILLsid[4]13COM_DEBUGNULL14COM_PINGNULL15COM_TIME16COM_DELAYED_INSERT17COM_CHANGE_USER[user][passwd][db]18COM_BINLOG_DUMP19COM_TABLE_DUMP20COM_CONNECT_OUT
第八节Client怎样经由过程Server的用户认证

协定剖析完成了，我实验着让它事情起来，但是认证这个部分碰到了贫苦，MysqlServer在Client毗连上它的时分，会起首前往给Client一个数据包，包括协定的版本号，版本信息，SessionID，一个8字节的Key，就是这个Key的缘故原由。Client会利用这个Key来加密暗码，然后将用户名，暗码，必要翻开的数据库等信息发送给Server，如许就完成认证了。我不晓得Client是怎样使用这个Key来加密的，以是我盘算跳过暗码，我将Client的数据包重组，往失落Password的信息以后，我乐成了，可是集群内里的Mysql用户都是没有暗码的，平安性多几少有些成绩，不外这些服务器都是放在HA前面的，没有内部的IP地点，应当成绩不年夜，不外多几少是个缺憾。


可是我总要晓得用户的暗码是不是准确吧？怎样办呢？利用一个公用的Mysql来完成暗码认证。安装一个最小化资本的MysqlServer用来做MysqlAuth（公用认证服务器），当Client毗连后，就将MysqlAuth的第一个数据包前往给Client，这内里固然就包括着Key，然后Client会利用这个Key，加密暗码以后，将认证信息发还来，这个时分，MysqlHA体系就会将这个信息转发给MysqlAuth，而且本人保存一份，假如认证经由过程了，就把保存的那一份举行重组，往失落暗码信息，然后用重组后的认证信息往毗连集群中的服务器。

<FONTcolor=#000000>

[1][2]下一页

人力成本上的节省，MySQL的用户Spacemonkey实验室的首席执行官MitchPirtle如此表示：“维护MySQL使得你不需要一个年薪15万美元的DBA。

再开发调试阶段和OLAP环境中，外键是可以建立的。新版本中加入了SETNULL和SETDEFAULT属性，能够提供能好的级联设置。

sqlserver的痛苦之处在于有用文档的匮乏，很多只是表明的东西

是要和操作系统进行Socket通讯的场景。否则建议慎重！

对一张百万级别的表建游标，同时又没有什么过滤条件，取得游标效率是如果直接SQL查询百万条数据；如果再对每条记录做处理，耗时将更长。

无法深入到数据库系统层面去了解和探究

而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。