仓库源文，站点原文

title: 搭建MySQLCluster集群环境 slug: mysqlCluster date: 2023-11-07 10:30:00+0800 image: mysql.png categories:

- cucStudy

tags:

- mysql
- Ubuntu
- docker

weight: 1 # You can add weight to some posts to override the default sorting (date descending)

comments: true

license: flase

math: true

toc: true

style:

keywords:

readingTime:

环境

配置环境

成品镜像

docker容器配置

  1. 下载镜像
docker pull ubuntu:20.04
docker pull jrei/systemd-ubuntu:20.04
  1. 创建网络
docker network create --driver bridge --subnet 192.168.50.0/24 --gateway 192.168.50.1 mysqlBridge
  1. 创建容器
# sql0
docker run -di --name sql0 -v /home/lihan/sqlStudy:/home/shareFiles --net mysqlBridge --ip 192.168.50.100 ubuntu:20.04
  1. 下载mysql-cluster-community-server安装包

将下载好的安装包放在宿主机挂载的目录下

  1. 安装mysql-cluster-community-server
# 进入容器
docker exec -it sql0 /bin/bash

# 创建mysql用户
adduser mysql 
# 密码123456
usermod -aG sudo mysql

# 解压文件到install目录
mkdir install
tar -xvf /home/shareFiles/mysql-cluster_8.0.35-1ubuntu20.04_amd64.deb-bundle.tar  -C install/
cd install

# 下载依赖包`libssl1.1`后，复制到宿主机挂载目录下

dpkg -i /home/shareFiles/libssl1.1_1.1.1-1ubuntu2.1~18.04.23_amd64.deb

# 更新修复`libssl1.1`

apt update
apt upgrade
  1. 制作镜像并创建sql1和sql2容器
# 下点需要的东西
apt install vim sudo dialog net-tools iputils-ping

# 退出容器
exit

docker stop sql0

docker commit sql0 lihansql:1.0

docker start sql0
# (可选)镜像保存为文件
docker save -o lihansql_1.0.tar lihansql:1.0

# 根据镜像创建容器sql1 sql2

# sql1
docker run -di --name sql1 -v /home/lihan/sqlStudy:/home/shareFiles --net mysqlBridge --ip 192.168.50.128 lihansql:1.0
# sql2
docker run -di --name sql2 -v /home/lihan/sqlStudy:/home/shareFiles --net mysqlBridge --ip 192.168.50.129 lihansql:1.0

配置集群管理器(Cluster Manager服务器)

  1. 安装ndb_mgmd
# 解压安装install目录下的依赖包
dpkg -i install/mysql-cluster-community-management-server_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-common_8.0.35-1ubuntu16.04_amd64.deb

dpkg -i install/mysql-cluster-community-client-plugins_8.0.35-1ubuntu20.04_amd64.deb 

# 安装提示缺少依赖`libgssapi-krb5-2` `libkrb5-3` `libsasl2-2`
apt --fix-broken install

# 继续安装
dpkg -i install/mysql-cluster-community-client-plugins_8.0.35-1ubuntu20.04_amd64.deb 

dpkg -i install/mysql-cluster-community-client-core_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-cluster-community-client_8.0.35-1ubuntu20.04_amd64.deb
  1. 配置ndb_mgmd
# 创建配置文件
mkdir /var/lib/mysql-cluster
vim /var/lib/mysql-cluster/config.ini

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2  
# Number of replicas

[ndb_mgmd]
# Management process options:
hostname=192.168.50.100  
# Hostname of the manager
datadir=/var/lib/mysql-cluster  
# Directory for the log files

[ndbd]
hostname=192.168.50.128 
# Hostname/IP of the first data node
NodeId=11            
# Node ID for this data node
datadir=/usr/local/mysql/data   
# Remote directory for the data files

[ndbd]
hostname=192.168.50.129 
# Hostname/IP of the second data node
NodeId=12            
# Node ID for this data node
datadir=/usr/local/mysql/data   
# Remote directory for the data files

[mysqld]
# SQL node options:
hostname=192.168.50.128 
# MySQL server/client i manager

[mysqld]
# SQL node options:
hostname=192.168.50.129 
# MySQL server/client i manager
  1. 启动ndb_mgmd
ndb_mgmd -f /var/lib/mysql-cluster/config.ini

显示以下信息

MySQL Cluster Management Server mysql-8.0.35 ndb-8.0.35
2023-11-07 08:24:08 [MgmtSrvr] INFO     -- The default config directory '/usr/mysql-cluster' does not exist. Trying to create it...
2023-11-07 08:24:08 [MgmtSrvr] INFO     -- Sucessfully created config directory
  1. (非必须)配置ndb_mgmd开机启动
