tnb0/ha/proxysql/init_proxysql0.sh

#!/bin/bash

########################################################################################################################
# 部署 proxysql 的节点上执行
########################################################################################################################

# 清空 proxysql 中的所有数据
docker exec -it proxysql mysql -uadmin -padmin -h127.0.0.1 -P6032 -e"
-- 进入管理终端或通过 docker exec 执行
DELETE FROM mysql_servers;
DELETE FROM mysql_users;
DELETE FROM mysql_query_rules;
DELETE FROM mysql_group_replication_hostgroups;
-- 清理旧的统计信息和日志（可选）
DELETE FROM monitor.mysql_server_connect_log;
DELETE FROM monitor.mysql_server_group_replication_log;

-- 立即生效并持久化（此时 ProxySQL 变成“白纸”一张）
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
"

# 重新初始化 proxysql
docker exec -i proxysql mysql -uadmin -padmin -h127.0.0.1 -P6032 <<EOF
-- 1. 添加基础服务器信息（只需加入到一个默认组，MGR 模块会自动挪动它们）
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'node211', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'node212', 3306);
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (10, 'node213', 3306);

-- 2. 配置 MGR 自动切换规则 (注意 backup_writer_hostgroup 必填)
INSERT INTO mysql_group_replication_hostgroups
(writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, backup_writer_hostgroup)
VALUES (10, 20, 30, 1, 1, 1, 40);

-- 3. 配置业务访问账号 (应用连接用)
INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('root', 'Root_123456', 10);

-- 4. 设置监控凭证与开关
UPDATE global_variables SET variable_value='root' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='Root_123456' WHERE variable_name='mysql-monitor_password';
-- 开启并加快监控频率
UPDATE global_variables SET variable_value='1000' WHERE variable_name='mysql-monitor_group_replication_healthcheck_interval';

-- 5. 一键加载
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;
EOF

# 验证配置结果
docker exec -it proxysql mysql -uadmin -padmin -h127.0.0.1 -P6032 -e "
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
"

# 添加路由规则
docker exec -i proxysql mysql -uadmin -padmin -h127.0.0.1 -P6032 <<EOF
-- 1. 清理旧规则
DELETE FROM mysql_query_rules;

-- 2. 规则 A：将所有 SELECT 语句路由到 HG 20 (读组)
-- match_digest 是 SQL 指纹正则，apply=1 表示匹配到就执行，不再往下走
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*', 20, 1);

-- 3. 规则 B：显式锁定带 FOR UPDATE 的 SELECT 到 HG 10 (写组)
-- 注意：这条规则的 rule_id 要比上一条小，或者优先级更高，防止被通配规则截获
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
VALUES (2, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- 加载并持久化
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
EOF

docker exec -i proxysql mysql -uadmin -padmin -h127.0.0.1 -P6032 -e"
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
"