MySQL to TiDB Migration: Streaming 100 Billion Records in Real Time

The Challenge: real-time Streaming + 100 Billion Historical Records

A leading payment service provider came to Xstreami with a primary goal: set up a real-time data streaming pipeline so every transaction in their MySQL databases would instantly flow into a high-performance TiDB analytics layer. Standard streaming — or so it seemed.

Then came the full picture. Their system held over 100 billion historical payment records spread across 40 MySQL tables — years of transactions, merchant profiles, fraud logs, reconciliation data, settlement records, and more. This historical data could not be left behind. It needed to be transformed with the same complex business logic as the live stream and loaded into TiDB before go-live. And there was one non-negotiable requirement: absolute zero data loss.

The operational challenges driving this transformation were severe:

• ● Query Performance Collapse: Joining 15–20 MySQL tables for a single analytics query took 5–10 seconds during peak hours — unacceptable for a real-time payment environment
• ● Massively Complex Transformation Logic: Business rules spanned fee calculations, currency conversions, tiered risk scoring, AML compliance flags, merchant-level settlement adjustments, and dynamic fraud thresholds — all needing to apply consistently to both historical and live data
• ● Zero Tolerance for Data Loss: In a regulated payments environment, even a single missing record means compliance failure, reconciliation errors, and potential financial liability. Data accuracy had to be 100%
• ● No Production Disruption: The migration of 100B+ historical records had to run in parallel with live transaction processing — MySQL could not be taken offline, and live streaming could not be delayed
• ● No Custom Code Acceptable: Engineering resources were already stretched. The team needed a platform that business analysts could configure and update without writing or deploying code

The Xstreami Solution: no code Data Transformation

Enter Xstreami—a cutting-edge real-time data streaming platformthat transforms how organizations handle data consolidation and mysql to tidb migration. For this payment service, Xstreami became the bridge between their legacy MySQL infrastructure and a modern, high-performance TiDB database.

What Makes Xstreami Different?

Unlike traditional ETL tools that require extensive programming and maintenance, Xstreami offers a revolutionary approach to data consolidation:

The Migration Journey: From 40 Tables to 1

The transformation happened in two critical phases: Initial Data Sync (IDS) for historical data migration, followed by continuous real-time streaming for ongoing transactions.

Deep Dive: The Payment Data Transformation

Let's examine exactly how Xstreami transformed the complex 40-table payment structure into a single, queryable table optimized for high-performance data pipelines.

Source: 40 MySQL Payment Tables

The original MySQL schema was highly normalized across multiple categories:

-- Core Transaction Tables
    transactions (txn_id, merchant_id, customer_id, amount, currency, status, created_at)
    transaction_details (detail_id, txn_id, payment_method_id, gateway_id, reference_id)
    transaction_items (item_id, txn_id, product_id, quantity, unit_price)
    -- Party Tables
    merchants (merchant_id, name, category_id, country_id, status, kyc_status)
    customers (customer_id, name, email, phone, risk_score, tier)
    merchant_accounts (account_id, merchant_id, bank_id, account_number)
    -- Payment Method Tables
    payment_methods (method_id, customer_id, type, provider, card_last4, expiry)
    payment_gateways (gateway_id, name, provider, region, fees)
    payment_processors (processor_id, gateway_id, processor_code, status)
    -- Settlement & Reconciliation
    settlements (settlement_id, txn_id, amount, status, settled_at)
    reconciliation (recon_id, settlement_id, status, discrepancy_amount)
    payouts (payout_id, merchant_id, amount, status, payout_date)
    -- Risk & Compliance
    fraud_checks (check_id, txn_id, risk_score, rules_triggered, status)
    compliance_logs (log_id, txn_id, check_type, result, timestamp)
    aml_records (aml_id, customer_id, risk_level, last_check_date)
    -- Reference Data
    countries (country_id, name, code, currency, tax_rate)
    currencies (currency_id, code, symbol, exchange_rate)
    merchant_categories (category_id, name, mcc_code, risk_level)
    products (product_id, name, category, price)
    -- Plus 20+ more tables for fees, disputes, refunds, chargebacks, etc.
  

Transformation: Xstreami's no code Logic

Destination: Single TiDB Payment Analytics Table

The resulting schema in TiDB is a fully denormalized, analytics-optimized table:

