MySQL+Orchestrator+ProxySQL高可用集群部署

2

  •  一、安装依赖

    apt update && apt install -y mysql-server mysql-client iptables-persistent curl wget

    二、放通防火墙 

    # 打开文件

vim /etc/iptables/rules.v4
    # 放行MySQL业务端口33306

-A INPUT -p tcp --dport 33306 -j ACCEPT

# 放行Orchestrator端口3000

-A INPUT -p tcp --dport 3000 -j ACCEPT

# 放行ProxySQL管理端口6032

-A INPUT -p tcp --dport 6032 -j ACCEPT

# 放行ProxySQL业务端口39092

-A INPUT -p tcp --dport 39092-j ACCEPT
    # 加载防火墙规则，永久生效

iptables-restore < /etc/iptables/rules.v4

# 检查防火墙规则是否生效

iptables -L -n

    三、配置hosts[ip 服务器名称]

    vim /etc/hosts 
    10.10.255.65 C65B

10.10.255.66 C66B

10.10.255.67 C67B

    四、MySQL配置

    1.配置文件：

    vim /etc/mysql/mysql.conf.d/mysqld.cnf

    65服务：

    [mysqld]

# 指定MySQL服务端口

port=33306

# 集群唯一标识，主从不能重复

server-id=65

# MySQL数据存储目录

datadir=/var/lib/mysql

# 开启二进制日志，用于主从复制

log_bin=mysql-bin

# 行级复制格式，保证主从数据强一致

binlog_format=row

# 中继日志前缀，文件存放在datadir目录下

relay_log=relay-bin

# 二进制日志过期清理天数

expire_logs_days=7

# 监听所有网卡，允许外部连接

bind-address=0.0.0.0

# MySQL8.0认证插件，适配复制账号

default_authentication_plugin=mysql_native_password

# 开启GTID模式，实现自动故障转移

gtid_mode=ON

# 强制GTID事务一致性

enforce_gtid_consistency=ON

# 从库提升为主库后可生成二进制日志

log_slave_updates=ON

# 普通用户可写，主库标识

read_only=0

# 超级用户可写，高可用核心配置

super_read_only=0

# MySQL错误日志路径

log_error=/var/log/mysql/error.log

# 开启慢查询日志

slow_query_log=1

# 慢查询日志存储路径

slow_query_log_file=/var/log/mysql/slow.log

# 慢查询阈值，执行超1秒记录日志

long_query_time=1

# MySQL进程PID文件路径

pid-file=/var/run/mysqld/mysqld.pid

# MySQL本地连接Socket文件路径

socket=/var/run/mysqld/mysqld.sock

report_host = 10.10.255.65

 

 

[mysql]

# 客户端连接默认Socket路径

socket=/var/run/mysqld/mysqld.sock

    66服务器：

    [mysqld]

port=33306

server-id=66

datadir=/var/lib/mysql

log_bin=mysql-bin

binlog_format=row

relay_log=relay-bin

expire_logs_days=7

bind-address=0.0.0.0

default_authentication_plugin=mysql_native_password

gtid_mode=ON

enforce_gtid_consistency=ON

log_slave_updates=ON

# 普通用户只读，防止误写入

read_only=1

# 超级用户只读，保障从库数据安全

super_read_only=1

log_error=/var/log/mysql/error.log

slow_query_log=1

slow_query_log_file=/var/log/mysql/slow.log

long_query_time=1

pid-file=/var/run/mysqld/mysqld.pid

socket=/var/run/mysqld/mysqld.sock

 

report_host = 10.10.255.66

 

[mysql]

socket=/var/run/mysqld/mysqld.sock

     67服务器：

    [mysqld]

port=33306

server-id=67

datadir=/var/lib/mysql

log_bin=mysql-bin

binlog_format=row

relay_log=relay-bin

expire_logs_days=7

bind-address=0.0.0.0

default_authentication_plugin=mysql_native_password

gtid_mode=ON

enforce_gtid_consistency=ON

log_slave_updates=ON

# 普通用户只读，防止误写入

read_only=1

# 超级用户只读，保障从库数据安全

super_read_only=1

log_error=/var/log/mysql/error.log

slow_query_log=1

slow_query_log_file=/var/log/mysql/slow.log

long_query_time=1

pid-file=/var/run/mysqld/mysqld.pid

socket=/var/run/mysqld/mysqld.sock

 

report_host = 10.10.255.67

 

[mysql]

socket=/var/run/mysqld/mysqld.sock

    2. 重启数据库

    # 重启MySQL生效

