1. 服务可用性
– 目标：99.9% 以上的可用性
– 年度 downtime：不超过 8.76 小时

2. 数据一致性
– 确保主从数据同步
– 避免数据丢失

3. 自动故障转移
– 检测主库故障
– 自动提升从库为主库
– 最小化故障转移时间

4. 可扩展性
– 支持读写分离
– 支持水平扩展

5. 可管理性
– 易于监控
– 易于维护
– 易于升级

1. 主库配置
vim /etc/my.cnf

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

2. 重启主库
systemctl restart mysqld

3. 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘repl_password’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;

4. 查看主库状态
mysql> SHOW MASTER STATUS;

输出示例：
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 12345 | | | |
+——————+———-+————–+——————+——————-+

5. 从库配置
vim /etc/my.cnf

[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1

6. 重启从库
systemctl restart mysqld

7. 配置从库连接主库
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl_password’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=12345;

8. 启动复制
mysql> START SLAVE;

9. 查看从库状态
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: mysql-bin.000001
Read_Master_Log_Pos: 12345
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 12345
Relay_Master_Log_File: mysql-bin.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: 12345
Relay_Log_Space: 12345
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: /var/lib/mysql/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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:

1. 安装半同步插件（主库和从库）
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so’;
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so’;

2. 启用半同步复制（主库）
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 10000; — 10秒

3. 启用半同步复制（从库）
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

4. 重启从库复制
mysql> STOP SLAVE;
mysql> START SLAVE;

5. 查看半同步状态（主库）
mysql> SHOW GLOBAL STATUS LIKE ‘Rpl_semi_sync%’;

输出示例：
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 100 |
| Rpl_semi_sync_master_net_wait_time | 1000 |
| Rpl_semi_sync_master_net_waits | 10 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 500 |
| Rpl_semi_sync_master_tx_wait_time | 5000 |
| Rpl_semi_sync_master_tx_waits | 10 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 10 |
+————————————+——-+

1. 配置所有节点的my.cnf
vim /etc/my.cnf

[mysqld]
server-id = 1 # 每个节点不同
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
binlog_checksum = NONE
log_slave_updates = ON
log_bin = binlog
binlog_format = ROW
auto_increment_increment = 7
auto_increment_offset = 1 # 每个节点不同
transaction_write_set_extraction = XXHASH64
loose-group_replication_group_name = “ce47c3c9-1491-433e-b2ce-642057b309f1”
loose-group_replication_start_on_boot = OFF
loose-group_replication_local_address = “192.168.1.100:33061”
loose-group_replication_group_seeds = “192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061”
loose-group_replication_bootstrap_group = OFF
loose-group_replication_single_primary_mode = ON
loose-group_replication_enforce_update_everywhere_checks = OFF

2. 重启所有节点
systemctl restart mysqld

3. 安装组复制插件（所有节点）
mysql> INSTALL PLUGIN group_replication SONAME ‘group_replication.so’;

4. 配置复制用户（所有节点）
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘repl_password’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;

5. 配置复制通道（所有节点）
mysql> CHANGE MASTER TO MASTER_USER=’repl’, MASTER_PASSWORD=’repl_password’ FOR CHANNEL
‘group_replication_recovery’;

6. 引导组（仅第一个节点）
mysql> SET GLOBAL group_replication_bootstrap_group = ON;
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group = OFF;

7. 加入组（其他节点）
mysql> START GROUP_REPLICATION;

8. 查看组状态
mysql> SELECT * FROM performance_schema.replication_group_members;

输出示例：
+—————————+————————————–+————-+————-+—————+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+—————————+————————————–+————-+————-+—————+
| group_replication_applier | 12345678-1234-1234-1234-1234567890ab | 192.168.1.100 | 3306 | ONLINE |
| group_replication_applier | 23456789-2345-2345-2345-234567890abc | 192.168.1.101 | 3306 | ONLINE |
| group_replication_applier | 34567890-3456-3456-3456-34567890abcd | 192.168.1.102 | 3306 | ONLINE |
+—————————+————————————–+————-+————-+—————+

1. 模拟主库故障
# 在主库上执行
shutdown -h now

2. 查看组状态（从其他节点）
mysql> SELECT * FROM performance_schema.replication_group_members;

输出示例：
+—————————+————————————–+————-+————-+—————+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+—————————+————————————–+————-+————-+—————+
| group_replication_applier | 12345678-1234-1234-1234-1234567890ab | 192.168.1.100 | 3306 | OFFLINE |
| group_replication_applier | 23456789-2345-2345-2345-234567890abc | 192.168.1.101 | 3306 | ONLINE |
| group_replication_applier | 34567890-3456-3456-3456-34567890abcd | 192.168.1.102 | 3306 | ONLINE |
+—————————+————————————–+————-+————-+—————+

3. 查看新的主库
mysql> SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE
VARIABLE_NAME=’group_replication_primary_member’;

输出示例：
+————————————–+
| VARIABLE_VALUE |
+————————————–+
| 23456789-2345-2345-2345-234567890abc |
+————————————–+

4. 故障节点恢复后重新加入组
# 启动故障节点
poweron

# 启动组复制
mysql> START GROUP_REPLICATION;

# 查看组状态
mysql> SELECT * FROM performance_schema.replication_group_members;

输出示例：
+—————————+————————————–+————-+————-+—————+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+—————————+————————————–+————-+————-+—————+
| group_replication_applier | 12345678-1234-1234-1234-1234567890ab | 192.168.1.100 | 3306 | ONLINE |
| group_replication_applier | 23456789-2345-2345-2345-234567890abc | 192.168.1.101 | 3306 | ONLINE |
| group_replication_applier | 34567890-3456-3456-3456-34567890abcd | 192.168.1.102 | 3306 | ONLINE |
+—————————+————————————–+————-+————-+—————+

1. 安装MySQL Shell
wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.36-linux-glibc2.12-x86_64.tar.gz
tar -xzf mysql-shell-8.0.36-linux-glibc2.12-x86_64.tar.gz
cd mysql-shell-8.0.36-linux-glibc2.12-x86_64/bin

2. 连接到第一个节点
./mysqlsh root@192.168.1.100

3. 检查实例状态
MySQL JS> dba.checkInstanceConfiguration(‘root@192.168.1.100:3306’)

输出示例：
{“status”: “ok”, “message”: “The instance is ready for InnoDB Cluster”}

4. 配置实例
MySQL JS> dba.configureInstance(‘root@192.168.1.100:3306’)

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

6. 添加其他节点
MySQL JS> cluster.addInstance(‘root@192.168.1.101:3306’)
MySQL JS> cluster.addInstance(‘root@192.168.1.102:3306’)

7. 查看集群状态
MySQL JS> cluster.status()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.100:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

1. 模拟主库故障
# 在主库上执行
shutdown -h now

2. 查看集群状态
MySQL JS> cluster.status()

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.101:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “OFFLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

3. 故障节点恢复后重新加入集群
# 启动故障节点
poweron

# 重新加入集群
MySQL JS> cluster.rejoinInstance(‘root@192.168.1.100:3306’)

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

输出示例：
{
“clusterName”: “myCluster”,
“defaultReplicaSet”: {
“name”: “default”,
“primary”: “192.168.1.101:3306”,
“ssl”: “REQUIRED”,
“status”: “OK”,
“statusText”: “Cluster is ONLINE and can tolerate up to ONE failure.”,
“topology”: {
“192.168.1.100:3306”: {
“address”: “192.168.1.100:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.101:3306”: {
“address”: “192.168.1.101:3306”,
“mode”: “R/W”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
},
“192.168.1.102:3306”: {
“address”: “192.168.1.102:3306”,
“mode”: “R/O”,
“readReplicas”: {},
“role”: “HA”,
“status”: “ONLINE”
}
}
}
}

1. 安装ProxySQL
apt-get install proxysql

2. 配置ProxySQL
vim /etc/proxysql.cnf

3. 添加MySQL节点
mysql -u admin -padmin -h 127.0.0.1 -P 6032

ProxySQL> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (1,
‘192.168.1.100’, 3306, 1, 1000);
ProxySQL> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (1,
‘192.168.1.101’, 3306, 1, 1000);
ProxySQL> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (2,
‘192.168.1.100’, 3306, 1, 1000);
ProxySQL> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections) VALUES (2,
‘192.168.1.101’, 3306, 1, 1000);

4. 配置读写分离规则
ProxySQL> INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, ‘^SELECT.*FOR UPDATE$’, 1, 1);
ProxySQL> INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, ‘^SELECT’, 2, 1);