CREATE TABLE payment_analytics (
  -- Transaction Core
  txn_id                  VARCHAR(50) PRIMARY KEY,
  txn_timestamp           TIMESTAMP,
  txn_amount              DECIMAL(15,2),
  txn_currency            VARCHAR(3),
  txn_status              VARCHAR(20),
                                                                  
  -- Merchant Information
  merchant_id             VARCHAR(50),
  merchant_name           VARCHAR(200),
  merchant_category       VARCHAR(100),
  merchant_country        VARCHAR(50),
  merchant_risk_level     VARCHAR(20),
                                                                  
  -- Customer Information
  customer_id             VARCHAR(50),
  customer_name           VARCHAR(200),
  customer_email          VARCHAR(200),
  customer_tier           VARCHAR(20),
  customer_risk_score     INT,
                                                                  
  -- Payment Details
  payment_method_type     VARCHAR(50),
  payment_provider        VARCHAR(100),
  payment_gateway         VARCHAR(100),
  card_last4              VARCHAR(4),
                                                                  
  -- Financial Calculations
  gross_amount            DECIMAL(15,2),
  gateway_fee             DECIMAL(10,2),
  platform_fee            DECIMAL(10,2),
  tax_amount              DECIMAL(10,2),
  net_amount              DECIMAL(15,2),
  merchant_share          DECIMAL(15,2),
                                                                  
  -- Settlement Info
  settlement_status       VARCHAR(20),
  settlement_date         DATE,
  payout_status           VARCHAR(20),
                                                                  
  -- Risk & Compliance
  fraud_check_status      VARCHAR(20),
  fraud_risk_score        INT,
  aml_check_status        VARCHAR(20),
  compliance_flags        JSON,
                                                                  
  -- Location Data
  country_code            VARCHAR(3),
  country_name            VARCHAR(100),
  region                  VARCHAR(100),
                                                                  
  -- Metadata
  created_at              TIMESTAMP,
  updated_at              TIMESTAMP,
  data_version            INT,
                                                                  
  -- Indexes
  INDEX idx_merchant (merchant_id),
  INDEX idx_customer (customer_id),
  INDEX idx_timestamp (txn_timestamp),
  INDEX idx_status (txn_status)
  );
  

The Result: Simplified Queries

What once required complex 15-20 table joins now becomes:

-- Get all high-value transactions for a merchant in the last 30 days
    SELECT 
      txn_id,
      txn_timestamp,
      customer_name,
      txn_amount,
      payment_method_type,
      fraud_risk_score,
      settlement_status
      FROM payment_analytics
      WHERE merchant_id = 'MERCH_12345'
      AND txn_timestamp >= NOW() - INTERVAL 30 DAY
      AND txn_amount > 10000
      ORDER BY txn_timestamp DESC;
      -- Query time: 0.2 seconds ⚡
      -- Previously: 8.5 seconds with 15+ table joins
  

Performance & Business Impact

The mysql to tidb migration delivered transformative results across all dimensions of the payment service's data infrastructure.

Real Business Benefits

How Xstreami Achieved Zero Data Loss at 100 Billion Record Scale

Transforming 100 billion historical payment records while simultaneously streaming live transactions is not a problem that scales with brute force. It requires an architecture purpose-built for exactness — where every record is accounted for, every transformation is verifiable, and no interruption can cause data loss. Xstreami's Initial Data Sync (IDS) engine was designed for exactly this.

Why IDS Guarantees Zero Data Loss

Xstreami's IDS engine eliminates data loss through layered, redundant guarantees — not just one safeguard, but many:

Seamless Transition to real-time

Once IDS completed, Xstreami automatically switched to real-time streaming mode. This transition was completely transparent—no manual intervention required, no service disruption, no downtime.

From that moment forward, every payment transaction in MySQL was:

1. Captured via Change Data Capture (CDC) within milliseconds
2. Transformed according to the same no code logic configured in the UI
3. Streamed to TiDB with sub-100ms latency
4. Available immediately for real-time analytics and reporting

The no code Advantage

Perhaps the most revolutionary aspect of this project wasn't the scale or speed—it was achieving all of this without writing a single line of code.

Traditional ETL vs. Xstreami

Single-Click Logic Updates

One of the most powerful features of Xstreami is the ability to modify transformation logic instantly through the UI. Here's a real example from the payment project:

Why no code Matters for Payment Systems

In the fast-paced world of fintech data streaming, agility is everything:

•  Regulatory Changes: When compliance requirements change, payment systems must adapt immediately. Xstreami enables same-day compliance updates.
•  Product Launches: New payment methods, currencies, or merchant types can be added to the data model without code deployments.
•  Business Experimentation: A/B testing different risk models or pricing structures requires rapid data pipeline changes—impossible with traditional ETL.
•  Reduced Risk: No code means no code bugs, no deployment failures, no production incidents from typos in SQL scripts.
•  Team Empowerment: Business analysts who understand payment logic can make changes directly without waiting for engineering resources.

How Does Xstreami Compare to Other Tools?

When evaluating tools for migrating MySQL to a distributed SQL platform like TiDB, the critical differentiators are whether the tool handles historical data migration and real-time streaming simultaneously — and whether it requires custom code to do it.

Unlike tools that handle either historical migration or real-time streaming — but not both— Xstreami delivers a unified, no code pipeline that covers the entire migration lifecycle without a single line of custom code.

Beyond Payment Systems: Universal Connectors

While this case study focuses on MySQL to TiDB migration, Xstreami's platform offers universal connectivity across 50+ data sources and destinations.

