MySQL InnoDB Cluser 多主模式实现高可用负载均衡

核心架构

MySQL 的 Group Replication 功能，可以在一组 MySQL 服务器之间实现自动主机选举，形成一主多从结构。经过高级配置后，可以实现多主多从结构。
MySQL Router 是一个轻量级透明中间件，可以自动获取上述集群的状态，规划 SQL 语句，分配到合理的 MySQL 后端进行执行。
MySQL Shell 是一个同时支持 JavaScript 和 SQL 的交互程序，可以快速配置 InnoDB Cluster。

准备工作

本次共 5 台机器，服务器均采用 Windows Server 2012，服务器地址如下：

172.16.220.10
172.16.220.11
172.16.220.12
172.16.220.13
172.16.220.14

下载 MySQL、Shell、Router 软件

https://cdn.mysql.com/Downloads/MySQL-8.0/mysql-8.0.15-winx64.zip
https://cdn.mysql.com/Downloads/MySQL-Router/mysql-router-8.0.15-winx64.zip
https://cdn.mysql.com/Downloads/MySQL-Shell/mysql-shell-8.0.15-windows-x86-64bit.zip

安装 MySQL

首先配置 my.ini，以下 my.ini 为 172.16.2201.10 上的配置：

配置 MySQL 数据库
D:/PhpWamp/phpwamp/server/mysql-8.1/bin/mysqld.exe --defaults-file=D:/PhpWamp/phpwamp/server/mysql-8.1/my.ini --initialize-insecure --user=root --basedir=D:/PhpWamp/phpwamp/server/mysql-8.1 --datadir=D:/PhpWamp/phpwamp/server/mysql-8.1/data

进入 MySQL 后，修改密码及配置权限：
安装配置 MySQL 服务
D:/PhpWamp/phpwamp/server/mysql-8.1/bin/mysqld.exe --install .MySQL.InnoDB.Cluster --defaults-file=D:/PhpWamp/phpwamp/server/mysql-8.1/my.ini

配置 Shell
打开 mysqlsh.exe，运行以下命令，5 台服务器均需要运行，注意修改连接地址：
# 检查mysql 配置文件   (5台主机都要操作此步骤)
dba.checkInstanceConfiguration('root@172.16.220.10:3346')

+----------------------------------+---------------+----------------+--------------------------------------------------+
| Variable                         | Current Value | Required Value | Note                                             |
+----------------------------------+---------------+----------------+--------------------------------------------------+
| binlog_checksum                  | CRC32         | NONE           | Update the server variable or restart the server |
| binlog_format                    | MIXED         | ROW            | Update the server variable or restart the server |
| enforce_gtid_consistency         | OFF           | ON             | Restart the server                               |
| gtid_mode                        | OFF           | ON             | Restart the server                               |
| log_slave_updates                | 0             | ON             | Restart the server                               |
| master_info_repository           | FILE          | TABLE          | Restart the server                               |
| relay_log_info_repository        | FILE          | TABLE          | Restart the server                               |
| transaction_write_set_extraction | OFF           | XXHASH64       | Restart the server                               |
+----------------------------------+---------------+----------------+--------------------------------------------------+

# 修复mysql 配置文件，必须用 root(5台主机都要操作此步骤)
dba.configureLocalInstance('root@172.16.220.10:3346')

Please provide the password for 'root@172.16.220.10:3346':
Detecting the configuration file...
Found configuration file at standard location: /etc/my.cnf
Do you want to modify this file? [Y|n]:  [Y|n]: Y


# 重启mysql

# 重新检查  (5台主机都要操作此步骤)            
dba.checkInstanceConfiguration('root@db1:3306')    
Please provide the password for 'root@db1:3306':
Validating instance...

The instance 'db1:3306' is valid for Cluster usage
{
    "status": "ok"
}

其中一台电脑上运行：
# 连接MySQL
shell.connect('root@172.16.220.10:3346')