5. 配置监控用户
ProxySQL> INSERT INTO mysql_users (username, password, default_hostgroup, active, max_connections) VALUES
(‘monitor’, ‘monitor’, 1, 1, 100);

6. 加载配置到运行时
ProxySQL> LOAD MYSQL SERVERS TO RUNTIME;
ProxySQL> LOAD MYSQL USERS TO RUNTIME;
ProxySQL> LOAD MYSQL QUERY RULES TO RUNTIME;

7. 保存配置到磁盘
ProxySQL> SAVE MYSQL SERVERS TO DISK;
ProxySQL> SAVE MYSQL USERS TO DISK;
ProxySQL> SAVE MYSQL QUERY RULES TO DISK;

8. 测试连接
mysql -u app_user -p -h 127.0.0.1 -P 6033

输出示例：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL)

mysql> SELECT @@hostname;

输出示例：
+————+
| @@hostname |
+————+
| db1 |
+————+

1. 安装HAProxy
apt-get install haproxy

2. 配置HAProxy
vim /etc/haproxy/haproxy.cfg

# 添加以下内容
frontend mysql
bind *:3306
mode tcp
option tcplog
default_backend mysql_servers

backend mysql_servers
mode tcp
balance roundrobin
option mysql-check user haproxy_check
server db1 192.168.1.100:3306 check
server db2 192.168.1.101:3306 check

