高可用性、负载均衡的mysql集群解决方案

1、数据库弱一致性四个隔离级别SQL-92标准中定义了四个隔离级别，这四个隔离级别在以前版本的SQL Server中即受到支持：READ UNCOMMITTEDREAD UNCOMMITTED是限制性最弱的隔离级别，因为该级别忽略其他事务放置的锁。使用READ UNCOMMITTED级别执行的事务，可以读取尚未由其他事务提交的修改后的数据值，这些行为称为“脏”读。这是因为在Read Uncommitted级别下，读取数据不需要加S锁，这样就不会跟被修改的数据上的X锁冲突。比如，事务1修改一行，事务2在事务1提交之前读取了这一行。如果事务1回滚，事务2就读取了一行没有提交的数据，这样的数据我们认为是

2、不存在的。READ COMMITTEDREAD COMMITTED(Nonrepeatable reads)是SQL Server默认的隔离级别。该级别通过指定语句不能读取其他事务已修改但是尚未提交的数据值，禁止执行脏读。在当前事务中的各个语句执行之间，其他事务仍可以修改、插入或删除数据，从而产生无法重复的读操作，或“影子”数据。比如，事务1读取了一行，事务2修改或者删除这一行并且提交。如果事务1想再一次读取这一行，它将获得修改后的数据或者发现这一样已经被删除，因此事务的第二次读取结果与第一次读取结果不同，因此也叫不可重复读。实验1query1：事务1-step1:创建实验数据select *

3、 into Employee from AdventureWorks.HumanResources.Employeealter table Employee add constraint pk_Employee_EmployeeID primary key(EmployeeID)-step2:设置隔离级别,这是数据库的默认隔离界别SET TRANSACTION ISOLATION LEVEL READ COMMITTED-step3:开启第一个事务BEGIN TRAN tran1 -step4:执行select操作,查看VacationHours,对查找的记录加S锁，在语句执行完以后自动释放S

4、锁 SELECT EmployeeID, VacationHours FROM Employee WHERE EmployeeID = 4; -step5:查看当前加锁情况,没有发现在Employee表上面有锁,这是因为当前的隔离界别是READ COMMITTED -在执行完step2以后马上释放了S锁. SELECT request_session_id, resource_type, resource_associated_entity_id, request_status, request_mode, resource_description FROM sys.dm_tran_locks

5、查看锁的情况如下图所示，我们发现在只有在数据库级别的S锁，而没有在表级别或者更低级别的锁，这是因为在Read Committed级别下，S锁在语句执行完以后就被释放。query2：事务2-step6:开启第二个事务BEGIN TRAN tran2; -step7:修改VacationHours,需要获得排它锁X,在VacationHours上没有有S锁 UPDATE Employee SET VacationHours = VacationHours - 8 WHERE EmployeeID = 4; -step8:查看当前加锁情况 SELECT request_session_id, res

6、ource_type, resource_associated_entity_id, request_status, request_mode, resource_description FROM sys.dm_tran_locks在开启另外一个update事务以后，我们再去查看当前的锁状况，如下图所示，我们发现在表(Object)级别上加了IX锁，在这张表所在的Page上也加了IX锁，因为表加了聚集索引，所以在叶子结点上加了X锁，这个锁的类型是KEY。然后我们回到事务1当中再次执行查询语句，我们会发现查询被阻塞，我们新建一个查询query3来查看这个时候的锁状况，其查询结果如下，我们可以发现

7、查询操作需要在KEY级别上申请S锁，在Page和表(Object)上面申请IS锁，但是因为Key上面原先有了X锁，与当前读操作申请的S锁冲突，所以这一步处于WAIT状态。如果此时提交事务2的update操作，那么事务1的select操作不再被阻塞，得到查询结果，但是我们发现此时得到的查询结果与第一次得到的查询结果不同，这也是为什么将read committed称为不可重复读，因为同一个事物内的两次相同的查询操作的结果可能不同。REPEATABLE READREPEATABLE READ是比READ COMMITTED限制性更强的隔离级别。该级别包括READ COMMITTED，并且另外指定了在

