2. 网络架构：
– 多网络路径：避免网络单点故障
– 网络隔离：将管理网络和业务网络分离
– 网络监控：实时监控网络状态

3. 硬件架构：
– 冗余硬件：电源、风扇、硬盘等
– 负载均衡：分发流量，避免单点压力
– 灾备站点：异地灾备，应对区域性灾难

4. 软件架构：
– 自动故障检测：实时检测系统状态
– 自动故障切换：在故障发生时自动切换
– 自动恢复：在故障恢复后自动同步数据

# 高可用架构设计原则
– 无单点故障：所有组件都有冗余
– 自动故障检测：实时检测系统状态
– 自动故障切换：在故障发生时自动切换
– 数据一致性：确保数据的一致性和完整性
– 可扩展性：支持系统的横向和纵向扩展
– 可监控性：实时监控系统状态，及时发现问题

2. 软件成本：
– 数据库许可证：企业版MySQL或第三方工具
– 监控工具：专业监控软件
– 备份工具：专业备份软件

3. 人力成本：
– 运维人员：专职运维人员
– 培训成本：技术培训费用

4. 运营成本：
– 电力：额外的电力消耗
– 冷却：额外的冷却设备
– 空间：额外的机房空间

# 成本效益分析
– 停机成本：每小时停机造成的损失
– 高可用投资：实施高可用方案的总成本
– 投资回报率：(停机成本 – 高可用投资) / 高可用投资

# 成本控制策略
1. 分阶段实施：根据业务需求，分阶段实施高可用方案
2. 合理配置：根据实际需求，合理配置硬件和软件
3. 自动化管理：通过自动化工具，减少人力成本
4. 云服务：考虑使用云服务，降低硬件和维护成本

# 重启MySQL服务
systemctl restart mysqld

# 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘ReplPassword123!’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;
mysql> FLUSH PRIVILEGES;

# 获取主库状态
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 123 | | | |
+—————+———-+————–+——————+——————-+

# 步骤2：配置从库
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin

# 重启MySQL服务
systemctl restart mysqld

# 配置从库复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;

# 启动复制
mysql> START SLAVE;

# 检查复制状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 123
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 123
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 123
Relay_Log_Space: 123
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

# 重启MySQL服务
systemctl restart mysqld

# 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘ReplPassword123!’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;
mysql> FLUSH PRIVILEGES;

# 获取主库1状态
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 123 | | | |
+—————+———-+————–+——————+——————-+

# 步骤2：配置主库2
# vi /etc/my.cnf
[mysqld]
server-id = 2
log-bin = /mysql/logs/binlog
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 2

# 重启MySQL服务
systemctl restart mysqld

# 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘ReplPassword123!’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;
mysql> FLUSH PRIVILEGES;

# 获取主库2状态
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 123 | | | |
+—————+———-+————–+——————+——————-+

# 步骤3：配置主库1复制主库2
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;

# 启动复制
mysql> START SLAVE;

# 步骤4：配置主库2复制主库1
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;

# 启动复制
mysql> START SLAVE;

# 步骤5：检查复制状态
# 在主库1上
mysql> SHOW SLAVE STATUS\G

# 在主库2上
mysql> SHOW SLAVE STATUS\G

# 步骤2：配置MySQL实例
# 配置每个MySQL实例的my.cnf文件
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
log-slave-updates = ON
relay-log-recovery = ON

# 重启MySQL服务
systemctl restart mysqld

# 步骤3：创建集群管理员用户
mysql> CREATE USER ‘clusteradmin’@’%’ IDENTIFIED BY ‘ClusterAdminPassword123!’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘clusteradmin’@’%’ WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

# 步骤4：初始化InnoDB Cluster
# 启动MySQL Shell
mysqlsh

# 连接到第一个实例
MySQL JS> shell.connect(‘clusteradmin@192.168.1.100:3306’)

# 创建集群
MySQL JS> var cluster = dba.createCluster(‘mycluster’)

# 添加其他实例
MySQL JS> cluster.addInstance(‘clusteradmin@192.168.1.101:3306’)
MySQL JS> cluster.addInstance(‘clusteradmin@192.168.1.102:3306’)

