[root@mysql-router1 ~]# systemctl start mysqlrouter.service 
[root@mysql-router1 ~]# systemctl enable mysqlrouter.service 
Created symlink /etc/systemd/system/multi-user.target.wants/mysqlrouter.service → /usr/lib/systemd/system/mysqlrouter.service.
[root@mysql-router1 ~]# systemctl status mysqlrouter.service

[root@mysql-router1 ~]# dnf install -y keepalived

global_defs {
  script_user root
  enable_script_security
}

vrrp_script chk_router {
    script "/etc/keepalived/check_router.sh"
    interval 1
    weight -2
}

vrrp_instance VI_1 {
    state MASTER
    interface ens160
    virtual_router_id 51
    priority 100 
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass mypassword
    }   
    virtual_ipaddress {
        192.168.10.200
    }   
    track_script {
        chk_router
    }   
}

global_defs {
  script_user root
  enable_script_security
}

vrrp_script chk_router {
    script "/etc/keepalived/check_router.sh"
    interval 1
    weight -2
}

vrrp_instance VI_1 {
    state BACKUP # 改为备用
    interface ens160
    virtual_router_id 51
    priority 99 # 降低优先级
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass mypassword
    }
    virtual_ipaddress {
        192.168.10.200
    }
    track_script {
        chk_router
    }
}

[root@mysql-router1 ~]# cat > /etc/keepalived/check_router.sh << EOF
#!/bin/bash
if pgrep mysqlrouter > /dev/null; then
    exit 0
else
    exit 1
fi
EOF

[root@mysql-router1 ~]# chmod +x /etc/keepalived/check_router.sh

[root@mysql-router1 ~]# systemctl enable keepalived
[root@mysql-router1 ~]# systemctl start keepalived

# 当前vip位于mysql-router1节点
[root@mysql-router1 ~]# ip a | grep 192.168.10
    inet 192.168.10.204/24 brd 192.168.10.255 scope global noprefixroute ens160
    inet 192.168.10.200/32 scope global ens160
# 停止mysqlrouter服务，模拟router1节点故障
[root@mysql-router1 ~]# systemctl stop mysqlrouter.service 
# 当前vip已经从mysql-router1节点移除。
[root@mysql-router1 ~]# ip a | grep 192.168.10
    inet 192.168.10.204/24 brd 192.168.10.255 scope global noprefixroute ens160

# 当前vip位于mysql-router2节点
[root@mysql-router2 ~]# ip a | grep 192.168.10
    inet 192.168.10.205/24 brd 192.168.10.255 scope global noprefixroute ens160
    inet 192.168.10.200/32 scope global ens160

# mysql-router1节点启动服务，模拟故障恢复
[root@mysql-router1 ~]# systemctl start mysqlrouter.service 
# 当前vip继续漂移到mysql-router1节点
[root@mysql-router1 ~]# ip a | grep 192.168.10
    inet 192.168.10.204/24 brd 192.168.10.255 scope global noprefixroute ens160
    inet 192.168.10.200/32 scope global ens160

[root@tiaoban ~]# mysql -h 192.168.10.200 -P6450 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 8.4.4-router MySQL Community Server - GPL

Copyright (c) 2000, 2025, 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> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

[root@mysql-node1 ~]# systemctl stop mysqld

MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-node3:3306", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE_PARTIAL", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "n/a", 
                "readReplicas": {}, 
                "role": "HA", 
                "shellConnectError": "MySQL Error 2003: Could not open connection to 'mysql-node1:3306': Can't connect to MySQL server on 'mysql-node1:3306' (111)", 
                "status": "(MISSING)"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    : "mysql-node3:3306"
}

mysql> show databases;
+-------------------------------+
| Database                      |
+-------------------------------+
| information_schema            |
| mysql                         |
| mysql_innodb_cluster_metadata |
| performance_schema            |
| sys                           |
+-------------------------------+
5 rows in set (0.00 sec)

