# 1. 准备环境
# 安装依赖
yum install -y epel-release
yum install -y python3 python3-pip openssh-clients

# 安装TiUP
curl –proto ‘=https’ –tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
source ~/.bash_profile

# 2. 创建拓扑文件
cat > topology.yaml << 'EOF' global: user: "tidb" ssh_port: 22 deploy_dir: "/tidb/app" data_dir: "/tidb/fgdata" replication_factor: 3 server_configs: tidb: log.slow-threshold: 300 prepared-plan-cache.enabled: true tikv: readpool.storage.use-unified-pool: true readpool.coprocessor.use-unified-pool: true storage.block-cache.capacity: "16GB" pd: replication.enable-placement-rules: true schedule.leader-schedule-limit: 4 schedule.region-schedule-limit: 2048 schedule.replica-schedule-limit: 64 pd_servers: - host: 192.168.1.100 - host: 192.168.1.101 - host: 192.168.1.102 tidb_servers: - host: 192.168.1.103 - host: 192.168.1.104 - host: 192.168.1.105 - host: 192.168.1.106 tikv_servers: - host: 192.168.1.107 - host: 192.168.1.108 - host: 192.168.1.109 - host: 192.168.1.110 - host: 192.168.1.111 - host: 192.168.1.112 tiflash_servers: - host: 192.168.1.113 - host: 192.168.1.114 EOF # 3. 部署集群 tiup cluster deploy fgedu-tidb-cluster v7.5.0 topology.yaml --user root -p # 4. 启动集群 tiup cluster start fgedu-tidb-cluster # 5. 查看集群状态 tiup cluster display fgedu-tidb-cluster

# 1. 优化TiDB配置
# 修改tidb.toml
cat > /tidb/app/tidb-192.168.1.103-4000/conf/tidb.toml << 'EOF' [performance] max-procs = 16 txns-limit = 10000 prepared-plan-cache.enabled = true prepared-plan-cache.capacity = 1000 [log] slow-threshold = 300 EOF # 2. 优化TiKV配置风哥提示： # 修改tikv.toml cat > /tidb/app/tikv-192.168.1.107-20160/conf/tikv.toml << 'EOF' [readpool] [readpool.storage] use-unified-pool = true unified-thread-pool-size = 8 [readpool.coprocessor] use-unified-pool = true unified-thread-pool-size = 8 [storage] [storage.block-cache] capacity = "16GB" EOF # 3. 优化PD配置 # 修改pd.toml cat > /tidb/app/pd-192.168.1.100-2379/conf/pd.toml << 'EOF' [schedule] leader-schedule-limit = 4 region-schedule-limit = 2048 replica-schedule-limit = 64 tolerant-size-ratio = 0.2 EOF # 4. 重启集群 tiup cluster restart fgedu-tidb-cluster

# 1. 部署Prometheus和Grafana
# 使用TiUP部署监控组件
tiup cluster deploy fgedu-monitor v7.5.0 topology-monitor.yaml –user root -p

# 2. 配置告警规则
# 创建告警规则文件
cat > /tidb/app/prometheus-192.168.1.115-9090/conf/rules/tidb.rules.yml << 'EOF' groups: - name: tidb rules: - alert: TiDBDown expr: up{job="tidb"} == 0 for: 5m labels: severity: critical annotations: summary: "TiDB实例宕机" description: "TiDB实例 {{ $labels.instance }} 已宕机超过5分钟" - alert: TiKVDown expr: up{job="tikv"} == 0 for: 5m labels: severity: critical annotations: summary: "TiKV实例宕机" description: "TiKV实例 {{ $labels.instance }} 已宕机超过5分钟" - alert: PDDown expr: up{job="pd"} == 0 for: 5m labels: severity: critical annotations: summary: "PD实例宕机" description: "PD实例 {{ $labels.instance }} 已宕机超过5分钟" EOF # 3. 配置告警通知 # 修改Alertmanager配置 cat > /tidb/app/alertmanager-192.168.1.115-9093/conf/alertmanager.yml << 'EOF' global: resolve_timeout: 5m route: group_by: ['alertname'] group_wait: 30s group_interval: 5m repeat_interval: 1h receiver: 'email' receivers: - name: 'email' email_configs: - to: 'admin@fgedu.net.cn' from: 'alertmanager@fgedu.net.cn' smarthost: 'smtp.fgedu.net.cn:587' auth_username: 'alertmanager@fgedu.net.cn' auth_password: 'password' require_tls: true EOF # 4. 重启监控组件 tiup cluster restart fgedu-monitor

# 1. 问题描述：需要将MySQL中的数据迁移到TiDB

# 2. 解决方案：
# 1. 使用Dumpling导出MySQL数据
tiup dumpling -h 192.168.2.100 -P 3306 -u root -p password -B fgedudb -o /tidb/backup/mysql_dump

# 2. 使用Lightning导入数据到TiDB
tiup lightning –pd 192.168.1.100:2379 –tidb-user root –tidb-password password –backend local –sorted-kv-dir /tidb/tmp –dumpling-dir /tidb/backup/mysql_dump

# 3. 验证数据
# 连接TiDB
mysql -h 192.168.1.103 -P 4000 -u root -p password

