The useful unit of MySQL write scale is not another writable copy of the same database. It is another database that owns different writes.

Situation

RetailCo has outgrown the architecture from the earlier MySQL replication article. A three-member InnoDB Cluster gives its order platform quorum, primary election, member recovery, and a stable MySQL Router endpoint. The primary, however, still executes every checkout transaction for every tenant.

The workload has one property that changes the design: almost every transaction belongs to one tenant. A checkout reads and writes one tenant’s customer, order, order items, inventory reservation, payment state, shipment, and idempotency records. Cross-tenant transactions are exceptional.

That gives RetailCo a natural ownership boundary. Instead of asking three members to agree on every tenant’s writes, it can place disjoint tenant sets on independent MySQL clusters:

flowchart TD
  Apps[RetailCo applications] --> Resolve[resolve authenticated tenant]
  Resolve --> Catalog[tenant placement catalog and cache]
  Catalog --> RouterA[Router A]
  Catalog --> RouterB[Router B]
  Catalog --> RouterC[Router C]
  RouterA --> ShardA[InnoDB Cluster A — tenants 101 to 399]
  RouterB --> ShardB[InnoDB Cluster B — tenants 400 to 799]
  RouterC --> ShardC[InnoDB Cluster C — tenants 800 to 1199]

Each InnoDB Cluster remains a single-primary high-availability unit. MySQL Shell’s AdminAPI manages the cluster, Group Replication maintains membership, and MySQL Router directs read-write connections to the current primary. MySQL documents that stack in InnoDB Cluster and Router bootstrap.

The write scale comes from the layer above MySQL: Cluster A never applies Cluster B’s tenant transactions. Adding a shard adds another write execution, buffer-pool, storage, binary-log, and failure domain.

RetailCo is a reference architecture, not a claimed customer deployment. The placement catalog, route epoch, and move workflow below are proposed designs. This article presents no measured throughput, latency, failover-time, migration-duration, or data-loss result.

The Problem

“Shard by tenant_id” is a data-distribution suggestion, not an operating model. A production design must answer questions that one MySQL instance previously answered implicitly:

  • Who is authoritative for tenant 487 right now?
  • What prevents an old application process from writing to the previous shard after a move?
  • How is a 2-TB tenant moved without making two writable copies authoritative?
  • Where do global uniqueness and foreign keys live?
  • How are 40 clusters upgraded, backed up, restored, and observed?
  • What happens when the tenant catalog is unavailable?
  • How is a business transaction completed when it crosses shards?

The wrong shortcut is to confuse any of these with sharding:

MechanismWhat it providesWhy it is not horizontal write ownership
More asynchronous replicasRead copies and promotion candidatesOne source still owns writes
InnoDB Cluster single-primaryQuorum and automated primary replacementOne primary executes the shard’s writes
Group Replication multi-primarySeveral write-entry points into one replicated datasetSuccessful transactions still propagate through the group
InnoDB ClusterSetDisaster-recovery clustersOnly the primary cluster is read-write
Native table partitioningLocal data pruning and lifecycle controlPartitions still consume one MySQL server’s compute
Application shardingSeparate databases own separate tenant setsThe application must now operate placement and movement

InnoDB ClusterSet deserves special emphasis. MySQL documents one read-write primary cluster and read-only replica clusters, with asynchronous inter-cluster replication and no semisynchronous support. It is a disaster-recovery topology, not a tenant-sharding layer: ClusterSet limitations.

The core question is therefore not whether MySQL can run more clusters. It is whether RetailCo can make tenant ownership explicit and keep it correct through routing, failure, migration, recovery, and fleet change.

Build Independent Write Domains

Separate the placement control plane from the query data plane

The tenant-placement catalog maps a stable tenant identity to a logical shard. It should be small, highly available, auditable, and boring. It should not sit in the critical path of every SQL statement.

Applications authenticate the tenant before choosing a database, look up the placement through a bounded cache, and open a connection to that shard’s logical Router endpoint. MySQL Router then solves a narrower problem: it discovers which member is the current primary inside that InnoDB Cluster. Router does not know that tenant 487 belongs on Shard B.

