mysql集群NDB方式部署

1. 基本信息

部署机器	角色	部署路径
192.168.0.1	管理节点	部署目录: /alidata1/mysql-cluster-8.4.3
192.168.0.2	管理节点
192.168.0.3	数据/SQL节点	数据目录:
192.168.0.4	数据/SQL节点	/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd
192.168.0.5	数据节点 – 新增	/alidata1/mysql-cluster-8.4.3/data/ndb-data
192.168.0.6	数据节点 – 新增	/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld

2. 安装NDB

## 1.将包上传 并解压
# pwd
/alidata1/
# tar zxf mysql-cluster-8.4.3-linux-glibc2.17-x86_64.tar.xz
# mv mysql-cluster-8.4.3-linux-glibc2.17-x86_64 mysql-cluster-8.4.3
# useradd mysql -s /sdbin/nologin -M  ## 新建用户## 2.配置与启动
############ 2.1 管理节点 192.168.0.1 和 192.168.0.2 操作 ############
## 2.1.1 创建目录
# mkdir /alidata1/mysql-cluster-8.4.3/data/ndb-mgmd## 2.1.2 新建配置
# cat mgmd.ini
[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M[ndb_mgmd default]
DataDir=/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd[NDBD DEFAULT]
NoOfReplicas = 2
DataMemory = 512M
LockPagesInMainMemory=1
ODirect=1
NoOfFragmentLogFiles=300
DataDir=/alidata1/mysql-cluster-8.4.3/data/ndb-data
MaxNoOfConcurrentOperations=100000
SchedulerSpinTimer=400
SchedulerExecutionTimer=100
RealTimeScheduler=1
TimeBetweenGlobalCheckpoints=1000
TimeBetweenEpochs=200
RedoBuffer=32M
MaxNoOfTables=1024
MaxNoOfOrderedIndexes=256[NDB_MGMD]
NodeId = 1
hostname = 192.168.0.1
[NDB_MGMD]
NodeId = 2
hostname = 192.168.0.2[NDBD]
NodeId = 13
hostname = 192.168.0.3[NDBD]
NodeId = 14
hostname = 192.168.0.4[mysqld]
NodeId = 53
hostname = 192.168.0.3
[mysqld]
NodeId = 54
hostname = 192.168.0.4## 数据恢复时需要, 否则会报错
[mysqld]
[mysqld]## 2.1.3 启动管理节点  -- 第一次启动需要加上-initial初始化 以后不需要
# chown -R mysql.mysql /alidata1/mysql-cluster-8.4.3
# ./bin/ndb_mgmd --initial -f /alidata1/mysql-cluster-8.4.3/mgmd.ini --configdir=/alidata1/mysql-cluster-8.4.3}/data/ndb-mgmd   ## 192.168.0.1 执行(其中一个管理节点)
# ./bin/ndb_mgmd -c 192.168.0.1,192.168.0.2 --ndb-nodeid=2 -f /alidata1/mysql-cluster-8.4.3/mgmd.ini --configdir=/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd/  ## 另外一个管理节点执行############ 2.2 数据节点 192.168.0.3 192.168.0.4 操作 ############
## 2.2.1 创建相关目录
# mkdir /alidata1/mysql-cluster-8.4.3/data/ndb-data## 2.2.2 启动
# chown -R mysql.mysql /alidata1/mysql-cluster-8.4.3
# ./bin/ndbd --ndb-mgmd-host=192.168.0.1,192.168.0.2############ 2.3 mysql节点 192.168.0.3 192.168.0.4 操作 ############
## 2.3.1 创建目录
# mkdir /alidata1/mysql-cluster-8.4.3/data/ndb-mysqld/ -p
# mkdir /alidata1/admin/logs/mysql/ -p
## 2.3.2 修改配置
# vim /etc/my.cnf
[mysqld]
ndbcluster
ndb-connectstring=192.168.0.1,192.168.0.2basedir=/alidata1/mysql-cluster-8.4.3
datadir=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld
socket=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld/mysql.sock
user=mysql
port=3306
mysql_native_password=ON
sql_mode=''character-set-server = utf8mb4
open_files_limit = 65535
max_connections = 2000
max_connect_errors = 100000
lower_case_table_names = 1
default_time_zone = '+8:00'#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /alidata1/admin/logs/mysql/slow.log
log-error = /alidata1/admin/logs/mysql/error.log
long_query_time = 10 ## 默认10s
binlog_format = row
#server-id 1
log-bin = mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 1
binlog_expire_logs_seconds = 864000#relay log 主从复制需要
#max_relay_log_size = 1G       ## 0
#relay_log_purge = 1           
#relay_log_recovery = 1 #buffers & cache              ## 默认值
table_open_cache = 2048       ## 4000
table_definition_cache = 2048 ## 2000
table_open_cache = 2048       ## 4000
max_heap_table_size = 96M     ## 16M
sort_buffer_size = 2M         ## 256K
join_buffer_size = 2M         ## 256K
thread_cache_size = 256       ## 9
thread_stack = 192K           ## 1M
tmp_table_size = 96M          ## 16M
key_buffer_size = 8M          ## 8M
read_buffer_size = 2M         ## 128K
read_rnd_buffer_size = 16M    ## 256K
bulk_insert_buffer_size = 32M ## 8M#innodb
innodb_buffer_pool_size = 1G    ##--linux system memory*40%  128M
innodb_buffer_pool_instances = 8                ## 1
innodb_data_file_path = ibdata1:1G:autoextend   ## ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1              ## 1
innodb_log_buffer_size = 64M                    ## 64M
innodb_log_file_size = 512M                     ## 48M
innodb_log_files_in_group = 3                   ## 2
innodb_max_dirty_pages_pct = 50                 ## 90
innodb_file_per_table = 1                       ## on
innodb_rollback_on_timeout                      ## off
innodb_status_file = 1                          ## 空
innodb_io_capacity = 5000                       ## 10000
transaction_isolation = READ-COMMITTED          ## REPEATABLE-READ
innodb_flush_method = O_DIRECT                  ## O_DIRECT[mysql_cluster]
ndb-connectstring=192.168.0.1,192.168.0.2[ndbd]
ndb-connectstring=192.168.0.1,192.168.0.2[mysqld_safe]
log-error=/alidata1/admin/logs/mysql/error.log
pid-file=/alidata1/admin/data/ndb-mysqld/mysql.pid[mysql]
socket=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld/mysql.sock
#!includedir /etc/my.cnf.d## 2.3.3初始化
# chown -R mysql.mysql /alidata1/mysql-cluster-8.4.3/data/ndb-mysqld/
# chown -R mysql.mysql /alidata1/admin/logs/mysql/
# ./bin/mysqld --initialize --user=mysql \--datadir=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld \--explicit_defaults_for_timestamp > /dev/null## 2.3.4 启动
# ./bin/mysqld_safe --defaults-file=/etc/my.cnf &## 2.3.5 修改默认密码
## echo `cat /alidata1/admin/logs/mysql/error.log |grep root|awk -F ':' '{print $NF}' `| sed 's/ //g'   ## 获取默认密码
# mysql -uroot -p
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'your_pass';## 3.验证集群状态
# ./bin/ndb_mgm -e show
onnected to management server at localhost port 1186 (using cleartext)
Cluster Configuration
---------------------
[ndbd(NDB)]	2 node(s)
id=13	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0, *)
id=14	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0)[ndb_mgmd(MGM)]	2 node(s)
id=1	@192.168.0.1  (mysql-8.4.3 ndb-8.4.3)
id=2	@192.168.0.2  (mysql-8.4.3 ndb-8.4.3)[mysqld(API)]	4 node(s)
id=53	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3)
id=54	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3)
id=55 (not connected, accepting connect from any host)
id=56 (not connected, accepting connect from any host)

