MySQL教程FG300-MySQL数据库架构设计

内容简介：本文主要介绍MySQL存储过程参数与返回值的相关知识，包括参数类型、使用方法和最佳实践等内容。风哥教程参考MySQL官方文档MySQL Architecture、MySQL Server Administration。 01 更多视频教程www.fgedu.net.cn

Part01-基础概念与理论知识

1.1 架构设计的重要性

数据库架构设计是整个系统架构的核心，良好的数据库架构能够支撑业务的高速发展，保障系统的稳定性和可扩展性。

1.2 架构设计原则

数据库架构设计的基本原则： 02 学习交流加群风哥微信: itpux-com

# 架构设计原则

1. 高可用性
– 消除单点故障
– 自动故障转移
– 数据冗余备份
– 快速故障恢复

2. 高性能
– 读写分离
– 分库分表
– 缓存优化
– 连接池管理

3. 可扩展性
– 水平扩展能力
– 垂直扩展能力
– 在线扩容
– 平滑迁移

4. 安全性
– 数据加密
– 访问控制
– 审计日志
– 备份恢复

5. 可维护性
– 标准化配置
– 自动化运维
– 监控告警
– 文档完善

Part02-生产环境规划与建议

2.1 主从复制架构

主从复制高可用架构设计：

# 主从复制高可用架构

1. 一主一从架构
主库: 192.168.1.100 (读写)
从库: 192.168.1.101 (只读)

适用场景:
– 小型应用
– 读压力不大
– 预算有限

2. 一主多从架构
主库: 192.168.1.100 (写)
从库1: 192.168.1.101 (读)
从库2: 192.168.1.102 (读)
从库3: 192.168.1.103 (读)

适用场景:
– 读密集型应用
– 报表查询
– 数据分析

3. 双主架构
主库1: 192.168.1.100 (读写)
主库2: 192.168.1.101 (读写)

适用场景:
– 双向同步需求
– 异地多活
– 高可用要求

4. 配置示例
# 主库配置
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
sync-binlog = 1
innodb_flush_log_at_trx_commit = 1

# 从库配置
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
log-slave-updates = 1

# 创建复制用户
CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘ReplP@ssw0rd123’;
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;

# 配置复制
CHANGE MASTER TO
MASTER_HOST=’192.168.1.100′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’ReplP@ssw0rd123′,
MASTER_AUTO_POSITION=1;

START SLAVE;

# 查看复制状态
SHOW SLAVE STATUS\G

输出示例:
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

2.2 集群架构

MySQL集群高可用架构设计： 03 学习交流加群风哥QQ113257174

# MySQL集群架构

1. InnoDB Cluster架构
节点1: 192.168.1.100 (Primary)
节点2: 192.168.1.101 (Secondary)
节点3: 192.168.1.102 (Secondary)

特点:
– 基于组复制
– 自动故障转移
– 强一致性
– 读写分离

2. 配置InnoDB Cluster
# 使用MySQL Shell配置
mysqlsh root@192.168.1.100:3306

# 检查实例配置
MySQL JS> dba.checkInstanceConfiguration(‘root@192.168.1.100:3306’)

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

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

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

输出示例:
A new InnoDB cluster will be created on instance ‘root@192.168.1.100:3306’.

Validating instance configuration at localhost:3306…
This instance reports its own address as 192.168.1.100:3306
Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using ‘192.168.1.100:3306’. Use the localAddress option to override.

* Checking connectivity and SSL configuration…

Creating InnoDB cluster ‘productionCluster’ on ‘root@192.168.1.100:3306’…

Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

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

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

输出示例:
{
“clusterName”: “productionCluster”,
“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”
}
}
}
}

Part03-生产环境项目实施方案

3.1 代理层架构

使用ProxySQL实现读写分离： 04 风哥提示：

# ProxySQL读写分离架构

1. 架构设计
应用 -> ProxySQL(6033) -> 主库(3306) [写]
-> 从库1(3306) [读]
-> 从库2(3306) [读]

2. 安装ProxySQL
yum install proxysql -y

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

datadir=”/var/lib/proxysql”

admin_variables=
{
admin_credentials=”admin:admin”
mysql_ifaces=”0.0.0.0:6032″
}

mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces=”0.0.0.0:6033″
default_schema=”information_schema”
stacksize=1048576
server_version=”8.0.36″
connect_timeout_server=3000
monitor_username=”monitor”
monitor_password=”monitor_password”
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}