8、当前事务提交之前，其他任何事务均不可以修改或删除当前事务已读取的数据。并发性低于READ COMMITTED，因为已读数据的共享锁在整个事务期间持有，而不是在每个语句结束时释放。比如，事务1读取了一行，事务2想修改或者删除这一行并且提交，但是因为事务1尚未提交，数据行中有事务1的锁，事务2无法进行更新操作，因此事务2阻塞。如果这时候事务1想再一次读取这一行，它读取结果与第一次读取结果相同，因此叫可重复读。实验2query1：事务1-step1:创建实验数据select * into Employee from AdventureWorks.HumanResources.Employeealte

9、r table Employee add constraint pk_Employee_EmployeeID primary key(EmployeeID)-step2:设置隔离级别SET TRANSACTION ISOLATION LEVEL REPEATABLE READ-step3:开启第一个事务BEGIN TRAN tran1 -step4:执行select操作,查看VacationHours SELECT EmployeeID, VacationHours FROM Employee WHERE EmployeeID = 4; -step5:查看当前加锁情况,发现在Employee表

10、上面有S锁,这是因为当前的隔离界别是REPEATABLE READ -S锁只有在事务执行完以后才会被释放. SELECT request_session_id, resource_type, resource_associated_entity_id, request_status, request_mode, resource_description FROM sys.dm_tran_locks查询锁状态的结果如下图所示，我们发现在KEY上面加了S锁，在Page和Object上面加了IS锁，这是因为在Repeatable Read级别下S锁要在事务执行完以后才会被释放。query2：事务2-

11、step6:开启第二个事务BEGIN TRAN tran2; -step7:修改VacationHours,需要获得排他锁X,在VacationHours上有S锁，出现冲突，所以update操作被阻塞 UPDATE Employee SET VacationHours = VacationHours - 8 WHERE EmployeeID = 4;执行上述update操作的时候发现该操作被阻塞，这是因为update操作要加排它锁X，而因为原先的查询操作的S锁没有释放，所以两者冲突。我们新建一个查询3执行查询锁状态操作，发现结果如下图所示，我们可以发现是WAIT发生在对KEY加X锁的操作上面。

12、此时再次执行查询1中的select操作，我们发现查询结果跟第一次相同，所以这个叫做可重复读操作。但是可重复读操作并不是特定指两次读取的数据一模一样，Repeatable Read存在的一个问题是幻读，就是第二次读取的数据返回的条目数比第一次返回的条目数更多。比如在Repeatable Read隔离级别下，事务1第一次执行查询select id from users where id1 and id disable语言支持包不安装，默认美国英语设定主机名：Vi/etc/sysconfig/networkHostname=xxx:wq检查主机名：Uname-a必须和上表中的一一对应。否则有问题。V

13、i/etc/hostsNdb1 64Ndb2 6Sql1 7Sql2 01更新：#rpm-import/rpm/RPM-GPG-KEY.dries.txt#yumupdate-y&yum-yinstalllynxlibawtxorg-x11-deprecated-libsnxfreenxarptables_jfhttpd-devel下载：Mysqlcluster版本（我下载的5.0.67社区版本）：rootndb1RHEL5#ls-lhMySQL

14、*|awkprint$9MySQL-client-community-5.0.67-0.rhel5.i386.rpmMySQL-clusterextra-community-5.0.67-0.rhel5.i386.rpmMySQL-clustermanagement-community-5.0.67-0.rhel5.i386.rpmMySQL-clusterstorage-community-5.0.67-0.rhel5.i386.rpmMySQL-clustertools-community-5.0.67-0.rhel5.i386.rpmMySQL-devel-community-5.0.6

15、7-0.rhel5.i386.rpmMySQL-server-community-5.0.67-0.rhel5.i386.rpmMySQL-shared-community-5.0.67-0.rhel5.i386.rpmMySQL-shared-compat-5.0.67-0.rhel4.i386.rpmMySQL-shared-compat-5.0.67-0.rhel5.i386.rpmMySQL-test-community-5.0.67-0.rhel5.i386.rpmperl-HTML-Template-2.9-1.el5.rf.noarch.rpmrootndb1RHEL5#在服务器

