MySQL-Cluster 搭建

节点 IP
* 数据节点：10.0.8.54, 10.0.8.81
* SQL Node：10.0.3.242
* 管理节点：10.0.0.229

SQL nodes

实际跑 mysqld 的机器

将安装包部署到机器上

# 将安装包拷贝到远程机器上
scp /home/yuzx/data/download/mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz yuzx@10.0.3.242:/home/yuzx/data/download/
scp /home/yuzx/data/download/mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz yuzx@10.0.8.81:/home/yuzx/data/download/
scp /home/yuzx/data/download/mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz yuzx@10.0.8.54:/home/yuzx/data/download/
# 解压到 /usr/local
sudo tar -xf /home/yuzx/data/download/mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz -C /usr/local 
# 创建软链接
sudo ln -sf -T /usr/local/mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64 /usr/local/mysql

创建用户

# 创建 mysql 用户组和用户 
groupadd mysql
useradd -g mysql -s /bin/false mysql

my.cnf 是关键，解决问题的根本之道，因为本机已经安装过 mysql 的单机版，所以这里定义了端口为 3308，实际部署改为 3306 即可

# 不要加 ndb 存储引擎配置，因为集群还没有准备好
sudo vim /usr/local/mysql/my.cnf

# Options for mysqld process
[mysqld]
basedir = "/usr/local/mysql"
datadir = "/usr/local/mysql/data"
socket = /tmp/mysql.sock
user = mysql
port = 3308
lower_case_table_names = 1
symbolic-links = 0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 创建系统数据库，所以结构是存储在 SQL Node 上，数据是存储在数据节点
cd /usr/local/mysql
sudo scripts/mysql_install_db --user=mysql \
  --basedir=/usr/local/mysql \
  --datadir=/usr/local/mysql/data \
  --defaults-file=/usr/local/mysql/my.cnf

# 更改 MySQL Server 目录和 data 目录的权限
cd /usr/local/mysql
sudo chown -R root .
sudo chown -R mysql data
sudo chgrp -R mysql .

自启动支持，可不做

# 增加开启自启动支持，因为不同的 OS 甚至不同版本都可能不同，可先不做
cp support-files/mysql.server /etc/rc.d/init.d/
chmod +x /etc/rc.d/init.d/mysql.server
chkconfig --add mysql.server

Data nodes

• 不需要跑 mysqld
• 需要 MySQL Cluster data node executable
  
  • ndbd 单线程版
  • ndbmtd 多线程版

cd /home/yuzx/data/download
tar -xf mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz
cd mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64
sudo cp bin/ndbd /usr/local/bin/ndbd
sudo cp bin/ndbmtd /usr/local/bin/ndbmtd
# 赋予二者可执行权限
sudo chmod +x /usr/local/bin/ndb*
ls -lh /usr/local/bin/ndb*

Management nodes

• 不需要跑 mysqld
• 需要 MySQL Cluster management server(ndb_mgmd)
• 需要 MySQL Cluster management client(ndb_mgm)

cd /home/yuzx/data/download
tar -xf mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64.tar.gz
cd mysql-cluster-gpl-7.4.8-linux-glibc2.5-x86_64
sudo cp bin/ndb_mgm* /usr/local/bin/
# 赋予二者可执行权限
sudo chmod +x /usr/local/bin/ndb_mgm*
ls -lh /usr/local/bin/ndb_mgm*

初始化配置

数据节点/SQL 节点需要 my.cnf
* connection string, 如何找到管理节点
* 开启 NDBCLUSTER 存储引擎

管理节点需要 config.ini
* 维护的副本数
* 数据节点的内存分配（针对数据和索引）
* 怎么找到数据节点，数据节点数据存在哪
* 怎么找到 SQL 节点

SQL nodes
主要是在上面的基础上增加配置

一旦在 my.cnf 中增加 [mysqld] ndbcluster [mysql_cluster] ndb-connectstring 配置，没有实际启动 cluster 而直接启动 mysqld，不能执行 create table 和 alter table

sudo vim /usr/local/mysql/my.cnf