# 杀死进程
pkill -f ndb_mgmd

# 创建启动脚本
vim /etc/systemd/system/ndb_mgmd.service

# 编辑ndb_mgmd.service

[Unit]
Description=MySQL NDB Cluster Management Server
After=network.target auditd.service

[Service]
Type=forking
ExecStart=/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure

[Install]
WantedBy=multi-user.target

# 采用daemon-reload重新加载配置
systemctl daemon-reload

# 启动ndb_mgmd开机运行
systemctl enable ndb_mgmd

# 启动ndb_mgmd
systemctl start ndb_mgmd

# 验证ndb_mgmd是否正在执行
systemctl status ndb_mgmd

# 应该输出类似信息
ndb_mgmd.service - MySQL NDB Cluster Management Server
    Loaded: loaded (/etc/systemd/system/ndb_mgmd.service, enabled)
    Active: active (running)
  1. 管理ndb_mgmd

# 设置允许其他MySQL Cluster节点接入，如无`ufw`等防火墙，可以跳过这一步
ufw allow from 192.168.50.100
ufw allow from 192.168.50.128
ufw allow from 192.168.50.129

配置数据节点(Data Nodes)

  1. 安装ndbd
# 安装依赖
sudo apt-get -f install 
sudo apt install libclass-methodmaker-perl

# 安装ndbd
dpkg -i install/mysql-cluster-community-data-node_8.0.35-1ubuntu20.04_amd64.deb
  1. 创建并配置 配置文件
# 创建配置文件
vim /etc/my.cnf

# 编辑my.cnf

[mysql_cluster]
# Options for NDB Cluster processes:
ndb-connectstring=192.168.50.100  
# location of cluster manager

# 创建数据目录
mkdir -p /usr/local/mysql/data
  1. 启动ndbd
# 启动
ndbd

# 输出类似信息
2023-11-08 00:47:06 [ndbd] INFO     -- Angel connected to '192.168.50.100:1186'
2023-11-08 00:47:07 [ndbd] INFO     -- Angel allocated nodeid: 11

# 如果出现连接问题，请打开防火墙
ufw allow from 192.168.50.100
ufw allow from 192.168.50.128
ufw allow from 192.168.50.129
  1. (非必须)配置ndbd开机启动
# 杀死进程
pkill -f ndbd

# 创建启动脚本
vim /etc/systemd/system/ndbd.service

# 编辑ndbd.service

[Unit]
Description=MySQL NDB Data Node Daemon
After=network.target auditd.service

[Service]
Type=forking
ExecStart=/usr/sbin/ndbd
ExecReload=/bin/kill -HUP $MAINPID
KillMode=process
Restart=on-failure

[Install]
WantedBy=multi-user.target


# 采用daemon-reload重新加载配置
systemctl daemon-reload

# 启动ndb_mgmd开机运行
systemctl enable ndbd

# 启动ndb_mgmd
systemctl start ndbd

# 验证ndb_mgmd是否正在执行
systemctl status ndbd

# 应该输出类似信息
ndbd.service - MySQL NDB Data Node Daemon
    Loaded: loaded (/etc/systemd/system/ndbd.service, enabled)
    Active: active (running)

配置SQL节点(配置并运行MySQL Server 和 Client)

  1. 安装依赖
# 安装联网依赖
apt update
apt install libaio1 libmecab2

# 解压安装install目录下的依赖包
dpkg -i install/mysql-common_8.0.35-1ubuntu16.04_amd64.deb

dpkg -i install/mysql-cluster-community-client-plugins_8.0.35-1ubuntu20.04_amd64.deb 

# 安装提示缺少依赖`libgssapi-krb5-2` `libkrb5-3` `libsasl2-2`
apt --fix-broken install

# 继续安装
dpkg -i install/mysql-cluster-community-client-plugins_8.0.35-1ubuntu20.04_amd64.deb 

dpkg -i install/mysql-cluster-community-client-core_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-cluster-community-client_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-client_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-cluster-community-server-core_8.0.35-1ubuntu20.04_amd64.deb
# 安装提示缺少依赖`libnuma1`
apt --fix-broken install

# 继续安装
dpkg -i install/mysql-cluster-community-server-core_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-cluster-community-server_8.0.35-1ubuntu20.04_amd64.deb
# 安装提示缺少依赖`libnuma1`
apt --fix-broken install

# 继续安装
dpkg -i install/mysql-cluster-community-server_8.0.35-1ubuntu20.04_amd64.deb

#提示设置root密码
123456

