搭建MySQL高可用集群

kali
2021-10-22 / 0 评论 / 486 阅读 / 正在检测是否收录...

MySQL InnoDB Cluster 搭建过程

  1. 基于Ubuntu 20.04系统搭建
  2. 系统防火墙均已关闭
  3. 为了方便管理,主节点和从节点root密码统一使用一个
  4. 模式:单主多从
graph LR
A[Application/Client] --> B[Mysql Router]
B --> A
    B --> C[MySQL Master R/W]
    C --> |Group Replication| D[Slave01 R/O]
    C --> |Group Replication| E[Slave02 R/O]
    C --> |Group Replication| F[Slave02 R/O]
    C --> |Group Replication| G[Slave02 R/O]
节点IP作用
node01192.168.121.135
node02192.168.121.136
node03192.168.121.137
node04192.168.121.138路由管理

系统配置(所有服务器)

在每台服务器上配置hosts,编辑/etc/hosts文件,加入以下内容

192.168.121.135 node01
192.168.121.136 node02
192.168.121.137 node03
192.168.121.138 node04

软件安装

主节点、从节点、路由管理节点初始化,安装MySQL官方apt源

sudo wget https://repo.mysql.com/mysql-apt-config_0.8.19-1_all.deb && sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb && sudo apt-get update

主节点、从节点安装mysql-server、mysql-shell

sudo apt install mysql-server mysql-shell -y

路由管理节点安装mysql-shell、mysql-router

sudo apt install mysql-router mysql-shell -y

MySQL配置(主、从服务器)

修改MySQL配置文件/etc/mysql/mysql.conf.d/mysqld.cnf(主节点)

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
datadir        = /var/lib/mysql
log-error    = /var/log/mysql/error.log

default-storage-engine = INNODB
character-set-server = utf8mb4
port = 3306


#复制框架
server_id = 1
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
max_connections = 100
max_allowed_packet = 100M

#组复制设置
#server必须为每个事务收集写集合,并使用XXHASH64哈希算法将其编码为散列
transaction_write_set_extraction = XXHASH64
#告知插件加入或创建组命名,UUID
loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。
loose-group_replication_start_on_boot = off
#告诉插件使用IP地址,端口33061用于接收组中其他成员转入连接
loose-group_replication_local_address = "node01:33061"
#启动组server,种子server,加入组应该连接这些的ip和端口;其他server要加入组得由组成员同意
loose-group_replication_group_seeds = "node01:33061,node02:33061,node03:33061"
loose-group_replication_ip_whitelist = "node01,node02,node03,node04"
loose-group_replication_bootstrap_group = off
# 使用MGR的单主模式
loose-group_replication_single_primary_mode = on
loose-group_replication_enforce_update_everywhere_checks = off
disabled_storage_engines = MyISAM,BLACKHOLE,FEDERATED,CSV,ARCHIVE

从节点也需要修改,只需要把配置文件中的server_id、loose-group_replication_local_address修改为对应的值

例如node02

server_id = 2
loose-group_replication_local_address = "node02:33061"

以此类推

重启MySQL

sudo service mysql restart

使root用户支持远程登录

update mysql.user set host='%' where user='root';

赋予管理集群所需的权限

GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

刷新权限

flush privileges;

配置本地实例

在所有节点服务器上配置本地实例,使用mysqlsh工具

dba.configureLocalInstance('root@localhost:3306', {'password': 'root', 'interactive': false});

检查实例状态

dba.checkInstanceConfiguration("root@localhost:3306");

如果输出以下内容说明配置成功。

The instance 'node01:3306' is valid to be used in an InnoDB cluster.

{
    "status": "ok"
}

创建集群

在主节点上创建集群,同样使用mysqlsh工具

# 连接实例
shell.connect('root@localhost:3306');
# 创建一个集群,命名为 'myCluster'
var cluster = dba.createCluster('myCluster');
# 查看集群状态
cluster.status();

输出以下内容就表示创建成功

