Skip to main content

How to Set Up ProxySQL Sharding

ProxySQL supports three primary sharding approaches: user-based, schema-based, and data-based. Choose the approach that best fits your application architecture.

Option 1: User-Based Sharding​

The simplest approach — queries are routed based on which user connects. No query rules are needed.

INSERT INTO mysql_users (username, password, active, default_hostgroup, comment)
VALUES
  ('accounts',     'shard0_pass', 1, 0, 'Routed to the accounts shard'),
  ('transactions', 'shard1_pass', 1, 1, 'Routed to the transactions shard'),
  ('logging',      'shard2_pass', 1, 2, 'Routed to the logging shard');

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Application mapping:

App ModuleShard UserHostgroup
CRMaccounts0
OLTPtransactions1
Log Managerlogging2

Option 2: Schema-Based Sharding​

Map schema names to destination hostgroups via query rules. Assumes schemas follow a naming convention such as shard_0, shard_1, etc.

INSERT INTO mysql_query_rules (rule_id, active, schemaname, destination_hostgroup, apply)
VALUES
  (1, 1, 'shard_0', 0, 1),
  (2, 1, 'shard_1', 1, 1),
  (3, 1, 'shard_2', 2, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Option 3: Data-Based Sharding​

The most flexible approach — routes specific rows (identified by a shard key) to different hostgroups. Requires careful rule design but allows sharding at the table or row level.

Example Schema​

loc_account_data table:
+----------------------------------------+---------+---------+
| loc_id                                 | user    | acc_id  |
+----------------------------------------+---------+---------+
| 20086020554955909836090724037181646035 | joe32   | 1       |
| 21503957780049285539986052866765125704 | sam57   | 2       |
| 75863560943999160082133817802533222835 | pam18   | 3       |
+----------------------------------------+---------+---------+

loc_mapping table:
+----------------------------------------+---------+
| loc_id                                 | region  |
+----------------------------------------+---------+
| 20086020554955909836090724037181646035 | AMERICA |
| 21503957780049285539986052866765125704 | EMEA    |
| 75863560943999160082133817802533222835 | OCEANIA |
+----------------------------------------+---------+

INSERT Rules​

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (1, 1, 'loc_account_data.*20086020554955909836090724037181646035', 0, 1),
  (2, 1, 'loc_account_data.*21503957780049285539986052866765125704', 1, 1),
  (3, 1, 'loc_account_data.*75863560943999160082133817802533222835', 2, 1);

UPDATE / DELETE / SELECT Rules​

Match the shard key directly for all other statement types:

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (4, 1, 'loc_id = 20086020554955909836090724037181646035', 0, 1),
  (5, 1, 'loc_id = 21503957780049285539986052866765125704', 1, 1),
  (6, 1, 'loc_id = 75863560943999160082133817802533222835', 2, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Considerations​

  • User and schema sharding are simpler to implement and maintain — prefer these where possible
  • Data-based sharding requires a rule for every shard key value and every statement type (INSERT, UPDATE, DELETE, SELECT)
  • Cross-shard joins must be handled at the application level — ProxySQL routes to a single hostgroup per query
  • Data-based sharding can be combined with read/write split by adding additional rules per shard
  • Sharding is not limited to these three methods — they are the most common patterns