mysql集群配置文档.docx

1、mysql集群配置文档MySQL Cluster Installation一、介绍如何安装配置基于2台服务器的MySQL集群。并且实现任意一台服务器出现问题或宕机时MySql集群依然能够继续运行。可以实现Mysql双机的高可用及负载均衡。安装环境及软件包：Red Hat Enterprise Linux 4 RPM (x86)Server6.3.2019.4MPick a mirrorMD5: f65b8effac15c290c850ae1204442e4bClient6.3.207.0MPick a mirrorMD5: 460945780aab679aafd9eec51b9d0447Clu

2、ster storage engine6.3.201.6MPick a mirrorMD5: 370121daafe8f5b8fcc04a56d069e8a4Cluster storage engine management6.3.201.1MPick a mirrorMD5: 484c42ebe14e31efda62501a4e2d3cb6Cluster storage engine basic tools6.3.208.0MPick a mirrorMD5: d291e63d36ad7b10cbbad505f1ce65adCluster storage engine extra tools

3、6.3.203.9MPick a mirrorServer1: 192.168.24.21Server2: 192.168.24.22二、在Server1和Server2上安装MySQL以下步骤需要在Server1和Server2上各做一次卸载老版本：# rpm qa | grep mysql -检查是否已经安装过mysql，如果装过先将以前的版本卸载掉如上图所示需要先把mysqlclient10-3.23.58-4.RHEL4.1进行卸载：# rpm e mysqlclient10-3.23.58-4.RHEL4.1进入下载的文件所在的目录，依次运行以下命令：# rpm ivh MySQL-

4、Cluster-gpl-server-6.3.20-0.rhel4.i386.rpm# rpm ivh MySQL-Cluster-gpl-client-6.3.20-0.rhel4.i386.rpm# rpm ivh MySQL-Cluster-gpl-storage-6.3.20-0.rhel4.i386.rpm# rpm ivh MySQL-Cluster-gpl-management-6.3.20-0.rhel4.i386.rpm# rpm ivh MySQL-Cluster-gpl-tools-6.3.20-0.rhel4.i386.rpm# rpm ivh MySQL-Cluste

5、r-gpl-extra-6.3.20-0.rhel4.i386.rpm# ln -s /usr/libexec/ndbd /usr/bin# ln -s /usr/libexec/ndb_mgmd /usr/bin# ln -s /usr/libexec/ndb_cpcd /usr/bin# ln -s /usr/libexec/mysqld /usr/bin# ln -s /usr/libexec/mysqlmanager /usr/bin# mysql_install_db -user=mysql三、安装并配置节点以下步骤需要在Server1和Server2上各做一次1.配置管理节点配置文

6、件：# mkdir /var/lib/mysql-cluster# cd /var/lib/mysql-cluster# vi config.ini在config.ini中添加如下内容：ndbd defaultNoOfReplicas= 2MaxNoOfConcurrentOperations= 10000DataMemory= 80MIndexMemory= 24MTimeBetweenWatchDogCheck= 30000DataDir= /var/lib/mysql-clusterMaxNoOfOrderedIndexes= 512StartPartialTimeout=100Star

7、tPartitionedTimeout=100ArbitrationTimeout=5000TransactionDeadlockDetectionTimeout=5000HeartbeatIntervalDbDb=5000StopOnError=0ndb_mgmd defaultDataDir= /var/lib/mysql-clusterndb_mgmdId=1HostName= 192.168.24.21ndb_mgmdId=2HostName= 192.168.24.22ndbdId= 3HostName= 192.168.24.21ndbdId= 4HostName= 192.168

8、.24.22mysqldArbitrationRank=2 (非常重要,全靠有它,才可以形成仲裁竞争,从而当另一个机子当了时,此机还可以有知道partion完整的节点)mysqldArbitrationRank=2tcp defaultPortNumber= 631322.配置通用f文件，mysqld及ndbd,ndb_mgmd均使用此文件.# vi /etc/f在f中添加如下内容：mysqlddefault-storage-engine=ndbcluster 避免在sql语句中还要加入ENGINE=NDBCLUSTER。ndbcluster 这个要加上，否则mysql会启动不了ndb-con

