欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

如何搭建 MySQL 高可用高性能集群

程序员文章站 2022-07-01 09:18:00
目录mysql ndb cluster 是什么部署 sql 服务mysql ndb cluster 是什么mysql ndb cluster 是 mysql 的一个高可用、高冗余版本,适用于分布式计算...

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-cluster
vi /var/lib/mysql-cluster/config.ini

config.ini

[ndbd default]
# options affecting ndbd processes on all data nodes:
noofreplicas=2                     # number of fragment replicas
datamemory=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 node
nodeid=1                           # node id for this management node
datadir=/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 address
nodeid=2                          # node id for this data node
datadir=/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 address
nodeid=3                        # node id for this data node
datadir=/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 22
ufw allow 1186
ufw 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=1
mysql 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> show
connected to management server at: localhost:1186
cluster 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 22
ufw allow 2202
ufw enable

复制 ndbd 和 ndbmtd 到数据服务器

#复制到192.168.0.106
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd ndb1@192.168.0.106:/home/ndb1
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbmtd ndb1@192.168.0.106:/home/ndb1

#复制到192.168.0.104
scp ./mysql-cluster-gpl-7.6.17-linux-glibc2.12-x86_64/bin/ndbd ndb2@192.168.0.104:/home/ndb2
scp ./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.106
cp -rfv /home/ndb1/ndbd /usr/local/bin
cp -rfv /home/ndb1/ndbmtd /usr/local/bin

#192.168.0.104
cp -rfv /home/ndb2/ndbd /usr/local/bin
cp -rfv /home/ndb2/ndbmtd /usr/local/bin

赋予 ndbd 可执行权限

chmod +x /usr/local/bin/ndbd
chmod +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# ndbd
2021-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> show
connected to management server at: localhost:1186
cluster 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# ndbd
2021-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> show
connected to management server at: localhost:1186
cluster 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/ndb2
scp ./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.gz
cp -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/mysql

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server
export path=$path:/usr/local/mysql/bin
source /etc/profile

开启防火墙

ufw allow 22
ufw allow 3306
ufw enable

创建 mysql 数据存放的目录

mkdir -p /data/mysql/data
mkdir -p /data/mysql/run
mkdir -p /var/log/mysql

创建 mysql 用户,创建相关目录

groupadd mysql
useradd -r -g mysql -s /bin/false mysql
chown mysql:mysql /data/mysql/data
chmod 750 /data/mysql/data

chown mysql:mysql /data/mysql/run
chmod 750 /data/mysql/run

chown mysql:mysql /var/log/mysql
chmod 750 /var/log/mysql

创建 mysql 配置文件

mkdir -p /etc/mysql
vi /etc/mysql/my.cnf
my.cnf
[mysqld]
# options for mysqld process:
ndbcluster # run ndb storage engine

pid-file = /data/mysql/run/mysqld.pid
socket = /data/mysql/run/mysqld.sock
datadir = /data/mysql/data
# log-error = /var/log/mysql/error.log
# by default we only accept connections from localhost
bind-address = 192.168.0.106
# disabling symbolic-links is recommended to prevent assorted security risks
symbolic-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/mysql
2021-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=45790
2021-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> show
connected to management server at: localhost:1186
cluster 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> show
cluster 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=ndbcluster
default charset=utf8mb4
collate=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> show
connected to management server at: localhost:1186
cluster 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> show
connected to management server at: localhost:1186
cluster 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> show
connected to management server at: localhost:1186
cluster 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 -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 4
server 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 its
affiliates. other names may be trademarks of their respective
owners.

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)

数据库集群部署成功了,总结一下集群的注意事项

  1. 创建表的时候,需要设置engine=ndbcluster,具体请看上面的建表脚本。
  2. 每个 sql 服务需要创建一样的用户密码
  3. 管理服务器不能全部发生故障,否则集群数据库操作失败。
  4. 数据服务器不能全部发生故障,否则集群数据库操作失败。
  5. sql 服务器发生故障期间建立的数据库,在恢复后不会自动同步新建数据库过来,需要手动在故障恢复后的服务器上创建同名数据库,之后数据才会自动同步过来。
  6. 只要管理服务器和数据服务器越多,故障发生时,才能保证数据安全的写入,才不会导致数据库系统不可用。
  7. sql 服务器越多,把数据库访问的请求通过负载均衡服务分摊到各个 sql 服务器,才能承受更多的并发量。
  8. 集群启动必须按照以下顺序依次启动,管理服务->数据服务->sql服务。

以上就是如何搭建 mysql 高可用高性能集群的详细内容,更多关于搭建 mysql 高可用高性能集群的资料请关注其它相关文章!

相关标签: MySQL 集群