1.命令行部署方式
Ubuntu 22.04 系统上部署 MySQL 9.1 InnoDB Cluster 集群方案
环境规划
服务器配置
2核4g 存储68G SWAP分配4g
# 设置主机名和解析
sudo hostnamectl set-hostname mysql-1 # 节点1上执行
sudo hostnamectl set-hostname mysql-2 # 节点2上执行
sudo hostnamectl set-hostname mysql-3 # 节点3上执行
VIP 192.168.1.20 mysql-vip -
# 添加主机解析
sudo tee -a /etc/hosts <<EOF
192.168.177.128 mysql-1
192.168.177.129 mysql-2
192.168.177.130 mysql-3
EOF
#查看当前时区设置
timedatectl
列出所有可用时区，查找中国时区
timedatectl list-timezones | grep -i Asia/Shanghai
设置时区为北京时间
sudo timedatectl set-timezone Asia/Shanghai
验证设置
timedatectl
软件版本
MySQL 9.1.0
Keepalived 2.2.7
MySQL Router 9.1.0
一、基础环境准备
1. 各节点执行 - 更新系统并安装依赖
bash
sudo apt update && sudo apt upgrade -y
sudo apt install -y build-essential cmake bison libncurses5-dev libssl-dev libaio-dev \
libreadline-dev libbz2-dev libcurl4-openssl-dev libarchive-dev \
libprotobuf-dev libprotoc-dev protobuf-compiler libevent-dev git \
libzstd-dev liblz4-dev libsnappy-dev liblzo2-dev libnuma-dev libedit-dev \
pkg-config
2. 创建用户和目录
bash
# 创建mysql用户
sudo useradd -r -s /bin/false mysql
sudo useradd -m app
# 创建数据目录
sudo mkdir -p /data/mysql/3306/{data,log,binlog,relaylog,slowlog,tmp}
sudo mkdir -p /data/mysql-router
sudo mkdir -p /app/logs /app/scripts
# 设置权限
sudo chown -R mysql:mysql /data/mysql
sudo chown -R app:app /app
3. 配置系统参数
bash
sudo tee /etc/sysctl.d/mysql.conf << EOF
fs.file-max = 655350
net.ipv4.ip_local_port_range = 10000 65535
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 32768
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 30
vm.swappiness = 10
vm.overcommit_memory = 1
EOF
sudo sysctl -p /etc/sysctl.d/mysql.conf
4. 配置系统限制
bash
sudo tee /etc/security/limits.d/mysql.conf << EOF
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535
mysql soft memlock unlimited
mysql hard memlock unlimited
EOF
5. 创建 4GB 交换文件（根据需求调整大小）
sudo fallocate -l 4G /swapfile
sudo chmod 600 /swapfile
sudo mkswap /swapfile
sudo swapon /swapfile
echo '/swapfile none swap sw 0 0' | sudo tee -a /etc/fstab
二、编译安装 MySQL 9.1
1. 下载 MySQL 源码
bash
cd /tmp
wget https://dev.mysql.com/get/Downloads/MySQL-9.1/mysql-9.1.0.tar.gz
tar -xzf mysql-9.1.0.tar.gz
cd mysql-9.1.0
mkdir build
cd build
2. 编译配置（根据 9.1 版本调整）
bash
cmake .. \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/3306/data \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/data/mysql/3306/data/mysql.sock \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_USER=mysql \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_0900_ai_ci \
-DWITH_ZSTD=system \
-DWITH_LZ4=system \
-DWITH_SNAPPY=system \
-DWITH_LZO=system \
-DWITH_NUMA=ON \
-DNUMACTL_INCLUDE_DIR=/usr/include \
-DNUMACTL_LIBRARY=/usr/lib/x86_64-linux-gnu/libnuma.so \
-DENABLED_PROFILING=ON \
-DWITH_DEBUG=OFF \
-DWITH_SSL=system \
-DWITH_LIBWRAP=OFF \
-DWITH_EDITLINE=system \
-DWITH_LIBEVENT=system \
-DWITH_PAM=ON \
-DWITH_VALGRIND=OFF
#清理之前的缓存可重新执行编译
rm -rf CMakeCache.txt CMakeFiles
3. 编译安装
bash
make -j$(nproc)
sudo make install
4. 配置 MySQL 服务
bash
# 创建系统服务
sudo tee /etc/systemd/system/mysql.service << EOF
[Unit]
Description=MySQL 9.1 Server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ExecReload=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf reload
ExecStop=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown
PrivateTmp=true
Restart=always
LimitNOFILE=65535
LimitMEMLOCK=infinity
[Install]
WantedBy=multi-user.target
EOF
三、配置 MySQL（基于提供的配置文件并适配 9.1）
1. 初始化数据库
bash
sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
记录下初始化密码，后续登录mysql修改root密码
2. 创建并配置 my.cnf（各节点 server_id 不同）
bash
# 节点1的配置文件
sudo tee /etc/my.cnf << EOF
[client]
socket = /data/mysql/3306/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
# General
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
pid_file = /data/mysql/3306/data/mysql.pid
character_set_server = utf8mb4
transaction_isolation = READ-COMMITTED
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' #检查格式换行
log_error = /data/mysql/3306/log/mysqld.err
default_time_zone = '+8:00'
log_timestamps = system
tmpdir = /data/mysql/3306/tmp
secure_file_priv = /data/mysql/3306/tmp
# Slow log
slow_query_log = ON
long_query_time = 0.5
slow_query_log_file = /data/mysql/3306/slowlog/slow.log
# Connection
back_log = 2048
max_connections = 500
max_connect_errors = 10000
interactive_timeout = 1800
wait_timeout = 1800
thread_cache_size = 128
max_allowed_packet = 1G
skip_name_resolve = ON
# Session
read_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
join_buffer_size = 4M
# InnoDB
innodb_buffer_pool_size = 6144M
innodb_buffer_pool_instances = 4
#innodb_log_file_size = 512M 从 MySQL 8.0 开始，innodb_log_file_size 和 innodb_log_files_in_group 被合并为单个参数：
#innodb_log_files_in_group = 2
innodb_redo_log_capacity = 512M # 替代旧参数
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
#innodb_undo_tablespaces = 2
innodb_max_undo_log_size = 1024M
innodb_undo_log_truncate = 1
innodb_page_cleaners = 8
innodb_io_capacity = 200
innodb_io_capacity_max = 500
#innodb_data_file_path = ibdata1:1G:autoextend
# 将 innodb_data_file_path 调整为实际大小（768 pages = 12MB）
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_method = O_DIRECT
innodb_purge_threads = 4
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_neighbors = 1
innodb_checksum_algorithm = crc32
innodb_strict_mode = ON
innodb_print_all_deadlocks = ON
innodb_numa_interleave = ON
innodb_open_files = 65535
innodb_adaptive_hash_index = OFF
# Replication
server_id = 1 #根据每个实际节点配置
log_bin = /data/mysql/3306/binlog/mysql-bin
relay_log = /data/mysql/3306/relaylog/relay-bin #中继日志路径
sync_binlog = 1
binlog_format = ROW
#master_info_repository = TABLE #已弃用
#relay_log_info_repository = TABLE #已弃用
relay_log_recovery = ON
log_slave_updates = ON
binlog_expire_logs_seconds = 604800
#slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN' #已弃用
skip_slave_start = ON
slave_net_timeout = 60
binlog_error_action = ABORT_SERVER
#super_read_only = ON # 开启只读模式
# Semi-Sync Replication
plugin_load = "validate_password.so;semisync_master.so;semisync_slave.so"
#rpl_semi_sync_master_enabled = ON
rpl_semi_sync_slave_enabled = ON
rpl_semi_sync_master_timeout = 1000
# GTID
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_gtid_simple_recovery = ON
# Multithreaded Replication
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_preserve_commit_order = ON
#transaction_write_set_extraction = XXHASH64 #已弃用
#binlog_transaction_dependency_tracking = WRITESET_SESSION #已弃用
binlog_transaction_dependency_history_size = 25000
# Others
open_files_limit = 65535
max_heap_table_size = 32M
tmp_table_size = 32M
table_open_cache = 65535
table_definition_cache = 65535
table_open_cache_instances = 64
# InnoDB Cluster配置
plugin_load_add = 'group_replication.so'
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
group_replication_group_name = "da38e84a-4fc2-4c1f-b8b2-2c7f25def55f"
group_replication_start_on_boot = OFF
group_replication_local_address = "mysql-1:33061" # 各节点修改为自身地址
group_replication_group_seeds = "mysql-1:33061,mysql-2:33061,mysql-3:33061"
group_replication_bootstrap_group = OFF
group_replication_consistency = "EVENTUAL"
group_replication_flow_control_mode = "QUOTA"
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
EOF
3. 启动 MySQL 服务
bash
sudo systemctl daemon-reload
sudo systemctl start mysql
sudo systemctl enable mysql
apt install mysql-client-core-8.0
4. 配置 MySQL 初始密码
-- 设置 root 密码,大小写加字符
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Lj@111111';
CREATE USER 'root'@'%' IDENTIFIED BY 'Lj@111111';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql -uroot -p'Lj@111111'
四、配置 MySQL InnoDB Cluster
1. 创建集群管理员用户
bash
mysql -u root -pLj@111111 << EOF
CREATE USER 'clusteradmin'@'%' IDENTIFIED WITH caching_sha2_password BY 'Lj@111111';
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;
EOF
2. 使用 MySQL Shell 创建集群
安装MySQL Shell 9.1.0的.deb包
通过wget命令下载指定版本的MySQL Shell安装包：
wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell_9.1.0-1ubuntu22.04_amd64.deb
安装过程中可能依赖libcurl4等库，可通过以下命令安装：
sudo apt update
sudo apt install -y libcurl4
使用dpkg命令安装下载的.deb包：
sudo dpkg -i mysql-shell_9.1.0-1ubuntu22.04_amd64.deb
mysqlsh --version
#如果使用 dpkg 卸载sudo dpkg -r mysql-shell 完全删除sudo dpkg -r mysql-shell
安装 MySQL Shell（二进制）的方式，与上面deb包方式二选一
# 下载并安装 MySQL Shell（推荐使用这种）
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-9.1.0-linux-glibc2.17-x86-64bit.tar.gz
tar -xvf mysql-shell-9.1.0-linux-glibc2.17-x86-64bit.tar.gz
sudo mv mysql-shell-9.1.0-linux-glibc2.17-x86-64bit /usr/local/mysql-shell
sudo ln -s /usr/local/mysql-shell/bin/mysqlsh /usr/local/bin/mysqlsh
# 验证安装
mysqlsh --version
3. 在主节点mysql-1上创建集群
mysqlsh --user=clusteradmin --password=Lj@111111 --uri=clusteradmin@mysql-1:3306
\js
# 在MySQL Shell中执行
dba.configureInstance('clusteradmin@mysql-1', {
clusterAdmin: 'clusteradmin',
restart: true
});
创建集群
var cluster = dba.createCluster('myCluster');
获取集群对象
var cluster = dba.getCluster();
查看集群信息
cluster.status();
4. 在从节点mysql-2上加入集群
mysqlsh --user=clusteradmin --password=Lj@111111 --uri=clusteradmin@mysql-2:3306
\js
# 在MySQL Shell中执行
dba.configureInstance('clusteradmin@mysql-2', {
clusterAdmin: 'clusteradmin',
restart: true
});
5. 在从节点mysql-3上加入集群
mysqlsh --user=clusteradmin --password=Lj@111111 --uri=clusteradmin@mysql-3:3306
\js
# 在MySQL Shell中执行
dba.configureInstance('clusteradmin@mysql-3', {
clusterAdmin: 'clusteradmin',
restart: true
});
6. 配置完成后，返回到集群的管理节点（mysql-1），使用 cluster.addInstance 方法将 mysql-2，mysql-3 添加到集群中：
添加mysql-2
cluster.addInstance(
{
host: 'mysql-2',
port: 3306,
user: 'clusteradmin',
password: 'Lj@111111'
},
{
recoveryMethod: 'clone'
}
);
添加mysql-3
cluster.addInstance(
{
host: 'mysql-3',
port: 3306,
user: 'clusteradmin',
password: 'Lj@111111'
},
{
recoveryMethod: 'clone'
}
);
获取集群对象
var cluster = dba.getCluster();
#检查集群状态，检查前先获取下集群对象，否真状态会显示异常
cluster.status();
#检查集群状态（如果有错误信息，可以进行下面的修复步骤）
cluster.status();
7. 修复集群报错问题
##在新加节点的服务器上重启mysql服务
systemctl restart mysql.service
##在主节点上执行
var cluster = dba.getCluster();
cluster.rescan();
cluster.status();
// 尝试从完全故障中恢复集群
var cluster = dba.rebootClusterFromCompleteOutage();
8. 当虚拟机关闭之后，第二天开机之后同步失效
#如果Group Replication未运行可以重启 Group Replication
##在 MySQL Shell 中
mysqlsh --user=clusteradmin --password=Lj@111111 --uri=clusteradmin@mysql-3:3306
var cluster = dba.rebootClusterFromCompleteOutage();
获取集群对象
var cluster = dba.getCluster();
查看集群信息
cluster.status();
9. 当错误无法解决需要删除myCluster重新创建一个myCluster
// 检查是否已存在集群
try {
var cluster = dba.getCluster('myCluster')
print(cluster.status())
} catch(e) {
print("没有找到现有集群或集群已解散")
}
// 解散现有集群（如果存在）
try {
var cluster = dba.getCluster('myCluster')
// 强制解散集群（包含force选项以处理不健康状态）
cluster.dissolve({force: true})
print("集群已成功解散")
} catch(e) {
print("解散集群时出错: " + e)
}
在每个集群节点上执行：
sql
STOP GROUP_REPLICATION;
RESET MASTER;
SET GLOBAL group_replication_bootstrap_group=OFF;
使用 dba.dropMetadataSchema() 命令删除元数据模式：
dba.dropMetadataSchema()
最后执行重新创建myCluster步骤
五、安装和配置 MySQL Router
MySQL Router 9.1.0 二进制部署
完整部署流程
1. 下载 MySQL Router 二进制包
bash
# 创建安装目录
sudo mkdir -p /usr/local/mysqlrouter
cd /usr/local/mysqlrouter
# 下载 MySQL Router 9.1.0
sudo wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-9.1.0-linux-glibc2.17-x86_64.tar.xz
# 解压文件
sudo tar xf mysql-router-9.1.0-linux-glibc2.17-x86_64.tar.xz
# 重命名目录
sudo mv mysql-router-9.1.0-linux-glibc2.17-x86_64 mysql-router
2. 设置环境变量
bash
# 添加到系统PATH
echo 'export PATH=/usr/local/mysqlrouter/mysql-router/bin:$PATH' | sudo tee /etc/profile.d/mysqlrouter.sh
source /etc/profile.d/mysqlrouter.sh
3. 创建专用用户和目录
bash
# 创建用户和组
sudo groupadd mysqlrouter
sudo useradd -r -g mysqlrouter -s /bin/false mysqlrouter
# 创建配置目录
sudo mkdir -p /etc/mysqlrouter
sudo mkdir -p /var/log/mysqlrouter
sudo mkdir -p /var/run/mysqlrouter
# 设置权限
sudo chown -R mysqlrouter:mysqlrouter /etc/mysqlrouter /var/log/mysqlrouter /var/run/mysqlrouter
4. 生成初始配置
bash
# 使用集群管理账户引导配置,根据实际节点mysql-1，mysql-2，mysql-3修改对应ip或域名
sudo /usr/local/mysqlrouter/mysql-router/bin/mysqlrouter --bootstrap clusteradmin@mysql-1:3306 \
--directory /etc/mysqlrouter \
--user=mysqlrouter \
--force
5. 配置读写端口
编辑配置文件 /etc/mysqlrouter/mysqlrouter.conf：
bash
vi /etc/mysqlrouter/mysqlrouter.conf
修改以下部分：
ini
[routing:primary]
bind_address = 0.0.0.0
bind_port = 8888
destinations = metadata-cache://myCluster/default?role=PRIMARY
routing_strategy = first-available
protocol = classic
[routing:secondary]
bind_address = 0.0.0.0
bind_port = 6666
destinations = metadata-cache://myCluster/default?role=SECONDARY
routing_strategy = round-robin
protocol = classic
#注意节点id
[metadata_cache:myCluster]
router_id=1 # 每个节点设置唯一ID (1,2,3...)
6. 创建 Systemd 服务
bash
sudo tee /etc/systemd/system/mysqlrouter.service <<EOF
[Unit]
Description=MySQL Router
After=network.target
[Service]
Type=simple
User=mysqlrouter
Group=mysqlrouter
RuntimeDirectory=mysqlrouter
RuntimeDirectoryMode=0755
# 主启动命令
ExecStart=/usr/local/mysqlrouter/mysql-router/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
# 重启设置
Restart=on-failure
RestartSec=5s
# 资源限制
LimitNOFILE=65536
# 日志设置
StandardOutput=journal
StandardError=journal
SyslogIdentifier=mysqlrouter
[Install]
WantedBy=multi-user.target
EOF
7. 启动并启用服务
bash
# 重新加载systemd配置
sudo systemctl daemon-reload
# 启用服务
sudo systemctl enable mysqlrouter
# 启动服务
sudo systemctl start mysqlrouter
8. 验证安装
bash
# 检查服务状态
sudo systemctl status mysqlrouter
# 检查监听端口
sudo netstat -tulnp | grep mysqlrouter
# 预期输出应包含：
# tcp6 0 0 :::8888 :::* LISTEN <PID>/mysqlrouter
# tcp6 0 0 :::6666 :::* LISTEN <PID>/mysqlrouter
# 测试写端口连接
mysql -h 127.0.0.1 -P 8888 -u clusteradmin -pLj@111111 -e "SELECT @@hostname; CREATE DATABASE IF NOT EXISTS router_test;"
# 测试读端口连接
mysql -h 127.0.0.1 -P 6666 -u clusteradmin -pLj@111111 -e "SELECT @@hostname; SHOW DATABASES;"
多节点部署注意事项
1. 节点差异化配置
在每个节点上，修改以下配置项：
ini
[metadata_cache:myCluster]
router_id=1 # 每个节点设置唯一ID (1,2,3...)
8. 主节点故障转移测试
# 停止当前主节点的MySQL服务
sudo systemctl stop mysql
# 等待30秒后，通过读端口连接并查看当前节点
mysql -h mysql-1 -P 6666 -u root -p -e "SELECT @@server_id, @@hostname;"
# 启动MySQL服务并检查自动恢复情况
sudo systemctl start mysql
六. 安装 Keepalived
虚拟 IP (VIP): 192.168.177.131
公共网络接口: ens33 (使用 ip a 命令查看你的实际接口名称)
1. 在所有节点上安装 Keepalived
bash
sudo apt update
sudo apt install keepalived -y
验证安装
bash
keepalived --version
# 应输出: Keepalived v2.2.4 (或其他版本)
2. 配置 Keepalived
在 mysql-1 (主节点) 上配置
bash
sudo tee /etc/keepalived/keepalived.conf <<EOF
global_defs {
router_id MYSQL_HA_NODE1
enable_script_security
script_user root
}
# MySQL Router 监控脚本
vrrp_script chk_mysqlrouter {
script "/usr/local/bin/check_mysqlrouter.sh"
interval 2
weight 2
fall 2
rise 2
}
vrrp_instance VI_1 {
state MASTER
interface ens33 # 更改为你的网络接口
virtual_router_id 51
priority 101 # 主节点优先级最高
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.177.131/24
}
track_script {
chk_mysqlrouter
}
notify_master "/usr/local/bin/notify_wechat.sh MASTER"
notify_backup "/usr/local/bin/notify_wechat.sh BACKUP"
notify_fault "/usr/local/bin/notify_wechat.sh FAULT"
}
EOF
在 mysql-2 (备节点1) 上配置
bash
sudo tee /etc/keepalived/keepalived.conf <<EOF
global_defs {
router_id MYSQL_HA_NODE2
enable_script_security
script_user root
}
vrrp_script chk_mysqlrouter {
script "/usr/local/bin/check_mysqlrouter.sh"
interval 2
weight 2
fall 2
rise 2
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 100 # 优先级低于主节点
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.177.131/24
}
track_script {
chk_mysqlrouter
}
notify_master "/usr/local/bin/notify_wechat.sh MASTER"
notify_backup "/usr/local/bin/notify_wechat.sh BACKUP"
notify_fault "/usr/local/bin/notify_wechat.sh FAULT"
}
EOF
在 node3 (备节点2) 上配置
bash
sudo tee /etc/keepalived/keepalived.conf <<EOF
global_defs {
router_id MYSQL_HA_NODE3
enable_script_security
script_user root
}
vrrp_script chk_mysqlrouter {
script "/usr/local/bin/check_mysqlrouter.sh"
interval 2
weight 2
fall 2
rise 2
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 99 # 优先级最低
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.177.131/24
}
track_script {
chk_mysqlrouter
}
notify_master "/usr/local/bin/notify_wechat.sh MASTER"
notify_backup "/usr/local/bin/notify_wechat.sh BACKUP"
notify_fault "/usr/local/bin/notify_wechat.sh FAULT"
}
EOF
3. 创建监控脚本
创建 MySQL Router 检查脚本（所有节点）
bash
sudo tee /usr/local/bin/check_mysqlrouter.sh <<'EOF'
#!/bin/bash
# 检查MySQL Router是否运行
if systemctl is-active --quiet mysqlrouter; then
exit 0
else
# 尝试重启一次
systemctl restart mysqlrouter
sleep 2
if systemctl is-active --quiet mysqlrouter; then
exit 0
else
exit 1
fi
fi
EOF
sudo chmod +x /usr/local/bin/check_mysqlrouter.sh
4. 创建企业微信通知脚本（所有节点）
bash
sudo tee /usr/local/bin/notify_wechat.sh <<'EOF'
#!/bin/bash
WEBHOOK_URL="https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=d402043a-3389-4e0f-8626-f05d429b2227"
HOSTNAME=$(hostname)
TIMESTAMP=$(date "+%Y-%m-%d %H:%M:%S")
case $1 in
MASTER)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}\n事件：VIP已切换至本机(MASTER)"
;;
BACKUP)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}\n事件：本机转为备用节点(BACKUP)"
;;
FAULT)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}\n事件：节点故障(FAULT)"
;;
ROUTER_DOWN)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}\n事件：MySQL Router停止运行，已尝试重启"
;;
*)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}\n事件：未知状态($1)"
;;
esac
curl -s -H "Content-Type: application/json" \
-d "{\"msgtype\": \"text\",\"text\": {\"content\": \"${MESSAGE}\"}}" \
${WEBHOOK_URL}
EOF
sudo chmod +x /usr/local/bin/notify_wechat.sh
将 YOUR_WEBHOOK_KEY 替换为你的企业微信机器人 Webhook 密钥
5. 启动并启用服务
bash
# 测试配置文件
sudo keepalived -t -f /etc/keepalived/keepalived.conf
bash
# 所有节点执行
sudo systemctl enable keepalived
sudo systemctl start keepalived
# 重启服务
sudo systemctl restart keepalived
验证 Keepalived 集群
检查服务状态
bash
sudo systemctl status keepalived
检查 VIP 分配
bash
ip addr show ens33 | grep "192.168.177.131"
# 应只在主节点上显示VIP
测试故障转移
停止主节点的 Keepalived:
bash
# 在主节点执行
sudo systemctl stop keepalived
检查备节点上的 VIP:
bash
# 在备节点执行
ip addr show ens33 | grep "192.168.177.131"
检查企业微信通知:
应收到 VIP 切换的通知
测试 MySQL Router 监控
停止 VIP 所在节点的 MySQL Router:
bash
sudo systemctl stop mysqlrouter
等待 4-6 秒后检查:
bash
systemctl status mysqlrouter
# 应该已被自动重启
如果无法重启，检查 VIP 是否迁移:
VIP 应迁移到其他节点
bash
# 查看 Keepalived 日志
grep keepalived /var/log/syslog
# 跟踪实时日志
tail -f /var/log/syslog | grep keepalived
# 强制切换主节点
# 在备节点执行
sudo systemctl stop keepalived
sudo systemctl start keepalived
通过以上步骤，您已经在 Ubuntu 22.04 上成功部署了一个高可用的 Keepalived 集群，用于管理 VIP 并监控 MySQL Router 服务。所有状态变化都会通过企业微信通知，确保您能及时了解集群状态。
七. 配置日志清理与免密传输
# 创建备份用户和目录（在备份服务器mysql-1和远程服务器mysql-2上执行）
sudo adduser app
#设置app用户密码为 111111
sudo passwd app # 设置密码
#将用户添加到 sudo 组（授予管理员权限）
sudo usermod -aG sudo app
sudo useradd -m app
sudo mkdir -p /backups/mysqlrouter_logs
sudo chown app:app /backups/mysqlrouter_logs
sudo chown app:app /backups/mysqlrouter_logs
#免密码使用 sudo
echo "app ALL=(ALL) NOPASSWD:ALL" >>/etc/sudoers
# 在MySQL Router节点上执行
sudo useradd -m app
sudo -u app mkdir -p /home/app/scripts /home/app/.ssh
sudo app passwd
# 创建日志清理脚本
sudo tee /home/app/scripts/log_cleanup.sh <<'EOF'
#!/bin/bash
# 日志目录
LOG_DIR="/etc/mysqlrouter/log"
# 备份目录
BACKUP_DIR="/home/app/log_backups"
# 远程备份服务器
REMOTE_USER="app"
REMOTE_HOST="mysql-2"
REMOTE_DIR="/backups/mysqlrouter_logs"
# 创建备份目录
mkdir -p $BACKUP_DIR
# 生成时间戳
TIMESTAMP=$(date +%Y%m%d%H%M%S)
# 打包日志文件
BACKUP_FILE="$BACKUP_DIR/mysqlrouter_logs_$TIMESTAMP.tar.gz"
tar -czf $BACKUP_FILE -C $LOG_DIR .
# 使用SCP免密传输
scp -o StrictHostKeyChecking=no $BACKUP_FILE $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR
# 传输完成后清理旧日志
find $LOG_DIR -name "*.log" -type f -mtime +7 -exec rm -f {} \;
# 保留最近3个备份
find $BACKUP_DIR -name "*.tar.gz" -type f -mtime +30 | sort | head -n -3 | xargs rm -f
# 发送通知
/usr/local/keepalived/bin/notify_wechat.sh "LOG_CLEAN_COMPLETE"
EOF
sudo chown app:app /home/app/scripts/log_cleanup.sh
sudo chown app:app /home/app/log_backups
sudo usermod -aG mysqlrouter app
sudo chmod 750 /etc/mysqlrouter/log
sudo chmod +x /home/app/scripts/log_cleanup.sh
设置SSH密钥对
sudo -u app ssh-keygen -t rsa -b 4096 -f /home/app/.ssh/id_rsa -N ""
sudo -u app ssh-copy-id -i /home/app/.ssh/id_rsa.pub app@mysql-2
# 测试免密登录
sudo -u app ssh -i /home/app/.ssh/backup_key app@mysql-2 "echo 'SSH connection successful'"
# 添加定时任务
sudo -u app crontab -e
# 每周一凌晨2点清理日志
0 2 * * 1 /home/app/scripts/log_cleanup.sh >> /home/app/log_cleanup.log 2>&1
# 每月最后一天凌晨2:30清理日志
30 2 28-31 * * [ $(date -d tomorrow +\%d) -eq 1 ] && /home/app/scripts/log_cleanup.sh >> /home/app/log_cleanup.log 2>&1
2.自动化shell脚本一键部署
#!/bin/bash
# MySQL 9.1 InnoDB Cluster自动化部署脚本（优化版）
# 使用方法: sudo ./deploy_mysql_cluster.sh <节点类型> <节点ID>
# 节点类型: master或slave
# 节点ID: 1, 2, 3...
set -e
# 参数检查
if [ $# -ne 2 ]; then
echo "Usage: sudo $0 <master|slave> <node-id>"
exit 1
fi
NODE_TYPE=$1
NODE_ID=$2
HOSTNAME="mysql-$NODE_ID"
MYSQL_VERSION="9.1.0"
ROOT_PASSWORD="Lj@111111"
CLUSTER_ADMIN_PASS="Lj@111111"
VIRTUAL_IP="192.168.177.131"
INTERFACE="ens33"
MYSQL_PORT=3306
GROUP_SEEDS="mysql-1:33061,mysql-2:33061,mysql-3:33061"
GROUP_NAME="da38e84a-4fc2-4c1f-b8b2-2c7f25def55f"
# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[0;33m'
NC='\033[0m'
# 日志函数
log_info() {
echo -e "${GREEN}[INFO] $1${NC}"
}
log_warn() {
echo -e "${YELLOW}[WARN] $1${NC}"
}
log_error() {
echo -e "${RED}[ERROR] $1${NC}"
exit 1
}
# 检查root权限
check_root() {
if [ "$(id -u)" != "0" ]; then
log_error "This script must be run as root"
fi
}
# 设置主机名和解析
setup_hosts() {
log_info "Setting up hostname and hosts file..."
# 设置主机名
hostnamectl set-hostname "$HOSTNAME"
# 添加主机解析
if ! grep -q "mysql-1" /etc/hosts; then
cat >> /etc/hosts <<EOF
192.168.177.128 mysql-1
192.168.177.129 mysql-2
192.168.177.130 mysql-3
EOF
fi
# 设置时区
timedatectl set-timezone Asia/Shanghai
}
# 安装依赖
install_dependencies() {
log_info "Installing dependencies..."
apt update && apt upgrade -y
apt install -y build-essential cmake bison libncurses5-dev libssl-dev libaio-dev \
libreadline-dev libbz2-dev libcurl4-openssl-dev libarchive-dev \
libprotobuf-dev libprotoc-dev protobuf-compiler libevent-dev git \
libzstd-dev liblz4-dev libsnappy-dev liblzo2-dev libnuma-dev libedit-dev \
pkg-config keepalived mysql-client-core-8.0 cron sshpass
}
# 创建用户和目录
create_users_dirs() {
log_info "Creating users and directories..."
# 创建用户
if ! id -u mysql >/dev/null 2>&1; then
useradd -r -s /bin/false mysql
fi
if ! id -u app >/dev/null 2>&1; then
useradd -m app
echo "app:$ROOT_PASSWORD" | chpasswd
echo "app ALL=(ALL) NOPASSWD:ALL" >> /etc/sudoers
fi
# 创建数据目录
/bin/bash -c "mkdir -p /data/mysql/3306/{data,log,binlog,relaylog,slowlog,tmp}"
mkdir -p /data/mysql-router
mkdir -p /app/logs /app/scripts
# 设置权限
chown -R mysql:mysql /data/mysql
chown -R app:app /app
}
# 配置系统参数
configure_system() {
log_info "Configuring system parameters..."
# 系统参数
cat > /etc/sysctl.d/mysql.conf << EOF
fs.file-max = 655350
net.ipv4.ip_local_port_range = 10000 65535
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 32768
net.ipv4.tcp_max_syn_backlog = 16384
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 30
vm.swappiness = 10
vm.overcommit_memory = 1
EOF
sysctl -p /etc/sysctl.d/mysql.conf
# 系统限制
cat > /etc/security/limits.d/mysql.conf << EOF
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535
mysql soft memlock unlimited
mysql hard memlock unlimited
EOF
# 交换文件
if [ ! -f /swapfile ]; then
fallocate -l 4G /swapfile
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
echo '/swapfile none swap sw 0 0' >> /etc/fstab
fi
}
# 编译安装MySQL
install_mysql() {
log_info "Installing MySQL $MYSQL_VERSION..."
# 下载源码
cd /tmp
if [ ! -f mysql-$MYSQL_VERSION.tar.gz ]; then
wget https://dev.mysql.com/get/Downloads/MySQL-9.1/mysql-$MYSQL_VERSION.tar.gz
fi
tar -xzf mysql-$MYSQL_VERSION.tar.gz
cd mysql-$MYSQL_VERSION
if [ ! -d build ]; then
mkdir build
fi
cd build
# 编译配置
cmake .. \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql/3306/data \
-DSYSCONFDIR=/etc \
-DMYSQL_TCP_PORT=$MYSQL_PORT \
-DMYSQL_UNIX_ADDR=/data/mysql/3306/data/mysql.sock \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_USER=mysql \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_0900_ai_ci \
-DWITH_ZSTD=system \
-DWITH_LZ4=system \
-DWITH_SNAPPY=system \
-DWITH_LZO=system \
-DWITH_NUMA=ON \
-DNUMACTL_INCLUDE_DIR=/usr/include \
-DNUMACTL_LIBRARY=/usr/lib/x86_64-linux-gnu/libnuma.so \
-DENABLED_PROFILING=ON \
-DWITH_DEBUG=OFF \
-DWITH_SSL=system \
-DWITH_LIBWRAP=OFF \
-DWITH_EDITLINE=system \
-DWITH_LIBEVENT=system \
-DWITH_PAM=ON \
-DWITH_VALGRIND=OFF
# 编译安装
make -j$(nproc)
make install
# 创建系统服务
cat > /etc/systemd/system/mysql.service << EOF
[Unit]
Description=MySQL $MYSQL_VERSION Server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ExecReload=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf reload
ExecStop=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown
PrivateTmp=true
Restart=always
LimitNOFILE=65535
LimitMEMLOCK=infinity
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
}
# 配置MySQL
configure_mysql() {
log_info "Configuring MySQL..."
# 初始化数据库
if [ ! -d /data/mysql/3306/data/mysql ]; then
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql \
--basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
fi
# 创建配置文件
cat > /etc/my.cnf << EOF
[client]
socket = /data/mysql/3306/data/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
# General
user = mysql
port = $MYSQL_PORT
basedir = /usr/local/mysql
datadir = /data/mysql/3306/data
socket = /data/mysql/3306/data/mysql.sock
pid_file = /data/mysql/3306/data/mysql.pid
character_set_server = utf8mb4
transaction_isolation = READ-COMMITTED
sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
log_error = /data/mysql/3306/log/mysqld.err
default_time_zone = '+8:00'
log_timestamps = system
tmpdir = /data/mysql/3306/tmp
secure_file_priv = /data/mysql/3306/tmp
# Slow log
slow_query_log = ON
long_query_time = 0.5
slow_query_log_file = /data/mysql/3306/slowlog/slow.log
# Connection
back_log = 2048
max_connections = 500
max_connect_errors = 10000
interactive_timeout = 1800
wait_timeout = 1800
thread_cache_size = 128
max_allowed_packet = 1G
skip_name_resolve = ON
# Session
read_buffer_size = 2M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
join_buffer_size = 4M
# InnoDB
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 4
innodb_redo_log_capacity = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_max_undo_log_size = 1024M
innodb_undo_log_truncate = 1
innodb_page_cleaners = 8
innodb_io_capacity = 200
innodb_io_capacity_max = 500
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_method = O_DIRECT
innodb_purge_threads = 4
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_neighbors = 1
innodb_checksum_algorithm = crc32
innodb_strict_mode = ON
innodb_print_all_deadlocks = ON
innodb_numa_interleave = ON
innodb_open_files = 65535
innodb_adaptive_hash_index = OFF
# Replication
server_id = $((100 + $NODE_ID))
log_bin = /data/mysql/3306/binlog/mysql-bin
relay_log = /data/mysql/3306/relaylog/relay-bin
sync_binlog = 1
binlog_format = ROW
relay_log_recovery = ON
log_slave_updates = ON
binlog_expire_logs_seconds = 604800
skip_slave_start = ON
slave_net_timeout = 60
binlog_error_action = ABORT_SERVER
# Semi-Sync Replication
plugin_load = "validate_password.so;semisync_master.so;semisync_slave.so"
rpl_semi_sync_slave_enabled = ON
rpl_semi_sync_master_timeout = 1000
# GTID
gtid_mode = ON
enforce_gtid_consistency = ON
binlog_gtid_simple_recovery = ON
# Multithreaded Replication
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 8
slave_preserve_commit_order = ON
binlog_transaction_dependency_history_size = 25000
# Others
open_files_limit = 65535
max_heap_table_size = 32M
tmp_table_size = 32M
table_open_cache = 65535
table_definition_cache = 65535
table_open_cache_instances = 64
# InnoDB Cluster配置
plugin_load_add = 'group_replication.so'
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
group_replication_group_name = "$GROUP_NAME"
group_replication_start_on_boot = OFF
group_replication_local_address = "$HOSTNAME:33061"
group_replication_group_seeds = "$GROUP_SEEDS"
group_replication_bootstrap_group = OFF
group_replication_consistency = "EVENTUAL"
group_replication_flow_control_mode = "QUOTA"
group_replication_flow_control_applier_threshold = 25000
group_replication_flow_control_certifier_threshold = 25000
EOF
# 启动MySQL服务
systemctl start mysql
systemctl enable mysql
sleep 5s
if mysql -uroot -p"${ROOT_PASSWORD}" -e "SELECT 1" >/dev/null 2>&1; then
echo "密码验证成功"
else
echo "密码验证失败，请设置密码"
# 设置root密码
mysql -uroot -e \
"ALTER USER 'root'@'localhost' IDENTIFIED BY '$ROOT_PASSWORD'; \
CREATE USER 'root'@'%' IDENTIFIED BY '$ROOT_PASSWORD'; \
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; \
CREATE USER 'clusteradmin'@'%' IDENTIFIED WITH caching_sha2_password BY '$CLUSTER_ADMIN_PASS'; \
GRANT ALL PRIVILEGES ON *.* TO 'clusteradmin'@'%' WITH GRANT OPTION; \
FLUSH PRIVILEGES;"
fi
}
# 安装MySQL Shell
install_mysql_shell() {
log_info "Installing MySQL Shell..."
cd /tmp
wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell_${MYSQL_VERSION}-1ubuntu22.04_amd64.deb
apt install -y ./mysql-shell_${MYSQL_VERSION}-1ubuntu22.04_amd64.deb
}
# 配置InnoDB Cluster
configure_cluster() {
if [ "$NODE_TYPE" = "master" ]; then
log_info "Configuring InnoDB Cluster on master node..."
# 在主节点上创建集群
mysqlsh --uri=clusteradmin@mysql-1:3306 --js <<EOF
dba.configureInstance('clusteradmin@mysql-1:3306', {
clusterAdmin: 'clusteradmin',
clusterAdminPassword: '$CLUSTER_ADMIN_PASS',
restart: true
});
var cluster = dba.createCluster('myCluster');
cluster.addInstance('clusteradmin@mysql-2:3306', {recoveryMethod: 'clone'});
cluster.addInstance('clusteradmin@mysql-3:3306', {recoveryMethod: 'clone'});
EOF
else
log_info "Configuring instance for cluster on slave node..."
# 在从节点上配置实例
echo "主节点已经配置完成，从节点不需要配置"
fi
}
# 安装配置MySQL Router
install_mysql_router() {
log_info "Installing MySQL Router..."
# 创建安装目录
mkdir -p /usr/local/mysqlrouter
cd /usr/local/mysqlrouter
# 下载MySQL Router
wget https://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-${MYSQL_VERSION}-linux-glibc2.17-x86_64.tar.xz
tar xf mysql-router-${MYSQL_VERSION}-linux-glibc2.17-x86_64.tar.xz
rm -rf mysql-router
mv mysql-router-${MYSQL_VERSION}-linux-glibc2.17-x86_64 mysql-router
# 设置环境变量
echo 'export PATH=/usr/local/mysqlrouter/mysql-router/bin:$PATH' > /etc/profile.d/mysqlrouter.sh
. /etc/profile.d/mysqlrouter.sh
# 创建用户和目录
if ! getent group mysqlrouter >/dev/null; then
groupadd -r mysqlrouter
echo "已创建mysqlrouter组"
else
echo "mysqlrouter组已存在，跳过创建"
fi
# 创建mysqlrouter用户（如果不存在）
if ! id -u mysqlrouter >/dev/null 2>&1; then
useradd -r -g mysqlrouter -s /bin/false mysqlrouter
echo "已创建mysqlrouter用户"
else
echo "mysqlrouter用户已存在，跳过创建"
fi
mkdir -p /etc/mysqlrouter /var/log/mysqlrouter /var/run/mysqlrouter
chown -R mysqlrouter:mysqlrouter /etc/mysqlrouter /var/log/mysqlrouter /var/run/mysqlrouter
# 生成初始配置
mysqlrouter --bootstrap clusteradmin@mysql-1:3306 \
--directory /etc/mysqlrouter \
--user=mysqlrouter \
--force
# 修改配置文件
cat >> /etc/mysqlrouter/mysqlrouter.conf << EOF
[routing:primary]
bind_address = 0.0.0.0
bind_port = 8888
destinations = metadata-cache://myCluster/default?role=PRIMARY
routing_strategy = first-available
protocol = classic
[routing:secondary]
bind_address = 0.0.0.0
bind_port = 6666
destinations = metadata-cache://myCluster/default?role=SECONDARY
routing_strategy = round-robin
protocol = classic
EOF
# 创建Systemd服务
cat > /etc/systemd/system/mysqlrouter.service << EOF
[Unit]
Description=MySQL Router
After=network.target mysql.service
Requires=mysql.service
[Service]
Type=simple
User=mysqlrouter
Group=mysqlrouter
RuntimeDirectory=mysqlrouter
RuntimeDirectoryMode=0755
ExecStart=/usr/local/mysqlrouter/mysql-router/bin/mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf
Restart=on-failure
RestartSec=5s
LimitNOFILE=65536
StandardOutput=journal
StandardError=journal
SyslogIdentifier=mysqlrouter
[Install]
WantedBy=multi-user.target
EOF
# 启动服务
systemctl daemon-reload
systemctl enable mysqlrouter
systemctl start mysqlrouter
}
# 安装配置Keepalived
install_keepalived() {
log_info "Installing and configuring Keepalived..."
# 安装Keepalived
apt install -y keepalived
# 创建监控脚本
cat > /usr/local/bin/check_mysql_services.sh << 'EOF'
#!/bin/bash
# 检查MySQL服务状态
check_mysql() {
if ! systemctl is-active --quiet mysql; then
systemctl restart mysql
sleep 2
if ! systemctl is-active --quiet mysql; then
/usr/local/bin/notify_wechat.sh "MYSQL_DOWN" "MySQL服务停止，重启失败"
exit 1
else
/usr/local/bin/notify_wechat.sh "MYSQL_RESTARTED" "MySQL服务已重启"
fi
fi
}
# 检查MySQL Router服务状态
check_mysqlrouter() {
if ! systemctl is-active --quiet mysqlrouter; then
systemctl restart mysqlrouter
sleep 2
if ! systemctl is-active --quiet mysqlrouter; then
/usr/local/bin/notify_wechat.sh "ROUTER_DOWN" "MySQL Router服务停止，重启失败"
exit 1
else
/usr/local/bin/notify_wechat.sh "ROUTER_RESTARTED" "MySQL Router服务已重启"
fi
fi
}
check_mysql
check_mysqlrouter
# 如果都正常，返回0
exit 0
EOF
chmod +x /usr/local/bin/check_mysql_services.sh
# 创建通知脚本
cat > /usr/local/bin/notify_wechat.sh << 'EOF'
#!/bin/bash
WEBHOOK_URL="https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=d402043a-3389-4e0f-8626-f05d429b2227"
HOSTNAME=$(hostname)
TIMESTAMP=$(date "+%Y-%m-%d %H:%M:%S")
IP_ADDR=$(hostname -I | awk '{print $1}')
case $1 in
MASTER)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：VIP已切换至本机(MASTER)"
;;
BACKUP)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：本机转为备用节点(BACKUP)"
;;
FAULT)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：节点故障(FAULT)"
;;
MYSQL_DOWN)
MESSAGE="【MySQL服务告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：MySQL服务停止，重启失败\n详情：$2"
;;
MYSQL_RESTARTED)
MESSAGE="【MySQL服务恢复】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：MySQL服务已自动重启"
;;
ROUTER_DOWN)
MESSAGE="【MySQL Router告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：MySQL Router服务停止，重启失败\n详情：$2"
;;
ROUTER_RESTARTED)
MESSAGE="【MySQL Router恢复】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：MySQL Router服务已自动重启"
;;
*)
MESSAGE="【MySQL集群告警】\n时间：${TIMESTAMP}\n主机：${HOSTNAME}(${IP_ADDR})\n事件：未知状态($1)\n详情：$2"
;;
esac
curl -s -H "Content-Type: application/json" \
-d "{\"msgtype\": \"text\",\"text\": {\"content\": \"${MESSAGE}\"}}" \
${WEBHOOK_URL} >/dev/null 2>&1
EOF
chmod +x /usr/local/bin/notify_wechat.sh
# 配置Keepalived
if [ "$NODE_TYPE" = "master" ]; then
PRIORITY=101
STATE="MASTER"
else
PRIORITY=$((100 - $NODE_ID))
STATE="BACKUP"
fi
cat > /etc/keepalived/keepalived.conf << EOF
global_defs {
router_id MYSQL_HA_NODE$NODE_ID
enable_script_security
script_user root
}
vrrp_script chk_mysql_services {
script "/usr/local/bin/check_mysql_services.sh"
interval 5
weight 2
fall 2
rise 1
timeout 10
}
vrrp_instance VI_1 {
state $STATE
interface $INTERFACE
virtual_router_id 51
priority $PRIORITY
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
$VIRTUAL_IP/24
}
track_script {
chk_mysql_services
}
notify_master "/usr/local/bin/notify_wechat.sh MASTER"
notify_backup "/usr/local/bin/notify_wechat.sh BACKUP"
notify_fault "/usr/local/bin/notify_wechat.sh FAULT"
# 增加服务状态跟踪
track_service {
mysql
mysqlrouter
}
}
EOF
# 启动服务
systemctl enable keepalived
systemctl start keepalived
# 添加定时检查任务
(crontab -l 2>/dev/null; echo "*/5 * * * * /usr/local/bin/check_mysql_services.sh >> /var/log/mysql_services_check.log 2>&1") | crontab -
}
# 配置日志清理
setup_log_cleanup() {
log_info "Setting up log cleanup..."
# 创建备份目录
mkdir -p /backups/mysqlrouter_logs
chown app:app /backups/mysqlrouter_logs
sudo usermod -aG mysqlrouter app
sudo chmod 750 /etc/mysqlrouter/log
sudo -u app mkdir -p /home/app/scripts /home/app/.ssh
# 创建日志清理脚本
cat > /home/app/scripts/log_cleanup.sh << 'EOF'
#!/bin/bash
LOG_DIR="/etc/mysqlrouter/log"
BACKUP_DIR="/home/app/log_backups"
REMOTE_USER="app"
REMOTE_HOST="mysql-2"
REMOTE_DIR="/backups/mysqlrouter_logs"
mkdir -p $BACKUP_DIR
TIMESTAMP=$(date +%Y%m%d%H%M%S)
BACKUP_FILE="$BACKUP_DIR/mysqlrouter_logs_$TIMESTAMP.tar.gz"
tar -czf $BACKUP_FILE -C $LOG_DIR .
scp -o StrictHostKeyChecking=no $BACKUP_FILE $REMOTE_USER@$REMOTE_HOST:$REMOTE_DIR
find $LOG_DIR -name "*.log" -type f -mtime +7 -exec rm -f {} \;
find $BACKUP_DIR -name "*.tar.gz" -type f -mtime +30 | sort | head -n -3 | xargs rm -f
EOF
chown app:app /home/app/scripts/log_cleanup.sh
chmod +x /home/app/scripts/log_cleanup.sh
# 设置SSH密钥
if [ ! -f /home/app/.ssh/id_rsa ]; then
sudo -u app ssh-keygen -t rsa -b 4096 -f /home/app/.ssh/id_rsa -N ""
fi
# 如果是主节点，将公钥复制到其他节点
if [ "$NODE_TYPE" = "master" ]; then
log_info "Please manually copy the SSH public key to other nodes:"
cat /home/app/.ssh/id_rsa.pub
sudo -u app sshpass -p "$ROOT_PASSWORD" ssh-copy-id -i /home/app/.ssh/id_rsa.pub app@mysql-2
sudo -u app ssh -i /home/app/.ssh/backup_key app@mysql-2 "echo 'SSH connection successful'"
fi
# 添加定时任务
(sudo -u app crontab -l 2>/dev/null; echo "0 2 * * 1 /home/app/scripts/log_cleanup.sh >> /home/app/log_cleanup.log 2>&1") | sudo -u app crontab -
(sudo -u app crontab -l 2>/dev/null; echo "30 2 28-31 * * [ \$(date -d tomorrow +\\%d) -eq 1 ] && /home/app/scripts/log_cleanup.sh >> /home/app/log_cleanup.log 2>&1") | sudo -u app crontab -
}
# 主执行流程
main() {
check_root
setup_hosts
install_dependencies
create_users_dirs
configure_system
install_mysql
configure_mysql
install_mysql_shell
if [ "$NODE_TYPE" = "master" ]; then
configure_cluster
else
# 从节点需要等待主节点集群初始化完成
sleep 60
configure_cluster
fi
install_mysql_router
install_keepalived
setup_log_cleanup
log_info "MySQL InnoDB Cluster deployment completed on $HOSTNAME!"
}
main
使用说明
将上述脚本保存为 deploy_mysql_cluster.sh
给脚本执行权限：chmod +x deploy_mysql_cluster.sh
在三台服务器上分别执行：
主节点(mysql-1):
bash
sudo ./deploy_mysql_cluster.sh master 1
从节点1(mysql-2):
bash
sudo ./deploy_mysql_cluster.sh slave 2
从节点2(mysql-3):
bash
sudo ./deploy_mysql_cluster.sh slave 3
注意事项
脚本需要以root权限运行
主节点(mysql-1)需要先执行
脚本会自动处理大部分配置，但SSH密钥需要手动复制到其他节点
首次运行后，可能需要手动检查集群状态并处理任何异常
生产环境建议修改脚本中的默认密码
网络接口名称(ens33)可能需要根据实际环境调整
这个自动化脚本涵盖了您需求中的所有配置步骤，包括MySQL编译安装、InnoDB Cluster配置、MySQL Router部署、Keepalived高可用设置以及日志清理配置。
–initialize-insecure的核心作用
不生成临时密码
当使用–initialize（无-insecure）初始化时，MySQL 会生成一个随机的临时密码并输出到日志中，该密码仅在首次登录时有效。
使用–initialize-insecure后，MySQL 不会生成临时密码，root 用户的初始密码为空，可直接无密码登录。
# 带-insecure参数的初始化命令
sudo /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
# 初始化后直接登录（无密码）
mysql -u root