3. 配置开机自启

## 1. 开机自启文件
# cat /etc/systemd/system/ndb-mgmd.service 
[Unit]
Description=NDB MGMD Server
After=network.target[Service]
ExecStart=/alidata1/mysql-cluster-8.4.3/bin/ndb_mgmd -c 192.168.0.1,192.168.0.2 --ndb-nodeid=1 -f /alidata1/mysql-cluster-8.4.3/mgmd.ini --configdir=/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd/
PIDFile=/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd/ndb_1.pid
Restart=always
PrivateTmp=true
LimitNOFILE=65535
LimitMEMLOCK=infinity[Install]
WantedBy=multi-user.target# cat /etc/systemd/system/ndb-mysqld.service 
[Unit]
Description=NDB MySQL Server
After=network.target[Service]
User=mysql
Group=mysql
ExecStart=/alidata1/mysql-cluster-8.4.3/bin/mysqld --daemonize --pid-file=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld/mysql.pid
ExecStop=/alidata1/mysql-cluster-8.4.3/bin/mysqladmin shutdown
PIDFile=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld/mysql.pid
Restart=always
PrivateTmp=true
LimitNOFILE=65535
LimitMEMLOCK=infinity[Install]
WantedBy=multi-user.target## 由于NDB集群有启停顺序, 因此开机自启只做了 ndb-mgmd,   待管理节点启动后手动启动数据节点和mysqld
## 启动/关闭顺序
## 启动顺序: 管理节点 --> 数据节点  --> mysqld
## 关闭顺序: 管理节点( ./bin/ndb_mgm -e shutdown 会自动关闭数据节点) --> mysqld

