随着业务规模增长，单机MySQL面临性能瓶颈和单点故障风险。本文将深入讲解MySQL高可用架构方案和分库分表策略，协助你构建可扩展的数据库架构。
高可用架构概述
高可用的目标
|
指标 |
说明 |
目标值 |
|
可用性 |
系统正常运行时间占比 |
99.99%（年停机<53分钟） |
|
RTO |
恢复时间目标 |
< 30秒 |
|
RPO |
数据恢复点目标 |
0（零数据丢失） |
常见高可用方案对比
|
方案 |
复杂度 |
数据一致性 |
故障切换 |
适用场景 |
|
主从复制 |
低 |
最终一致 |
手动 |
读多写少 |
|
MHA |
中 |
最终一致 |
自动 |
通用场景 |
|
MGR |
中 |
强一致 |
自动 |
金融级场景 |
|
MySQL Cluster |
高 |
强一致 |
自动 |
超高可用 |
|
Galera Cluster |
中 |
强一致 |
自动 |
多主写入 |
MHA高可用架构
MHA简介
MHA（Master High Availability）是一款开源的MySQL高可用解决方案，由日本DeNA公司开发。它能在主库故障时自动完成故障检测和主从切换，一般能在10-30秒内完成切换。
MHA架构
┌─────────────────┐
│ MHA Manager │
│ (监控和管理) │
└────────┬────────┘
│ 监控
┌─────────────────┼─────────────────┐
│ │ │
▼ ▼ ▼
┌────────────┐ ┌────────────┐ ┌────────────┐
│ Master │ │ Slave1 │ │ Slave2 │
│ (MHA Node) │ │ (MHA Node) │ │ (MHA Node) │
└────────────┘ └────────────┘ └────────────┘
│ ▲ ▲
└─────────────────┴─────────────────┘
主从复制MHA组件
- MHA Manager：管理节点，负责监控主库状态、执行故障切换
- MHA Node：数据节点，部署在每台MySQL服务器上
MHA安装配置
1. 安装MHA Node（所有MySQL节点）
# 安装依赖
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
# 下载并安装MHA Node
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58.tar.gz
tar -xzf mha4mysql-node-0.58.tar.gz
cd mha4mysql-node-0.58
perl Makefile.PL
make && make install2. 安装MHA Manager（管理节点）
# 安装依赖
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# 下载并安装MHA Manager
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58.tar.gz
tar -xzf mha4mysql-manager-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make && make install3. 配置SSH免密登录
# 在Manager节点生成密钥
ssh-keygen -t rsa
# 分发公钥到所有节点
ssh-copy-id root@192.168.1.100 # Master
ssh-copy-id root@192.168.1.101 # Slave1
ssh-copy-id root@192.168.1.102 # Slave24. 创建MHA配置文件
# /etc/mha/app1.cnf
[server default]
# MySQL复制用户
user=root
password=root123
repl_user=repl
repl_password=Repl@123456
# SSH用户
ssh_user=root
# MHA工作目录
manager_workdir=/var/log/mha/app1
manager_log=/var/log/mha/app1/manager.log
remote_workdir=/var/log/mha/app1
# 故障切换脚本
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
# 检测间隔
ping_interval=1
[server1]
hostname=192.168.1.100
port=3306
candidate_master=1
[server2]
hostname=192.168.1.101
port=3306
candidate_master=1
[server3]
hostname=192.168.1.102
port=3306
no_master=15. VIP切换脚本
#!/usr/bin/env perl
# /usr/local/bin/master_ip_failover
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.1.200';
my $interface = 'eth0';
my $key = '1';
GetOptions(
'command=s' => $command,
'ssh_user=s' => $ssh_user,
'orig_master_host=s' => $orig_master_host,
'orig_master_ip=s' => $orig_master_ip,
'orig_master_port=i' => $orig_master_port,
'new_master_host=s' => $new_master_host,
'new_master_ip=s' => $new_master_ip,
'new_master_port=i' => $new_master_port,
);
exit &main();
sub main {
if ($command eq "stop" || $command eq "stopssh") {
# 停止VIP
my $exit_code = 1;
eval {
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@
";
exit $exit_code;
}
exit $exit_code;
}
elsif ($command eq "start") {
# 启动VIP
my $exit_code = 10;
eval {
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ($command eq "status") {
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user@$new_master_host "ip addr add $vip/24 dev $interface"`;
`ssh $ssh_user@$new_master_host "arping -c 3 -I $interface $vip"`;
}
sub stop_vip() {
`ssh $ssh_user@$orig_master_host "ip addr del $vip/24 dev $interface"`;
}
sub usage {
print "Usage: master_ip_failover --command=start|stop|stopssh|status
";
}6. 检查和启动MHA
# 检查SSH连接
masterha_check_ssh --conf=/etc/mha/app1.cnf
# 检查复制状态
masterha_check_repl --conf=/etc/mha/app1.cnf
# 启动MHA Manager
nohup masterha_manager --conf=/etc/mha/app1.cnf
--remove_dead_master_conf
--ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
# 检查MHA状态
masterha_check_status --conf=/etc/mha/app1.cnfMHA故障切换流程
- 检测主库故障：Manager通过ping_interval检测主库状态
- 确认主库宕机：多次检测失败后确认主库故障
- 选择新主库：根据配置和数据最新程度选择候选主库
- 补齐差异日志：从各从库收集差异binlog，补齐新主库
- 切换VIP：执行master_ip_failover脚本切换VIP
- 重新配置从库：将其他从库指向新主库
MySQL Group Replication (MGR)
MGR简介
MySQL Group Replication是MySQL 5.7.17引入的官方高可用方案，基于Paxos协议实现分布式一致性。
MGR特点
- 多主/单主模式：支持单主（推荐）和多主模式
- 自动故障检测：基于组成员协议自动检测故障节点
- 自动故障切换：单主模式下自动选举新主库
- 强一致性：基于Paxos协议保证数据一致性
MGR架构
单主模式：
┌─────────────────────────────────────────────────┐
│ Group Replication │
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ Primary │ │Secondary│ │Secondary│ │
│ │ (R/W) │ │ (R/O) │ │ (R/O) │ │
│ └────┬────┘ └────┬────┘ └────┬────┘ │
│ │ │ │ │
│ └─────────────┼─────────────┘ │
│ │ │
│ Group Communication │
│ (Paxos协议) │
└─────────────────────────────────────────────────┘MGR配置
1. 准备工作
所有节点配置my.cnf：
[mysqld]
# 基础配置
server_id = 1 # 每个节点不同
gtid_mode = ON
enforce_gtid_consistency = ON
log_bin = mysql-bin
binlog_format = ROW
log_slave_updates = ON
binlog_checksum = NONE
master_info_repository = TABLE
relay_log_info_repository = TABLE
# MGR配置
plugin_load_add = 'group_replication.so'
transaction_write_set_extraction = XXHASH64
group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
group_replication_start_on_boot = OFF
group_replication_local_address = "192.168.1.100:33061"
group_replication_group_seeds = "192.168.1.100:33061,192.168.1.101:33061,192.168.1.102:33061"
group_replication_bootstrap_group = OFF
# 单主模式（推荐）
group_replication_single_primary_mode = ON
group_replication_enforce_update_everywhere_checks = OFF2. 创建复制用户
-- 在所有节点执行
SET SQL_LOG_BIN = 0;
CREATE USER 'repl'@'%' IDENTIFIED BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
GRANT BACKUP_ADMIN ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN = 1;
-- 配置复制通道
CHANGE REPLICATION SOURCE TO
SOURCE_USER = 'repl',
SOURCE_PASSWORD = 'Repl@123456'
FOR CHANNEL 'group_replication_recovery';3. 启动MGR集群
-- 在第一个节点（引导节点）
SET GLOBAL group_replication_bootstrap_group = ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group = OFF;
-- 在其他节点
START GROUP_REPLICATION;4. 查看集群状态
-- 查看组成员
SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 3a3b3c3d-1111-2222-3333-444444444444 | node1 | 3306 | ONLINE | PRIMARY | 8.0.32 | XCom |
| group_replication_applier | 4a4b4c4d-1111-2222-3333-444444444444 | node2 | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |
| group_replication_applier | 5a5b5c5d-1111-2222-3333-444444444444 | node3 | 3306 | ONLINE | SECONDARY | 8.0.32 | XCom |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
-- 查看主节点
SELECT MEMBER_HOST, MEMBER_PORT
FROM performance_schema.replication_group_members
WHERE MEMBER_ROLE = 'PRIMARY';MGR与MySQL Router
MySQL Router可以与MGR配合实现自动故障切换。
# 初始化Router配置
mysqlrouter --bootstrap root@192.168.1.100:3306
--user=mysqlrouter
--directory=/etc/mysqlrouter
# 启动Router
mysqlrouter -c /etc/mysqlrouter/mysqlrouter.conf &生成的配置文件：
[routing:primary]
bind_address = 0.0.0.0
bind_port = 6446
destinations = metadata-cache://mycluster/?role=PRIMARY
routing_strategy = first-available
protocol = classic
[routing:secondary]
bind_address = 0.0.0.0
bind_port = 6447
destinations = metadata-cache://mycluster/?role=SECONDARY
routing_strategy = round-robin-with-fallback
protocol = classic分库分表
为什么需要分库分表
|
问题 |
表现 |
解决方案 |
|
单表数据量过大 |
查询慢、索引效率低 |
分表 |
|
单库写入瓶颈 |
TPS上限、连接数限制 |
分库 |
|
单机存储上限 |
磁盘空间不足 |
分库 |
|
单机内存限制 |
Buffer Pool不足 |
分库 |
分库分表策略
垂直拆分
按业务模块拆分数据库或表。
垂直分库：
┌──────────────────────────────────────────────────┐
│ 单体数据库 │
│ ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐ │
│ │ 用户表 │ │ 订单表 │ │ 商品表 │ │ 库存表 │ │
│ └────────┘ └────────┘ └────────┘ └────────┘ │
└──────────────────────────────────────────────────┘
↓ 垂直拆分
┌────────────┐ ┌────────────┐ ┌────────────────────┐
│ 用户库 │ │ 订单库 │ │ 商品库 │
│ ┌────────┐ │ │ ┌────────┐ │ │ ┌────────┐┌────────┐│
│ │ 用户表 │ │ │ │ 订单表 │ │ │ │ 商品表 ││ 库存表 ││
│ └────────┘ │ │ └────────┘ │ │ └────────┘└────────┘│
└────────────┘ └────────────┘ └────────────────────┘
垂直分表：
┌─────────────────────────────────────┐
│ 用户表 │
│ id, name, age, avatar, bio, ... │
└─────────────────────────────────────┘
↓ 垂直拆分
┌─────────────────┐ ┌─────────────────┐
│ 用户基础表 │ │ 用户扩展表 │
│ id, name, age │ │ id, avatar, bio │
└─────────────────┘ └─────────────────┘水平拆分
按数据行拆分到多个库或表。
水平分表：
┌─────────────────────────────────────┐
│ 订单表（1000万行） │
└─────────────────────────────────────┘
↓ 水平拆分
┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ order_0 │ │ order_1 │ │ order_2 │ │ order_3 │
│ (250万行) │ │ (250万行) │ │ (250万行) │ │ (250万行) │
└─────────────┘ └─────────────┘ └─────────────┘ └─────────────┘
水平分库：
┌─────────────────────────────────────┐
│ 单库 │
│ 订单表（1000万行） │
└─────────────────────────────────────┘
↓ 水平拆分
┌─────────────┐ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ db_0 │ │ db_1 │ │ db_2 │ │ db_3 │
│ ┌─────────┐ │ │ ┌─────────┐ │ │ ┌─────────┐ │ │ ┌─────────┐ │
│ │ order_0 │ │ │ │ order_1 │ │ │ │ order_2 │ │ │ │ order_3 │ │
│ └─────────┘ │ │ └─────────┘ │ │ └─────────┘ │ │ └─────────┘ │
└─────────────┘ └─────────────┘ └─────────────┘ └─────────────┘分片键选择
分片键（Sharding Key）是决定数据路由的关键字段。
选择原则
- 高频查询条件：分片键应该是查询中最常用的条件
- 数据分布均匀：避免数据倾斜
- 避免跨分片查询：尽量让相关数据在同一分片
- 不可变：分片键值不应该被修改
常见分片策略
1. 取模分片
// 根据用户ID取模
int shardIndex = userId % shardCount;
String tableName = "order_" + shardIndex;2. 范围分片
// 根据时间范围
if (orderTime.getYear() == 2023) {
tableName = "order_2023";
} else if (orderTime.getYear() == 2024) {
tableName = "order_2024";
}3. 哈希分片
// 一致性哈希
int hash = consistentHash(userId);
int shardIndex = hash % shardCount;4. 复合分片
// 先按用户分库，再按订单ID分表
int dbIndex = userId % dbCount;
int tableIndex = orderId % tableCount;
String dataSource = "db_" + dbIndex;
String tableName = "order_" + tableIndex;ShardingSphere实战
ShardingSphere简介
Apache ShardingSphere是一套开源的分布式数据库中间件，包含：
- ShardingSphere-JDBC：轻量级Java框架，以jar包形式提供
- ShardingSphere-Proxy：透明化数据库代理，支持任何语言
ShardingSphere-JDBC配置
1. 添加依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.4.0</version>
</dependency>
2. 分库分表配置
spring:
shardingsphere:
# 数据源配置
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.100:3306/mydb_0?useSSL=false
username: root
password: root123
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.1.101:3306/mydb_1?useSSL=false
username: root
password: root123
# 分片规则
rules:
sharding:
tables:
# 订单表分片规则
t_order:
actual-data-nodes: ds$->{0..1}.t_order_$->{0..3}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
key-generate-strategy:
column: order_id
key-generator-name: snowflake
# 订单明细表分片规则
t_order_item:
actual-data-nodes: ds$->{0..1}.t_order_item_$->{0..3}
database-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: order_id
sharding-algorithm-name: table-inline
# 绑定表（关联查询优化）
binding-tables:
- t_order,t_order_item
# 广播表（全局表）
broadcast-tables:
- t_config
# 分片算法
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds$->{user_id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: t_order_$->{order_id % 4}
# 主键生成器
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1
# 显示SQL
props:
sql-show: true3. 实体类和Mapper
@Data
@TableName("t_order")
public class Order {
@TableId(type = IdType.ASSIGN_ID)
private Long orderId;
private Long userId;
private BigDecimal amount;
private Integer status;
private LocalDateTime createTime;
}
@Mapper
public interface OrderMapper extends BaseMapper<Order> {
@Select("SELECT * FROM t_order WHERE user_id = #{userId} ORDER BY create_time DESC")
List<Order> findByUserId(@Param("userId") Long userId);
}4. 使用示例
@Service
public class OrderService {
@Autowired
private OrderMapper orderMapper;
// 插入数据（自动路由到对应分片）
public void createOrder(Order order) {
orderMapper.insert(order);
}
// 根据分片键查询（准确路由）
public List<Order> getOrdersByUserId(Long userId) {
return orderMapper.findByUserId(userId);
}
// 跨分片查询（全分片扫描，性能较差）
public List<Order> getOrdersByStatus(Integer status) {
LambdaQueryWrapper<Order> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(Order::getStatus, status);
return orderMapper.selectList(wrapper);
}
}自定义分片算法
1. 实现分片算法接口
public class OrderTableShardingAlgorithm implements StandardShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Long> shardingValue) {
// 准确分片
Long orderId = shardingValue.getValue();
String suffix = String.valueOf(orderId % 4);
for (String tableName : availableTargetNames) {
if (tableName.endsWith(suffix)) {
return tableName;
}
}
throw new IllegalArgumentException("No target table found");
}
@Override
public Collection<String> doSharding(Collection<String> availableTargetNames,
RangeShardingValue<Long> shardingValue) {
// 范围分片
Range<Long> range = shardingValue.getValueRange();
List<String> result = new ArrayList<>();
for (String tableName : availableTargetNames) {
// 简化处理：范围查询返回所有表
result.add(tableName);
}
return result;
}
@Override
public String getType() {
return "ORDER_TABLE";
}
@Override
public void init(Properties props) {
}
}2. 注册自定义算法
# 在 META-INF/services/org.apache.shardingsphere.sharding.spi.ShardingAlgorithm 文件中添加
com.example.sharding.OrderTableShardingAlgorithm3. 使用自定义算法
spring:
shardingsphere:
rules:
sharding:
sharding-algorithms:
order-table-algorithm:
type: ORDER_TABLE分布式主键
雪花算法（Snowflake）
spring:
shardingsphere:
rules:
sharding:
key-generators:
snowflake:
type: SNOWFLAKE
props:
worker-id: 1
max-vibration-offset: 1UUID
spring:
shardingsphere:
rules:
sharding:
key-generators:
uuid:
type: UUID自定义主键生成器
public class CustomKeyGenerator implements KeyGenerateAlgorithm {
private AtomicLong counter = new AtomicLong(0);
@Override
public Comparable<?> generateKey() {
// 自定义主键生成逻辑
return System.currentTimeMillis() * 10000 + counter.incrementAndGet() % 10000;
}
@Override
public String getType() {
return "CUSTOM";
}
@Override
public void init(Properties props) {
}
}分布式事务
Seata AT模式
spring:
shardingsphere:
rules:
transaction:
default-type: XA
provider-type: Atomikos使用示例
@Service
public class OrderService {
@Autowired
private OrderMapper orderMapper;
@Autowired
private InventoryMapper inventoryMapper;
@ShardingTransactionType(TransactionType.XA)
@Transactional
public void createOrderWithInventory(Order order, Long productId) {
// 创建订单（可能在ds0）
orderMapper.insert(order);
// 扣减库存（可能在ds1）
inventoryMapper.decrease(productId, 1);
}
}分库分表最佳实践
容量规划
|
指标 |
提议值 |
说明 |
|
单表行数 |
< 1000万 |
超过后查询性能下降 |
|
单表大小 |
< 10GB |
便于备份和迁移 |
|
单库表数 |
< 500 |
避免元数据过多 |
|
分片数量 |
2的幂次 |
便于扩容 |
扩容方案
翻倍扩容
扩容前：4个分片
user_id % 4 = 0 → shard_0
user_id % 4 = 1 → shard_1
user_id % 4 = 2 → shard_2
user_id % 4 = 3 → shard_3
扩容后：8个分片
user_id % 8 = 0 → shard_0 (原shard_0的一半数据)
user_id % 8 = 4 → shard_4 (原shard_0的另一半数据)
...扩容步骤
- 准备新分片：创建新的数据库实例
- 数据迁移：使用工具迁移数据到新分片
- 双写验证：新旧分片同时写入，验证一致性
- 切换路由：更新分片规则
- 清理旧数据：删除已迁移的数据
常见问题
问题一：跨分片查询
// 不推荐：跨分片查询
SELECT * FROM t_order WHERE status = 1;
// 推荐：带分片键查询
SELECT * FROM t_order WHERE user_id = 123 AND status = 1;问题二：跨分片JOIN
// 不推荐：跨分片JOIN
SELECT o.*, i.* FROM t_order o
JOIN t_order_item i ON o.order_id = i.order_id;
// 推荐：使用绑定表
binding-tables:
- t_order,t_order_item问题三：跨分片排序分页
// 问题：跨分片分页效率低
SELECT * FROM t_order ORDER BY create_time LIMIT 10000, 10;
// 优化1：使用游标分页
SELECT * FROM t_order WHERE create_time > '2024-01-01'
ORDER BY create_time LIMIT 10;
// 优化2：二次查询
// 第一次：各分片查询ID
// 第二次：根据ID准确查询问题四：全局唯一ID
// 使用雪花算法
@TableId(type = IdType.ASSIGN_ID)
private Long id;
// 或使用分布式ID服务
private Long id = idGenerator.nextId();总结
本文详细介绍了MySQL高可用架构和分库分表方案：
- MHA：成熟的主从高可用方案，自动故障切换
- MGR：MySQL官方高可用方案，基于Paxos协议
- 分库分表：解决单机性能瓶颈的有效手段
- ShardingSphere：功能强劲的分库分表中间件
选择提议：
- 中小规模：MHA + 读写分离
- 金融级场景：MGR + MySQL Router
- 大规模数据：ShardingSphere分库分表
下一篇我们将介绍MySQL性能调优与运维实战。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
暂无评论...
