Mysql InnoDB Cluster集群部署

最新推荐文章于 2026-04-24 16:33:02 发布

原创最新推荐文章于 2026-04-24 16:33:02 发布 · 528 阅读 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

天空阁楼

关注

标签

#mysql

分类数据库

InnoDB 多主模式集群部署

关于MySQL多主模式的组复制配置参考（MySQL组复制）

安装MySQL Shell

安装配置

#创建文件夹
mkdir /usr/local/mysqlshell
#解压文件
tar xf mysql-shell-8.0.21-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/mysqlshell
#赋权
chown -R mysql:mysql /usr/local/mysqlshell
#配置环境变量
export PATH=$PATH:/usr/local/mysqlshell/bin
#重启生效
source /etc/profile

创建集群

#任意一台服务器中执行
/usr/local/mysqlshell/bin/mysqlsh -h 服务器ip -P 3306 -uroot -p
#创建服务信息
var cluster = dba.createCluster('mysqlCluster', {adoptFromGR: true});
#获取集群名称
var cluster = dba.getCluster();
#查看集群状态
cluster.status();

#在存活节点（Primary）上更新种子列表（移除失效节点
SET GLOBAL group_replication_group_seeds = 'mysqlnode2:33061,mysqlnode3:33061,mysqlnode4:33061';
#获取集群名称
var cluster = dba.getCluster('mysqlCluster');
#尝试重新加入集群
cluster.rejoinInstance('root@mysqlnode1:3306', {force: true});
#从集群中移除
cluster.removeInstance("root@mysqlnode1:3306", {force: true});
#加入集群
cluster.addInstance("root@mysqlnode1:3306");
#选择CLone,完成后节点重新加入集群

安装MySQL Router

安装配置

#在某一台服务器中安装mysql router
#创建文件夹
mkdir /usr/local/mysqlrouter
#解压文件
tar xf mysql-router-8.0.21-linux-glibc2.12-x86_64.tar.xz -C /usr/local/mysqlrouter
#赋权
chown -R mysql:mysql /usr/local/mysqlrouter
chmod -R 755 /usr/local/mysqlrouter
#配置环境变量
export PATH=$PATH:/usr/local/mysqlrouter/bin
#重启生效
source /etc/profile

# 引导

#使用root用户进行引导
/usr/local/mysqlrouter/bin/mysqlrouter --bootstrap root@127.0.0.1:3306 --directory=/usr/local/mysql/mysqlrouter --conf-use-sockets --account icrouter --user=mysql --force

--bootstrap：指定集群任一节点地址（需具备元数据访问权限）
--directory：生成配置文件的根目录（自动创建 log/run/data 子目录）
--conf-use-sockets：启用 Unix 域套接字连接（提升本地访问性能）

配置自启动

#配置自启动
vi /etc/systemd/system/mysqlrouter.service
#配置内容
[Unit]
Description=MySQL Router Service
After=network.target mysql.service
Requires=mysql.service

