MySQL NDB Cluster 分布式架构搭建 自定义启动、重启和关闭集群Shell脚本

���次NDB Cluster使用三台虚拟机进行搭建，一台作为管理节点；而对于另外两台服务器，每一台都充当着数据节点和SQL节点的角色。注意不是MGR主从复制架构，而是分布式MySQL架构。

创建 /var/lib/mysql-cluster/config.ini Cluster全局配置文件

[NDBD DEFAULT]NoOfReplicas =2DataMemory = 512MMaxNoOfExecutionThreads = 4[NDB_MGMD]NodeId=1hostname =192.168.182.102datadir =/var/lib/mysql-cluster[NDBD]NodeId =2hostname =192.168.182.103datadir =/usr/local/mysql-cluster/data[NDBD]NodeId =3hostname =192.168.182.104datadir =/usr/local/mysql-cluster/data[mysqld]NodeId =4hostname =192.168.182.103[mysqld]NodeId =5hostname =192.168.182.104

在管理节点中执行以下命令，初始化管理节点（注意mysql cluster路径是固定的，否则会报错）

/usr/local/mysql/mysql-cluster/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --initial --ndb-nodeid=1

创建 /etc/my.cnf mysql配置文件

[mysqld]basedir=/usr/local/mysql-clusterdatadir=/usr/local/mysql-cluster/datasocket=/usr/local/mysql-cluster/sock/mysql.sockuser=hadoopsymbolic-links=0ndbclusterndb-connectstring=192.168.182.102port=3306[mysql_cluster]ndb-connectstring=192.168.182.102[ndbd]ndb-connectstring=192.168.182.102[mysqld_safe]log-error=/usr/local/mysql-cluster/logs/mysql.pidpid-file=/usr/local/mysql-cluster/logs/error.log[mysql]socket=/usr/local/mysql-cluster/sock/mysql.sock### include all files from the config directory###!includedir /etc/my.cnf.d

 在初始化mysqld之前，需要将data、logs、sock文件夹创建完毕，否则无法初始化

[hadoop@hadoop103 mysql-cluster]$ pwd/usr/local/mysql-cluster[hadoop@hadoop103 mysql-cluster]$ lsbin  docs  include  lib  LICENSE  man  README  share  support-files[hadoop@hadoop103 mysql-cluster]$ mkdir data[hadoop@hadoop103 mysql-cluster]$ mkdir sock[hadoop@hadoop103 mysql-cluster]$ mkdir logs

 初始化mysqld，同时没有对root账号设置密码

[hadoop@hadoop103 mysql-cluster]$ bin/mysqld --initialize-insecure --user=hadoop2024-03-01T22:50:53.201408Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 8161)2024-03-01T22:50:53.201412Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 431 (requested 4000)2024-03-01T22:50:53.202613Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.2024-03-01T22:50:53.202952Z 0 [System] [MY-013169] [Server] /usr/local/mysql-cluster/bin/mysqld (mysqld 8.0.36-cluster) initializing of server in progress as process 29472024-03-01T22:50:53.211541Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2024-03-01T22:50:53.873353Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2024-03-01T22:50:54.189948Z 0 [System] [MY-010865] [NDB] '--initialize' -> ndbcluster plugin disabled2024-03-01T22:50:55.344321Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.[hadoop@hadoop103 mysql-cluster]$ sudo cp support-files/mysql.server /etc/init.d/mysqld[hadoop@hadoop103 mysql-cluster]$ sudo chmod +x /etc/init.d/mysqld

再初始化初始化数据节点，注意此时初始化完毕会自动连接到管理节点

[hadoop@hadoop103 mysql-cluster]$ bin/ndbd --initial2024-03-02 06:51:57 [ndbd] INFO     -- Angel connected to '192.168.182.102:1186'2024-03-02 06:51:57 [ndbd] INFO     -- Angel allocated nodeid: 2

 集群目前连接情况

[hadoop@hadoop102 mysql-cluster]$ bin/ndb_mgm -e showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)]2 node(s)id=2@192.168.182.103  (mysql-8.0.36 ndb-8.0.36, starting, Nodegroup: 0)id=3 (not connected, accepting connect from 192.168.182.104)[ndb_mgmd(MGM)]1 node(s)id=1@192.168.182.102  (mysql-8.0.36 ndb-8.0.36)[mysqld(API)]2 node(s)id=4 (not connected, accepting connect from 192.168.182.103)id=5 (not connected, accepting connect from 192.168.182.104)

注意SQL节点需要在数据节点全部启动后，才能开始启动，所以别着急启动mysqld

同时为了防止在之后停止mysqld的时候，出现rm: cannot remove '/var/lock/subsys/mysql': Permission denied报错，导致后续无法启动mysqld，需要修改该文件夹的权限

sudo chown -R hadoop:hadoop /var/lock/subsys

第三台服务器操作同第二台操作一致。在第三台服务器完成数据节点初始化之后，便可以启动两个SQL节点。整个集群启动成功后的连接情况如下

[hadoop@hadoop102 mysql-cluster]$ bin/ndb_mgm -e showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)]2 node(s)id=2@192.168.182.103  (mysql-8.0.36 ndb-8.0.36, Nodegroup: 0, *)id=3@192.168.182.104  (mysql-8.0.36 ndb-8.0.36, Nodegroup: 0)[ndb_mgmd(MGM)]1 node(s)id=1@192.168.182.102  (mysql-8.0.36 ndb-8.0.36)[mysqld(API)]2 node(s)id=4@192.168.182.103  (mysql-8.0.36 ndb-8.0.36)id=5@192.168.182.104  (mysql-8.0.36 ndb-8.0.36)

