9.InnoDB Cluster 架构图

InnoDB Cluster 架构

架构图

下载 MySQL Shell

cd /usr/local/
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.35-linux-glibc2.12-x86-64bit.tar.gz
tar xvf mysql-shell-8.0.35-linux-glibc2.12-x86-64bit.tar.gz
ln -s mysql-shell-8.0.35-linux-glibc2.12-x86-64bit mysql-shell
export PATH=$PATH:/usr/local/mysql-shell/bin

部署 InnoDB Cluster

1. 初始化实例

2. 创建超级管理员账号

三个节点都要创建：

set session sql_log_bin=0;
create user root@'%' identified by '123456';
grant all on *.* to root@'%' with grant option;
set session sql_log_bin=1;

3. 配置实例

三个节点均要执⾏，⾸先配置 node1。

MySQL JS > dba.configureInstance('root:123456@192.168.79.10:3306', { 
    clusterAdmin: 'cluster_admin', 
    clusterAdminPassword: 'cluster_pass' 
})
Configuring MySQL instance at 192.168.79.10:3306 for use in an InnoDB cluster...
 This instance reports its own address as 192.168.79.10:3306
 Assuming full account name 'cluster_admin'@'%' for cluster_admin
 applierWorkerThreads will be set to the default value of 4.
 NOTE: Some configuration options need to be fixed:
 +----------------------------------------+---------------+----------------+----------------------------+
 | Variable                               
Configuring instance...
 | Current Value | Required Value | Note       
|
 +----------------------------------------+---------------+----------------+----------------------------+
 | binlog_transaction_dependency_tracking | COMMIT_ORDER  | WRITESET       
| Update the 
server variable |
 +----------------------------------------+---------------+----------------+----------------------------+
Do you want to perform the required configuration changes? [y/n]: y
 Creating user cluster_admin@%.
 Account cluster_admin@% was successfully created.
 WARNING: '@@binlog_transaction_dependency_tracking' is deprecated and will be removed 
in a future release. (Code 1287).
 The instance '192.168.79.10:3306' was configured to be used in an InnoDB cluster.

命令中的 clusterAdmin 和 clusterAdminPassword ⽤来指定集群的管理账号及密码。

接下来，配置 node2, node3：

MySQL JS > dba.configureInstance('root:123456@192.168.79.20:3306', {
    clusterAdmin:'cluster_admin', clusterAdminPassword:'cluster_pass' })

MySQL JS > dba.configureInstance('root:123456@192.168.79.30:3306', {
    clusterAdmin:'cluster_admin', clusterAdminPassword:'cluster_pass' })

4. 创建 InnoDB Cluster

因为 node1 是 Primary 节点，这⾥登录 node1 执⾏创建操作。

MySQL JS > shell.connect('cluster_admin:cluster_pass@192.168.79.10:3306')
MySQL 192.168.79.10:3306 ssl JS > dba.createCluster('myCluster', { disableClone: false })
MySQL  192.168.79.10:3306 ssl  JS > dba.createCluster('myCluster', { disableClone: 
false })
 A new InnoDB Cluster will be created on instance '192.168.79.10:3306'.
 Validating instance configuration at 192.168.79.10:3306...
 This instance reports its own address as 192.168.79.10:3306
 * Checking connectivity and SSL configuration...
 Instance configuration is suitable.
 NOTE: Group Replication will communicate with other members using '192.168.79.10:3306'. 
Use the localAddress option to override.
 Creating InnoDB Cluster 'myCluster' on '192.168.79.10:3306'...
 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:myCluster>

5. 添加节点

添加节点依赖于 Cluster 对象，在执⾏添加操作之前，先通过 dba.getCluster 命令获取⼀个 Cluster 对象。

MySQL 192.168.79.10:3306 ssl JS > cluster=dba.getCluster('myCluster')
<Cluster:myCluster>

// 添加 node2
MySQL 192.168.79.10:3306 ssl JS > 
cluster.addInstance('cluster_admin:cluster_pass@192.168.79.20:3306')

// 添加 node3
MySQL 192.168.79.10:3306 ssl JS > 
cluster.addInstance('cluster_admin:cluster_pass@192.168.79.30:3306')

6. 查看 Cluster 状态

MySQL 192.168.79.10:3306 ssl JS > cluster.status()

Cluster 状态输出：

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

部署 MySQL Router

MySQL Router 工作流程

应用向 MySQL Router 发起连接MySQL Router 检查并选择一个可用的后端节点MySQL Router 与后端节点建立连接MySQL Router 转发应用与后端节点之间的数据包如果后端节点出现问题，MySQL Router 会断开应用端连接，应用重试后选择其他节点

下载 MySQL Router

cd /usr/local/
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-8.0.35-linux-glibc2.12-x86_64.tar.xz
tar xvf mysql-router-8.0.35-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-router-8.0.35-linux-glibc2.12-x86_64 mysql-router
export PATH=$PATH:/usr/local/mysql-router/bin

基于刚刚创建的 Cluster 初始化 MySQL Router

mkdir -p /data/myrouter/6446
mysqlrouter --bootstrap cluster_admin@192.168.79.10:3306 --user=mysql --directory /data/myrouter/6446 --conf-use-sockets --report-host='192.168.79.100'

启动 MySQL Router

# 使用脚本启动
sh /data/myrouter/6446/start.sh

# 或直接启动
mysqlrouter -c /data/myrouter/6446/mysqlrouter.conf --user=mysql &

默认会开启五个监听端⼝：

在初始化的过程中，MySQL Router 的元数据信息会更新到 InnoDB Cluster 中。
Cluster 相关的 Router 信息可通过 cluster.listRouters() 命令查看。

查看 Router 信息

MySQL 192.168.79.10:3306 ssl JS > cluster.listRouters()
{
 "clusterName": "myCluster", 
"routers": {
 "192.168.79.100::": {
 "hostname": "192.168.79.100", 
"lastCheckIn": "2024-01-24 23:07:27", 
"roPort": "6447", 
"roXPort": "6449", 
"rwPort": "6446", 
"rwXPort": "6448", 
"version": "8.0.35"
  }
  }
 }

至此，InnoDB Cluster 搭建完毕。

高可用测试

测试脚本

vim check_mysql_connection.py

import pymysql
import time
import sys

hostname = '192.168.79.100'
username = 'test_user'
password = '123456'
database = 'information_schema'

def check_mysql(hostname, port, username, password, database, mode):
    try:
        conn = pymysql.connect(
            host=hostname,
            port=port,
            user=username,
            password=password,
            database=database
        )
        cursor = conn.cursor()

        cursor.execute("SELECT @@hostname")
        result = cursor.fetchone()
        print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 当前节点: {result[0]}")

        cursor.close()
        conn.close()
        return True
    except pymysql.Error as e:
        print(f"{time.strftime('%Y-%m-%d %H:%M:%S')} 操作失败:", e)
        return False

if len(sys.argv) > 1:
    mode = sys.argv[1]
    if mode == 'rw':
        port = 6446
    else:
        port = 6447
else:
    print("请提供命令行参数 mode (rw/ro)")
    sys.exit(1)

while True:
    check_mysql(hostname, port, username, password, database, mode)
    time.sleep(1)