flowchart TD
  Request[request with authenticated tenant] --> Cache{placement cached}
  Cache -->|yes| Placement[tenant, shard, epoch]
  Cache -->|no| Catalog[read placement catalog]
  Catalog --> Placement
  Placement --> Endpoint[resolve logical shard endpoint]
  Endpoint --> Router[shard-local MySQL Router]
  Router --> Primary[current shard primary]
  Primary --> Fence{epoch accepted}
  Fence -->|yes| Transaction[execute tenant-local transaction]
  Fence -->|no| Refresh[reject and refresh placement]

If the catalog is unavailable, warm application processes can continue serving known stable placements for a deliberately bounded period. New tenant creation, tenant moves, and cold-cache routing should pause. This converts a catalog incident from an immediate global data-plane outage into a controlled degradation, while the cache limit prevents stale placement from living forever.

A minimal catalog might look like this:

CREATE TABLE tenant_placement (
    tenant_id       BINARY(16)  NOT NULL,
    shard_id        VARCHAR(64) NOT NULL,
    placement_epoch BIGINT      NOT NULL,
    state           ENUM(
                        'STABLE',
                        'COPYING',
                        'CATCHING_UP',
                        'FENCED',
                        'CUTOVER',
                        'OBSERVING'
                    ) NOT NULL,
    updated_at      TIMESTAMP(6) NOT NULL,
    PRIMARY KEY (tenant_id)
);

CREATE TABLE shard_endpoint (
    shard_id               VARCHAR(64)  NOT NULL,
    logical_write_endpoint VARCHAR(255) NOT NULL,
    region                 VARCHAR(32)  NOT NULL,
    capacity_class         VARCHAR(32)  NOT NULL,
    status                 ENUM('ACTIVE', 'DRAINING', 'OFFLINE') NOT NULL,
    PRIMARY KEY (shard_id)
);

The catalog stores logical endpoints, not database passwords. Credentials belong in a secret-management system. Every placement mutation also needs an append-only audit event recording old shard, new shard, old epoch, new epoch, actor, reason, and timestamp. That history becomes essential during restore.

Put the tenant key in every transactional identity

The schema must make accidental cross-tenant access difficult. Tenant-owned tables should carry tenant_id, and their primary and foreign keys should retain it:

CREATE TABLE orders (
    tenant_id BINARY(16)   NOT NULL,
    order_id  BINARY(16)   NOT NULL,
    customer_id BINARY(16) NOT NULL,
    status    VARCHAR(32)  NOT NULL,
    total     DECIMAL(18,2) NOT NULL,
    created_at TIMESTAMP(6) NOT NULL,
    PRIMARY KEY (tenant_id, order_id),
    KEY orders_customer (tenant_id, customer_id)
);

CREATE TABLE order_items (
    tenant_id BINARY(16) NOT NULL,
    order_id  BINARY(16) NOT NULL,
    line_id   BINARY(16) NOT NULL,
    product_id BINARY(16) NOT NULL,
    quantity  INT NOT NULL,
    PRIMARY KEY (tenant_id, order_id, line_id),
    CONSTRAINT order_items_order_fk
      FOREIGN KEY (tenant_id, order_id)
      REFERENCES orders (tenant_id, order_id)
);

Application-generated identifiers avoid coordinating one global auto-increment sequence. A composite tenant key also makes a missing tenant predicate more visible in query review, but it is not authorization. The service must bind the authenticated tenant, prevent callers from substituting it, and test every repository method for tenant isolation.

Global data needs an explicit category:

  • small, slowly changing reference data can be copied to every shard;
  • globally unique usernames or idempotency keys need a separate registry or a tenant-scoped definition;
  • product search and cross-tenant analytics should use a derived search, warehouse, or lake system;
  • shared mutable rows such as one global inventory counter defeat tenant independence and may need a different domain boundary.

Persist placement; do not recompute ownership forever

hash(tenant_id) % shard_count is operationally hostile because changing the shard count remaps most tenants. Consistent or rendezvous hashing reduces movement and can produce an initial candidate shard, but consistent hashing is not the whole architecture.

RetailCo needs deliberate exceptions:

  • a hot tenant may require a dedicated cluster;
  • regulated data may be pinned to a region;
  • a tenant may need a higher storage class;
  • a draining shard must stop accepting new placements;
  • a large move may be scheduled instead of triggered automatically.

