MySQL
适用环境：RHEL/OEL7 Linux
1. 分表分库概述
1.1 什么是分表分库
分表分库是一种数据库水平扩展方案，通过将数据分散到多个表或多个数据库实例中，以解决单表数据量过大、性能下降的问题。
学习交流加群风哥微信: itpux-com 更多视频教程www.fgedu.net.cn
1.2 分表分库的优势
- 提高查询性能
- 增加系统可用性
- 便于维护和管理
- 支持更大的数据量
1.3 分片中间件选择
- ShardingSphere-Proxy
- MyCAT
- TDDL
- ProxySQL
2. 硬件及系统要求
2.1 硬件要求
- 分片中间件服务器: 至少4核CPU，8GB内存
- MySQL实例服务器: 至少4核CPU，16GB内存，500GB SSD
- 网络: 千兆网卡，低延迟网络环境
2.2 系统要求
- 操作系统: RHEL/OEL 7.x
- 内核版本: 3.10.0-1160.el7.x86_64或更高
- 文件系统: ext4或xfs
- 关闭SELinux或设置为permissive模式
- 关闭防火墙或开放必要端口
3. 环境准备
3.1 系统更新
yum update -y3.2 安装必要依赖
yum install -y wget curl telnet net-tools java-1.8.0-openjdk3.3 关闭防火墙和SELinux
systemctl stop firewalld
systemctl disable firewalld
setenforce 0
sed -i 's/SELINUX=enforcing/SELINUX=permissive/g' /etc/selinux/config3.4 配置主机名和hosts文件
# 分片中间件服务器
echo "sharding-proxy" > /etc/hostname
hostnamectl set-hostname sharding-proxy
# MySQL实例服务器1
echo "mysql-instance1" > /etc/hostname
hostnamectl set-hostname mysql-instance1
# MySQL实例服务器2
echo "mysql-instance2" > /etc/hostname
hostnamectl set-hostname mysql-instance2
# 在所有服务器上都添加hosts条目
cat >> /etc/hosts << EOF
192.168.1.100 sharding-proxy
192.168.1.101 mysql-instance1
192.168.1.102 mysql-instance2
EOF4. 分片中间件安装
4.1 下载ShardingSphere-Proxy
cd /tmp
wget https://archive.apache.org/dist/shardingsphere/5.0.0/apache-shardingsphere-5.0.0-shardingsphere-proxy-bin.tar.gz4.2 解压安装包
tar -zxvf apache-shardingsphere-5.0.0-shardingsphere-proxy-bin.tar.gz -C /opt/
mv /opt/apache-shardingsphere-5.0.0-shardingsphere-proxy-bin /opt/shardingsphere-proxy4.3 配置ShardingSphere-Proxy
cd /opt/shardingsphere-proxy/conf
cp server.yaml.example server.yaml
cp config-sharding.yaml.example config-sharding.yaml4.4 启动ShardingSphere-Proxy
cd /opt/shardingsphere-proxy/bin
./start.sh5. MySQL 实例安装
5.1 下载MySQL 8.0 RPM包
cd /tmp
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm5.2 安装MySQL仓库
rpm -ivh mysql80-community-release-el7-3.noarch.rpm5.3 安装MySQL 8.0
yum install -y mysql-community-server5.4 启动MySQL服务
systemctl start mysqld
systemctl enable mysqld5.5 获取初始密码
grep 'temporary password' /var/log/mysqld.log5.6 安全配置MySQL
mysql_secure_installation
提示：按照提示设置新密码，移除匿名用户，禁用远程root登录，移除test数据库，重新加载权限表。
5.7 创建分片数据库
mysql -u root -p
# 在实例1上创建数据库
CREATE DATABASE db_0;
CREATE DATABASE db_1;
# 在实例2上创建数据库
CREATE DATABASE db_0;
CREATE DATABASE db_1;
6. 分片配置
6.1 编辑分片配置文件
vi /opt/shardingsphere-proxy/conf/config-sharding.yaml6.2 配置分片规则
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://mysql-instance1:3306/db_0?serverTimezone=UTC&useSSL=false
username: root
password: Root123!@#
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://mysql-instance1:3306/db_1?serverTimezone=UTC&useSSL=false
username: root
password: Root123!@#
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_2:
url: jdbc:mysql://mysql-instance2:3306/db_0?serverTimezone=UTC&useSSL=false
username: root
password: Root123!@#
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_3:
url: jdbc:mysql://mysql-instance2:3306/db_1?serverTimezone=UTC&useSSL=false
username: root
password: Root123!@#
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..3}.t_order_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 4}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..3}.t_order_item_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
databaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 4}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
broadcastTables:
- t_config
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
6.3 重启ShardingSphere-Proxy
cd /opt/shardingsphere-proxy/bin
./stop.sh
./start.sh7. 验证测试
7.1 连接到ShardingSphere-Proxy
mysql -h sharding-proxy -P 3307 -u root -p7.2 创建分片表
CREATE TABLE t_order (
order_id BIGINT NOT NULL,
user_id INT NOT NULL,
status VARCHAR(50),
PRIMARY KEY (order_id)
);
CREATE TABLE t_order_item (
order_item_id BIGINT NOT NULL,
order_id BIGINT NOT NULL,
user_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (order_item_id)
);
CREATE TABLE t_config (
id INT NOT NULL,
config_key VARCHAR(50) NOT NULL,
config_value VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);7.3 插入测试数据
INSERT INTO t_order (order_id, user_id, status) VALUES (1, 1, 'SUCCESS');
INSERT INTO t_order (order_id, user_id, status) VALUES (2, 2, 'SUCCESS');
INSERT INTO t_order (order_id, user_id, status) VALUES (3, 3, 'SUCCESS');
INSERT INTO t_order (order_id, user_id, status) VALUES (4, 4, 'SUCCESS');
INSERT INTO t_order_item (order_item_id, order_id, user_id, product_id, quantity) VALUES (1, 1, 1, 1001, 2);
INSERT INTO t_order_item (order_item_id, order_id, user_id, product_id, quantity) VALUES (2, 1, 1, 1002, 1);
INSERT INTO t_order_item (order_item_id, order_id, user_id, product_id, quantity) VALUES (3, 2, 2, 1001, 1);
INSERT INTO t_order_item (order_item_id, order_id, user_id, product_id, quantity) VALUES (4, 3, 3, 1003, 3);
INSERT INTO t_config (id, config_key, config_value) VALUES (1, 'version', '1.0.0');
INSERT INTO t_config (id, config_key, config_value) VALUES (2, 'env', 'production');7.4 查询测试数据
SELECT * FROM t_order;
SELECT * FROM t_order_item;
SELECT * FROM t_config;
# 按分片键查询
SELECT * FROM t_order WHERE user_id = 1;
SELECT * FROM t_order WHERE order_id = 1;
7.5 在各个实例上验证数据分布
# 在实例1上查看
mysql -h mysql-instance1 -u root -p
USE db_0;
SHOW TABLES;
SELECT * FROM t_order_0;
SELECT * FROM t_order_1;
USE db_1;
SHOW TABLES;
SELECT * FROM t_order_0;
SELECT * FROM t_order_1;
# 在实例2上查看
mysql -h mysql-instance2 -u root -p
USE db_0;
SHOW TABLES;
SELECT * FROM t_order_0;
SELECT * FROM t_order_1;
USE db_1;
SHOW TABLES;
SELECT * FROM t_order_0;
SELECT * FROM t_order_1;
8. 性能优化
8.1 操作系统参数优化
vi /etc/sysctl.conf# 网络参数
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_tw_reuse = 1
# 文件系统参数
fs.file-max = 65535
# 内存参数
vm.swappiness = 10
vm.overcommit_memory = 1
sysctl -p8.2 MySQL参数优化
vi /etc/my.cnf[mysqld]
# 基础参数
max_connections = 2000
max_connect_errors = 10000
# 缓存参数
key_buffer_size = 256M
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 32M
# 查询参数
query_cache_size = 64M
query_cache_type = 1
# 其他参数
table_open_cache = 4096
thread_cache_size = 512
systemctl restart mysqld8.3 ShardingSphere-Proxy优化
vi /opt/shardingsphere-proxy/conf/server.yamlrules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PRIVILEGES_PERMITTED
props:
max-connections-size-per-query: 1
kernel-executor-size: 16 # CPU核数
proxy-frontend-flush-threshold: 128 # 128KB
proxy-frontend-executor-size: 16 # CPU核数
proxy-backend-query-fetch-size: 1000
check-table-metadata-enabled: false
sql-show: false
cd /opt/shardingsphere-proxy/bin
./stop.sh
./start.sh9. 故障排查
9.1 常见问题及解决方法
9.1.1 ShardingSphere-Proxy启动失败
# 检查日志
cat /opt/shardingsphere-proxy/logs/stdout.log
# 检查配置文件
vi /opt/shardingsphere-proxy/conf/config-sharding.yaml
# 检查MySQL连接
telnet mysql-instance1 3306
9.1.2 分片规则配置错误
# 检查分片配置
vi /opt/shardingsphere-proxy/conf/config-sharding.yaml
# 重启Proxy
cd /opt/shardingsphere-proxy/bin
./stop.sh
./start.sh
9.1.3 数据分布不均匀
# 检查分片键选择是否合理
# 调整分片规则
vi /opt/shardingsphere-proxy/conf/config-sharding.yaml
9.1.4 查询性能问题
# 检查SQL语句是否使用分片键
# 优化索引
ALTER TABLE t_order ADD INDEX idx_user_id (user_id);
# 调整MySQL参数
vi /etc/my.cnf
9.1.5 连接数过多
# 调整连接池配置
vi /opt/shardingsphere-proxy/conf/config-sharding.yaml
# 调整MySQL最大连接数
vi /etc/my.cnf
max_connections = 2000
警告：本指南仅供参考，实际部署时请根据具体环境进行调整。在生产环境中，建议进行充分的测试和监控。
提示：定期监控分片中间件和MySQL实例的状态，确保系统正常运行。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
