Linux下配置MySQLCluster7.2

最新推荐文章于 2021-09-21 08:53:09 发布

原创最新推荐文章于 2021-09-21 08:53:09 发布 · 697 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据 #配置 #服务器 #mysql #clustering

MySQL 同时被 2 个专栏收录

6 篇文章

订阅专栏

综合

5 篇文章

订阅专栏

于2012.7.29晚

Cluster最小可以在一台机器上配置完成，也可以在两台机器上配置，推荐最小在三台机器上进行配置！
Mysql Cluster 的管理节点的默认端口是1186，数据节点的默认端口是2202

一：系统环境

MGMNode—————–CentOS桌面版IP：192.168.25.112
dataNode——————-Ubuntu服务器版IP：192.168.25.111
sqlNode———————Ubuntu桌面版IP：192.168.25.97
dataNode——————-Ubuntu服务器版IP：192.168.25.111

从官网上下载mysql-cluster-gpl-7.2.6-linux2.6-i686.tar.gz

二：基本配置

1.配置mgmNode：（主要是一个配置文件config.ini和mgmd和mgm）

>>tar-C/usr/tmp/ –zxvfmysql-cluster-gpl-7.2.6-linux2.6-i686.tar.gz
>>cp/usr/tmp/mysql-cluster-gpl-7.2.6-linux2.6-i686/bin/mgm*/usr/bin
>>mkdir/var/lib/mysql-cluster(可以自定义的)
>>vi/var/lib/mysql-cluster/config.ini

接下来配置config.ini文件

[ndb default]
NoOfReplicas=2
DataMemory=80M
IndexMemory=18M

[ndb_mgmd]
hostname=192.168.25.112
datadir=/var/lib/mysql-cluster
[ndbd]
hostname=192.168.25.111
datadir=/usr/local/data-node/mysql/data
[ndbd]
hostname=192.168.25.111
datadir=/usr/local/data-node/mysql/data
[mysqld]
hostname=192.168.25.97

至此manage节点配置全部完成
2.配置data节点和sql节点（主要是一个配置文件：my.cnf和/usr/local/sql-node/和/usr/local/data-node目录）

>>tar     -C     /usr/tmp/      –zxvf     mysql-cluster-gpl-7.2.6-linux2.6-i686.tar.gz
>>cp     /usr/tmp/mysql-cluster-gpl-7.2.6-linux2.6-i686     /usr/local/
>>ln      –s     /usr/local/mysql-cluster-gpl-7.2.6-linux2.6-i686     /usr/local/mysql
>>mkdir     /usr/local/sql-node/     /usr/local/data-node
>>cp     /usr/local/mysql/     /usr/local/sql-node/
>>cp     /usr/local/mysql/     /usr/local/data-node/
>>cd     /usr/local/mysql
>>vi     /etc/my.cnf

接下来配置etc/my.cnf：(用作data节点的配置文件)

[ndb_cluster]
connectstring=192.168.25.112
配置：etc/sql-note/my.cnf:(用作sql节点的配置文件)
[mysqld]
ndbcluster
default-storage-engine=NDBCLUSTER
ndb-connectstring=192.168.25.112

当sql节点和data节点的文件都配置好了之后，要对相应及相应文件夹进行附权和自启动设置：
具体命令如下（根据前面的文件路径）
2.1.将当前目录切换成mysql文件夹目录下然后运行给出的script文件来创建系统数据库具体命令为：shell> cd mysqlshell> scripts/mysql_install_db –user=mysql2.2.然后为MySQL服务器和数据目录设定必要的权限
具体命令为：
shell> chown -R root .shell> chown -R mysql datashell> chgrp -R mysql .

2.3.将MySQL安装脚本复制到适当的目录，令它可以执行，然后设定它当操作系统启动的时候来运行
具体命令为：shell> cp support-files/mysql.server /etc/rc.d/init.d/shell> chmod +x /etc/rc.d/init.d/mysql.servershell> chkconfig –add mysql.server

至此全部配置完成.
启动：

先启动管理节点：

>>如果是第一次启动或者是修改了config.ini配置文件，则执行以下命令使之生效
>>ndb_mgmd    -f    /var/lib/mysql-cluster/config.ini     --initial     --configdir=/var/lib/mysql-cluster

>>ndb_mgmd      –f      /var/lib/mysql-cluster/config.ini      --configdir=/var/lib/mysql-cluster

启动data节点

ndbd     -- ndb-connectstring=192.168.25.112

启动sql节点

前面目录/bin/mysqld –-defaults-file=”etc/sql-node/my.cnf” （读取自定义的my.cnf文件，这里要mkdir /etc/sql-node/）