4. 数据验证

##  在192.168.0.3上执行建库建表语句, 注意建表语句需要指定引擎 ENGINE=ndbcluster; 在192.168.0.4 mysql客户端也可以看到新建的库/表
mysql> create database db_test;
mysql> show create table db_test.t_test \G
*************************** 1. row ***************************Table: t_test
Create Table: CREATE TABLE `t_test` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)## 通过命令行插入1000个数据 -- 192.168.0.3/4 操作
# for i in `seq 1 1000`; do ./bin/mysql -uroot -p'you_pass' -e "insert into db_test.t_test(id, name) values($i,$i);"; done## 命令行产看会有1000行数据 -- 192.168.0.3/4 操作
mysql> select count(*) from db_test.t_test;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)## 查看表数据在数据节点的分布情况 -- 任一机器都可以
# ./bin/ndb_desc -c 192.168.0.1,192.168.0.2 -d db_test t_test -p
...............
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
0        510      	510         	32768            	32768               	0            	0                 	
1        490      	490         	32768            	32768               	0            	0

5. 新增数据节点

## 在管理节点的配置文件 mgmd.ini 中有以下参数, 所以新增的数据节点也必须是成对存在,否则要另外设置NoOfReplicas, 
## 首先必须确保新加入的数据节点作为新的group加入, 默认2个数据节点为1个group, Group数量 = NDB节点总数 / NoOfReplicas数。
[NDBD DEFAULT]
NoOfReplicas = 2现新增数据节点 192.168.0.5, 192.168.0.6, 操作步骤如下
## 1. 修改管理节点配置 192.168.0.1 192.168.0.2 新增以下内容
# vim mgmd.ini
[NDBD]
NodeId = 15
hostname = 192.168.0.5[NDBD]
NodeId = 16
hostname = 192.168.0.6## 2. 关闭其中一个管理节点(192.168.0.1) 然后重新加载配置  -- 其中一台管理节点操作即可
# systemctl stop ndb-mgmd.service   ## 此时在192.168.0.2 执行./bin/ndb_mgm -e show 会发现管理节点只剩下一个 整个集群还能正常对外提供服务 数据的增删改查操作正常
# ./bin/ndb_mgmd --ndb-nodeid=1 --reload -f /alidata1/mysql-cluster-8.4.3/mgmd.ini --configdir=/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd/   ## 此命令会启动管理节点
# kill -9 `ps aux|grep ndb_mgmd |grep -v 'grep'|awk '{print $2}'`   ## kill掉自动起来的进程, 使用systemctl 来启动
# systemctl start ndb-mgmd.service    ## 启动
# ./bin/ndb_mgm -e show               ## 查看状态 
Connected to management server at localhost port 1186 (using cleartext)
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=13	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0, *)
id=14	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0)
id=15 (not connected, accepting connect from 192.168.0.5)
id=16 (not connected, accepting connect from 192.168.0.6)[ndb_mgmd(MGM)]	2 node(s)
id=1	@192.168.0.1  (mysql-8.4.3 ndb-8.4.3)
id=2	@192.168.0.2  (mysql-8.4.3 ndb-8.4.3)[mysqld(API)]	4 node(s)
id=53	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3)
id=54	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3)
id=55 (not connected, accepting connect from any host)
id=56 (not connected, accepting connect from any host)## 3. 循环重启旧的数据节点, sql节点
# ./bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> 13 restart 
....
ndb_mgm> 14 restart
....# systemctl restart ndb-mysqld## 4. 启动新的数据节点 -- 192.168.0.5 192.168.0.6 操作
## 4.1.将包上传 并解压
# pwd
/alidata1/
# tar zxf mysql-cluster-8.4.3-linux-glibc2.17-x86_64.tar.xz
# mv mysql-cluster-8.4.3-linux-glibc2.17-x86_64 mysql-cluster-8.4.3
# useradd mysql -s /sdbin/nologin -M                   ## 新建用户
# mkdir -p /alidata1/mysql-cluster-8.4.3/data/ndb-data ## 新增目录## 4.2 启动
# chown -R mysql.mysql /alidata1/mysql-cluster-8.4.3
# cd /alidata1/mysql-cluster-8.4.3
# ./bin/ndbd --ndb-mgmd-host=192.168.0.1,192.168.0.2## 5. 为新节点增加分组
## 5.1 新的数据节点加入集群后 执行 show命令发现新的数据节点状态没有分组
....
id=15	@192.168.0.5  (mysql-8.4.3 ndb-8.4.3, no nodegroup)
id=16	@192.168.0.6  (mysql-8.4.3 ndb-8.4.3, no nodegroup)## 5.2 执行分组命令
# ./bin/ndb_mgm 
-- NDB Cluster -- Management Client --
ndb_mgm> create nodegroup 15,16
Connected to management server at localhost port 1186 (using cleartext)
Nodegroup 1 created
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=13	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0, *)
id=14	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0)
id=15	@192.168.0.5  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 1)
id=16	@192.168.0.6  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 1)## 6. 原来的数据重新分片 -- 没找到相关命令## 7. 插入10W条数据再次查看数据分布情况
# ./bin/ndb_desc -c 192.168.0.1,192.168.0.2 -d db_test t_test -p
..........
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
0        	24943    	24943       	819200           	425984              	0            	0                 	
1        	25089    	25089       	819200           	425984              	0            	0                 	
2        	25007    	25007       	819200           	425984              	0            	0                 	
3        	24961    	24961       	819200           	425984              	0            	0

