数据库

CentOS 安装配置 MySQL Cluster 7

•

1. 节点约定

Cluster管理节点（MGMD） 192.168.1.2
Cluster数据节点1（NDBD1） 192.168.1.10
Cluster数据节点2（NDBD2） 192.168.1.11
Cluster查询节点1（MYSQLD1） 192.168.1.20
Cluster查询节点2（MYSQLD2） 192.168.1.21

2. 目录约定

cluster源码路径：/usr/local/src
cluster所有节点安装路径为/usr/local/mysql
cluster管理节点数据路径：/data/mysql-cluster
cluster管理节点配置文件路径：/data/mysql-cluster/config.ini
cluster数据节点数据路径：/data/mysql
cluster查询节点数据路径：/data/mysql
查询节点和数据节点配置文件路径：/etc/my.inf

3. Cluster 管理节点安装

3.1 创建mysql用户和组
# groupadd -g 27 mysql
# useradd -u 27 -g mysql -c "MySQL Server" -d /var/lib/mysql -s /sbin/nologin mysql
3.2 下载二进制mysql cluster
# cd /usr/local/src/
# wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/
mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
3.3 安装cluster管理节点
# tar zxvf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 /usr/local/
# cd /usr/local/
# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 mysql
# cd /usr/local/mysql
# chown -R mysql:mysql .
# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
3.4 添加环境变量
# sed -i '/unset -f pathmunge/a\export PATH=$PATH:/usr/local/mysql/bin' /etc/profile
3.5 添加mysql库搜索路径
# echo "usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
# ldconfig
3.6 修改config.ini配置
# mkdir /data/mysql-cluster
# cp support-files/config.huge.ini /data/mysql-cluster/config.ini
# chmod 644 /data/mysql-cluster/config.ini

修改为如下内容：

######################################################
# MySQL NDB Cluster Huge Sample Configuration File #
######################################################
# This files assumes that you are using at least 9 #
# hosts for running the cluster. Hostnames and paths #
# listed below should be changed to match your setup #
######################################################

[NDBD DEFAULT]
NoOfReplicas: 2
DataDir: /data/mysql
FileSystemPath: /data/mysql

# Data Memory, Index Memory, and String Memory #
DataMemory: 6000M
IndexMemory: 1500M
StringMemory: 5

# Transaction Parameters #
MaxNoOfConcurrentTransactions: 4096
MaxNoOfConcurrentOperations: 100000
MaxNoOfLocalOperations: 100000

# Transaction Temporary Storage #
MaxNoOfConcurrentIndexOperations: 8192
MaxNoOfFiredTriggers: 4000
TransactionBufferMemory: 1M

# Scans and buffering #
MaxNoOfConcurrentScans: 300
MaxNoOfLocalScans: 32
BatchSizePerLocalScan: 64
LongMessageBuffer: 1M

# Logging and Checkpointing #
NoOfFragmentLogFiles: 300
FragmentLogFileSize: 16M
MaxNoOfOpenFiles: 40
InitialNoOfOpenFiles: 27
MaxNoOfSavedMessages: 25

# Metadata Objects #
MaxNoOfAttributes: 1500
MaxNoOfTables: 400
MaxNoOfOrderedIndexes: 200
MaxNoOfUniqueHashIndexes: 200
MaxNoOfTriggers: 770

# Boolean Parameters #
LockPagesInMainMemory: 0
StopOnError: 1
Diskless: 0
ODirect: 0

# Controlling Timeouts, Intervals, and Disk Paging #
TimeBetweenWatchDogCheck: 6000
TimeBetweenWatchDogCheckInitial: 6000
StartPartialTimeout: 30000
StartPartitionedTimeout: 60000
StartFailureTimeout: 1000000
HeartbeatIntervalDbDb: 2000
HeartbeatIntervalDbApi: 3000
TimeBetweenLocalCheckpoints: 20
TimeBetweenGlobalCheckpoints: 2000
TransactionInactiveTimeout: 0
TransactionDeadlockDetectionTimeout: 1200
DiskSyncSize: 4M
DiskCheckpointSpeed: 10M
DiskCheckpointSpeedInRestart: 100M
ArbitrationTimeout: 10