The catalog is therefore authoritative. Hashing proposes; the placement record decides.

Fence stale routes with a placement epoch

A cache invalidation message is not a correctness boundary. A process can miss it, pause, and resume with an old route. RetailCo should increment placement_epoch whenever ownership moves and maintain a small tenant_home record on the authoritative shard.

At the start of a write transaction, the data-access layer compares its expected tenant and epoch with the local authoritative record. A request carrying epoch 41 cannot write after the target becomes authoritative at epoch 42. This check should be part of the transaction contract, not a best-effort preflight call.

The epoch does not solve every split-brain path. Administrative accounts, background jobs, repair scripts, and change-data-capture consumers must all use the same ownership rule or be explicitly fenced. The useful invariant is:

At any placement epoch, exactly one shard is permitted to accept new business writes for a tenant.

Move tenants as a controlled state machine

Copying rows is the easy part. Cutover is where ownership can split. A safe move separates bulk transfer, change catch-up, validation, fencing, route mutation, and cleanup:

flowchart TD
  Stable[STABLE — source owns tenant] --> Copy[COPYING — chunked snapshot copy]
  Copy --> Catchup[CATCHING UP — apply captured changes]
  Catchup --> Validate[validate counts, checksums, and invariants]
  Validate --> Fence[FENCED — pause new tenant writes]
  Fence --> Tail[apply final change tail]
  Tail --> Install[install target tenant home with next epoch]
  Install --> Cutover[CUTOVER — update catalog atomically]
  Cutover --> Observe[observe target and reject stale source routes]
  Observe --> NewStable[STABLE — target owns tenant]
  NewStable --> Retire[retire source copy after recovery window]

The source remains authoritative during copy and catch-up. Binary-log change capture can carry the tail, but the implementation must preserve per-tenant ordering, idempotency, delete events, schema compatibility, and restart position. Before cutover, RetailCo validates more than row counts: financial totals, order-to-item relationships, inventory invariants, and a sample of application reads.

The write fence should be short, observable, and tested. Drain tenant-specific jobs, reject or queue new mutations, apply the final tail, install epoch 42 at the target, atomically change the catalog from source epoch 41 to target epoch 42, and refresh caches. The source must then reject epoch 41 traffic. Keep its data read-only through an observation and recovery window before deletion.

Do not dual-write the tenant to both shards as a shortcut. Partial success produces two histories, and “last write wins” is not a reconciliation rule for payments or inventory. If the move fails before cutover, resume from the source. If it fails after the catalog commits, recover toward the target unless an explicit, tested reverse-cutover procedure proves otherwise.

No zero-downtime or migration-duration claim is made here. This workflow is a proposed lab until its capture, fence, restart, rollback, and failure behavior are measured.

Keep routine transactions inside one shard

A local checkout can still use ordinary InnoDB ACID transactions because all participating tenant rows live together. Cross-shard foreign keys do not exist, and cross-shard joins become scatter-gather work or derived-data queries.

MySQL supports external XA transactions with prepare, commit, rollback, and recovery states. That capability does not make distributed checkout the default design. XA adds a coordinator, prepared-state recovery, more failure combinations, and a wider latency path: XA transactions.

Prefer boundaries that avoid routine cross-shard ACID:

  • keep a tenant’s checkout data colocated;
  • write an outbox event in the same local transaction as the business change;
  • use idempotent consumers and a saga for multi-domain workflows;
  • expose explicit pending, compensated, and failed business states;
  • send cross-tenant reporting to derived systems.

If the most important transaction constantly updates tenants on different shards, tenant_id is probably the wrong ownership key—or the business domain needs decomposition before sharding.

Operate schema and recovery as fleet workflows

One schema migration is now many independently failing migrations. Use expand-contract changes, canary one low-risk shard, record a migration ledger per shard, throttle work, pause on replication or storage pressure, and make every step resumable. Application code must tolerate mixed schema versions during rollout. A single global DDL window is not a distributed transaction.

Backup also changes shape. Each shard needs backups and point-in-time recovery appropriate to its RPO and RTO. The placement catalog needs its own backups. Placement audit events must survive long enough to answer: “Which shard owned tenant 487 at 10:37:22?”