6. 新增查询节点

## 1.将包上传 并解压
# pwd
/alidata1/
# tar zxf mysql-cluster-8.4.3-linux-glibc2.17-x86_64.tar.xz
# mv mysql-cluster-8.4.3-linux-glibc2.17-x86_64 mysql-cluster-8.4.3
# useradd mysql -s /sdbin/nologin -M                     ## 新建用户
# mkdir -p /alidata1/mysql-cluster-8.4.3/data/ndb-mysqld ## 新增目录
# mkdir -p /alidata1/admin/logs/mysql
# chown -R mysql.mysql /alidata1/mysql-cluster-8.4.3 /alidata1/admin/logs/mysql## 2.1 修改配置 初始化
# vim /etc/my.cnf  -- 和最开始安装雷同
# ./bin/mysqld --initialize --user=mysql --datadir=/alidata1/mysql-cluster-8.4.3/data/ndb-mysqld --explicit_defaults_for_timestamp > /dev/null## 2.2 修改默认密码 
## echo `cat /alidata1/admin/logs/mysql/error.log |grep root|awk -F ':' '{print $NF}' `| sed 's/ //g'   ## 获取默认密码
# mysql -uroot -p
mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'your_pass';## 3. 修改管理节点配置 新增配置
# vim mgmd.ini
......
[mysqld]
NodeId = 55
hostname = 192.168.0.5[mysqld]
NodeId = 56
hostname = 192.168.0.6## 4. 关闭其中一个管理节点(192.168.0.1) 然后重新加载配置  -- 其中一台管理节点操作即可
# systemctl stop ndb-mgmd.service   ## 此时在192.168.0.2 执行./bin/ndb_mgm -e show 会发现管理节点只剩下一个 整个集群还能正常对外提供服务 数据的增删改查操作正常
# ./bin/ndb_mgmd --ndb-nodeid=1 --reload -f /alidata1/mysql-cluster-8.4.3/mgmd.ini --configdir=/alidata1/mysql-cluster-8.4.3/data/ndb-mgmd/   ## 此命令会启动管理节点
# kill -9 `ps aux|grep ndb_mgmd |grep -v 'grep'|awk '{print $2}'`   ## kill掉自动起来的进程, 使用systemctl 来启动
# systemctl start ndb-mgmd.service    ## 启动
# ./bin/ndb_mgm -e show               ## 查看状态 
Connected to management server at localhost port 1186 (using cleartext)
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=13	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0, *)
id=14	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 0)
id=15	@192.168.0.5  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 1)
id=16	@192.168.0.6  (mysql-8.4.3 ndb-8.4.3, Nodegroup: 1)[ndb_mgmd(MGM)]	2 node(s)
id=1	@192.168.0.1  (mysql-8.4.3 ndb-8.4.3)
id=2	@192.168.0.2  (mysql-8.4.3 ndb-8.4.3)[mysqld(API)]	6 node(s)
id=53	@192.168.0.3  (mysql-8.4.3 ndb-8.4.3)
id=54	@192.168.0.4  (mysql-8.4.3 ndb-8.4.3)
id=55	@192.168.0.5  (mysql-8.4.3 ndb-8.4.3)
id=56	@192.168.0.6  (mysql-8.4.3 ndb-8.4.3)
id=57 (not connected, accepting connect from any host)
id=58 (not connected, accepting connect from any host)

