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.2
basedir=/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/bash
set -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