至此基本的Cluster 就安装配置完成了。

三：问题及注意

Linux系统版本为32位安装不了cluster 64位，反之亦然
在启动管理节点的时候（第一次初始化启动了，但随后又修改了配置文件config.ini 要使修改的内容生效，应该在启动管理节点的时候加上initial参数)
在启动ndbd时找不到命令，应该输入/usr/local/data-node/mysql/bin/ndbd 来启动(全部路径)
在启动sql节点时候由于/usr/local/mysql/文件夹下默认的装有mysql server，启动sql节点时在/etc/my.cnf配置文件中产生了冲突。所以应该分开放置配置文件，这里把配置文件放到了/etc/sql-node/my.cnf中，（启动sql节点的时候应该指明配置文件位置）
一般而言，每个数据节点上磁盘配置空间应为 DataMemory 7倍。该空间用于存储 2 个局部检查点（ LCP）、重做（ Redo）日志和 3个备份文件。如果您使用磁盘数据，将需要为其分配空间 —— 包括对预留额外空间进行备份。
mgm节点启动：ndb-mgmd -f 或–config-file “配置文件”
ndbd –initial：只有初次启动或者在一个备份、还原操作或者一项配置改变之后重启的
关闭cluster：ndb_mgm -e shutdown

补充（配置参数及启动参数）

关于配置文件记录
config.ini文件
[mysql_cluster]：这个参数可以被所有可执行的程序读取操作，如ndb-connectstring=hostname
[COMPUTER]：定义簇主机
[NDBD]：定义数据节点
[MYSQLD]：定义集群的mysql服务器节点
[MGM]或[NDB_MGMD]：定义管理服务器节点
[TCP]：定义节点之间的TCP、ip连接，TCP/IP为默认的协议
[SHM]：定义节点见的共享内存连接

ndbd Options and Variables: MySQL 5.5

ndbd Options and Variables

Table 17.12. ndb_mgmd Options and Variables: MySQL 5.5

这里写图片描述

关于mysql-cluster配置文件记录

config.ini：

# TCP PARAMETERS -------------------------------------------------
[tcp default]
SendBufferMemory =2M 
ReceiveBufferMemory =2M 
# Increasing the sizes of these 2 buffers beyond the default values 
# helps prevent bottlenecks due to slow disk I/O. 
# MANAGEMENT NODE PARAMETERS -------------------------------------------------------------------

[ndb_mgmd default] 
DataDir =C:/mysql-cluster/mgm-node/logs

# It is possible to use a different data directory for each management 
# server, but for ease of administration it is preferable to be 
# consistent. 

[ndb_mgmd] 
HostName=192.168.21.111
# NodeId =management-server-A-nodeid 

#[ndb_mgmd] 
# HostName =192.168.24.24
# NodeId =management-server-B-nodeid 

# Using 2 management servers helps guarantee that there is always an 
# arbitrator in the event of network partitioning
# DATA NODE PARAMETERS ------------------------------------------------------------------------
[ndbd default] 
NoOfReplicas =2 

# Using 2 replicas is recommended to guarantee availability of data; 
# using only 1 replica does not provide any redundancy, which means 
# that the failure of a single data node causes the entire cluster to 
# shut down. We do not recommend using more than 2 replicas, since 2 is 
# sufficient to provide high availability, and we do not currently test 
# with greater values for this parameter. 

###################Disk data configuration parameters#################

#DiskPageBufferMemory=

#the default value is 64M(2000 pages of 32kb each),this determines the amount of space used for caching pages on disk.

#SharedGlobalMemory=

#this parameter determines the amount of memory that is used for log buffers,disk operations (such as page requests and wait queues),and metadata for tablespaces,log file groups,undo files,and data files.the shared global memory pool also provides memory used for satisfying the memory requirements of the INITIAL_SIZE and UNDO_BUFFER_SIZE options used with CREATE LOGFILE GROUP and ALTER LOGFILE GROUP statements,including any default value implied for these options by the setting of the InitialLogFileGroup data node configuration parameter.
NDB 7.2.0 ,the default value is 128M(0-64T)

#DiskIOThreadPool=

#this parameter determines the number of unbound threads used for Disk Data file access.Currently,this parameter applies to Disk Data I/O.You can obtain better performance by placing data files, undo log files,and the data node file system on separate physical disks.then you can set this parameter higher to enable separate threads to headle the files on each disk.if you use solid-state disk drives ,which can handle many more disk i/o threads thand conventional disks,request higher values for this parameter.the default value is 2.

#FileSystemPathDataFiles=