# 检查集群状态
MySQL JS> cluster.status()

# 步骤5：配置MySQL Router
# 下载并安装MySQL Router

# 配置MySQL Router
mysqlrouter –bootstrap clusteradmin@192.168.1.100:3306 –user=mysqlrouter

# 启动MySQL Router
systemctl start mysqlrouter

# 检查MySQL Router状态
systemctl status mysqlrouter

# 步骤1：配置主从复制
# 主库配置
# vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW

# 从库配置
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin

# 配置复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;
mysql> START SLAVE;

# 步骤2：配置监控
# 安装监控工具
# 配置Prometheus和Grafana监控MySQL状态

# 步骤3：配置故障切换脚本
# vi /mysql/scripts/failover.sh
#!/bin/bash
# failover.sh

# 检查主库状态
ping -c 3 192.168.1.100 > /dev/null
if [ $? -ne 0 ]; then
# 主库不可达，执行故障切换
echo “Master is down, performing failover…”

# 停止从库复制
mysql -u root -pPassword123! -e “STOP SLAVE;”

# 提升从库为主库
mysql -u root -pPassword123! -e “RESET SLAVE ALL;”
mysql -u root -pPassword123! -e “RESET MASTER;”

# 更新应用连接配置
sed -i ‘s/192.168.1.100/192.168.1.101/g’ /app/config/database.php

# 重启应用服务
systemctl restart app.service

echo “Failover completed successfully!”
else
echo “Master is up, no failover needed.”
fi

# 设置执行权限
chmod +x /mysql/scripts/failover.sh

# 添加cron任务
# crontab -e
*/5 * * * * /mysql/scripts/failover.sh

# 步骤4：验证高可用方案
# 模拟主库故障
# 在主库上执行
systemctl stop mysqld

# 检查故障切换是否成功
# 查看从库状态
mysql> SHOW MASTER STATUS;

# 检查应用是否正常运行
curl http://app.example.com

# 步骤1：配置主主复制
# 主库1配置
# vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 1

# 主库2配置
# vi /etc/my.cnf
[mysqld]
server-id = 2
log-bin = /mysql/logs/binlog
binlog-format = ROW
auto-increment-increment = 2
auto-increment-offset = 2

# 配置双向复制
# 在主库1上
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;
mysql> START SLAVE;

# 在主库2上
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;
mysql> START SLAVE;

# 步骤2：配置负载均衡
# 安装HAProxy
yum install -y haproxy

# 配置HAProxy
# vi /etc/haproxy/haproxy.cfg
frontend mysql_frontend
bind *:3306
mode tcp
default_backend mysql_backend

backend mysql_backend
mode tcp
balance roundrobin
server mysql1 192.168.1.100:3306 check
server mysql2 192.168.1.101:3306 check

# 启动HAProxy
systemctl start haproxy
systemctl enable haproxy

# 步骤3：配置监控
# 安装Prometheus和Grafana
# 配置MySQL监控面板

# 步骤4：配置自动故障切换
# 安装Keepalived
yum install -y keepalived

# 配置Keepalived
# vi /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
state MASTER
interface eth0
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.1.100/24
}
}

# 启动Keepalived
systemctl start keepalived
systemctl enable keepalived

# 步骤5：验证高可用方案
# 模拟主库1故障
# 在主库1上执行
systemctl stop mysqld

# 检查HAProxy状态
haproxy -f /etc/haproxy/haproxy.cfg -c

# 检查应用是否正常运行
curl http://app.example.com

# 步骤1：配置InnoDB Cluster
# 配置3个MySQL实例
# 每个实例的my.cnf配置
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON
master-info-repository = TABLE
relay-log-info-repository = TABLE
binlog-checksum = NONE
log-slave-updates = ON
relay-log-recovery = ON

# 初始化InnoDB Cluster
mysqlsh
MySQL JS> shell.connect(‘clusteradmin@192.168.1.100:3306’)
MySQL JS> var cluster = dba.createCluster(‘mycluster’)
MySQL JS> cluster.addInstance(‘clusteradmin@192.168.1.101:3306’)
MySQL JS> cluster.addInstance(‘clusteradmin@192.168.1.102:3306′)

