The Complete Production SOP: How to Deploy and Run TiDB Distributed HTAP MySQL-Compatible Scaling in Autonomous AI Agents

---
name: "tidb-distributed-htap-mysql-scale"
description: "Scale relational MySQL workloads horizontally with distributed ACID transactions using TiDB."
metadata:
  author: "PingCAP"
  version: "1.0.0"
  runtime_compatibility:
    - "codex"
    - "claude-code"
    - "cursor"
    - "windsurf"
triggers:
  - "scale mysql with tidb"
  - "configure tiflash replica"
  - "execute htap query tidb"
permissions:
  fs_read: true
  fs_write: "workspace"
  shell_exec: false
  net_access: false
---

# TiDB Distributed HTAP MySQL-Compatible Scaling

Operates TiDB distributed databases: deploying TiDB, TiKV (row-based transactional engine), and TiFlash (columnar analytical engine), partitioning large tables, and executing real-time HTAP queries.

## When to Invoke
Invoke this skill whenever you need to:
- scale mysql with tidb
- configure tiflash replica
- execute htap query tidb

## Execution SOP & Rules
1. Connect using standard MySQL 8 client drivers specifying TiDB cluster port 4000.
2. Enable columnar replica: ALTER TABLE orders SET TIFLASH REPLICA 1; and monitor replica status.
3. Run EXPLAIN ANALYZE to verify query optimizer chose TiFlash columnar scan for aggregation queries.
4. Manage auto-sharding regions and placement rules via TiDB Dashboard and PD control CLI.

## Verification Checklist
- Run local linting, syntax validation, and type-checks before reporting completion.
- Ensure all created or modified files adhere to workspace boundaries and avoid uncommitted secrets.
- Verify tests or build commands exit cleanly with return code 0.

-- Validate TiDB Version and Node Roles
SELECT tidb_version();
SELECT * FROM information_schema.cluster_info WHERE type IN ('tidb', 'tikv', 'tiflash', 'pd');

-- Configure columnar replication for orders and order_items
ALTER TABLE orders SET TIFLASH REPLICA 1;
ALTER TABLE order_items SET TIFLASH REPLICA 1;

-- Verification query executed by agent SOP
SELECT TABLE_SCHEMA, TABLE_NAME, REPLICA_COUNT, PROGRESS, AVAILABLE 
FROM INFORMATION_SCHEMA.TIFLASH_REPLICA 
WHERE TABLE_NAME IN ('orders', 'order_items');

-- Check optimizer selection
EXPLAIN ANALYZE
SELECT 
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(i.quantity * i.unit_price) AS total_revenue,
    COUNT(DISTINCT o.user_id) AS active_purchasers
FROM orders o
JOIN order_items i ON o.id = i.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('month', o.order_date);

-- If optimizer fails to route to columnar, enforce via SQL hint:
-- SELECT /*+ read_from_storage(tiflash[o, i]) */ ...