# 重新安装
dpkg -i install/mysql-cluster-community-server_8.0.35-1ubuntu20.04_amd64.deb

dpkg -i install/mysql-server_8.0.35-1ubuntu20.04_amd64.deb
  1. 配置MySQL server
# MySQL Server 配置文件默认为 /etc/mysql/my.cnf
vim /etc/mysql/my.cnf

# 编辑my.cnf
[mysqld]
# Options for mysqld process:
ndbcluster                      
# run NDB storage engine
ndb-connectstring=192.168.50.100 

# default engine
default_storage_engine=ndbcluster

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


# 重启
#systemctl restart mysql

# 开机启动
#systemctl enable mysql
  1. 启动MySQL server
# 启动mysql数据库
mysqld --user=root &
# 进入mysql数据库
mysql --socket=/run/mysqld/mysqld.sock -u root -p
# 小tips：这里用了sudo 记得可能第一遍输入电脑的root密码，第二次输入mysql密码

验证MySQL Cluster安装

启动ndb_mgmd


ndb_mgmd -f /var/lib/mysql-cluster/config.ini

启动ndbd

ndbd
  1. 连接MySQL Server
mysql -u root -p

mysqld是用来启动mysql数据库的命令，mysql是打开并执行sql语句的命令。mysql.sock是随每一次 mysql server启动生成的。因此在未启动服务的情况下，直接运行mysql命令，会报错找不到*.sock

# mysqld启动mysql数据库 --user=mysql 指定mysql用户 & 后台运行
mysqld --user=root &

# 关闭mysql数据库
mysqladmin -u root -p shutdown

一般情况下，mysql禁止root用户登陆数据库

权限问题，使用sudo mysql -u root -p，输入密码后，可以正常登陆

find / -name *.sock查找.sock文件，启动时添加参数--socket=/run/mysqld/mysqld.sock，根据查找到的路径指定.sock文件

  1. 查看NDB引擎的相关信息
SHOW ENGINES NDB STATUS \G

1

quit
  1. 集群管理器控制台上查看集群信息
ndb_mgm
# 进入ndb_mgm控制台
SHOW
# 查看集群信息
# 显示如下信息
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11   @192.168.50.128  (mysql-8.0.35 ndb-8.0.35, starting, Nodegroup: 0)
id=12 (not connected, accepting connect from 192.168.50.129)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.50.100  (mysql-8.0.35 ndb-8.0.35)

[mysqld(API)]   2 node(s)
id=13 (not connected, accepting connect from 192.168.50.128)
id=14 (not connected, accepting connect from 192.168.50.129)

发现ndbd正常连接，但是mysql无法连接

不知道为什么，一定要将集群的所有其他节点配置连接好后，才能连接上mysql节点。 吧sql2的ndbd配置好后，sql1和sql2的mysql就可以连接上了。

  1. 制作镜像并创建sql2容器

# 退出容器
exit

docker stop sql1

docker commit sql1 lihan_ndbd_sql:1.0

docker start sql1
# (可选)镜像保存为文件
docker save -o lihan_ndbd_sql.tar lihan_ndbd_sql:1.0

# 根据镜像创建容器sql1 sql2

# sql2
docker run -di --name sql2 -v /home/lihan/sqlStudy:/home/shareFiles --net mysqlBridge --ip 192.168.50.129 lihan_ndbd_sql:1.0

docker exec -it sql2 /bin/bash

ndbd

mysqld --user=root &
  1. 验证连接
ndb_mgm
# 进入ndb_mgm控制台
SHOW
# 查看集群信息
# 显示如下信息
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=11   @192.168.50.128  (mysql-8.0.35 ndb-8.0.35, Nodegroup: 0, *)
id=12   @192.168.50.129  (mysql-8.0.35 ndb-8.0.35, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.50.100  (mysql-8.0.35 ndb-8.0.35)

[mysqld(API)]   2 node(s)
id=13   @192.168.50.128  (mysql-8.0.35 ndb-8.0.35)
id=14   @192.168.50.129  (mysql-8.0.35 ndb-8.0.35)

向MySQL集群插入数据


-- 首先, 在一个sql节点创建数据库clustertest:
CREATE DATABASE clustertest;

-- 其次转到新数据库:
USE clustertest;

-- 再次，创建表test_table:
CREATE TABLE test_table (name VARCHAR(20), value VARCHAR(20)) ENGINE=ndbcluster;

-- 如果`my.cnf`中没有设置，这里需要显式规定ndbcluster引擎. 
-- 现在可以插入数据了:
INSERT INTO test_table (name,value) VALUES('some_name','some_value');

-- 最后在另一个sql节点验证数据插入：
SELECT * FROM test_table;