systemctl restart mysql

# 检查MySQL状态

systemctl status mysql

# 检查端口监听

netstat -tulpn | grep 33306

     3.65主数据库添加账号

    -- 清理并创建 Orchestrator 数据库
DROP DATABASE IF EXISTS orchestrator;

CREATE DATABASE orchestrator;

-- 1. Orchestrator 专用账号

DROP USER IF EXISTS 'orc'@'%';

CREATE USER 'orc'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON orchestrator.* TO 'orc'@'%';

GRANT SUPER, RELOAD, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'orc'@'%';

GRANT SELECT ON mysql.* TO 'orc'@'%';

 

DROP USER IF EXISTS 'orc'@'localhost';

CREATE USER 'orc'@'localhost' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON orchestrator.* TO 'orc'@'localhost';

GRANT SUPER, RELOAD, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'orc'@'localhost';

GRANT SELECT ON mysql.* TO 'orc'@'localhost';

 

-- 2. 主从复制账号

DROP USER IF EXISTS 'repl'@'%';

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

 

-- 3. 应用业务账号

DROP USER IF EXISTS 'app'@'%';

CREATE USER 'app'@'%' IDENTIFIED BY '123456';

GRANT ALL ON *.* TO 'app'@'%';

 

-- 4. ProxySQL监控账号

DROP USER IF EXISTS 'monitor'@'%';

CREATE USER 'monitor'@'%' IDENTIFIED BY '123456';

GRANT SELECT ON *.* TO 'monitor'@'%';

 

FLUSH PRIVILEGES;

    4.导出数据：（65）

    mysqldump -uroot -P33306 --all-databases --source-data=2 --single-transaction --set-gtid-purged=ON --triggers --routines --events --force --default-character-set=utf8mb4 > /usr/local/install/full_backup.sql

    5.导入数据：（66,67）

    -- 进入数据库停止同步

STOP SLAVE;

RESET SLAVE ALL;

RESET MASTER;

SET GLOBAL super_read_only=OFF;

SET GLOBAL read_only=OFF;
    # 导入数据

mysql -uroot -P33306 < /usr/local/install/full_backup.sql

     6.66、67开启同步

    --开启同步

SET GLOBAL read_only=ON;

SET GLOBAL super_read_only=ON;

STOP SLAVE;

 

CHANGE MASTER TO

MASTER_HOST='10.10.255.65',

MASTER_PORT=33306,

MASTER_USER='repl',

MASTER_PASSWORD='123456',

MASTER_AUTO_POSITION=1,

GET_MASTER_PUBLIC_KEY=1;

 

START SLAVE;

 

SHOW SLAVE STATUS\G;

     成功标志：

    Slave_IO_Running: Yes

Slave_SQL_Running: Yes

    7.如果出现Error connecting to source 'repl@10.10.255.65:33306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Access denied for user 'repl'@'10.10.255.67' (using password: YES)这个错误，就主服务器再次执行创建账号。

    五、部署 Orchestrator

    1. 安装

    wget https://github.com/openark/orchestrator/releases/download/v3.2.6/orchestrator_3.2.6_amd64.deb

dpkg -i orchestrator_3.2.6_amd64.deb

apt -f install -y

    2.配置文件：

    vim /etc/orchestrator.conf.json

    65配置：

    {

  "Debug": false,

  "ListenAddress": "0.0.0.0:3000",

 

  "BackendDB": "sqlite",

  "SQLite3DataFile": "/var/lib/orchestrator/orchestrator.db",

 

  "MySQLTopologyUser": "orc",

  "MySQLTopologyPassword": "123456",

  "MySQLTopologyPort": 33306,

 

  "RaftEnabled": true,

  "RaftDataDir": "/var/lib/orchestrator",

  "RaftBind": "10.10.255.65:10008",

  "RaftNodes": [

    "10.10.255.65:10008",

    "10.10.255.66:10008",

    "10.10.255.67:10008"

  ]

}

    66配置：

    {

  "Debug": false,

  "ListenAddress": "0.0.0.0:3000",

 

  "BackendDB": "sqlite",

  "SQLite3DataFile": "/var/lib/orchestrator/orchestrator.db",

 

  "MySQLTopologyUser": "orc",

  "MySQLTopologyPassword": "123456",

  "MySQLTopologyPort": 33306,

 

  "RaftEnabled": true,

  "RaftDataDir": "/var/lib/orchestrator",

  "RaftBind": "10.10.255.66:10008",

  "RaftNodes": [

    "10.10.255.65:10008",

    "10.10.255.66:10008",

    "10.10.255.67:10008"

  ]

}

    67配置：

    {

  "Debug": false,

  "ListenAddress": "0.0.0.0:3000",

 

  "BackendDB": "sqlite",

  "SQLite3DataFile": "/var/lib/orchestrator/orchestrator.db",

 

  "MySQLTopologyUser": "orc",

  "MySQLTopologyPassword": "123456",

  "MySQLTopologyPort": 33306,

 

  "RaftEnabled": true,

  "RaftDataDir": "/var/lib/orchestrator",

  "RaftBind": "10.10.255.67:10008",

  "RaftNodes": [

    "10.10.255.65:10008",

    "10.10.255.66:10008",

    "10.10.255.67:10008"

  ]

}

    3.清除数据

    systemctl stop orchestrator

