Windows下mysql集群的搭建

ゞ浴缸里的玫瑰 2022-08-25 15:57 225阅读 0赞

## Mysql cluster环境搭建 ##

#### 硬件环境 ####

需要5台服务器

Win1：  IP:192.168.2.2 (管理节点)

Win2：  IP:192.168.2.3 (数据节点)

Win3：  IP:192.168.2.4 (数据节点)

Win4：  IP:192.168.2.5 (sql节点)

Win5：  IP:192.168.2.13 (sql节点)

#### 软件环境 ####

Window server 2008

#### 搭建步骤 ####

l  管理节点（192.168.2.2）安装：

1、  解压mysql-cluster-gpl-7.2.9-win64.zip包，开始Managementnode的安装配置。注意Management node一定要安装在C盘下。

2、  进入目录新建config.ini，配置内容如下：

![Image 1][]![20140317001443296][]

config.ini内容为：

<table> 
 <tbody> 
 <tr> 
 <td> # Options affecting ndbd processes on all data nodes: [ndbd default] #NoOfReplicas=2&nbsp;&nbsp;&nbsp; # Number of replicas #DataMemory=3072M&nbsp;&nbsp;&nbsp; # How much memory to allocate for data storage #DataMemory=512M #IndexMemory=256M&nbsp;&nbsp; # How much memory to allocate for index storage &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # For DataMemory and IndexMemory, we have used the &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # default values. Since the "world" database takes up &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # only about 500KB, this should be more than enough for &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; # this example Cluster setup. #StringMemory=25 &nbsp; #ODirect=1 #MaxNoOfLocalScans=64 &nbsp; MaxNoOfTables=3000 #MaxNoOfOrderedIndexes=2048 #MaxNoOfUniqueHashIndexes=512 MaxNoOfAttributes=4000 #MaxNoOfAttributes=24576 #MaxNoOfTriggers=14336 #MaxNoOfConcurrentOperations=5000000 &nbsp; #MaxAllocate=50M &nbsp; #LockPagesInMainMemory=1 &nbsp; #MaxNoOfConcurrentTransactions=16384 &nbsp; #NoOfFragmentLogFiles=48&nbsp; &nbsp; &nbsp; [NDB_MGMD]&nbsp; NodeId=1&nbsp; HostName=192.168.2.2 &nbsp;#管理节点服务器&nbsp; # Storage Engines&nbsp; # DataDir=C:/mysql/bin/cluster-logs&nbsp; &nbsp; [NDBD]&nbsp; NodeId=2&nbsp; HostName=192.168.2.3 &nbsp;#MySQL集群db1的IP地址&nbsp; #DataDir= D:/Program Files/mysqlcluster/datanode/mysql/bin/cluster-data #如果不存在就创建一个&nbsp; &nbsp; [NDBD]&nbsp; NodeId=3&nbsp; HostName=192.168.2.4 &nbsp;#MySQL集群db2的IP地址&nbsp; #DataDir= D:/Program Files/mysqlcluster/datanode/mysql/bin/cluster-data&nbsp; #如果不存在就创建一个&nbsp; &nbsp; [MYSQLD]&nbsp; NodeId=4&nbsp; HostName=192.168.2.5 &nbsp; [MYSQLD]&nbsp; NodeId=5&nbsp; HostName=192.168.2.13 </td> 
 </tr> 
 </tbody> 
</table>

3、  启动管理节点

在cmd命令行下cd 到集群/bin目录下执行：ndb\_mgmd –f  config.ini，出现以下字样表明成功。

![Image 1][]![20140317001516625][]

l  数据节点（192.168.2.3、192.168.2.4）安装

1、  解压mysql-cluster-gpl-7.2.9-win64.zip包，开始data node的安装配置。拷贝一份my-default修改名称为my.ini。

![Image 1][]![20140317001537734][]

2、  修改my.ini文件，配置内容如下：

<table> 
 <tbody> 
 <tr> 
 <td> # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [mysql_cluster]&nbsp; # Options for data node process:&nbsp; ndb-connectstring=192.168.2.2 [mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin &nbsp; # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... &nbsp; &nbsp; # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M &nbsp; sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES </td> 
 </tr> 
 </tbody> 
</table>

3、  启动数据节点

分别在两台服务器上执行，在cmd命令行下cd 到集群/bin目录下执行：ndbd，出现以下字样表明成功。

![Image 1][]![20140317001547375][]

l  sql节点（192.168.2.5、192.168.2.13）安装

1、  解压mysql-cluster-gpl-7.2.9-win32.zip包，开始sql node的安装配置。拷贝一份my-default修改名称为my.ini。

2、  修改my.ini文件，配置内容如下：

<table> 
 <tbody> 
 <tr> 
 <td> # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. &nbsp; [mysqld] ndbcluster ndb-connectstring=192.168.2.2 # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M &nbsp; # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin &nbsp; # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... &nbsp; &nbsp; # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M &nbsp; sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES </td> 
 </tr> 
 </tbody> 
</table>

3、  启动sql节点

分别在两台服务器上执行，在cmd命令行下cd 到集群/bin目录下执行：mysqld --console，出现以下字样表明成功。

![20140317001557125][]

![Image 1][]

![Image 1][]![20140317001605937][]

## 测试集群 ##

1、调用集群自带的管理工具查看各个节点状态：在cmd命令行下cd 到集群/bin目录下执行：ndb\_mgm à show 出现以下内容，表明节点正常启动。

![Image 1][]![20140317001614375][]

2、  错误提示

![Image 1][]![20140317001625796][]

3、  使用mysql进行管理工作，在cmd命令行下cd 到集群/bin目录下执行：

mysql –uroot –p   (root默认的密码为空)。

![Image 1][]![20140317001636312][]

接下来跟操作单机数据库没啥区别。

注意在创建表的时候记得加入engine = ndbcluster，这样的表在数据节点中才能共享。

4、  关闭其中一台sql节点或数据节点，测试是否集群还能使用。

[Image 1]: 
[20140317001443296]: /images/20220824/4549f1baf99b445c93e2f9f8f2073d78.png
[20140317001516625]: /images/20220824/d81f6e79d5f243138c8acc2674912a89.png
[20140317001537734]: /images/20220824/674a2e88f9134fa8bc2c3d81266af053.png
[20140317001547375]: /images/20220824/19b019837c8e471bba5a64135226cf10.png
[20140317001557125]: /images/20220824/4b47cf0c170a4b53a1f5e0d1f23a5339.png
[20140317001605937]: /images/20220824/7e7f73d88877496b9acc583fc2c02301.png
[20140317001614375]: /images/20220824/c2bcb7c13cd54861923eb6a29431b3d0.png
[20140317001625796]: /images/20220824/f1ec673780dd478b8cc7af6a959c1b22.png
[20140317001636312]: /images/20220824/456452973636436b927cc9edfc25e136.png