16、上安装以上包，在安装的过程中如果缺少包或者库，采用：yuminstallxxxx自行安装。建立目录：#mkdir/var/lib/mysql-cluster-p以下分别操作：安装cluster组件：#Rpm-UvhMySQL-xx-xx.rpm，根据不同，可以少安装部分组件。根据你需要而定。163、26上，我安装了：rootndb1RHEL5#rpm-aq|grepMySQLMySQL-clusterstorage-community-5.0.67-0.rhel5MySQL-clustertools-community-5.0.67-0.rhel5MySQL-clustermanagement

17、-community-5.0.67-0.rhel5MySQL-shared-community-5.0.67-0.rhel5perl-DBD-MySQL-3.0007-1.fc6MySQL-server-community-5.0.67-0.rhel5rootndb1RHEL5#101、77上，我安装了：rootsql1#rpm-aq|grepMySQLMySQL-clusterstorage-community-5.0.67-0.rhel4MySQL-devel-community-5.0.67-0.rhel4MySQL-server-community-5.0.67-0.rhel4MySQ

18、L-client-community-5.0.67-0.rhel4MySQL-shared-community-5.0.67-0.rhel4rootsql1#以下在ndb1（164）和ndb2（26）上操作rootndb1#vi/var/lib/mysql-cluster/config.iniNDBDDEFAULTNoOfReplicas=2DataMemory=800MIndexMemory=400MMYSQLDDEFAULTNDB_MGMDDEFAULTTCPDEFAULT#SectionfortheclustermanagementnodeNDB_MGMD#IPaddressofthem

19、anagementnode(thissystem)ID=1HostName=64NDB_MGMD#IPaddressofthemanagementnode(thissystem)ID=2HostName=6#SectionforthestoragenodesNDBD#IPaddressofthefirststoragenodeHostName=7DataDir=/var/lib/mysql-clusterNDBD#IPaddressofthesecondstoragenodeHostName=192.168.131.

20、101DataDir=/var/lib/mysql-cluster#oneMYSQLDperstoragenodeMYSQLDMYSQLDMYSQLDMYSQLDMYSQLDMYSQLDMYSQLD:wq以下在mysqlAPI上操作（这里，我设定了7个API，以后可以随时加入）MysqldAPI的配置文件：Vi/etc/frootndb1#cat/etc/fmysqldndbclusterndb-connectstring=host=64,host=6ndb_mgmconnect-string=host=64,hos

21、t=6ndbdconnect-string=host=64,host=6:wq分别启动ndb_mgmd/ndbd/mysqld164/26:ndb_mgmd-f/var/lib/mysql-cluster/config.ini77/101:Ndbd-initial164/26/77/101:/etc/rc.d/init.d/mysqlstart在管理节点ndb1(164)和ndb2(26)上查看各节点的情况：rootndb1#ndb_mgm-NDBCluster-ManagementClient-ndb_mgmsho

22、wConnectedtoManagementServerat:64:1186ClusterConfiguration-ndbd(NDB)2node(s)id=37(Version:5.0.67,Nodegroup:0,Master)id=401(Version:5.0.67,Nodegroup:0)ndb_mgmd(MGM)2node(s)id=164(Version:5.0.67)id=26(Version:5.0.67)mysqld(API)7node(s)id

23、=501(Version:5.0.67)id=66(Version:5.0.67)id=764(Version:5.0.67)id=87(Version:5.0.67)id=9(notconnected,acceptingconnectfromanyhost)id=10(notconnected,acceptingconnectfromanyhost)id=11(notconnected,acceptingconnectfromanyhost)ndb_mgm以上说明一切正常。将服务增加到开机

24、启动服务项中：164/26:echondb_mgmd-f/var/lib/mysql-cluster/config.ini/etc/rc.d/init.d/ndb_mgmdchmod755/etc/rc.d/init.d/ndb_mgmd77/101:Echondbd/etc/rc.d/init.d/ndbdChmod755/etc/rc.d/init.d/ndbdChkconfig-level2345ndbdonOK，到此mysqlcluster配置完成。强调：1）由于数据放在内存中，需要在ndb节点上加大内存的数量。按照1：1.1的比例，如果数据量达到3.6GB，需要4GB的内存。2）由于