3. 创建监控用户
mysql> CREATE USER ‘haproxy_check’@’%’ IDENTIFIED BY ”;

4. 重启HAProxy
systemctl restart haproxy

5. 测试连接
mysql -u app_user -p -h 192.168.1.10:3306

输出示例：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 MySQL Community Server – GPL

mysql> SELECT @@hostname;

输出示例：
+————+
| @@hostname |
+————+
| db1 |
+————+

1. 多数据中心部署
– 跨数据中心复制
– 地理分布式架构
– 灾难恢复计划

2. 网络设计
– 冗余网络
– 负载均衡
– 网络监控

3. 存储设计
– 冗余存储
– 存储快照
– 存储监控

4. 服务器设计
– 冗余服务器
– 负载均衡
– 服务器监控

1. 监控
– 实时监控
– 告警机制
– 性能分析

2. 备份
– 定期备份
– 备份验证
– 灾难恢复测试

3. 升级
– 滚动升级
– 灰度发布
– 回滚计划

4. 故障演练
– 定期故障演练
– 故障恢复测试
– 故障分析

5. 文档
– 架构文档
– 操作手册
– 应急响应计划

1. 架构组成
– 3节点InnoDB Cluster（主从复制）
– 2节点ProxySQL（负载均衡）
– 2节点HAProxy（TCP负载均衡）
– 监控系统（Prometheus + Grafana）
– 备份系统（XtraBackup）

2. 配置步骤

a. 部署MySQL节点
– 配置3个MySQL实例
– 启用GTID和binlog
– 配置组复制

b. 部署InnoDB Cluster
– 使用MySQL Shell创建集群
– 添加所有节点到集群
– 配置自动故障转移

c. 部署ProxySQL
– 配置MySQL服务器组
– 配置读写分离规则
– 配置健康检查

d. 部署HAProxy
– 配置TCP负载均衡
– 配置健康检查
– 配置会话持久化

e. 部署监控系统
– 配置Prometheus采集MySQL指标
– 配置Grafana仪表板
– 配置告警规则

f. 部署备份系统
– 配置XtraBackup定期备份
– 配置备份验证
– 配置备份清理

3. 故障转移测试

a. 模拟主库故障
– 关闭主库
– 观察自动故障转移
– 验证服务可用性

b. 模拟ProxySQL故障
– 关闭一个ProxySQL节点
– 观察流量切换
– 验证服务可用性

c. 模拟HAProxy故障
– 关闭一个HAProxy节点
– 观察流量切换
– 验证服务可用性

4. 性能测试

a. 负载测试
– 使用sysbench进行读写测试
– 验证在高负载下的可用性
– 分析性能瓶颈

b. 故障恢复测试
– 模拟故障并记录恢复时间
– 验证数据一致性
– 优化恢复流程

1. 灾难恢复计划
– 定义灾难级别
– 制定恢复策略
– 确定恢复时间目标（RTO）
– 确定恢复点目标（RPO）

2. 备份策略
– 全量备份：每天一次
– 增量备份：每小时一次
– 二进制日志：实时备份

3. 恢复流程

a. 从全量备份恢复
– 停止MySQL服务
– 恢复全量备份
– 应用增量备份
– 应用二进制日志
– 启动MySQL服务

b. 从复制恢复
– 提升从库为主库
– 重新配置其他从库
– 更新应用连接字符串

4. 测试恢复流程
– 定期测试恢复流程
– 记录恢复时间
– 优化恢复流程

5. 演练
– 定期进行灾难恢复演练
– 模拟各种灾难场景
– 评估恢复效果