Supported Connectors

Multi-Source Scenarios

Xstreami excels at consolidating data from multiple sources into unified destinations:

•  Customer 360: Combine data from CRM (Salesforce), support tickets (Zendesk), orders (MySQL), and product usage (MongoDB) into a single customer view
•  real-time Analytics: Stream from operational databases (PostgreSQL), clickstream data (Kafka), and mobile events (Kinesis) into BigQuery
•  Hybrid Cloud: Synchronize on-premise Oracle databases with cloud-native services like Snowflake and Redshift
•  Event-Driven Architecture: Capture changes from any database and publish to Kafka topics for downstream microservices

Key Takeaways for Payment & Fintech Companies

This real-world payment system transformation demonstrates the transformative potential of modern real-time data streaming platforms. Here are the critical lessons:

Conclusion: The real-time Data Revolution

This payment service's journey from 40 fragmented MySQL tables to a single, real-time TiDB analytics powerhouse represents more than just a technical migration—it's a fundamental shift in how modern financial services should approach data infrastructure.

The results speak for themselves:

•  100 billion records migrated without disrupting live production operations using Xstreami's IDS engine
•  95% query performance improvement by eliminating complex multi-table joins
•  real-time payment insights with sub-100ms latency from MySQL to TiDB
•  Zero lines of code written—all transformation logic configured through the UI
•  Single-click logic updates enabling rapid business adaptation
•  10x faster time-to-market for new payment features and reports

What makes this transformation truly remarkable is not just the scale or speed, but the accessibility. By providing a no code data transformation platform, Xstreami democratized the ability to build and maintain enterprise-grade data pipelines. Business analysts who understand payment logic can now make changes directly, without waiting for engineering resources.

Looking for a Consulting Firm to Migrate from MySQL to a Distributed SQL Platform?

Mafiree's database consulting team specializes in end-to-end MySQL to TiDB migrations from traditional RDBMS systems to modern distributed SQL platforms. We have executed MySQL to TiDB migrations for payment providers, fintech companies, and high-volume e-commerce platforms across India and APAC — combining certified DBA expertise with the Xstreami platform for a fully managed, zero-downtime migration delivery.

Talk to a Migration Specialist → 

What makes this transformation truly remarkable is not just the scale or speed, but the accessibility. By providing a no code data transformation platform, Xstreami democratized the ability to build and maintain enterprise-grade data pipelines. Business analysts who understand payment logic can now make changes directly, without waiting for engineering resources.

What This Means for the Payments Industry

This case study has implications far beyond one payment service:

•  For fintech companies: real-time payment data streaming is no longer a luxury requiring massive engineering investment—it's an accessible capability that can be deployed in weeks
•  For data teams: The no code approach frees engineers from writing and maintaining repetitive ETL scripts, allowing them to focus on higher-value work
•  For business leaders: Faster data pipeline development means faster product launches, quicker regulatory compliance, and more agile responses to market changes
•  For the industry: As payment volumes continue to explode and real-time expectations become universal, platforms like Xstreami set the new standard for what's possible

Is Your Payment System Ready for real-time Streaming? 5 Questions to Ask

Before committing to a data infrastructure overhaul, the right questions matter more than the right tools. Here's how to self-assess whether your payment system has the same structural bottlenecks this case study solved:

1. How complex are your analytics queries?
   
   If your queries involve multiple joins across several tables, your system is likely optimized for transactions, not analytics. Complex joins increase query time and reduce performance. 
   
   In many cases, teams rely on 10–20 table joins, leading to slow reports and heavy database load. A denormalized, analytics-ready structure can simplify queries and dramatically improve performance.
2. How fresh is your data?
   
   If your data has even a 15–30 minute delay, your business decisions are always behind reality. 
   
   real-time systems powered by CDC (Change Data Capture) enable sub-100ms latency, ensuring your dashboards, alerts, and decision engines operate on live data — not outdated snapshots.
3. How quickly can you adapt to changes?
   
   If updating a business rule requires development effort, testing, and deployment cycles, your system lacks flexibility. 
   
   Modern no code or low-code platforms allow teams to implement changes in minutes instead of days — reducing risk and improving responsiveness to business needs.
4. Where is your engineering time going?
   
   If your team spends significant time maintaining ETL pipelines, fixing failures, or handling schema changes, that’s a hidden cost. 
   
   Instead of focusing on product innovation, engineers get stuck managing infrastructure. Reducing code-heavy pipelines can free up valuable engineering bandwidth.
5. Can your system scale with your growth?
   
   If scaling your system requires re-architecture, your current setup is already a limitation. 
   
   A scalable architecture should handle increased data volume and traffic seamlessly — without requiring constant redesign or performance tuning.

If you recognize even a few of these challenges in your current system, it’s a strong signal that your data architecture needs modernization.

Plan Your real-time Migration ? 

The payment service in this case study took the leap—and achieved results that exceeded their most optimistic projections. Your organization can too.