# Buffering and Logging #
UndoIndexBuffer: 2M
UndoDataBuffer: 1M
RedoBuffer: 32M
LogLevelStartup: 15
LogLevelShutdown: 3
LogLevelStatistic: 0
LogLevelCheckpoint: 0
LogLevelNodeRestart: 0
LogLevelConnection: 0
LogLevelError: 15
LogLevelCongestion: 0
LogLevelInfo: 3
MemReportFrequency: 0

# Backup Parameters #
BackupDataBufferSize: 2M
BackupLogBufferSize: 2M
BackupMemory: 64M
BackupWriteSize: 32K
BackupMaxWriteSize: 256K

[MGM DEFAULT]
PortNumber: 1186
DataDir: /data/mysql-cluster

[TCP DEFAULT]
SendBufferMemory: 2M

#######################################
# Change HOST1 to the name of the NDB_MGMD host
# Change HOST2 to the name of the NDB_MGMD host
# Change HOST3 to the name of the NDB_MGMD host
# Change HOST4 to the name of the NDBD host
# Change HOST5 to the name of the NDBD host
# Change HOST6 to the name of the NDBD host
# Change HOST7 to the name of the NDBD host
# Change HOST8 to the name of the NDBD host
# Change HOST9 to the name of the NDBD host
#######################################

[NDB_MGMD]
NodeId: 1
HostName: 192.168.1.2
ArbitrationRank: 1

#[NDB_MGMD]
#NodeId: 2
#HostName: HOST2
#ArbitrationRank: 1

#[NDB_MGMD]
#NodeId: 3
#HostName: HOST3
#ArbitrationRank: 1

[NDBD]
NodeId: 10
HostName: 192.168.1.10

[NDBD]
NodeId: 11
HostName: 192.168.1.11

#[NDBD]
#NodeId: 12
#HostName: HOST12

#[NDBD]
#NodeId: 13
#HostName: HOST13

#[NDBD]
#NodeId: 14
#HostName: HOST14

#[NDBD]
#NodeId: 15
#HostName: HOST15

######################################################
# Note: The following can be MySQLD connections or #
# NDB API application connecting to the cluster #
######################################################

[API]
NodeId: 20
HostName: 192.168.1.20
ArbitrationRank: 2

[API]
NodeId: 21
HostName: 192.168.1.21
ArbitrationRank: 2

[API]
NodeId: 22

[API]
NodeId: 23

[API]
NodeId: 24

[API]
NodeId: 25
3.7 启动管理节点
# ndb_mgmd -f /data/mysql-cluster/config.ini

如果修改了配置文件，请用以下命令启动

# ndb_mgmd -f /data/mysql-cluster/config.ini –reload

4. Cluster 数据节点安装

4.1 创建mysql用户和组
# groupadd -g 27 mysql
# useradd -u 27 -g mysql -c "MySQL Server" -d /var/lib/mysql -s /sbin/nologin mysql
4.2 下载二进制mysql cluster
# cd /usr/local/src/
# wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/
mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
4.3 安装cluster数据节点
# tar zxvf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 /usr/local/
# cd /usr/local/
# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 mysql
# cd /usr/local/mysql
# chown -R mysql:mysql .
# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
4.4 添加环境变量
# sed -i '/unset -f pathmunge/a\export PATH=$PATH:/usr/local/mysql/bin' /etc/profile
4.5 添加mysql库搜索路径
# echo "usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
# ldconfig
4.6 配置数据节点
# cp support-files/my-huge.cnf /etc/my.cnf
# vim /etc/my.cnf

添加如下内容