[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/usr/local/mysqlrouter/bin/mysqlrouter -c /usr/local/mysql/mysqlrouter/mysqlrouter.conf
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure
RestartSec=5s
LimitNOFILE=65535
RuntimeDirectory=mysqlrouter
LogsDirectory=mysqlrouter

[Install]
WantedBy=multi-user.target

#重启生效
sudo systemctl daemon-reload
sudo systemctl enable mysqlrouter
sudo systemctl start mysqlrouter

组复制自启动

注：将mysql_cluster_recovery.sh放到指定位置后，执行以下内容
#转换换行符为Unix格式
sed -i 's/\r$//' /usr/local/mysql/mysql_cluster_recovery.sh

#配置系统服务 (/etc/systemd/system/mysql-cluster-recovery.service)
[Unit]
Description=MySQL InnoDB Cluster Recovery
After=network.target mysql.service
Requires=mysql.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_cluster_recovery.sh

[Install]
WantedBy=multi-user.target

mysql_cluster_recovery.sh 具体内容：

#!/bin/bash
LOG_FILE="/usr/local/mysql/mysql_cluster_recovery.log"
LOCK_FILE="/usr/local/mysql/cluster_recovery.lock"
CONFIG_FILE="/usr/local/mysql/.my.cnf"

# 配置节点优先级（全小写）
NODE_PRIORITY=("mysqlnode1" "mysqlnode2" "mysqlnode3" "mysqlnode4")
CURRENT_NODE=$(hostname -s | tr '[:upper:]' '[:lower:]')

# 获取当前节点优先级索引
PRIORITY_INDEX=-1
for i in "${!NODE_PRIORITY[@]}"; do
    [[ "${NODE_PRIORITY[$i]}" == "$CURRENT_NODE" ]] && PRIORITY_INDEX=$i && break
done
[[ $PRIORITY_INDEX -eq -1 ]] && echo "[$(date)] Node not in priority list" >> $LOG_FILE && exit 1

# 时间配置
BOOTSTRAP_TIMEOUT=300
CASCADE_INTERVAL=300
DELAY=$((PRIORITY_INDEX * CASCADE_INTERVAL))

# 文件锁
exec 9>$LOCK_FILE
if ! flock -n 9; then
    echo "[$(date)] Script already running" >> $LOG_FILE
    exit 1
fi

# 增强版MySQL检查
check_mysql() {
    for i in {1..60}; do
        if /usr/local/mysql/mysql8.0.34/bin/mysqladmin --defaults-extra-file=$CONFIG_FILE ping &>/dev/null; then
            return 0
        fi
        sleep 1
    done
    echo "[$(date)] MySQL启动失败" >> $LOG_FILE
    exit 1
}

# 安全引导函数（新增分布式锁）
safe_bootstrap() {
    echo "[$(date)] 尝试引导集群..." >> $LOG_FILE
    lock_acquired=$(/usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -NBe "SELECT GET_LOCK('gr_bootstrap_lock', 60)")
    if [ "$lock_acquired" -eq 1 ]; then
        timeout $BOOTSTRAP_TIMEOUT /usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -e "
        SET GLOBAL group_replication_bootstrap_group=ON;
        START GROUP_REPLICATION;
        SET GLOBAL group_replication_bootstrap_group=OFF;"
        local ret=$?
        /usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -e "SELECT RELEASE_LOCK('gr_bootstrap_lock')"
        return $ret
    else
        echo "[$(date)] 获取分布式锁失败" >> $LOG_FILE
        return 1
    fi
}

# 新增状态检查函数
check_self_status() {
    local status=$(/usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -NBe \
        "SELECT MEMBER_STATE FROM performance_schema.replication_group_members 
         WHERE MEMBER_HOST = '$CURRENT_NODE'")
    [[ "$status" == "ONLINE" ]] && return 0 || return 1
}

# 主恢复流程
recover_cluster() {
    # 阶段1：基于优先级的延迟等待
    echo "[$(date)] 启动优先级延迟：$DELAY秒" >> $LOG_FILE
    for ((wait=0; wait<DELAY; wait+=10)); do
        sleep 10
        for ((i=0; i<PRIORITY_INDEX; i++)); do
            node=${NODE_PRIORITY[$i],,}
            if /usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -h $node -e "SELECT 1" &>/dev/null; then
                echo "[$(date)] 检测到高优先级节点在线：$node" >> $LOG_FILE
                for retry in {1..10}; do
                    # 关键改进：加入前检查自身状态
                    if check_self_status; then
                        echo "[$(date)] 当前节点已处于ONLINE状态" >> $LOG_FILE
                        exit 0
                    fi
                    
                    output=$(/usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -e "START GROUP_REPLICATION" 2>&1)
                    exit_code=$?
                    
                    if [ $exit_code -eq 0 ]; then
                        echo "[$(date)] 成功加入集群：$node" >> $LOG_FILE
                        exit 0
                    else
                        if [[ "$output" == *"ERROR 3093"* ]]; then
                            echo "[$(date)] 集群已运行，当前节点可能已自动加入" >> $LOG_FILE
                            sleep 5  # 等待状态更新
                            check_self_status && exit 0 || continue
                        elif [[ "$output" == *"ERROR 3663"* ]]; then
                            echo "[$(date)] 检测到并发操作，等待后重试（${retry}/10）" >> $LOG_FILE
                            sleep $((5 + retry * 3))
                        else
                            echo "[$(date)] 加入失败：$output" >> $LOG_FILE
                            break
                        fi
                    fi
                done
            fi
        done
    done

    # 阶段2：GTID仲裁（保持不变）
    declare -A gtid_status
    for node in "${NODE_PRIORITY[@]}"; do
        gtid_status[$node]=$(/usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -h ${node,,} -NBe \
            "SELECT GTID_SUBTRACT(@@global.gtid_executed, '')" 2>/dev/null || echo "NULL")
    done

    # 选举最新节点（保持不变）
    elected_node=$CURRENT_NODE
    for node in "${NODE_PRIORITY[@]}"; do
        [[ "${gtid_status[$node]}" == "NULL" ]] && continue
        [[ "${gtid_status[$node]}" > "${gtid_status[$elected_node]}" ]] && elected_node=$node
    done

    # 最终恢复逻辑（保持不变）
    if [[ "$elected_node" == "$CURRENT_NODE" ]]; then
        safe_bootstrap || {
            /usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -e "RESET MASTER; RESET REPLICA ALL;"
            exit 1
        }
    else
        for retry in {1..15}; do
            output=$(/usr/local/mysql/mysql8.0.34/bin/mysql --defaults-extra-file=$CONFIG_FILE -h ${elected_node,,} -e "START GROUP_REPLICATION" 2>&1)
            if [ $? -eq 0 ]; then
                echo "[$(date)] 成功加入集群" >> $LOG_FILE
                exit 0
            else
                if [[ "$output" == *"ERROR 3663"* ]]; then
                    echo "[$(date)] 检测到并发操作，等待后重试（${retry}/15）" >> $LOG_FILE
                    sleep $((10 + retry * 5))
                else
                    echo "[$(date)] 加入失败：$output" >> $LOG_FILE
                    break
                fi
            fi
        done
    fi

    # 最终强制引导
    safe_bootstrap && exit 0 || exit 1
}

# 主流程
echo "[$(date)] 启动集群恢复流程" >> $LOG_FILE
check_mysql
recover_cluster >> $LOG_FILE 2>&1

flock -u 9
exit 0