# 创建集群
# var cluster = dba.createCluster('mycluster')
var cluster = dba.createCluster('mycluster', {multiPrimary: true, force: true})

A new InnoDB cluster will be created on instance 'root@172.16.220.10:3346'.

Creating InnoDB cluster 'main' on 'root@172.16.220.10:3346'...
Adding Seed Instance...

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

# 添加子节点
cluster.addInstance('root@172.16.220.11:3346')
cluster.addInstance('root@172.16.220.12:3346')

cluster.addInstance('root@172.16.220.13:3346')
cluster.addInstance('root@172.16.220.14:3346')

# 查看节点信息
cluster.status()

# 查看基本信息
cluster.describe();

# 退出之后，再查看节点信息
var cluster = dba.getCluster();
cluster.status();

MySQL [172.16.220.10 ssl] JS> cluster.status()
{
    "clusterName": "mycluster",
    "defaultReplicaSet": {
        "name": "default",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to 2 failures.",
        "topology": {
            "172.16.220.10:3346": {
                "address": "172.16.220.10:3346",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "172.16.220.11:3346": {
                "address": "172.16.220.11:3346",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "172.16.220.12:3346": {
                "address": "172.16.220.12:3346",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "172.16.220.13:3346": {
                "address": "172.16.220.13:3346",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "172.16.220.14:3346": {
                "address": "172.16.220.14:3346",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        },
        "topologyMode": "Multi-Primary"
    },
    "groupInformationSourceMember": "172.16.220.10:3346"
}

查看最终状态
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST   | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 6446e1da-4632-11e9-bc0a-4ccc6a123cdf | 172.16.220.10 |        3346 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 87112871-4613-11e9-a64e-4ccc6a123c66 | 172.16.220.12 |        3346 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 9020fd22-4612-11e9-bcbb-4ccc6a124138 | 172.16.220.13 |        3346 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | a7785098-4633-11e9-8af6-4ccc6a123be0 | 172.16.220.11 |        3346 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | f1883c54-4606-11e9-8e4a-4ccc6a1242e6 | 172.16.220.14 |        3346 | ONLINE       | PRIMARY     | 8.0.15         |
+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+
5 rows in set (0.02 sec)

配置 Router
D:/PhpWamp/phpwamp/server/mysql-8.1/bin/mysqlrouter.exe --bootstrap root@172.16.220.10:3346

Please enter MySQL password for root:

WARNING: The MySQL server does not have SSL configured and metadata used by the router may be transmitted unencrypted.

Bootstrapping system MySQL Router instance...
MySQL Router  has now been configured for the InnoDB cluster 'main'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'main':
- Read/Write Connections: localhost:6446    读写
- Read/Only Connections: localhost:6447     只读

X protocol connections to cluster 'main':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

Existing configurations backed up to /etc/mysqlrouter/mysqlrouter.conf.bak

# File automatically generated during MySQL Router bootstrap

[DEFAULT]
name=system
keyring_path=D:/PhpWamp/phpwamp/server/mysql-8.1/var/lib/mysqlrouter/keyring
master_key_path=D:/PhpWamp/phpwamp/server/mysql-8.1/mysqlrouter.key
connect_timeout=15
read_timeout=30
dynamic_state=D:/PhpWamp/phpwamp/server/mysql-8.1/var/lib/mysqlrouter/state.json

[logger]
level = INFO

[metadata_cache:mycluster]
router_id=6
user=mysql_router6_gl0rrazm7cya
metadata_cluster=mycluster
ttl=0.5

[routing:mycluster_default_rw]
bind_address=0.0.0.0
bind_port=6446
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=classic

[routing:mycluster_default_x_rw]
bind_address=0.0.0.0
bind_port=64460
destinations=metadata-cache://mycluster/default?role=PRIMARY
routing_strategy=round-robin
protocol=x

故障模拟
在 Java 中的连接配置
# 多主模式：loadbalance  
jdbc.url=jdbc:mysql:loadbalance://172.16.220.10:3346,172.16.220.11:3346,172.16.220.12:3346,172.16.220.13:3346,172.16.220.14:3346/eis?loadBalanceBlacklistTimeout=5000&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true

# 主从模式:replication  
jdbc.url=jdbc:mysql:replication://172.16.220.10:3346,172.16.220.11:3346,172.16.220.12:3346,172.16.220.13:3346,172.16.220.14:3346/eis?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true

备注信息
var cluster = dba.rebootClusterFromCompleteOutage(); # 如果需要重启集群

dba.dropMetadataSchema(); # 删除schema

cluster.rejoinInstance("root@172.16.220.10:3346"); # 重新加入节点，我本地测试的时候发现rejoin一直无效

cluster.dissolve({force:true});  # 解散集群

cluster.addInstance("root@172.16.220.10:3346"); # 增加节点

cluster.removeInstance("root@172.16.220.10:3346"); # 删除节点

cluster.removeInstance('root@172.16.220.10:3346',{force:true}); # 强制删除节点

cluster.describe(); # 集群描述

# 集群节点状态
* ONLINE: The instance is online and participating in the cluster.
* OFFLINE: The instance has lost connection to the other instances.
* RECOVERING: The instance is attempting to synchronize with the cluster by retrieving transactions it needs before it can become an ONLINE member.
* UNREACHABLE: The instance has lost communication with the cluster.
* ERROR: The instance has encountered an error during the recovery phase or while applying a transaction.

# 集群有哪些状态
* OK – 所有节点处于online状态，有冗余节点。
* OK_PARTIAL – 有节点不可用，但仍有冗余节点。
* OK_NO_TOLERANCE – 有足够的online节点，但没有冗余，例如：两个节点的Cluster，其中一个挂了，集群就不可用了。
* NO_QUORUM – 有节点处于online状态，但达不到法定节点数，此状态下Cluster无法写入，只能读取。
* UNKNOWN – 不是online或recovering状态，尝试连接其他实例查看状态。
* UNAVAILABLE – 组内节点全是offline状态，但实例在运行，可能实例刚重启还没加入Cluster

6 回帖

MySQL InnoDB Cluser 多主模式实现高可用负载均衡

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册关于

请输入回帖内容 ...

zxniuniu • 5 年前
付费者作者

数据量太大，日志太多，还没具体开始找原因，其它的数据库均正常，就是 solo 的数据库存在数据自动回滚的问题，我就是先问下，solo 内部是不是有什么其它机制导致的，实在不行，准备再撤回来，不采用集群，solo 单独数据库了，数据量太大，不好排查。

1 回复
其他回帖
88250 • 5 年前
订阅者

客气了，也感谢你让我知道潜在的问题，等以后有空我进行一下相关测试 🙏
zxniuniu • 5 年前
付费者作者

@88250 D 大，再打扰下 😄 ，发现个问题，如果我数据为采用 InnoDB Cluster 的主从模式，在发布文章后，主 SQL 数据库正常更新，4 秒后 SQL 回滚，更新失败。我其它的数据库操作均正常，就是 solo 的数据库存在该问题。

1 回复
88250 • 5 年前
订阅者

为什么有回滚呢？

1 回复
查看全部回帖

核心架构

准备工作

下载 MySQL、Shell、Router 软件

安装 MySQL

相关帖子

安装 MariaDB 在 Ubuntu20.4 中

如何在 MySQL / MariaDB 中导入导出数据，导入导出数据库文件、Excel、CSV

Debian9,Debian10 安装 LNMP 环境 (所有版本最新)

通过 solo 快速搭建个人博客 (Mariadb 数据库)

Springboot 使用 Logback 自动同步指定级别日志到 MariaDB 数据库

CentOS 安装 MariaDB 数据库，修改字符集

MariaDB 快速入门

欢迎来到这里！