#if this parameter is specified ,then mysql cluster disk Data data files are placed in the indicated directory.

#FileSystemPathUndoFiles=

#is this parameter is specified, then mysql cluster disk data undo log files are placed in the indicated directory.

#InitialLogFileGroup=string

#this parameter can be used to specify a log file group that is created when performing an initial start of the cluster.
#example:InitialLogFileGroup=name=LG1;undo_buffer_size=128M;undo1.log:250M;undo2.log:150M

#ExtraSendBufferMemory=

#this parameter specifies the amount of transporter send buffer memory to allocate in addition to any set using TotalSendBufferMemory,SendBufferMemory,or both

#TotalSendBufferMemory=

#this parameter is used to determine the total amount of memory to allocate on this node for shared send buffer memory among all configured transporters.its minimum permitted value is 256KB, maxMimum is 4394967039

#InitialLogFileGroup=name=LG1; undo_buffer_size=128M;undo1.log:250M;undo2.log:150M
#InitialTablespace=name=TS1; extent_size=10M;data1.dat:2G;data2.dat:4G

#BackupDataDir=/usr/ming/install-file/data

#FileSystemPathDataFiles=/usr/ming/install-file/data/disk-data
#FileSystemPathUndoFiles=/usr/ming/install-file/data/undo-logdata

#LockPagesInMainMemory =1 

# On Linux and Solaris systems, setting this parameter locks data node 
# processes into memory. Doing so prevents them from swapping to disk,
# which can severely degrade cluster performance. 

DataMemory =700M 
IndexMemory =284M

# The values provided for DataMemory and IndexMemory assume 4 GB RAM 
# per data node. However, for best results, you should first calculate 
# the memory that would be used based on the data you actually plan to 
# store (you may find the ndb_size.pl utility helpful in estimating 
# this), then allow an extra 20% over the calculated values. Naturally, 
# you should ensure that each data node host has at least as much 
# physical memory as the sum of these two values. 

# ODirect =1 

# Enabling this parameter causes NDBCLUSTER to try using O_DIRECT 
# writes for local checkpoints and redo logs; this can reduce load on 
# CPUs. We recommend doing so when using MySQL Cluster on systems running 
# Linux kernel 2.6 or later. 

NoOfFragmentLogFiles =190
#this param can be calculate by formula:6*DataMemory(MB)/64

DataDir =C:/mysql-cluster/data-node/data 
MaxNoOfConcurrentOperations =100000 

SchedulerSpinTimer =400 
SchedulerExecutionTimer =100 
RealTimeScheduler =1 
# Setting these parameters allows you to take advantage of real-time scheduling 
# of NDBCLUSTER threads to get higher throughput. 

TimeBetweenGlobalCheckpoints =1000 
TimeBetweenEpochs =200 
DiskCheckpointSpeed =10M 
DiskCheckpointSpeedInRestart =100M 
RedoBuffer =32M 

# CompressedLCP =1 
# CompressedBackup =1 
# Enabling CompressedLCP and CompressedBackup causes, respectively, local 
#checkpoint files and backup files to be compressed, which can result in a space 
#savings of up to 50% over noncompressed LCPs and backups. 

# MaxNoOfLocalScans =64 
MaxNoOfTables =1024 
MaxNoOfOrderedIndexes =256 

[ndbd] 
HostName =192.168.21.252
# NodeId =data-node-A-nodeid 

#LockExecuteThreadToCPU=1 
#LockMaintThreadsToCPU =0 
# On systems with multiple CPUs, these parameters can be used to lock NDBCLUSTER 
# threads to specific CPUs 

[ndbd] 
HostName =192.168.21.251
# NodeId =data-node-B-nodeid 

#LockExecuteThreadToCPU =1 
#LockMaintThreadsToCPU =0 

# You must have an [ndbd] section for every data node in the cluster; 
# each of these sections must include a HostName. Each section may 
# optionally include a NodeId for convenience, but in most cases, it is 
# sufficient to allow the cluster to allocate node IDs dynamically. If 
# you do specify the node ID for a data node, it must be in the range 1 
# to 48 inclusive and must be unique among all IDs specified for 
# cluster nodes. 

# SQL NODE / API NODE PARAMETERS ------------------------------------------------------------
[mysqld] 
HostName =192.168.24.24
# NodeId =sql-node-A-nodeid 
#AutoReconnect=true

[mysqld]
HostName =192.168.21.21
#AutoReconnect=true

[mysqld] 

[mysqld] 

[mysqld] 

[mysqld]

my.ini：

[mysql_cluster]
ndb-connectstring=192.168.21.111:1186    # host name or ip address

