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

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

目录

    如何搭建(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)
    

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

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

    发布于 2021-06-21 22:20:54
    收藏
    分享
    海报
    0 条评论
    159
    上一篇:mybatis(如何判断list集合是否包含指定数据) 下一篇:如何使用Spring自定义Xml标签
    目录

      0 条评论

      本站已关闭游客评论,请登录或者注册后再评论吧~

      忘记密码?

      图形验证码