9、nectstring=192.168.24.21,192.168.24.22ndbdconnect-string=192.168.24.21,192.168.24.22ndb_mgmconnect-string=192.168. 24.21,192.168.24.22ndb_mgmdconfig-file=/var/lib/mysql-cluster/config.inimysql_clusterndb-connectstring= 192.168. 24.21,192.168.24.22保存退出后。3.分别启动Server1、Server2的mysql服务：# service mysql s

10、tart启动mysql之前一定要先保证ndbd和ndb_mgmd没有启动，否则会导致mysql启动不了4.启动管理节点Server1为：# ndb_mgmd -ndb_nodeid=1启动管理节点Server2为：# ndb_mgmd -ndb_nodeid=2注:在启动时有一个警告提示Cluster configuration warning: arbitrator with id 1 and db node with id 3 on same host 192.168.24.21 arbitrator with id 2 and db node with id 4 on same host

11、 192.168.24.22 Running arbitrator on the same host as a database node may cause complete cluster shutdown in case of host failure.说节点1和3，2和4的arbitrator一样，可能引起整个集群失败。（可以不用放在心上）5. 自动重起功能的添加：chkconfig -add mysqlchkconfig level 345 mysql onrootmysql1 mysql#vi /etc/rc.local后添加如下一行/usr/local/mysql/bin/ndb

12、_mgmd -f /var/lib/mysql-cluster/config.ini四、初始化集群在Server1中# ndbd -nodeid=3 -initial在Server2中# ndbd -nodeid=4 -iniitial注：只有在第一次启动ndbd时或者对config.ini进行改动后才需要使用-initial参数！五、检查工作状态在任意一台机子上启动管理终端：# ndb_mgm键入show命令查看当前工作状态：（下面是一个状态输出示例）- NDB Cluster - Management Client -ndb_mgm showConnected to Management

13、Server at: 192.168.24.21:1186Cluster Configuration-ndbd(NDB) 2 node(s)id=3 192.168.24.21 (Version: 5.2.3, Nodegroup: 0, Master)id=4 192.168. 24.22 (Version: 5.2.3, Nodegroup: 0)ndb_mgmd(MGM) 2 node(s)id=1 192.168.24.21 (Version: 5.2.3)id=2 192.168.24.22 (Version: 5.2.3)mysqld(API) 2 node(s)id=5 (not

14、 connected, accepting connect from any host)id=6 (not connected, accepting connect from any host)ndb_mgm如果上面没有问题，现在开始加入mysqld(API)：注意，这篇文档对于MySQL并没有设置root密码，推荐你自己设置Server1和Server2的MySQL root密码。在Server1 中：#mysqld_safe -ndb_nodeid=5 -user=mysql &在Server2 中：#mysqld_safe -ndb_nodeid=6 -user=mysql &# ndb

15、_mgm -e show信息如下：Connected to Management Server at: 192.168.1.111:1186Cluster Configuration-ndbd(NDB) 2 node(s)id=3 192.168.1.111 (Version: 5.2.3, Nodegroup: 0, Master)id=4 192.168.1.110 (Version: 5.2.3, Nodegroup: 0)ndb_mgmd(MGM) 2 node(s)id=1 192.168.1.111 (Version: 5.2.3)id=2 192.168.1.110 (Versi

16、on: 5.2.3)mysqld(API) 4 node(s)id=5 192.168.1.111 (Version: 5.2.3)id=6 192.168.1.110 (Version: 5.2.3)ok，可以测试了：在Server1 中# /usr/local/mysql/bin/mysql -u root -pcreate database aa; use aa; CREATE TABLE ctest (i INT) ； INSERT INTO ctest () VALUES (1); SELECT * FROM ctest;应该可以看到1 row returned信息（返回数值1）。如

17、果上述正常，则换到Server2，观察效果。如果成功，则在Server2中执行INSERT再换回到Server1观察是否工作正常。如果都没有问题，那么恭喜成功！六、破坏性测试将Server1或Server2的网线拔掉（即ifconfig eth0 down)，观察另外一台集群服务器工作是否正常（可以使用SELECT查询测试）。测试完毕后，重新插入网线即可。注意：在未对集群做任何读写操作前，此测试结果无效，因为，集群初始后只在/var/lib/mysql-cluster/下建了几个空目录，还没有正常协同工作，会出现整个所有存储(ndbd)节点关闭.也可以这样测试：在Server1或Server2