# 查看数据
USE fgedudb;
SELECT COUNT(*) FROM fgedu_users;

# 4. 效果：成功将MySQL数据迁移到TiDB，数据完整性得到保证

# 1. 问题描述：TiDB查询性能不佳，响应时间长

# 2. 分析过程：
# 1. 查看慢查询日志
cat /tidb/logs/tidb/slow-query.log

# 2. 发现慢SQL
# Time: 2024-01-01T10:00:00.000+08:00
# User@Host: root[root] @ 192.168.1.100 [192.168.1.100]
# Query_time: 10.234567 Lock_time: 0.000000 Rows_sent: 1000 Rows_examined: 1000000
# SET timestamp=1672533600;
SELECT * FROM fgedudb.fgedu_orders WHERE order_date >= ‘2024-01-01’;

# 3. 分析执行计划
EXPLAIN SELECT * FROM fgedudb.fgedu_orders WHERE order_date >= ‘2024-01-01’;

# 3. 解决方案：
# 1. 添加索引
ALTER TABLE fgedudb.fgedu_orders ADD INDEX idx_order_date (order_date);

# 2. 优化SQL语句
SELECT id, customer_id, amount FROM fgedudb.fgedu_orders WHERE order_date >= ‘2024-01-01’;

# 3. 调整TiDB参数
SET GLOBAL tidb_executor_concurrency = 8;
SET GLOBAL tidb_max_parallelism = 8;

# 4. 效果：查询响应时间从10秒减少到0.1秒

# 1. 问题描述：需要确保TiDB集群的高可用性

# 2. 解决方案：
# 1. 部署多副本
# 在topology.yaml中配置副本数
global:
replication_factor: 3

# 2. 配置跨机架部署
# 在topology.yaml中添加机架标签
tikv_servers:
– host: 192.168.1.107
labels:
rack: rack1
– host: 192.168.1.108
labels:
rack: rack1
– host: 192.168.1.109
labels:
rack: rack2
– host: 192.168.1.110
labels:
rack: rack2
– host: 192.168.1.111
labels:
rack: rack3
– host: 192.168.1.112
labels:
rack: rack3

# 3. 配置容灾规则
# 创建placement rule
cat > placement-rule.json << 'EOF' { "group_id": "rack", "rules": [ { "group_id": "rack", "id": "rack1", "start_key": "", "end_key": "", "role": "voter", "count": 1, "label_constraints": [ { "key": "rack", "op": "in", "values": ["rack1"] } ] }, { "group_id": "rack", "id": "rack2", "start_key": "", "end_key": "", "role": "voter", "count": 1, "label_constraints": [ { "key": "rack", "op": "in", "values": ["rack2"] } ] }, { "group_id": "rack", "id": "rack3", "start_key": "", "end_key": "", "role": "voter", "count": 1, "label_constraints": [ { "key": "rack", "op": "in", "values": ["rack3"] } ] } ] } EOF # 应用placement rule ./pd-ctl -u http://192.168.1.100:2379 placement-rules load --in=placement-rule.json # 4. 测试故障转移 # 模拟TiKV节点故障 tiup cluster stop fgedu-tidb-cluster -R tikv:192.168.1.107:20160 # 查看集群状态 tiup cluster display fgedu-tidb-cluster # 验证服务可用性 mysql -h 192.168.1.103 -P 4000 -u root -p password -e "SELECT 1;" # 5. 效果：TiDB集群在节点故障时能够自动故障转移，保证服务可用性

更多学习教程公众号风哥教程itpux_com

Part05-风哥经验总结与分享

5.1 项目实施总结

本项目成功实施了一个高可用、高性能、可扩展的TiDB集群，包括：

• 集群部署：使用TiUP部署了3节点PD + 4节点TiDB + 6节点TiKV + 2节点TiFlash的集群
• 配置优化：针对TiDB、TiKV、PD进行了参数优化，提高了系统性能
• 监控告警：部署了Prometheus和Grafana，配置了告警规则和通知
• 数据迁移：使用Dumpling和Lightning将MySQL数据迁移到TiDB
• 性能优化：通过添加索引、优化SQL语句、调整参数等方式提高了查询性能
• 高可用保障：通过多副本、跨机架部署、容灾规则等方式确保了集群的高可用性

5.2 最佳实践建议

TiDB生产实践的最佳实践建议：

• 合理的硬件配置：根据业务需求选择合适的硬件
• 规范的部署流程：使用TiUP部署和管理集群
• 持续的性能优化：定期分析慢查询，优化SQL语句
• 完善的监控体系：建立完善的监控和告警机制
• 定期的健康检查：定期检查集群状态和性能
• 详细的故障演练：定期进行故障演练，提高应对故障的能力
• 及时的版本升级：关注TiDB的版本更新，及时升级到稳定版本

5.3 未来发展规划

TiDB的未来发展规划：

• 云原生部署：将TiDB部署到云环境，利用云平台的弹性能力
• 智能化运维：利用AI技术自动管理和优化集群
• 边缘计算集成：支持边缘计算场景的部署
• 多模型支持：支持更多数据模型，如文档、图形等
• Serverless架构：按需使用的数据库服务
• 与大数据生态集成：与Hadoop、Spark等大数据生态系统深度集成

from tidb视频:www.itpux.com