[mysqld]
ndbcluster # run NDB storage engine
ndb-connectstring=192.168.1.2 # location of management server

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
log-error = /data/mysql/mysqld.log
skip-name-resolve

# Options for ndbd process:
[mysql_cluster]
ndb-connectstring=192.168.1.2 # location of management server
4.7 启动数据节点
# ndbd –initial

第二次启动请用ndbd，不要加—initial参数，不然数据全没了

# ndbd

5. Cluster 查询节点安装

5.1 创建mysql用户和组
# groupadd -g 27 mysql
# useradd -u 27 -g mysql -c "MySQL Server" -d /var/lib/mysql -s /sbin/nologin mysql
5.2 下载二进制mysql cluster
# cd /usr/local/src/
# wget http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.2/
mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
5.3 安装cluster数据节点
# tar zxvf mysql-cluster-gpl-7.2.6-linux2.6-x86_64.tar.gz
# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 /usr/local/
# cd /usr/local/
# mv mysql-cluster-gpl-7.2.6-linux2.6-x86_64 mysql
# cd /usr/local/mysql
# chown -R mysql:mysql .
# scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql
5.4 添加环境变量
# sed -i '/unset -f pathmunge/a\export PATH=$PATH:/usr/local/mysql/bin' /etc/profile
5.5 添加mysql库搜索路径
# echo "usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
# ldconfig
5.6 配置查询节点
# cp support-files/my-huge.cnf /etc/my.cnf
# vim /etc/my.cnf

添加如下内容

[mysqld]
ndbcluster # run NDB storage engine
ndb-connectstring=192.168.1.2 # location of management server

basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
log-error = /data/mysql/mysqld.log
skip-name-resolve

# Options for ndbd process:
[mysql_cluster]
ndb-connectstring=192.168.1.2 # location of management server
5.7 配置查询节点服务
# cp support-files/mysql.server /etc/init.d/mysqld
# chmod 755 /etc/init.d/mysqld
5.8 启动查询节点
# service mysqld start

6. 关闭集群

# ndb_mgm -e shutdown

这时候会陆续关闭数据节点和管理节点，最后关闭查询节点

# service mysqld stop

注意点如下：

1.启动顺序必须是管理节点-》数据节点-》查询节点

2.在mysql集群中.当table引擎为NDBCLUSTER时才做集群，其他非NDBCLUSTER表和一般mysql数据库表一样，不会共享数据. NDBCLUSTER 表数据存储在Data node服务器内存中，Data Node可以为1台或多台服务器，它们之间存放共享数据，Data Node服务器可以分组数据copy，因此在建表的时候一定要用ENGINE=NDB或ENGINE=NDBCLUSTER指定使用NDB集群存储引擎，或用ALTER TABLE选项更改表的存储引擎

例如：2,3,4,5 为四台Data Node服务器ID. 2,3为组0。 4，5为组1， 2，3维持数据相同， 4，5维持数据相同， 组0和组1维持数据不同

3.sql node 服务器中，非NDBCLUSTER数据存在本身数据库中，table引擎为NDBCLUSTER时，数据存储在Data Node 中。当查询NDBCLUSTER表时，它会从Data node集群中提起数据

4.NDB表必须有一个主键，因此创建表的时候必须定义主键，否则NDB存储引擎将自动生成隐含的主键

5.Sql节点的用户权限表仍然采用MYISAM存储引擎保存的，所以在一个Sql节点创建的MySql用户只能访问这个节点，如果要用同样的用户访问别的Sql节点，需要在对应的Sql节点追加用户。虽然在MySql Cluster7.2版本开始提供了”用户权限共享”

6.DataMemory和IndexMemory大小按照按数据库大小设置，官方给出了公式
(内存) 数据大小 *副本 * 1.25 = 数据库内存总需求
实例:50 GB * 2 * 1.25 = 125 GB
(数据大小 *副本 * 1.25)/节点数 = 每个节点的内存大小
实例:(2 GB * 2 * 1.25)/4 = 1.25 GB