[root@tiaoban ~]# mysql -h 192.168.10.200 -P6450 -u root -p
# 创建数据库
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
# 查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
# 切换数据库
mysql> use test_db;
Database changed
# 创建表
mysql> CREATE TABLE `t_test` (
 `id` int(11) NOT NULL,
 `age` int(11) DEFAULT NULL,
 `score` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 3 warnings (0.01 sec)

# 插入表数据
mysql> INSERT INTO `t_test` VALUES (1, 2, 1);
Query OK, 1 row affected (0.01 sec)
# 查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
# 查看表数据
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
+-----+------+-------+
2 rows in set (0.00 sec)

[root@mysql-node3 ~]# mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)
# 切换数据库
mysql> use test_db;
Database changed
# 查看表
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t_test            |
+-------------------+
1 row in set (0.00 sec)
# 查询表数据
mysql> select * from t_test;
+-----+------+-------+
| id  | age  | score |
+-----+------+-------+
|   1 |    2 |     1 |
+-----+------+-------+
2 rows in set (0.00 sec)

[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6446 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node1     0
[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6446 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node1     0

[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6447 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node2     1
[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6447 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node3     1

[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6450 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node2     1
[root@tiaoban ~]# mysql -uroot -p123.com -h192.168.10.200 -P 6450 --protocol=TCP -N -r -B -e"select @@hostname, @@global.read_only;"
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql-node3     1

[root@mysql-node4 ~]# vim /etc/my.cnf
[mysqld]
bind-address = 0.0.0.0
server_id=4                        # 唯一的服务器 ID，不能和现有节点冲突
log_bin=mysql-bin                  # 启用二进制日志
binlog_expire_logs_seconds=604800  # [可选]设置日志文件保留的时长，单位是秒(默认不删除文件)
binlog_format=ROW                  # 确保 binlog 使用 ROW 模式
gtid_mode=ON                       # 启用 GTID
enforce-gtid-consistency=ON        # 强制 GTID 一致性
[root@mysql-node4 ~]# systemctl restart mysqld

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
# 切换到js模式
MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 获取集群
 MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster();
# 查看集群状态
 MySQL  127.0.0.1:3306 ssl  JS > cluster.status();
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-node2:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-node2:3306"
}
# 检查扩容机器是否符合加入，如果不符合根据提示修改配置。
 MySQL  127.0.0.1:3306 ssl  JS > dba.checkInstanceConfiguration('root@mysql-node4:3306');
Please provide the password for 'root@mysql-node4:3306': *******
Save password for 'root@mysql-node4:3306'? [Y]es/[N]o/Ne[v]er (default No): y
Validating MySQL instance at mysql-node4:3306 for use in an InnoDB Cluster...

This instance reports its own address as mysql-node4:3306
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.

Checking whether existing tables comply with Group Replication requirements...
No incompatible tables detected

Checking instance configuration...
Instance configuration is compatible with InnoDB cluster

The instance 'mysql-node4:3306' is valid for InnoDB Cluster usage.

{
    "status": "ok"
}
# 加入集群
 MySQL  127.0.0.1:3306 ssl  JS > cluster.addInstance('root@mysql-node4:3306');
# 查看集群状态
 MySQL  127.0.0.1:3306 ssl  JS > cluster.status();
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-node2:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node4:3306": {
                "address": "mysql-node4:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-node2:3306"
}

[root@mysql-router1 ~]# mysqlrouter --bootstrap root@192.168.10.201:3306 --user=mysqlrouter --directory=/etc/mysqlrouter
[root@mysql-router1 ~]# systemctl restart mysqlrouter

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
# 切换到js模式
MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 获取集群
MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster();
# 查看集群状态
MySQL  127.0.0.1:3306 ssl  JS > cluster.status();
# 定义要移除的节点主机名
MySQL  127.0.0.1:3306 ssl  JS > var memberToBeRemoved = "mysql-node4";
# 删除节点
MySQL  127.0.0.1:3306 ssl  JS > cluster.removeInstance(memberToBeRemoved);
# 查看集群状态
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-node2:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-node2:3306"
}

[root@mysql-node1 ~]# mysqlsh -h127.0.0.1 -P3306 -uroot -p123.com
# 切换到js模式
MySQL  127.0.0.1:3306 ssl  SQL > \js
Switching to JavaScript mode...
# 获取集群
MySQL  127.0.0.1:3306 ssl  JS > var cluster = dba.getCluster();
# 查看集群状态
MySQL  127.0.0.1:3306 ssl  JS > cluster.status();

MySQL  127.0.0.1:3306 ssl  JS > cluster.switchToMultiPrimaryMode()
Switching cluster 'myCluster' to Multi-Primary mode...

Instance 'mysql-node2:3306' remains PRIMARY.
Instance 'mysql-node1:3306' was switched from SECONDARY to PRIMARY.
Instance 'mysql-node3:3306' was switched from SECONDARY to PRIMARY.

The cluster successfully switched to Multi-Primary mode.
# 查看集群状态
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Multi-Primary"
    }, 
    "groupInformationSourceMember": "mysql-node1:3306"
}

MySQL  127.0.0.1:3306 ssl  JS > cluster.switchToSinglePrimaryMode()
Switching cluster 'myCluster' to Single-Primary mode...

Instance 'mysql-node1:3306' remains PRIMARY.
Instance 'mysql-node2:3306' was switched from PRIMARY to SECONDARY.
Instance 'mysql-node3:3306' was switched from PRIMARY to SECONDARY.

WARNING: Existing connections that expected a R/W connection must be disconnected, i.e. instances that became SECONDARY.

The cluster successfully switched to Single-Primary mode.
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-node1:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-node1:3306"
}

MySQL  127.0.0.1:3306 ssl  JS > cluster.setPrimaryInstance('mysql-node2')
Setting instance 'mysql-node2' as the primary instance of cluster 'myCluster'...

Instance 'mysql-node3:3306' remains SECONDARY.
Instance 'mysql-node1:3306' was switched from PRIMARY to SECONDARY.
Instance 'mysql-node2:3306' was switched from SECONDARY to PRIMARY.

The instance 'mysql-node2' was successfully elected as primary.
# 查看集群状态
MySQL  127.0.0.1:3306 ssl  JS > cluster.status()
{
    "clusterName": "myCluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "mysql-node2:3306", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "mysql-node1:3306": {
                "address": "mysql-node1:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node2:3306": {
                "address": "mysql-node2:3306", 
                "memberRole": "PRIMARY", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }, 
            "mysql-node3:3306": {
                "address": "mysql-node3:3306", 
                "memberRole": "SECONDARY", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "replicationLag": "applier_queue_applied", 
                "role": "HA", 
                "status": "ONLINE", 
                "version": "8.4.4"
            }
        }, 
        "topologyMode": "Single-Primary"
    }, 
    "groupInformationSourceMember": "mysql-node2:3306"
}