25、NDB和mysqld（API）都很耗费内存，所以建议将NDB放在164和26上。可能启动的时候会有警告，但是没关系的。查看数据和内存情况：77：rootsql2#toptop-16:39:36up1:59,1user,loadaverage:1.37,0.76,0.60Tasks:80total,2running,78sleeping,0stopped,0zombieCpu(s):4.0%us,4.0%sy,0.0%ni,87.3%id,2.9%wa,0.2%hi,1.5%si,0.0%stMem:2075600ktotal,2005868kused,69732kfree,68256kbuff

26、ersSwap:2031608ktotal,0kused,2031608kfree,1400812kcachedPIDUSERPRNIVIRTRESSHRS%CPU%MEMTIME+COMMAND2306mysql250119m15m3952S220.810:20.94mysqld23791root1501587m484m31mR2023.99:34.97ndbd由于77只有2GB的内存，而在config.ini中，把1.2GB的内存分配给了NDB，所以，加上mysqld用掉的，2GB的内存似乎已经所剩无几了。查看77上的数据大小：rootsql2#cd/var/lib/mysql-clust

27、er/ndb_4_fs/rootsql2ndb_4_fs#du-lh1.3GB连接API创建数据库：由于上面4台都做为mysqld的API，所以创建数据库的时候，都需要创建一遍。以下操作在4台API上都需要操作：#Mysql-uroot-pxxxxxxxxxxxx-AMysqlcreatedatabasetestdatabase;Mysqlgrantallon*.testdatabasetoroot192.168.131.%identifiedbyxxxxxxxxxxxxxxx;Mysqlflushprivileges;Mysqlcreatetabletest(int(1);Mysqlinse

28、rtintotest(1);Mysqlquit;以上做完以后，可以通过任意一台API上创建表，并写数据到表中，其他数据库都会同步写入。分别连接每台服务器进行检查：#Mysql-uroot-pxxxxxxxxxxxx-AMysqlusetestdatabase;Mysqlselect*fromtest;如果输出结果完全相同，表明mysqlcluster已经可以正常工作了。在2台API上设置LVSMysqlcluster做好以后，数据库分别建立同名的数据库以后，权限分配好，然后只要在一台上写入数据，其他的NDB就存储了相同的数据。用 程序连接任意一台API写数据，如果程序中未设置API的选择和判断

29、，只使用了其中一个API，一旦API当机，则无法写入数据，必须修改程序。即便做了 API的判断和选择，因为没有实现负载均衡，服务器的性能没有充分利用。高可用性也没有达到目标。所以，我们现在在2台API之间做LVS。LVS采用ultramonkey（）首先在NDB1（164）和NDB2（26）上下载heartbeat的软件包：下载所有的rpm包：Cd/usr/local/srcMkdirheartbeatCdheartbeat#Wgetxxx.xxx.rpm我下载了如下的软件包：rootndb1heartbeat#ls-lh*.rpm|awkpr

30、int$9;arptables-noarp-addr-0.99.2-1.rh.el.um.1.noarch.rpmheartbeat-1.2.3.cvs.20050927-1.rh.el.um.4.i386.rpmheartbeat-ldirectord-1.2.3.cvs.20050927-1.rh.el.um.4.i386.rpmheartbeat-pils-1.2.3.cvs.20050927-1.rh.el.um.4.i386.rpmheartbeat-stonith-1.2.3.cvs.20050927-1.rh.el.um.4.i386.rpmipvsadm-1.21-1.rh.e

31、l.1.um.1.i386.rpmlibnet--1.rh.el.um.1.i386.rpmperl-Authen-SASL-2.08-1.rh.el.um.1.noarch.rpmperl-Convert-ASN1-0.18-1.rh.el.um.1.noarch.rpmperl-IO-Socket-SSL-0.96-1.rh.el.um.1.noarch.rpmperl-ldap-0.3202-1.rh.el.um.1.noarch.rpmperl-Mail-IMAPClient-2.2.9-1.rh.el.um.1.noarch.rpmperl-Net-SSLeay-1.2

