Core Business Object Sharding Explained with the mysqlda MySQL Middleware

1. Overview of Distributed Data Sharding

In distributed architectures the hardest problem is data distribution. Most database middleware adopt a simple "split‑by‑database‑and‑table" approach, which is generic but brings three major issues:

Scaling requires moving data slice by slice; if growth exceeds the initial estimate, insufficient slices cause hard migrations.

Data for a single business entity is scattered across multiple databases, making aggregation and joins difficult.

Cross‑database operations introduce distributed transactions; although two‑phase commit exists, it is not always reliable, especially for financial systems demanding strong consistency.

Core‑business‑object sharding selects a primary business entity (e.g., a customer in an internet system) as the partition key. The middleware maps this key—such as a phone number or email—to a specific MySQL instance (the "belonging library"). The mapping is persisted, so all subsequent operations for that entity are routed to the same database.

When capacity needs to expand, a new MySQL instance is added to the cluster, its weight is increased, and new entities are more likely to be assigned to it. Once the new instance reaches a target load, weights are re‑balanced, ensuring even growth without moving existing data.

Benefits of core‑business‑object sharding include:

No need to pre‑estimate slices; scaling adds new instances without moving data.

All data for a business entity resides in one database, enabling arbitrary aggregation and joins.

Each instance holds a full set of business tables, eliminating cross‑database transactions and delegating strong consistency to the single‑node database.

However, this approach has drawbacks:

The core entity must be chosen carefully at design time; changing it later is difficult.

Some systems have multiple core entities, making this model unsuitable (e.g., integrated online‑offline banking).

Both traditional split‑by‑table and core‑entity sharding are mainstream design patterns; the appropriate choice depends on the specific scenario.

2. mysqlda Middleware

2.1 Architecture

mysqlda is a proxy‑style distributed MySQL middleware that fully complies with the MySQL communication protocol, bridging application servers and MySQL clusters. Its internal process model follows a parent‑child hierarchy.

2.2 Working Principle

Data is partitioned by core business objects across multiple belonging libraries; each library contains a full set of business tables. A library consists of an ordered list of MySQL servers (primary plus replicas). If the primary fails, mysqlda automatically fails over to the next server.

On startup, mysqlda loads connection credentials from etc/mysqlda.conf and restores persisted mappings of core objects and their associated objects from files such as etc/mysqlda.save and etc/mysqlda.<correl_object_class>.save.

Application servers connect using standard MySQL client APIs. mysqlda authenticates the client, then intercepts extended SQL statements that specify a core object or an associated object, for example: select library (core_object) or select library_by_correl_object (object_class) (object). It looks up (or creates via weighted consistent hashing) the belonging library, selects the first healthy MySQL server from that library’s list, obtains a connection from the pool, and bridges the client’s subsequent DSL/DML commands to that server.

During normal operation, applications can issue additional extended SQL to re‑select a different library based on another core or associated object, allowing dynamic routing. If a MySQL server becomes unavailable, mysqlda iterates through the replica list (e.g., MASTER → SLAVE1 → SLAVE2 …) to maintain availability.

Applications can also bind an associated object to a core object using:

set correl_object <object_class> <object> <core_object>

which persists the relationship for future lookups.

2.3 Simple Deployment Example

Assume three MySQL libraries, each with a primary‑replica pair. User A registers with phone number 13812345678. The application sends select library 13812345678 to mysqlda; the middleware hashes the phone number, assigns it to library N, persists the mapping, and bridges the connection.

During account creation, the application issues set correl_object account_no 331234567890 13812345678, binding the account number to the phone number. Subsequent requests can locate the user’s library either by phone ( select library 13812345678) or by account number ( select library_by_correl_object account_no 331234567890), ensuring all business data resides in the same MySQL instance.

2.4 Internal Data Entities and Relationships

Key entities:

forward_instance : a MySQL belonging library.

forward_servers list : ordered list of MySQL servers for that library.

unused_forward_session list and forward_session list : pools of idle and active connections.

forward_correl_object_class and forward_correl_object : classes and instances of associated objects bound to a core object.

forward_library : the mapping between a core object (or associated object) and a forward_instance.

accepted_session : communication session between an application server and mysqlda; forward_session : session between mysqlda and a MySQL server. When a library is selected or switched, these sessions are bridged.

Source: calvinwilliams www.oschina.net/p/mysqlda