{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "node01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures.", 
        "topology": {
            "node01:3306": {
                "address": "node01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.27"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "node01:3306"
}

从节点加入集群

在主节点中打开mysqlsh工具

# 连接实例
shell.connect('root@localhost:3306');
# 根据集群名字获取集群对象
var cluster = dba.getCluster('myCluster');
# 将node02节点加入集群
cluster.addInstance('root@node02:3306');

如果出现

WARNING: A GTID set check of the MySQL instance at 'node02:3306' determined that it contains transactions that do not originate from the cluster, which must be discarded before it can join the cluster.

node02:3306 has the following errant GTIDs that do not exist in the cluster:
bbdb0716-32e7-11ec-86e0-000c29040a05:1-2

WARNING: Discarding these extra GTID events can either be done manually or by completely overwriting the state of node02:3306 with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

Having extra GTID events is not expected, and it is recommended to investigate this further and ensure that the data can be removed prior to choosing the clone recovery method.

Please select a recovery method [C]lone/[A]bort (default Abort):

输入C继续,之后提示出下面内容说明加入成功

The instance 'node02:3306' was successfully added to the cluster.

其他从节点加入的方式一致

查询集群状态

cluster.status()

输出下面内容

{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "node01:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "node01:3306": {
                "address": "node01:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.27"
            }, 
            "node02:3306": {
                "address": "node02:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.27"
            }, 
            "node03:3306": {
                "address": "node03:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": null, 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.0.27"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "node01:3306"
}

可以看到,所有节点均已加入集群,并且正常运行

配置 MySQL Router

创建配置文件保存目录

sudo mkdir /mysqlrouter

将创建的目录授权给mysqlrouter用户

编辑/etc/apparmor.d/usr.bin.mysqlrouter文件,加入以下内容

# Allow config file access
  /mysqlrouter/ rw,
  /mysqlrouter/** rw,

重启apparmor

sudo /etc/init.d/apparmor restart

配置 MySQL Router,生成配置文件到/data/mysqlrouter目录

sudo mysqlrouter --bootstrap node01:3306 --directory /mysqlrouter --user=root --conf-use-sockets --force

--conf-use-sockets 表示使用 unix sockets 通信

--directory 指定配置文件保存目录

输出下面内容表示创建成功

# Bootstrapping MySQL Router instance at '/mysqlrouter'...

- Creating account(s) (only those that are needed, if any)
- Verifying account (using it to run SQL queries that would be run by Router)
- Storing account in keyring
- Adjusting permissions of generated files
- Creating configuration /mysqlrouter/mysqlrouter.conf

# MySQL Router configured for the InnoDB Cluster 'myCluster'

After this MySQL Router has been started with the generated configuration

    $ mysqlrouter -c /mysqlrouter/mysqlrouter.conf

InnoDB Cluster 'myCluster' can be reached by connecting to:

## MySQL Classic protocol

- Read/Write Connections: localhost:6446, /mysqlrouter/mysql.sock
- Read/Only Connections:  localhost:6447, /mysqlrouter/mysqlro.sock

## MySQL X protocol

- Read/Write Connections: localhost:6448, /mysqlrouter/mysqlx.sock
- Read/Only Connections:  localhost:6449, /mysqlrouter/mysqlxro.sock

启动 MySQL Router

sudo /mysqlrouter/start.sh

启动完成后会监听下面几个端口

端口socket文件协议服务
6446/mysqlrouter/mysql.sockMySQLMySQL 读写
6447/mysqlrouter/mysqlro.sockMySQLMySQL 只读
6448/mysqlrouter/mysqlx.sockMySQL XMySQL X 读写
6449/mysqlrouter/mysqlxro.sockMySQL XMySQL X 只读
8443HTTPSWeb 管理

验证集群

  1. 登录后,新建一个数据库和表,往里面写进数据,查看从节点数据会不会同步;
  2. 关闭node01的mysql服务,route将主节点自动切换到node02,node02从只读变为可读写,重新启动node01 mysql后,node01变为只读模式。
0

评论 (0)

取消