32、5-1.rh.el.um.1.i386.rpmperl-Parse-RecDescent-1.94-1.el5.rf.noarch.rpmperl-Parse-RecDescent-1.94-1.rh.el.um.1.noarch.rpmperl-XML-NamespaceSupport-1.08-1.rh.el.um.1.noarch.rpmperl-XML-SAX-0.12-1.rh.el.um.1.noarch.rpmrootndb1heartbeat#Heartbeat中包含以下几部分：1)MasterDirector（分发器）-MD2)BackupDirector（备份分发器）-BD

33、3)Realserver（真实服务器，可以有2个以上）-RSIP设置并确认：MD:Eth0:64/24/GW:Eth1:/24MD:Eth0:6/24/GW:Eth1:VIP:05/24/GW:-用户访问的统一虚拟IPRS1:01/24/GW:RS2:7/24/GW:。等等以下操作在所有服务器上执行：主

34、机名确认：分别执行：#uname-a主机名对应表中所列。在MD和BD修改IP转发：#vimodprobe.shmodprobeip_vs_dhmodprobeip_vs_ftpmodprobeip_vsmodprobeip_vs_lblcmodprobeip_vs_lblcrmodprobeip_vs_lcmodprobeip_vs_nqmodprobeip_vs_rrmodprobeip_vs_sedmodprobeip_vs_shmodprobeip_vs_wlcmodprobeip_vs_wrr:wq#chmod755modprobe.sh#shmodprobe.sh#vi/etc/mo

35、dulesip_vs_dhip_vs_ftpip_vsip_vs_lblcip_vs_lblcrip_vs_lcip_vs_nqip_vs_rrip_vs_sedip_vs_ship_vs_wlcip_vs_wrr:wq#Vi/etc/sysctl.confnet.ipv4.ip_forward=0改为：net.ipv4.ip_forward=1使修改生效：/sbin/sysctl-p在MD和BD上安装heartbeat软件包#Rpm-Uvhperl-xx-xx-xx.rpm#Yuminstallheartbeat#Rpm-Uvharptables-noarp-addr-0.99.2-1.rh

36、.el.um.1.noarch.rpm#rpm-Uvhperl-Mail-POP3Client-2.17-1.el5.centos.noarch.rpm缺少perl包，就使用yuminstallperl-xx-xx#Perl-CPAN-eshell这样安装的perl包不知道为何不好使？奇怪这里VIP实际上是绑定在2台director上。所以director之间需要做心跳处理。心跳线使用eth1口，用交叉线连接起来。这样可以避免影响其他服务器。配置heartbeatHeartbeat有3个配置文件：Ha.cfAuthkeysHaresourcesldirectord进程的配置文件Ldirecto

37、rd.cf一共需要配置4个配置文件。#viha.cflogfacilitylocal0bcasteth1mcasteth169410auto_failbackoffnodendb1nodendb2respawnhacluster/usr/lib/heartbeat/ipfailapiauthipfailgid=haclientuid=hacluster:wq#viauthkeysauth33md5514a49f83820e34c877ff48770e48ea7:wq#viharesourcesndb1ldirectord:ldirectord.cfLVSSyncDaemonS

38、wap:masterIPaddr2:05/24/eth0/55Ndb2上需要将主机名更改一下。:wq设置属性并使heartbeat开机启动#chmod600/etc/ha.d/authkeys#/sbin/chkconfig-level2345heartbeaton#/sbin/chkconfig-delldirectord启动heartbeat：/etc/init.d/ldirectordstop/etc/init.d/heartbeatstart在MD和BD上检查VIP是否生效：ipaddrsheth0rootndb1ha.d#ipadd

39、rsheth02:eth0:mtu1500qdiscpfifo_fastqlen1000link/ether00:30:48:28:c6:85brdff:ff:ff:ff:ff:ffinet64/24brd55scopeglobaleth0inet05/24brd55scopeglobalsecondaryeth0inet6fe80:230:48ff:fe28:c685/64scopelinkvalid_lftforeverpreferred_lftforeverrootndb1ha.d#rootndb2#ipaddrsheth02:eth0:mtu1500qdiscpfifo_fastqlen1000link/ether00:30:48:28:c4:afbrdff:ff:ff:ff:ff:ffinet6/24brd55scopeglobaleth0inet6fe80:230:48ff:fe28:c4af/64scopelinkvalid_lftforeverprefer