Restoring every shard to the same wall-clock timestamp is necessary for a fleet-wide incident but may not be sufficient. A tenant moved between shards can exist in both backups with different authority epochs. Recovery automation must replay placement history, identify the authoritative copy at the recovery point, and prevent both copies from becoming writable.

Group Replication member recovery is not backup. It repairs or catches up a group member from the live cluster; it does not recover an operator-dropped tenant or provide an independent historical restore.

In Practice

The documented pattern is isolation plus explicit placement

Shopify has publicly described moving from one large failure domain to pods—isolated groups containing a subset of shops—and later described balancing MySQL shards at terabyte scale. Its published architecture is not proof that RetailCo should copy the implementation, but it demonstrates the operating consequence of tenant sharding: placement, isolation, and movement become first-class platform responsibilities. The shorter multi-tenant database field note connects those public decisions to this repository’s broader isolation model.

For RetailCo, the recommended architecture is:

  1. Use one single-primary InnoDB Cluster—or one cloud-managed HA MySQL database—as each shard’s availability unit.
  2. Route by authenticated tenant_id through an explicit placement catalog.
  3. Keep the full tenant transaction graph on one shard.
  4. Fence moves with a monotonically increasing epoch and one authoritative writer.
  5. Treat schema rollout, backup, restore, hot-tenant isolation, and capacity balancing as fleet capabilities.

The deployment substrate changes who operates HA, not who owns sharding:

DeploymentHA unit inside one application shardRetailCo still owns
On-premises or virtual machinesInnoDB Cluster and redundant MySQL RoutersPlacement, movement, fleet operations, backup, DR
AWSOne RDS for MySQL Multi-AZ DB cluster per shardPlacement, movement, fleet operations, recovery contract
Google CloudOne Cloud SQL for MySQL regional HA instance per shardPlacement, movement, fleet operations, recovery contract
AzureOne MySQL Flexible Server HA deployment per shardPlacement, movement, fleet operations, recovery contract

AWS currently documents one writer and two readable standbys for an RDS Multi-AZ DB cluster. Google documents regional high availability for Cloud SQL for MySQL, and Azure documents a primary plus managed standby for MySQL Flexible Server HA. Those services can remove member-level work. They do not supply RetailCo’s tenant map or move protocol.

When the control plane becomes a product, evaluate a product

Custom application sharding is a strong fit when the tenant boundary is stable, cross-tenant transactions are rare, and the organization wants conventional MySQL inside every shard. It is a poor fit when every team would have to rebuild routing, resharding, topology, and transaction coordination independently.

Two alternatives deserve a documented evaluation, not a casual name-check:

  • Vitess externalizes MySQL sharding into VTGate routing, VSchema and Vindex metadata, topology services, and VReplication workflows such as MoveTables and Reshard. Vitess 23 documents single-shard, multi-shard best-effort, and two-phase-commit transaction modes, while recommending that cross-shard updates be minimized. Start with VSchema, VReplication, and distributed transactions.
  • TiDB is not an InnoDB sharding layer. It uses stateless TiDB SQL servers, TiKV storage, and PD scheduling to provide a distributed database behind a MySQL-compatible protocol. PingCAP documents meaningful differences from MySQL, including unsupported features and transaction-semantics qualifications: TiDB 8.5 architecture FAQ and TiDB 8.5 MySQL compatibility.

The conclusion is deliberately narrow. RetailCo should choose tenant-sharded standard MySQL when preserving MySQL behavior and exploiting a clean tenant boundary matter most. It should evaluate Vitess when the sharding control plane itself has become the repeated problem. It should evaluate TiDB only as a database-platform migration, with SQL, isolation, operations, ecosystem, and failure semantics tested—not as a transparent switch that merely adds shards.

Neither alternative is lab-verified in this article.

Benchmark the routing and operations, not only steady-state TPS

Status: PROPOSED LAB — NO EMPIRICAL RESULTS.

Compare one, two, four, and eight shard clusters with the same aggregate hardware per phase and a tenant-local order workload. Run both uniform demand and a skewed distribution with one hot tenant. Capture:

  • attempted and successful TPS;
  • P50, P95, and P99 transaction and commit latency;
  • CPU, storage latency, IOPS, network, and binary-log volume by shard;
  • Group Replication certification, apply queues, and flow control;
  • connection counts and Router behavior;
  • placement-cache hit, miss, and stale-route rejection rates;
  • cross-shard transaction attempts;
  • tenant-copy throughput and source impact;
  • write-fence duration and ambiguous client outcomes;
  • schema-rollout duration and pause events.