18、上：# ps aux | grep ndbd将会看到所有ndbd进程信息：root 5578 0.0 0.3 6220 1964 ? S 03:14 0:00 ndbdroot 5579 0.0 20.4 492072 102828 ? R 03:14 0:04 ndbdroot 23532 0.0 0.1 3680 684 pts/1 S 07:59 0:00 grep ndbd然后杀掉一个ndbd进程以达到破坏MySQL集群服务器的目的：# kill -9 5578 5579之后在另一台集群服务器上使用SELECT查询测试。并且在管理节点服务器的管理终端中执行show命令会看到被破坏的那台

19、服务器的状态。测试完成后，只需要重新启动被破坏服务器的ndbd进程即可：# ndbd -ndb_nodeid=此存储节点的id注意！前面说过了，此时是不用加-inital参数的！至此，MySQL双机集群就配置完成了！七、正确的安装log（提供参考）rootCT151 tmp# rpm -ivh MySQL-Cluster-gpl-server-6.3.20-0.rhel4.i386.rpmwarning: MySQL-Cluster-gpl-server-6.3.20-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5Prepa

20、ring. # 100% 1:MySQL-Cluster-gpl-serve# 100%PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !To do so, start the server, then issue the following commands:/usr/bin/mysqladmin -u root password new-password/usr/bin/mysqladmin -u root -h CT151 password new-passwordAlternatively you can run:/u

21、sr/bin/mysql_secure_installationwhich will also give you the option of removing the testdatabases and anonymous user created by default. This isstrongly recommended for production servers.See the manual for more instructions.Please report any problems with the /usr/bin/mysqlbug script!The latest inf

22、ormation about MySQL is available at Support MySQL by buying support/licenses from Notes regarding SELinux on this platform:=The default policy might cause server startup to fail because it is not allowed to access critical files. In this case, please update your installation. The default policy mig

23、ht also cause inavailability of SSL related features because the server is not allowed to access /dev/random and /dev/urandom. If this is a problem, please do the following: 1) install selinux-policy-targeted-sources from your OS vendor 2) add the following two lines to /etc/selinux/targeted/src/pol

24、icy/domains/program/mysqld.te: allow mysqld_t random_device_t:chr_file read; allow mysqld_t urandom_device_t:chr_file read; 3) cd to /etc/selinux/targeted/src/policy and issue the following command: make loadrootCT151 tmp# rpm -ivh MySQL-Cluster-gpl-client-6.3.20-0.rhel4.i386.rpmwarning: MySQL-Clust

25、er-gpl-client-6.3.20-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5Preparing. # 100% 1:MySQL-Cluster-gpl-clien# 100%rootCT151 tmp# rpm -ivh MySQL-Cluster-gpl-storage-6.3.20-0.rhel4.i386.rpmwarning: MySQL-Cluster-gpl-storage-6.3.20-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f

26、5Preparing. # 100% 1:MySQL-Cluster-gpl-stora# 100%rootCT151 tmp# rpm -ivh MySQL-Cluster-gpl-management-6.3.20-0.rhel4.i386.rpmwarning: MySQL-Cluster-gpl-management-6.3.20-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5Preparing. # 100% 1:MySQL-Cluster-gpl-manag# 100%rootCT151 tmp# rpm -iv

27、h MySQL-Cluster-gpl-tools-6.3.20-0.rhel4.i386.rpmwarning: MySQL-Cluster-gpl-tools-6.3.20-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5Preparing. # 100% 1:MySQL-Cluster-gpl-tools# 100%rootCT151 tmp# rpm -ivh MySQL-Cluster-gpl-extra-6.3.20-0.rhel4.i386.rpmwarning: MySQL-Cluster-gpl-extra-6.3.20-0.rhel4.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5Preparing. # 100% 1:MySQL-Cluster-gpl-extra# 100%rootCT151 tmp# ln -s /usr/libexec/ndbd /usr/binrootCT151 tmp# ln -s /usr/libexec/ndb_mgmd /usr/binrootCT151 tmp# ln -s /usr/libexec/ndb_cpcd /usr/binrootCT151 tmp# ln -s /us