7. 数据备份与恢复

## 1. 数据备份脚本
# cat /alidata1/admin/scripts/ndb_bak.sh
#!/bin/bashset -e ## 当前时间
mydate=`date +%Y%m%d`
## mysql部署路径
mysql_home=/alidata1/mysql-cluster-8.4.3
## 备份
${mysql_home}/bin/ndb_mgm -e "start backup ${mydate}"## 2. 使用命令行来备份刚才的数据  -- 192.168.0.1 操作
# ./bin/ndb_mgm -e "start backup 20241130"## 3. 数据恢复
## 3.1 使用命令行将表数据清空 -- 192.168.0.3 操作
# ./bin/mysql -uroot -p'your_pass'
mysql> truncate table db_test.t_test;
mysql> select count(*) from db_test.t_test;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)# 3.2 数据恢复
## 恢复操作需要在所有ndb 数据节点操作
## --nodeid            当前数据节点的id
## --backupid          备份时使用的备份id 默认为日期
## --restore-data      恢复数据
## --include-databases 指定数据库
## --include-tables    指定表
## --backup_path       备份路径
## --remap-column=nacos.test.id:offset:10000  指定偏移量 一般不需要## 192.168.0.3 操作
# ./bin/ndb_restore --ndb-connectstring=192.168.0.1,192.168.0.2 --nodeid=13 --backupid=20241130 --restore-data --include-databases=db_test  --backup_path=/alidata1/mysql-cluster-8.4.3/data/ndb-data/BACKUP/BACKUP-20241130## 192.168.0.4 操作
./bin/ndb_restore --ndb-connectstring=192.168.0.1,192.168.0.2 --nodeid=14 --backupid=20241130 --restore-data --include-databases=db_test  --backup_path=/alidata1/mysql-cluster-8.4.3/data/ndb-data/BACKUP/BACKUP-20241130## 192.168.0.5 操作
./bin/ndb_restore --ndb-connectstring=192.168.0.1,192.168.0.2 --nodeid=15 --backupid=20241130 --restore-data --include-databases=db_test  --backup_path=/alidata1/mysql-cluster-8.4.3/data/ndb-data/BACKUP/BACKUP-20241130## 192.168.0.6 操作
./bin/ndb_restore --ndb-connectstring=192.168.0.1,192.168.0.2 --nodeid=16 --backupid=20241130 --restore-data --include-databases=db_test  --backup_path=/alidata1/mysql-cluster-8.4.3/data/ndb-data/BACKUP/BACKUP-20241130## 3.3 查看表数据分布情况
# ./bin/ndb_desc -c 192.168.0.1,192.168.0.2 -d db_test t_test -p
..........
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
0        	24943    	24943       	819200           	425984              	0            	0                 	
1        	25089    	25089       	819200           	425984              	0            	0                 	
2        	25007    	25007       	819200           	425984              	0            	0                 	
3        	24961    	24961       	819200           	425984              	0            	0