# 步骤2：配置MySQL Router
# 安装MySQL Router
# 配置MySQL Router
mysqlrouter –bootstrap clusteradmin@192.168.1.100:3306 –user=mysqlrouter

# 启动MySQL Router
systemctl start mysqlrouter
systemctl enable mysqlrouter

# 步骤3：配置负载均衡
# 安装HAProxy
# 配置HAProxy
# vi /etc/haproxy/haproxy.cfg
frontend mysql_frontend
bind *:3306
mode tcp
default_backend mysql_backend

backend mysql_backend
mode tcp
balance roundrobin
server router1 192.168.1.100:6446 check
server router2 192.168.1.101:6446 check

# 启动HAProxy
systemctl start haproxy
systemctl enable haproxy

# 步骤4：配置监控
# 安装Prometheus和Grafana
# 配置MySQL Cluster监控面板

# 步骤5：配置灾备
# 在异地数据中心配置从库
# 配置主从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;
mysql> START SLAVE;

# 步骤6：验证高可用方案
# 模拟主库故障
# 在主库上执行
systemctl stop mysqld

# 检查集群状态
mysqlsh
MySQL JS> shell.connect(‘clusteradmin@192.168.1.101:3306’)
MySQL JS> var cluster = dba.getCluster()
MySQL JS> cluster.status()

# 检查应用是否正常运行
curl http://app.example.com

# 配置Prometheus
# vi /etc/prometheus/prometheus.yml
scrape_configs:
– job_name: ‘mysql’
static_configs:
– targets: [‘192.168.1.100:9104’, ‘192.168.1.101:9104’]

# 启动Prometheus
systemctl start prometheus
systemctl enable prometheus

# 步骤2：配置MySQL Exporter
# 安装MySQL Exporter
download mysql_exporter

# 配置MySQL Exporter
# 创建监控用户
mysql> CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘ExporterPassword123!’;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;
mysql> FLUSH PRIVILEGES;

# 创建配置文件
# vi /etc/.mysqld_exporter.cnf
[client]
user=exporter
password=ExporterPassword123!

# 启动MySQL Exporter
nohup ./mysqld_exporter –config.my-cnf=/etc/.mysqld_exporter.cnf &

# 步骤3：配置Grafana
# 安装Grafana
yum install -y grafana

# 启动Grafana
systemctl start grafana-server
systemctl enable grafana-server

# 配置Grafana数据源
# 登录Grafana，添加Prometheus数据源

# 导入MySQL监控面板
# 导入ID为7362的MySQL监控面板

# 步骤4：配置告警
# 在Grafana中配置告警规则
# 配置邮件告警

# 步骤5：配置自动化管理脚本
# 监控复制状态脚本
# vi /mysql/scripts/monitor_replication.sh
#!/bin/bash
# monitor_replication.sh

LOG_FILE=”/mysql/logs/replication_monitor.log”

# 检查复制状态
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G” | grep -E “Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master”)

if echo “$SLAVE_STATUS” | grep -q “Slave_IO_Running: No”; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Slave IO is not running!” >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave IO is not running” admin@example.com < $LOG_FILE fi if echo "$SLAVE_STATUS" | grep -q "Slave_SQL_Running: No"; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Slave SQL is not running!" >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave SQL is not running” admin@example.com < $LOG_FILE fi SECONDS_BEHIND_MASTER=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}') if [ "$SECONDS_BEHIND_MASTER" -gt 300 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Slave is behind master by $SECONDS_BEHIND_MASTER seconds!" >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave is behind master” admin@example.com < $LOG_FILE fi # 设置执行权限 chmod +x /mysql/scripts/monitor_replication.sh # 添加cron任务 # crontab -e */5 * * * * /mysql/scripts/monitor_replication.sh # 步骤6：验证监控效果 # 访问Grafana面板 # http://localhost:3000 # 检查监控数据 # 查看MySQL复制状态 # 查看系统资源使用情况