mysql_servers =
(
{ hostgroup_id=1, hostname=”192.168.1.100″, port=3306, weight=1 },
{ hostgroup_id=2, hostname=”192.168.1.101″, port=3306, weight=1 },
{ hostgroup_id=2, hostname=”192.168.1.102″, port=3306, weight=1 }
)

mysql_users =
(
{ username=”app_user”, password=”app_password”, default_hostgroup=1, transaction_persistent=true }
)

mysql_query_rules =
(
{
rule_id=1
active=1
match_pattern=”^SELECT.*FOR UPDATE”
destination_hostgroup=1
apply=1
},
{
rule_id=2
active=1
match_pattern=”^SELECT”
destination_hostgroup=2
apply=1
}
)

4. 启动ProxySQL
systemctl start proxysql
systemctl enable proxysql

5. 管理ProxySQL
mysql -u admin -p -h 127.0.0.1 -P 6032

# 查看后端服务器
SELECT * FROM mysql_servers;

# 查看用户
SELECT * FROM mysql_users;

# 查看查询规则
SELECT * FROM mysql_query_rules;

# 加载配置到运行时
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

# 保存配置到磁盘
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

6. 测试读写分离
mysql -u app_user -p -h 127.0.0.1 -P 6033 -e “SELECT @@hostname;”
mysql -u app_user -p -h 127.0.0.1 -P 6033 -e “INSERT INTO test VALUES (1); SELECT @@hostname;”

输出示例:
+————+
| @@hostname |
+————+
| mysql-slave1 |
+————+

+————+
| @@hostname |
+————+
| mysql-master |
+————+

Part04-生产案例与实战讲解

4.1 分片策略

数据库分片策略设计： 05更多学习教程公众号风哥教程itpux_com

# 分库分表策略

1. 垂直分库
按业务模块拆分:
– user_db: 用户相关数据
– order_db: 订单相关数据
– product_db: 商品相关数据
– log_db: 日志相关数据

2. 水平分表
按数据范围或哈希拆分:
– user_0, user_1, user_2, user_3 (用户ID % 4)
– order_202601, order_202602, order_202603 (按月份)

3. 分片算法
# 哈希分片
shard_id = user_id % 4

# 范围分片
if user_id < 1000000:
shard = ‘user_0’
elif user_id < 2000000:
shard = ‘user_1’
elif user_id < 3000000:
shard = ‘user_2’
else:
shard = ‘user_3’

4. 创建分片表
— 创建分片表模板
CREATE TABLE user_template (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_name VARCHAR(64) NOT NULL,
email VARCHAR(128) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_user_name (user_name),
UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

— 创建分片表
CREATE TABLE user_0 LIKE user_template;
CREATE TABLE user_1 LIKE user_template;
CREATE TABLE user_2 LIKE user_template;
CREATE TABLE user_3 LIKE user_template;

5. 分片路由
# Python分片路由示例
def get_shard(user_id):
shard_id = user_id % 4
return f”user_{shard_id}”

def get_connection(user_id):
shard = get_shard(user_id)
# 根据分片获取对应的数据库连接
return connections[shard]

# 插入数据
user_id = 1234567
shard_table = get_shard(user_id)
connection = get_connection(user_id)
cursor = connection.cursor()
cursor.execute(f”INSERT INTO {shard_table} (id, user_name, email) VALUES (%s, %s, %s)”,
(user_id, ‘test_user’, ‘test@fgedu.net.cn’))

# 查询数据
user_id = 1234567
shard_table = get_shard(user_id)
connection = get_connection(user_id)
cursor = connection.cursor()
cursor.execute(f”SELECT * FROM {shard_table} WHERE id = %s”, (user_id,))

Part05-风哥经验总结与分享

5.1 架构设计最佳实践

数据库架构设计的最佳实践： 06 from mysql视频:www.itpux.com

# 架构设计最佳实践

1. 容量规划
– 预留3-5年的增长空间
– 考虑业务高峰期负载
– 评估数据增长速度
– 规划存储和计算资源

2. 高可用设计
– 消除单点故障
– 跨可用区部署
– 自动故障检测和切换
– 定期灾备演练

3. 性能优化
– 读写分离
– 分库分表
– 缓存策略
– 连接池管理

4. 安全防护
– 网络隔离
– 访问控制
– 数据加密
– 审计日志

5. 运维管理
– 标准化配置
– 自动化运维
– 监控告警
– 文档管理

6. 扩展性设计
– 支持水平扩展
– 支持在线扩容
– 平滑迁移方案
– 灰度发布支持