Then run the tests that expose the actual architecture:

flowchart TD
  Lab[sharded MySQL validation] --> Scale[scale test — one, two, four, eight shards]
  Lab --> Skew[hot-tenant and uneven-placement test]
  Lab --> Move[tenant move — copy, catch-up, fence, cutover]
  Lab --> Fail[shard primary and full-shard failure]
  Lab --> Control[catalog outage — warm and cold cache]
  Lab --> Change[schema rollout — canary, pause, resume]
  Lab --> Restore[point-in-time restore across a tenant move]

Do not promise linear scale. The result depends on tenant locality, workload skew, Router and connection behavior, per-shard storage, Group Replication overhead, global-service dependencies, and how much work remains cross-shard. Publish results only with exact versions, topology, dataset, workload, failure injection, and raw evidence.

Where It Breaks

Failure or constraintConsequenceRequired response
One tenant exceeds one shardThe tenant remains bounded by one clusterSub-shard that tenant by another stable key or decompose its hottest domain
Placement catalog unavailableCold routes and control-plane changes stopServe bounded warm-cache routes; pause creation and moves
Stale application routeWrite can reach the old shardEnforce tenant and placement epoch inside every write path
Cross-shard transaction becomes commonXA, sagas, or compensation dominate application logicRevisit the shard key and domain boundary
Uneven tenant growthSome shards saturate while others idleMeasure tenant demand and run deliberate rebalancing
Schema rollout fails halfwayFleet contains mixed schema versionsExpand-contract, ledger, canary, pause, resume, and compatible code
Tenant move spans backup timestampsRestore can produce two physical copiesPreserve placement history and recover one authoritative epoch
Entire shard failsOnly its tenants are unavailable, but impact is realTest shard-level DR, routing health, RPO, and RTO
Global registry failsMany shards can remain healthy while checkout blocksMinimize synchronous global dependencies and design degradation
Operations scale with shard countPatching, alerts, backup, and capacity work multiplyAutomate the fleet before multiplying clusters

When I would not use this architecture

I would not recommend custom tenant sharding when no stable ownership key exists, most transactions span tenants, one tenant alone exceeds the largest practical cluster, or the organization cannot build and test routing, movement, schema, backup, and recovery automation.

I would also avoid it when the current writer still has inexpensive local improvements available. Bad indexes, excessive secondary indexes, row contention, chatty transactions, poor connection management, and unbounded history remain bad after sharding—only multiplied.

RetailCo verdict

Good fit:               Tenant-local OLTP with uneven but movable tenant demand
Poor fit:               Frequent cross-tenant ACID or one indivisible hot tenant
Recommended option:     Explicit tenant catalog plus HA MySQL cluster per shard
Main advantage:         Independent write, compute, storage, and failure domains
Main limitation:        The application now owns placement and fleet correctness
Operational complexity: High until movement, schema, backup, and DR are automated
Migration complexity:   High; move one tenant cohort at a time with fencing

The decisive test is not whether several clusters can accept writes. It is whether RetailCo can prove which cluster owns each tenant during normal routing, failure, move, and restore. If that invariant is solid, additional clusters create real write domains. If it is vague, sharding creates additional ways to corrupt ownership.

What to Do Next

  • Problem: Confirm that one writer is still the bottleneck after query, schema, connection, and storage work—not merely the most visible metric.
  • Solution: Select a stable tenant boundary, build the placement catalog and epoch fence, and keep HA local to each shard.
  • Proof: Run uniform, skew, move, catalog-outage, shard-failure, schema-rollout, and point-in-time-recovery tests with archived evidence.
  • Action: Pilot two shards with a low-risk tenant cohort, automate movement and recovery before adding more, and keep the cross-shard rate on the architecture scorecard.

The MySQL track continues with failure operations: how ProxySQL and Orchestrator coordinate routing and promotion in a large MySQL replica topology—and where fencing still has to be designed explicitly.

References