如何搭建|如何搭建 MySQL 高可用高性能集群
目录
- MySQL NDB Cluster 是什么
- 搭建集群的前置工作
- 开始部署集群
- 部署管理服务器
- 部署数据服务器
- 部署 SQL 服务
- 所有集群服务部署完毕，我们来测试一下集群是否真的部署成功
- 数据库集群部署成功了，总结一下集群的注意事项
MySQL NDB Cluster 是什么
MySQL NDB Cluster 是 MySQL 的一个高可用、高冗余版本，适用于分布式计算环境。
文档链接
搭建集群的前置工作
至少准备 3 台服务器，一台作为管理服务器，两台作为数据服务器和 SQL 服务器，当然有更多的服务器会更好。
管理服务器mgm：192.168.0.105
数据服务器ndb1：192.168.0.106
数据服务器ndb2：192.168.0.104
sql服务器：192.168.0.106
sql服务器：192.168.0.104
开始部署集群
首先下载 MySQL NDB Cluster二进制文件，解压缩后开始下面的步骤。
部署管理服务器
更新系统
apt update -y && apt upgrade -y && apt install libncurses5 -y
复制 ndb_mgm 和 ndb_mgmd 到管理服务器
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndb_mgm* mgm@192.168.0.105:/home/mgm
在管理服务器复制 ndb_mgm 和 ndb_mgmd 到/usr/local/bin 文件夹
cp -rfv /home/mgm/ndb_mgm* /usr/local/bin
赋予 ndb_mgm 和 ndb_mgmd 可执行权限
chmod +x /usr/local/bin/ndb_mgm*
添加配置文件
mkdir /var/lib/mysql-clustervi /var/lib/mysql-cluster/config.ini
config.ini
[ndbd default]# Options affecting ndbd processes on all data nodes:NoOfReplicas=2# Number of fragment replicasDataMemory=98M# How much memory to allocate for data storage[ndb_mgmd]# Management process options:HostName=192.168.0.105# Hostname or IP address of management nodeNodeId=1# Node ID for this Management nodeDataDir=/var/lib/mysql-cluster# Directory for management node log files[ndbd]# Options for data node "A":# (one [ndbd] section per data node)HostName=192.168.0.104# Hostname or IP addressNodeId=2# Node ID for this data nodeDataDir=/data/mysql-cluster/data# Directory for this data node's data files[ndbd]# Options for data node "B”:# (one [ndbd] section per data node)HostName=192.168.0.106# Hostname or IP addressNodeId=3# Node ID for this data nodeDataDir=/data/mysql-cluster/data# Directory for this data node's data files[mysqld]# SQL node options:HostName=192.168.0.104# Hostname or IP address# (additional mysqld connections can be# specified for this node for various# purposes such as running ndb_restore)[mysqld]# SQL node options:HostName=192.168.0.106# Hostname or IP address# (additional mysqld connections can be# specified for this node for various# purposes such as running ndb_restore)
开启防火墙，集群管理服务默认使用 1186 端口
ufw allow 22ufw allow 1186ufw enable
初始化并启动管理服务器
cd /usr/local/bin/ndb_mgmd --initial --configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=1
当出现以下结果的时候，表示管理服务器已经启动成功了
root@mgm:/usr/local/bin# ndb_mgmd --initial --configdir=/var/lib/mysql-cluster -f /var/lib/mysql-cluster/config.ini --ndb-nodeid=1MySQL Cluster Management Server mysql-5.7.33 ndb-7.6.17
我们再执行 ndb_mgm 命令，可以查看当前集群的状态
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 (not connected, accepting connect from 192.168.0.104)id=3 (not connected, accepting connect from 192.168.0.106)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105(mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.0.104)id=5 (not connected, accepting connect from 192.168.0.106)
部署数据服务器
在所有数据服务器上执行以下操作
更新系统
apt update -y && apt upgrade -y && apt install libncurses5 -y
开启防火墙
ufw allow 22ufw allow 2202ufw enable
复制 ndbd 和 ndbmtd 到数据服务器
#复制到192.168.0.106scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd ndb1@192.168.0.106:/home/ndb1scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd ndb1@192.168.0.106:/home/ndb1#复制到192.168.0.104scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd ndb2@192.168.0.104:/home/ndb2scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd ndb2@192.168.0.104:/home/ndb2
在管理服务器复制 ndbd 和 ndbmtd 到/usr/local/bin 文件夹
#192.168.0.106cp -rfv /home/ndb1/ndbd /usr/local/bincp -rfv /home/ndb1/ndbmtd /usr/local/bin#192.168.0.104cp -rfv /home/ndb2/ndbd /usr/local/bincp -rfv /home/ndb2/ndbmtd /usr/local/bin
赋予 ndbd 可执行权限
chmod +x /usr/local/bin/ndbdchmod +x /usr/local/bin/ndbmtd
在/etc下加入my.cnf文件
vi /etc/my.cnf
my.cnf文件
[mysqld]# Options for mysqld process:ndbcluster# run NDB storage engine[mysql_cluster]# Options for NDB Cluster processes:ndb-connectstring=192.168.0.105# location of management server
创建数据保存的目录，必须与管理服务配置的路径一致
mkdir -p /data/mysql-cluster/data
启动数据服务
root@ndb1:/usr/local/bin# ndbd2021-06-20 08:10:23 [ndbd] INFO-- Angel connected to '192.168.0.105:1186'2021-06-20 08:10:23 [ndbd] INFO-- Angel allocated nodeid: 3
回到集群管理服务器查看集群状态，此时可以看到数据服务已经连接成功
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 (not connected, accepting connect from 192.168.0.104)id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, starting, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.0.104)id=5 (not connected, accepting connect from 192.168.0.106)
在另一台服务器（192.168.0.104）重复 4、5、6、7 步骤的操作，结果可看到
root@ndb2:/usr/local/bin# ndbd2021-06-20 08:20:10 [ndbd] INFO -- Angel connected to '192.168.0.105:1186'2021-06-20 08:20:10 [ndbd] INFO -- Angel allocated nodeid: 2
回到集群管理服务器查看集群状态，此时可以看到所有数据服务已经连接成功
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.0.104)id=5 (not connected, accepting connect from 192.168.0.106)在目录/data/mysql/data下面可以看到数据服务已经产生了数据root@ndb1:~# ls /data/mysql/data/ndb_3_fs ndb_3_out.log ndb_3.pid
部署 SQL 服务
复制 MySQL 到SQL服务器
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz ndb2@192.168.0.104:/home/ndb2scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gz ndb1@192.168.0.106:/home/ndb1
解压缩 MySQL, 然后复制到/usr/local目录
tar -zxvf mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64.tar.gzcp -rfv mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64 /usr/local/ln -snf /usr/local/mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64 /usr/local/mysqlcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.serverexport PATH=$PATH:/usr/local/mysql/binsource /etc/profile
开启防火墙
ufw allow 22ufw allow 3306ufw enable
创建 MySQL 数据存放的目录
mkdir -p /data/mysql/datamkdir -p /data/mysql/runmkdir -p /var/log/mysql
创建 mysql 用户，创建相关目录
groupadd mysqluseradd -r -g mysql -s /bin/false mysqlchown mysql:mysql /data/mysql/datachmod 750 /data/mysql/datachown mysql:mysql /data/mysql/runchmod 750 /data/mysql/runchown mysql:mysql /var/log/mysqlchmod 750 /var/log/mysql
创建 MySQL 配置文件
mkdir -p /etc/mysqlvi /etc/mysql/my.cnfmy.cnf[mysqld]# Options for mysqld process:ndbcluster # run NDB storage enginepid-file = /data/mysql/run/mysqld.pidsocket = /data/mysql/run/mysqld.sockdatadir = /data/mysql/data# log-error = /var/log/mysql/error.log# By default we only accept connections from localhostbind-address = 192.168.0.106# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links = 0[mysql_cluster]# Options for NDB Cluster processes:ndb-connectstring = 192.168.0.105 # location of management server[client]socket = /data/mysql/run/mysqld.sock
初始化MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql
记录下 MySQL 初始化生成的 root 用户密码 sF#Hy,IuT6d#
root@ndb1:~# /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql2021-06-20T12:23:26.874302Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2021-06-20T12:23:27.102146Z 0 [Warning] InnoDB: New log files created, LSN=457902021-06-20T12:23:27.145317Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2021-06-20T12:23:27.154405Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 50a15854-d1c2-11eb-9792-000c29681e23.2021-06-20T12:23:27.155927Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2021-06-20T12:23:28.339372Z 0 [Warning] CA certificate ca.pem is self signed.2021-06-20T12:23:28.624534Z 1 [Note] A temporary password is generated for root@localhost: sF#Hy,IuT6d#
启动MySQL
/usr/local/mysql/bin/mysqld_safe --user=mysql &
修改 root 用户密码
mysqladmin -uroot -p'sF#Hy,IuT6d#' password '123456'
回到集群管理服务器查看集群状态，此时可以看到有一个 SQL 服务已经连接上了
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 (not connected, accepting connect from 192.168.0.104)id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)
在另一台服务器（192.168.0.104）部署 SQL 服务，回到集群管理服务器查看集群状态，此时可以看到所有 SQL 服务已经连接成功
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showCluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)
所有集群服务部署完毕，我们来测试一下集群是否真的部署成功
在 192.168.0.106 的 MySQL 上创建数据库和表
CREATE DATABASE `wechat`; CREATE TABLE wechat.user (Column1 varchar(100) NULL,Column2 varchar(100) NULL)ENGINE=ndbclusterDEFAULT CHARSET=utf8mb4COLLATE=utf8mb4_general_ci; 插入数据并查看mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || ndbinfo || performance_schema || sys || wechat |+--------------------+6 rows in set (0.00 sec)mysql> select * from wechat.user; Empty set (0.02 sec)mysql> insert wechat.user (Column1, column2) value ('1', '2'); Query OK, 1 row affected (0.01 sec)mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 1 | 2 |+---------+---------+1 row in set (0.00 sec)
在另一个 SQL 服务器查询，结果是成功的
mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || ndbinfo || performance_schema || sys || wechat |+--------------------+6 rows in set (0.00 sec)mysql> select * from wechat.user; Empty set (0.07 sec)mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 1 | 2 |+---------+---------+1 row in set (0.00 sec)
现在我们把其中一个数据节点关掉，在管理服务器我们看到 ndbd已经关闭一个了
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)id=3 (not connected, accepting connect from 192.168.0.106)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)
写入一笔数据
mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 1 | 2 |+---------+---------+1 row in set (0.01 sec)mysql> insert into wechat.user (Column1, column2) value ('3', '4'); Query OK, 1 row affected (0.00 sec)mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 3 | 4 || 1 | 2 |+---------+---------+2 rows in set (0.00 sec)
在另一台 SQL 服务器查询，结果还是一致的
mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 3 | 4 || 1 | 2 |+---------+---------+2 rows in set (0.00 sec)
我们再关闭 192.168.0.106 SQL服务
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)id=3 (not connected, accepting connect from 192.168.0.106)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)id=5 (not connected, accepting connect from 192.168.0.106)
在 192.168.0.104 的 SQL 服务写入一笔数据
mysql> insert into wechat.user (Column1, column2) value ('5', '6'); Query OK, 1 row affected (0.00 sec)mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 5 | 6 || 3 | 4 || 1 | 2 |+---------+---------+3 rows in set (0.00 sec)
启动 192.168.0.106 的数据服务和SQL服务
root@mgm:/usr/local/bin# ndb_mgm-- NDB Cluster -- Management Client --ndb_mgm> showConnected to Management Server at: localhost:1186Cluster Configuration---------------------[ndbd(NDB)] 2 node(s)id=2 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0, *)id=3 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17, Nodegroup: 0)[ndb_mgmd(MGM)] 1 node(s)id=1 @192.168.0.105 (mysql-5.7.33 ndb-7.6.17)[mysqld(API)] 2 node(s)id=4 @192.168.0.104 (mysql-5.7.33 ndb-7.6.17)id=5 @192.168.0.106 (mysql-5.7.33 ndb-7.6.17)
在 192.168.0.106 查询数据库发现，发生故障期间产生的数据已经同步了过来
root@ndb1:~# mysql -uroot -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.33-ndb-7.6.17-cluster-gpl MySQL Cluster Community Server (GPL)Copyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement.mysql> select * from wechat.user; +---------+---------+| Column1 | Column2 |+---------+---------+| 1 | 2 || 5 | 6 || 3 | 4 |+---------+---------+3 rows in set (0.08 sec)
数据库集群部署成功了，总结一下集群的注意事项
- 创建表的时候，需要设置ENGINE=ndbcluster，具体请看上面的建表脚本。
- 每个 SQL 服务需要创建一样的用户密码
- 管理服务器不能全部发生故障，否则集群数据库操作失败。
- 数据服务器不能全部发生故障，否则集群数据库操作失败。
- SQL 服务器发生故障期间建立的数据库，在恢复后不会自动同步新建数据库过来，需要手动在故障恢复后的服务器上创建同名数据库，之后数据才会自动同步过来。
- 只要管理服务器和数据服务器越多，故障发生时，才能保证数据安全的写入，才不会导致数据库系统不可用。
- SQL 服务器越多，把数据库访问的请求通过负载均衡服务分摊到各个 SQL 服务器，才能承受更多的并发量。
- 集群启动必须按照以下顺序依次启动，管理服务->数据服务->SQL服务。
推荐阅读
- 考研英语阅读终极解决方案——阅读理解如何巧拿高分
- 如何寻找情感问答App的分析切入点
- mybatisplus如何在xml的连表查询中使用queryWrapper
- MybatisPlus使用queryWrapper如何实现复杂查询
- 六步搭建ES6语法环境
- 如何在Mac中的文件选择框中打开系统隐藏文件夹
- 漫画初学者如何学习漫画背景的透视画法（这篇教程请收藏好了！）
- java中如何实现重建二叉树
- Linux下面如何查看tomcat已经使用多少线程
- thinkphp|thinkphp 3.2 如何调用第三方类库