# Options for mysqld process
[mysqld]
basedir = "/usr/local/mysql"
datadir = "/usr/local/mysql/data"
socket = /tmp/mysql.sock
user = mysql
port = 3308
lower_case_table_names = 1
symbolic-links = 0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# run NDB storage engine
ndbcluster
ndb-connectstring=yuzx-pc
# Options for MySQL Cluster processes
[MYSQL_CLUSTER]
# location of management server
ndb-connectstring=10.0.0.229

Data nodes
因为本机存在 /usr/local/mysql/my.cnf，所以换个位置

mkdir /home/yuzx/data/mysql-cluster
sudo vim /home/yuzx/data/mysql-cluster/my.cnf

[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for MySQL Cluster processes:
ndb-connectstring=10.0.0.229  # location of management server

mkdir /home/yuzx/data/mysql-cluster
vim /home/yuzx/data/mysql-cluster/config.ini

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2  # Number of replicas
DataMemory=80M  # How much memory to allocate for data storage
IndexMemory=18M  # How much memory to allocate for index storage
# For DataMemory and IndexMemory, we have used the
# default values. Since the "world" database takes up
# only about 500KB, this should be more than enough for
# this example Cluster setup.
# Transaction Parameters
MaxNoOfConcurrentTransactions: 4096
MaxNoOfConcurrentOperations: 100000
MaxNoOfLocalOperations: 100000

[tcp default]
# TCP/IP options:
portnumber=2202  # This the default; however, you can use any
# port that is free for all the hosts in the cluster
# Note: It is recommended that you do not specify the port
# number at all and simply allow the default value to be used
# instead

[ndb_mgmd]
# Management process options:
hostname=10.0.0.229  # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
configdir=/home/yuzx/data/mysql-cluster

[ndbd]
# Options for data node "A":
# (one [ndbd] section per data node)
hostname=10.0.8.54  # Hostname or IP address
datadir=/home/yuzx/data/mysql-cluster  # Directory for this data node's data files
FileSystemPath=/home/yuzx/data/mysql-cluster

[ndbd]
# Options for data node "B":
hostname=10.0.8.81  # Hostname or IP address
datadir=/home/yuzx/data/mysql-cluster  # Directory for this data node's data files
FileSystemPath=/home/yuzx/data/mysql-cluster

[mysqld]
# SQL node options:
hostname=10.0.3.242  # Hostname or IP address
# (additional mysqld connections can be specified for this node for various
# purposes such as running ndb_restore)

Initial Startup of MySQL Cluster

# 1. management node
ndb_mgmd -f /home/yuzx/data/mysql-cluster/config.ini --configdir=/home/yuzx/data/mysql-cluster
# 查看文档，有个 restart type，如果为 N，则说明只需要重新 reload 即可生效
ndb_mgmd --reload -f /home/yuzx/data/mysql-cluster/config.ini --configdir=/home/yuzx/data/mysql-cluster


# 2. data node
# sudo mkdir -p /usr/local/mysql/data
sudo ndbd --defaults-file=/home/yuzx/data/mysql-cluster/my.cnf
sudo ndbd --defaults-file=/home/yuzx/data/mysql-cluster/my.cnf

# 3. sql node
cd /usr/local/mysql
sudo bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --user=mysql

# 3. management node
ndb_mgm
>> show

SQL node

# 设置 root 密码
bin/mysqladmin -h 127.0.0.1 -P 3308 -u root password mysql1
./bin/mysql -h 127.0.0.1 -P 3308 -u root -pmysql1 mysql
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mysql1' WITH GRANT OPTION;
FLUSH PRIVILEGES;
select host, user from user;

关闭集群

# 管理节点
ndb_mgm -e shutdown

# SQL node
mysqladmin -uroot shutdown

存储引擎测试

DROP TABLE IF EXISTS `City`;
CREATE TABLE `City` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` char(35) NOT NULL default '',
  `CountryCode` char(3) NOT NULL default '',
  `District` char(20) NOT NULL default '',
  `Population` int(11) NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=NDBCLUSTER DEFAULT CHARSET=latin1;

INSERT INTO `City` VALUES (1,'Kabul','AFG','Kabol',1780000);
INSERT INTO `City` VALUES (2,'Qandahar','AFG','Qandahar',237500);
INSERT INTO `City` VALUES (3,'Herat','AFG','Herat',186800);


select * from city t;