[mysqld]
ndbcluster
ndb-connectstring=192.168.21.111:1186
[ndbd]

[ndbd]

Summary of MySQL Cluster Start Phases

This section provides a simplified outline of the steps involved when MySQL Cluster data nodes are
started. More complete information can be found in MySQL Cluster Start Phases, in the NDB Internals
Guide.
These phases are the same as those reported in the output from the node_id STATUS command
in the management client (see Commands in the MySQL Cluster Management Client). These start
phases are also reported in the start_phase column of the ndbinfo.nodes table.
Start types. There are several different startup types and modes, as shown in the following list:
• Initial start. The cluster starts with a clean file system on all data nodes. This occurs either
when the cluster started for the very first time, or when all data nodes are restarted using the –initial [2049] option.
Note
Disk Data files are not removed when restarting a node using –initial [2049].
• System restart. The cluster starts and reads data stored in the data nodes. This occurs when
the cluster has been shut down after having been in use, when it is desired for the cluster to resume
operations from the point where it left off.
• Node restart. This is the online restart of a cluster node while the cluster itself is running.
• Initial node restart. This is the same as a node restart, except that the node is reinitialized and
started with a clean file system.
Setup and initialization (phase -1). Prior to startup, each data node (ndbd process) must be
initialized. Initialization consists of the following steps:
1. Obtain a node ID
Summary of MySQL Cluster Start Phases
2112
2. Fetch configuration data
3. Allocate ports to be used for inter-node communications
4. Allocate memory according to settings obtained from the configuration file
When a data node or SQL node first connects to the management node, it reserves a cluster node
ID. To make sure that no other node allocates the same node ID, this ID is retained until the node has
managed to connect to the cluster and at least one ndbd reports that this node is connected. This
retention of the node ID is guarded by the connection between the node in question and ndb_mgmd.
After each data node has been initialized, the cluster startup process can proceed. The stages which
the cluster goes through during this process are listed here:
• Phase 0. The NDBFS and NDBCNTR blocks start (see NDB Kernel Blocks). Data node file systems
are cleared on those data nodes that were started with –initial [2049] option.
• Phase 1. In this stage, all remaining NDB kernel blocks are started. MySQL Cluster connections
are set up, inter-block communications are established, and heartbeats are started. In the case of a
node restart, API node connections are also checked.
Note
When one or more nodes hang in Phase 1 while the remaining node or
nodes hang in Phase 2, this often indicates network problems. One possible
cause of such issues is one or more cluster hosts having multiple network
interfaces. Another common source of problems causing this condition is the
blocking of TCP/IP ports needed for communications between cluster nodes.
In the latter case, this is often due to a misconfigured firewall.
• Phase 2. The NDBCNTR kernel block checks the states of all existing nodes. The master node is
chosen, and the cluster schema file is initialized.
• Phase 3. The DBLQH and DBTC kernel blocks set up communications between them. The startup
type is determined; if this is a restart, the DBDIH block obtains permission to perform the restart.
• Phase 4. For an initial start or initial node restart, the redo log files are created. The number of
these files is equal to NoOfFragmentLogFiles [1931].
For a system restart:
• Read schema or schemas.
• Read data from the local checkpoint.
• Apply all redo information until the latest restorable global checkpoint has been reached.
For a node restart, find the tail of the redo log.
• Phase 5. Most of the database-related portion of a data node start is performed during this phase.
For an initial start or system restart, a local checkpoint is executed, followed by a global checkpoint.
Periodic checks of memory usage begin during this phase, and any required node takeovers are
performed.
• Phase 6. In this phase, node groups are defined and set up.
• Phase 7. The arbitrator node is selected and begins to function. The next backup ID is set, as
is the backup disk write speed. Nodes reaching this start phase are marked as Started. It is now
possible for API nodes (including SQL nodes) to connect to the cluster.
• Phase 8. If this is a system restart, all indexes are rebuilt (by DBDIH).
• Phase 9. The node internal startup variables are reset.
Commands in the MySQL Cluster Management Client
2113
• Phase 100 (OBSOLETE). Formerly, it was at this point during a node restart or initial node restart
that API nodes could connect to the node and begin to receive events. Currently, this phase is
empty.
• Phase 101. At this point in a node restart or initial node restart, event delivery is handed over to
the node joining the cluster. The newly-joined node takes over responsibility for delivering its primary
data to subscribers. This phase is also referred to as SUMA handover phase.
After this process is completed for an initial start or system restart, transaction handling is enabled. For
a node restart or initial node restart, completion of the startup process means that the node may now
act as a transaction coordinator.