## 架构设计
– 1主2从MySQL集群
– ProxySQL作为中间件
– 应用连接ProxySQL

## 实现步骤

### 1. 配置主从复制
# 主库配置
$ cat /etc/my.cnf
[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=ROW

# 从库配置
$ cat /etc/my.cnf
[mysqld]
server-id=2
relay_log=relay-bin
read_only=1

# 配置复制
CHANGE MASTER TO
MASTER_HOST=’192.168.1.101′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’password’,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=123456;

START SLAVE;

### 2. 配置 ProxySQL
# 添加服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
(1, ‘192.168.1.101’, 3306), — 主库
(2, ‘192.168.1.102’, 3306), — 从库1
(2, ‘192.168.1.103’, 3306); — 从库2

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

### 3. 应用配置
# 修改应用连接字符串
spring.datasource.url=jdbc:mysql://192.168.1.100:6033/shop?useSSL=false
spring.datasource.username=app_user
spring.datasource.password=password

### 4. 测试验证
# 查看连接池状态
SELECT * FROM stats_mysql_connection_pool;

# 查看查询路由情况
SELECT * FROM stats_mysql_query_digest;

# 性能测试
$ ab -n 10000 -c 100 http://localhost:8080/products

## 架构设计
– 3节点MGR集群
– 2节点ProxySQL集群
– 1节点HAProxy负载均衡

## 实现步骤

### 1. 配置 MGR 集群
# 配置 MGR
SET GLOBAL group_replication_bootstrap_group=ON;
CREATE GROUP_REPLICATION_GROUP(‘aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa’);
SET GLOBAL group_replication_bootstrap_group=OFF;

# 其他节点加入集群
SET GLOBAL group_replication_bootstrap_group=OFF;
START GROUP_REPLICATION;

### 2. 配置 ProxySQL
# 添加 MGR 节点
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
(1, ‘192.168.1.101’, 3306),
(1, ‘192.168.1.102’, 3306),
(1, ‘192.168.1.103’, 3306);

# 配置 MGR 监控
INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES
(1, 2, ‘function’, ‘MGR’);

### 3. 配置 HAProxy
$ cat /etc/haproxy/haproxy.cfg
frontend proxysql
bind *:3306
mode tcp
default_backend proxysql_servers

backend proxysql_servers
mode tcp
balance roundrobin
server proxysql1 192.168.1.104:6033 check
server proxysql2 192.168.1.105:6033 check

### 4. 测试故障转移
# 模拟主节点故障
$ systemctl stop mysql@101

# 查看故障转移情况
SELECT * FROM mysql_servers WHERE hostgroup_id=1;

# 验证服务可用性
$ mysql -u app_user -ppassword -h 192.168.1.100 -P 3306 -e “SELECT 1;”
+—+
| 1 |
+—+
| 1 |
+—+

## 架构设计
– ShardingSphere 作为分库分表中间件
– 4个MySQL实例，每个实例4个库
– 按用户ID分库分表

## 实现步骤

### 1. 配置 ShardingSphere
$ cat > sharding.yaml << EOF schemaName: social_db dataSources: ds_0: url: jdbc:mysql://192.168.1.101:3306/social_0?useSSL=false username: root password: password connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_1: url: jdbc:mysql://192.168.1.102:3306/social_0?useSSL=false username: root password: password connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_2: url: jdbc:mysql://192.168.1.103:3306/social_0?useSSL=false username: root password: password connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 ds_3: url: jdbc:mysql://192.168.1.104:3306/social_0?useSSL=false username: root password: password connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 50 shardingRule: tables: users: actualDataNodes: ds_${0..3}.social_${0..3}.users_${0..3} databaseStrategy: standard: shardingColumn: user_id shardingAlgorithmName: database_inline tableStrategy: standard: shardingColumn: user_id shardingAlgorithmName: table_inline shardingAlgorithms: database_inline: type: INLINE props: algorithm-expression: ${user_id % 4} table_inline: type: INLINE props: algorithm-expression: ${user_id % 4} EOF ### 2. 启动 ShardingSphere $ java -jar shardingsphere-proxy-5.4.0.jar --port 3307 --config sharding.yaml ### 3. 创建表结构 $ mysql -u root -p -h 127.0.0.1 -P 3307 CREATE TABLE users ( user_id BIGINT PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL ); ### 4. 测试分库分表 # 插入数据 INSERT INTO users (user_id, username, email) VALUES (1, 'user1', 'user1@fgedu.net.cn'), (2, 'user2', 'user2@fgedu.net.cn'), (3, 'user3', 'user3@fgedu.net.cn'), (4, 'user4', 'user4@fgedu.net.cn'); # 查询数据 SELECT * FROM users; # 查看数据分布 SELECT * FROM ds_0.social_0.users_0; SELECT * FROM ds_1.social_1.users_1; SELECT * FROM ds_2.social_2.users_2; SELECT * FROM ds_3.social_3.users_3;