为了测试集群是否能够正常存储数据，需要在SQL节点中设置MySQL远程连接配置。在这里我创建了一个名为hadoop的用户，并赋予全部操作权限

[hadoop@hadoop103 mysql-cluster]$ bin/mysql -urootmysql> CREATE USER 'hadoop'@'%' IDENTIFIED BY '123456';Query OK, 0 rows affected (0.03 sec)mysql> GRANT ALL PRIVILEGES ON *.* TO 'hadoop'@'%' WITH GRANT OPTION;Query OK, 0 rows affected (0.11 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.00 sec)

 在Navicat中测试连接情况

以下是自定义的集群启动Shell脚本 

#!/bin/bash/usr/local/mysql/mysql-cluster/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --config-cache=FALSE --ndb-nodeid=1ssh hadoop103 /usr/local/mysql-cluster/bin/ndbdssh hadoop104 /usr/local/mysql-cluster/bin/ndbdPASSWORD=123456echo $PASSWORD | sudo -S ssh -t root@hadoop103 'service mysqld start'echo $PASSWORD | sudo -S ssh -t root@hadoop104 'service mysqld start'

以下是自定义的关闭集群Shell脚本

#!/bin/bash# 定义要查找的端口号PORT=1186PASSWORD=123456# 使用 lsof 命令查找占用指定端口的进程，并提取进程 IDPID=$(echo $PASSWORD | sudo -S lsof -t -i:$PORT)# 检查是否找到了进程 IDif [ -z "$PID" ]; then    echo "未找到占用端口 $PORT 的进程"else    # 使用 kill 命令终止进程    /usr/local/mysql/mysql-cluster/bin/ndb_mgm -e shutdown    echo $PASSWORD | sudo -S kill -9 $PID    echo "已终止进程 $PID"fi# 使用 lsof 命令查找占用指定端口的进程，并提取进程 IDPID=$(echo $PASSWORD | sudo lsof -t -i:$PORT)# 检查是否找到了进程 IDif [ -z "$PID" ]; then    echo "未找到占用端口 $PORT 的进程, the cluster will start"    echo $PASSWORD | sudo -S ssh -t root@hadoop103 'service mysqld stop'    echo $PASSWORD | sudo -S ssh -t root@hadoop104 'service mysqld stop'else    # 使用 kill 命令终止进程    echo $PASSWORD | sudo -S kill -9 $PID    echo "已终止进程 $PID"fi

以下是自定义的集群重启Shell脚本（前提是hadoop102的root用户已经将ssh密钥分配给hadoop103和hadoop104的root用户，否则还需要输入密码；本机sudo不需要输入密码的前提是/etc/sudoers配置了该用户）

#!/bin/bash# 定义要查找的端口号PORT=1186PASSWORD=123456# 使用 lsof 命令查找占用指定端口的进程，并提取进程 IDPID=$(echo $PASSWORD | sudo -S lsof -t -i:$PORT)# 检查是否找到了进程 IDif [ -z "$PID" ]; then    echo "未找到占用端口 $PORT 的进程"else    # 使用 kill 命令终止进程    /usr/local/mysql/mysql-cluster/bin/ndb_mgm -e shutdown    echo $PASSWORD | sudo -S kill -9 $PID    echo "已终止进程 $PID"fi# 使用 lsof 命令查找占用指定端口的进程，并提取进程 IDPID=$(echo $PASSWORD | sudo lsof -t -i:$PORT)# 检查是否找到了进程 IDif [ -z "$PID" ]; then    echo "未找到占用端口 $PORT 的进程, the cluster will start"    /usr/local/mysql/mysql-cluster/bin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini --config-cache=FALSE --ndb-nodeid=1    ssh hadoop103 /usr/local/mysql-cluster/bin/ndbd    ssh hadoop104 /usr/local/mysql-cluster/bin/ndbd    echo $PASSWORD | sudo -S ssh -t root@hadoop103 'service mysqld restart'    echo $PASSWORD | sudo -S ssh -t root@hadoop104 'service mysqld restart'else    # 使用 kill 命令终止进程    echo $PASSWORD | sudo -S kill -9 $PID    echo "已终止进程 $PID"fi

以下是集群重启的日志与结果

[hadoop@hadoop102 mysql-cluster]$ ./restart.sh Connected to Management Server at: localhost:11863 NDB Cluster node(s) have shutdown.Disconnecting to allow management server to shutdown.已终止进程 3123未找到占用端口 1186 的进程, the cluster will startMySQL Cluster Management Server mysql-8.0.36 ndb-8.0.362024-03-02 07:20:42 [MgmtSrvr] INFO     -- Skipping check of config directory since config cache is disabled.2024-03-02 07:20:42 [MgmtSrvr] WARNING  -- at line 4: [DB] IndexMemory is deprecated, will use Number bytes on each ndbd(DB) node allocated for storing indexes instead2024-03-02 07:20:42 [ndbd] INFO     -- Angel connected to '192.168.182.102:1186'2024-03-02 07:20:42 [ndbd] INFO     -- Angel allocated nodeid: 22024-03-02 07:20:42 [ndbd] INFO     -- Angel connected to '192.168.182.102:1186'2024-03-02 07:20:42 [ndbd] INFO     -- Angel allocated nodeid: 3Pseudo-terminal will not be allocated because stdin is not a terminal.Shutting down MySQL.... SUCCESS! Starting MySQL... SUCCESS! Pseudo-terminal will not be allocated because stdin is not a terminal.Shutting down MySQL.... SUCCESS! Starting MySQL..... SUCCESS! 

参考链接

MySQL Cluster集群搭建-CSDN博客