环境
- windows11上的ubuntu22.04的wsl2
- ubuntu20.04
- mysql-cluster_8.0.35-1ubuntu20.04_amd64
配置环境
- sql0 192.168.50.100 管理节点
- sql1 192.168.50.128 数据节点[11] sql节点
- sql2 192.168.50.129 数据节点[12] sql节点
成品镜像
docker容器配置
- windows11上的ubuntu22.04的wsl2
- 下载镜像
1
2
| 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
2
| # sql0
docker run -di --name sql0 -v /home/lihan/sqlStudy:/home/shareFiles --net mysqlBridge --ip 192.168.50.100 ubuntu:20.04
|
- 下载
mysql-cluster-community-server
安装包
将下载好的安装包放在宿主机挂载的目录下
- 安装
mysql-cluster-community-server
1
2
3
4
5
6
7
8
9
10
11
12
| # 进入容器
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
|
1
2
3
4
5
6
7
8
| # 下载依赖包`libssl1.1`后,复制到宿主机挂载目录下
dpkg -i /home/shareFiles/libssl1.1_1.1.1-1ubuntu2.1~18.04.23_amd64.deb
# 更新修复`libssl1.1`
apt update
apt upgrade
|
- 制作镜像并创建
sql1
和sql2
容器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| # 下点需要的东西
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服务器)
- 安装
ndb_mgmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| # 解压安装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
|
- 配置
ndb_mgmd
1
2
3
| # 创建配置文件
mkdir /var/lib/mysql-cluster
vim /var/lib/mysql-cluster/config.ini
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
| [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
|
- 启动
ndb_mgmd
1
| ndb_mgmd -f /var/lib/mysql-cluster/config.ini
|
显示以下信息
1
2
3
| 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
|
- (非必须)配置
ndb_mgmd
开机启动
- docker的官方
Ubuntu20.04
镜像不带init,不支持systemd,因此跳过这个开机启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| # 杀死进程
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)
|
- 管理
ndb_mgmd
1
2
3
4
5
|
# 设置允许其他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)
- 安装
ndbd
1
2
3
4
5
6
| # 安装依赖
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
2
3
4
5
6
7
8
9
10
11
12
| # 创建配置文件
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
|
- 启动
ndbd
1
2
3
4
5
6
7
8
9
10
11
| # 启动
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
|
- (非必须)配置
ndbd
开机启动
- docker的官方
Ubuntu20.04
镜像不带init,不支持systemd,因此跳过这个开机启动
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
| # 杀死进程
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)
- sql1
- 标准的MySQL server不支持 MySQL Cluster 引擎 NDB. 这意味着我们需要安装含有定制的SQL服务器 MySQL Cluster软件.
- 安装依赖
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
| # 安装联网依赖
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
|
- 配置
MySQL server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
| # 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
|
- 启动
MySQL server
1
2
3
4
5
| # 启动mysql数据库
mysqld --user=root &
# 进入mysql数据库
mysql --socket=/run/mysqld/mysqld.sock -u root -p
# 小tips:这里用了sudo 记得可能第一遍输入电脑的root密码,第二次输入mysql密码
|
验证MySQL Cluster安装
启动ndb_mgmd
1
2
3
|
ndb_mgmd -f /var/lib/mysql-cluster/config.ini
|
启动ndbd
- 连接MySQL Server
报错1:
- 运行
service mysql(d) start
,报错unrecognized service
- 运行
mysql
,报错Connection error: Can't connect to local MySQL server through ***.sock
解决1:
mysqld是用来启动mysql数据库的命令,mysql是打开并执行sql语句的命令。mysql.sock是随每一次 mysql server启动生成的。因此在未启动服务的情况下,直接运行mysql命令,会报错找不到*.sock
1
2
3
4
5
| # mysqld启动mysql数据库 --user=mysql 指定mysql用户 & 后台运行
mysqld --user=root &
# 关闭mysql数据库
mysqladmin -u root -p shutdown
|
报错2:
[ERROR] Fatal error: Please read “Security” section of the manual to find out how to run mysqld as root
解决2:
一般情况下,mysql禁止root用户登陆数据库
权限问题,使用sudo mysql -u root -p
,输入密码后,可以正常登陆
报错3:
mysql -u root -p
输入密码后,报错Connection error: Can't connect to local MySQL server through ***.sock
解决3:
find / -name *.sock
查找.sock文件,启动时添加参数--socket=/run/mysqld/mysqld.sock
,根据查找到的路径指定.sock文件
mysql --socket=/run/mysqld/mysqld.sock -u root -p
- 查看NDB引擎的相关信息
1
| SHOW ENGINES NDB STATUS \G
|
- 集群管理器控制台上查看集群信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 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
就可以连接上了。
- 制作镜像并创建
sql2
容器
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
# 退出容器
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 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集群插入数据
- 注意为了使用集群功能, 必须使用NDB数据库引擎. 如果使用InnoDB (default)或其他引擎,将不能使用集群.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
-- 首先, 在一个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;
|
思考:在本例中,数据被插入到了哪个机器?
问题:
- 1 通过实验,你对一个分布式数据库系统有何理解?分布式数据库系统预计有何优越性?
- 2 你能设计一个方案验证集群系统在可靠性上优于集中式数据库系统吗?
- 3 同样是插入数据,你觉得MySQL Cluster和myCAT 在实体完整性保持方面是否可能会有不同?为什么?