rm -rf /var/lib/orchestrator/*

chown -R orchestrator:orchestrator /var/lib/orchestrator

    4.启动服务，顺序启动65，66，67一台一台启动

    systemctl start orchestrator

systemctl enable orchestrator

sleep 20

    5.查看状态

    systemctl status orchestrator

    6.在65上执行让orchestrator托管mysql

    orchestrator -c discover -i 10.10.255.65:33306 --ignore-raft-setup

orchestrator -c discover -i 10.10.255.66:33306 --ignore-raft-setup

orchestrator -c discover -i 10.10.255.67:33306 --ignore-raft-setup

    7.检查集群结果

    curl http://127.0.0.1:3000/api/raft-status

    成功标志：

    "Peers": [

  "10.10.255.65:10008",

  "10.10.255.66:10008",

  "10.10.255.67:10008"

]

    六、ProxySQL部署

    1. 下载安装

    wget https://github.com/sysown/proxysql/releases/download/v4.0.7/proxysql_4.0.7-ubuntu24_amd64.deb

dpkg -i proxysql_4.0.7-ubuntu24_amd64.deb

apt -f install -y

systemctl stop proxysql

rm -rf /var/lib/proxysql/*

    2.修改配置文件 

    vim /etc/proxysql.cnf
    # ProxySQL 数据目录

datadir="/var/lib/proxysql"

 

# 管理员账号密码配置

admin_variables=

{

    admin_credentials="admin:123456;cluster:123456"

    mysql_ifaces="0.0.0.0:6032"

}

 

# MySQL 业务与监控

mysql_variables=

{

    threads=4

    max_connections=2048

    interfaces="0.0.0.0:39092"

    monitor_username="monitor"

    monitor_password="123456"

}

    3.初始化数据（65执行）

    systemctl start proxysql
    mysql -uadmin -p'123456' -h127.0.0.1 -P6032
    DELETE FROM mysql_servers;

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (10, '10.10.255.65', 33306);

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '10.10.255.66', 33306);

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (20, '10.10.255.67', 33306);

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

 

 

DELETE FROM mysql_users;

INSERT INTO mysql_users (username,password,active,default_hostgroup) VALUES ('app','123456',1,10);

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

    4.检查初始化

    mysql -uadmin -p'123456' -h127.0.0.1 -P6032 -e "SELECT * FROM mysql_servers;"

    5.启动服务并查看状态

    #解除屏蔽

systemctl unmask proxysql

#启动

systemctl start proxysql

#开机启动

systemctl enable proxysql

#查看状态

systemctl status proxysql

    6.编写脚本

    mkdir -p /opt/scripts

vim /opt/scripts/proxysql_auto_failover.sh
    #!/bin/bash

exec >> /var/log/proxysql_auto_failover.log 2>&1

 

echo "================================================"

echo "执行时间: $(date)"

echo "================================================"

 

# 1. 获取集群名称

CLUSTER_NAME=$(curl -s http://127.0.0.1:3000/api/clusters | jq -r '.[0]')

echo "CLUSTER_NAME 执行值: [${CLUSTER_NAME}]"

 

# 2. 获取主库IP（已修复正确）

MASTER_IP=$(curl -s "http://127.0.0.1:3000/api/cluster/${CLUSTER_NAME}" | \

jq -r '.[0] | .SlaveHosts[0].Hostname as $replica |

if .SlaveHosts | length == 0 then .Key.Hostname

else

  if .Key.Hostname | test("^[0-9]") then .Key.Hostname

  else "10.10.255.65"

  end

end')

echo "MASTER_IP     最终IP: [${MASTER_IP}]"

 

# 3. 本机IP

CURRENT_IP=$(hostname -i | awk '{print $1}')

echo "CURRENT_IP    本机IP: [${CURRENT_IP}]"

 

echo "------------------------------------------------"

 

# 4. 只有主库才修改 ProxySQL

if [ "${CURRENT_IP}" = "${MASTER_IP}" ]; then

    echo "✅ 判定结果：本机是主库，开始更新 ProxySQL 路由"

 

    # ====================== 修复这里 ======================

    # ProxySQL 地址改为真实地址：10.10.255.65:6032

    # ======================================================

    mysql -uadmin -p'123456' -h10.10.255.65 -P6032 <<EOF

    UPDATE mysql_servers SET hostgroup_id=10 WHERE hostname='${CURRENT_IP}';

    UPDATE mysql_servers SET hostgroup_id=20 WHERE hostname!='${CURRENT_IP}';

    LOAD MYSQL SERVERS TO RUNTIME;

    SAVE MYSQL SERVERS TO DISK;

EOF

 

    echo "✅ ProxySQL 修改完成！主库=10，从库=20"

else

    echo "✅ 判定结果：本机是从库，不修改 ProxySQL"

fi

 

echo -e "执行完成！\n"
    chmod +x /opt/scripts/proxysql_auto_failover.sh

    7.配置定时任务

    crontab -e
    * * * * * /opt/scripts/proxysql_auto_failover.sh

    七、开机自愈脚本 + 自启配置

    1. 脚本

    mkdir -p /opt/scripts 
vim /opt/scripts/mysql_restore_on_boot.sh 
    #!/bin/bash

exec >> /var/log/mysql_restore_on_boot.log 2>&1

 

echo "================================================"

echo "【MySQL开机自动恢复】执行时间: $(date)"

echo "================================================"

 

# 等待MySQL启动

echo "→ 等待 MySQL 服务启动..."

while ! mysql -uroot -P33306 -e "SELECT 1" >/dev/null 2>&1; do

    sleep 2

    echo "  → 等待 MySQL 启动中..."

done

echo "✅ MySQL 已启动"

 

# 等待Orchestrator API启动

echo "→ 等待 Orchestrator API 启动..."

while ! curl -s http://127.0.0.1:3000/api/clusters >/dev/null 2>&1; do

    sleep 2

    echo "  → 等待 Orchestrator API 启动中..."

done

echo "✅ Orchestrator API 已启动"

 

# 获取本机IP

CURRENT_IP=$(hostname -i | awk '{print $1}')

echo "本机IP: ${CURRENT_IP}"

 

# 获取集群名称

CLUSTER_NAME=$(curl -s http://127.0.0.1:3000/api/clusters | jq -r '.[0]')

echo "集群名称: ${CLUSTER_NAME}"

 

# 获取主库IP

MASTER_IP=$(curl -s "http://127.0.0.1:3000/api/cluster/${CLUSTER_NAME}" | \

jq -r '.[0] | if (.SlaveHosts | length == 0) then .Key.Hostname else "10.10.255.65" end')

echo "当前主库IP: ${MASTER_IP}"

 

echo "------------------------------------------------"

 

# 判断本机是否为主库

if [ "${CURRENT_IP}" = "${MASTER_IP}" ]; then

    echo "✅ 本机是主库，无需配置主从"

    exit 0

fi

 

echo "⚠️ 本机是从库，开始自动恢复主从..."

 

# 停止旧复制

echo "→ 停止旧复制"

mysql -uroot -P33306 -e "STOP SLAVE;"

 

# 开启只读

echo "→ 开启只读模式"

mysql -uroot -P33306 -e "SET GLOBAL read_only=1; SET GLOBAL super_read_only=1;"

 

# 配置新主库（终极密码安全版）

echo "→ 配置主库: ${MASTER_IP}"

mysql -uroot -P33306 -e '

STOP SLAVE;

CHANGE MASTER TO

MASTER_HOST="'${MASTER_IP}'",

MASTER_PORT=33306,

MASTER_USER="repl",

MASTER_PASSWORD="123456",

MASTER_AUTO_POSITION=1,

GET_MASTER_PUBLIC_KEY=1;

START SLAVE;

'

 

echo "================================================"

echo "✅ 主从恢复完成！"

echo "================================================"

    # 赋予执行权限

    chmod +x /opt/scripts/mysql_restore_on_boot.sh

    2. 配置 systemd 开机自启

    # 创建systemd服务文件

    vim /etc/systemd/system/mysql_restore.service 
    [Unit]

Description=MySQL开机主从自愈服务

After=network.target mysqld.service orchestrator.service

 

[Service]

Type=oneshot

ExecStart=/opt/scripts/mysql_restore_on_boot.sh

 

[Install]

WantedBy=multi-user.target
    # 重新加载systemd配置

systemctl daemon-reload


# 启用开机自启

systemctl enable mysql_restore.service

    3.校验自启配置是否生效

    # 查看服务状态

systemctl status mysql_restore.service

 
# 查看开机自愈日志

tail -f /var/log/mysql_restore_on_boot.log

    八、日志切割

    1.配置 logrotate 自动切割清理

    cat > /etc/logrotate.d/mysql_proxysql_auto <<'EOF'

/var/log/proxysql_auto_failover.log

/var/log/mysql_restore_on_boot.log

{

    daily

    rotate 7

    missingok

    notifempty

    compress

    delaycompress

    copytruncate

    create 0644 root root

    su root root

}

EOF

    2.配置说明

    daily：每天切割一次

    rotate 7：只保留最近 7 天的日志，更早的自动删除

    compress：自动压缩旧日志

    copytruncate：不中断脚本运行，安全切割

    3.立即测试配置是否有效

    logrotate -d /etc/logrotate.d/mysql_proxysql_auto

    4.看到下面信息为成功

    log does not need rotating

    九、常用运维命令

    1.系统服务启停

    # MySQL 启停

systemctl start mysql

systemctl stop mysql

systemctl restart mysql

systemctl status mysql
    # Orchestrator 启停

systemctl start orchestrator

systemctl stop orchestrator

systemctl restart orchestrator

systemctl status orchestrator
    # ProxySQL 启停

systemctl start proxysql

systemctl stop proxysql

systemctl restart proxysql

systemctl status proxysql
    # 开机自愈服务（mysql_restore）

systemctl start mysql_restore

systemctl status mysql_restore

    2.MySQL 主从状态检查

    # 主从同步状态

mysql -uroot -P33306 -e "SHOW SLAVE STATUS\G"

# 查看主库当前二进制日志

mysql -uroot -P33306 -e "SHOW MASTER STATUS;"
 
# 查看主从延迟

mysql -uroot -P33306 -e "SHOW SLAVE STATUS\G" | grep Seconds_Behind_Master

# 查看所有数据库（验证数据一致）

mysql -uroot -P33306 -e "SHOW DATABASES;"

#查看从数据库对应信息

mysql -uroot -P33306 -e "SELECT * FROM mysql.slave_master_info\G;"

     3.Orchestrator 集群检查

    # 查看 Raft 集群状态

curl http://127.0.0.1:3000/api/raft-status

    4.ProxySQL 检查

    # 查看后端 MySQL 节点
mysql -uadmin -p'123456' -h127.0.0.1 -P6032 -e "SELECT * FROM mysql_servers;"
# 查看监控状态
mysql -uadmin -p'123456' -h127.0.0.1 -P6032 -e "SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;"
# 重新加载 ProxySQL 配置
mysql -uadmin -p'123456' -h127.0.0.1 -P6032 -e "LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;"

    5.高可用自动切换验证命令

    # 查看自动切换脚本日志

tail -f /var/log/proxysql_auto_failover.log

# 查看开机自愈日志

tail -f /var/log/mysql_restore_on_boot.log

# 测试脚本是否能正常运行

bash /opt/scripts/proxysql_auto_failover.sh

# 查看定时任务

crontab -l

    6.端口与防火墙检查

    # 查看端口监听

netstat -tulpn | grep -E "33306|3000|6032|6033|10008"

# 查看防火墙规则

iptables -L -n

    7.常用综合

    # MySQL主从状态

mysql -uroot -P33306 -e "SHOW SLAVE STATUS\G"

# Orchestrator集群状态

curl http://127.0.0.1:3000/api/raft-status

# ProxySQL节点状态

mysql -uadmin -p'123456' -h127.0.0.1 -P6032 -e "SELECT * FROM mysql_servers;"

    十、Spring Boot 配置【ProxySQL里面配置的】

    spring:

  datasource:

    url: jdbc:mysql://10.10.255.65:39092,10.10.255.66:39092,10.10.255.67:39092/testdb

    username: app

    password: 123456 




    • 赞(1)
    赏
    作者：数据分享发表，转载请注明来源！
    关键词：mysql
    分类：数据库

    相关文章