Scaling databases for high-performance applications often requires a combination of strategies like sharding and replication. By implementing MySQL master-master replication with sharding and ProxySQL, you can achieve horizontal scaling, high availability, and efficient query distribution.

1. Overview of Master-Master Sharding

Master-master sharding divides your database into multiple shards, each containing a subset of data. Each shard has its own master-master replication setup for redundancy. ProxySQL acts as a central proxy, routing queries to the appropriate shard based on sharding keys.

2. Architecture

The architecture consists of:

• Multiple Shards: Databases split by a sharding key (e.g., user ID ranges).
• Master-Master Replication: Each shard has two masters to handle read and write redundancy.
• ProxySQL: Routes queries to the appropriate shard and manages load balancing.

3. Setting Up Master-Master Sharding

3.1 Prepare the Shards

Divide your database schema and data across shards. For example:

• Shard 1: User IDs 1–1000
• Shard 2: User IDs 1001–2000

3.2 Configure Master-Master Replication

Set up replication for each shard:

• Master A: Configured to replicate to Master B.
• Master B: Configured to replicate to Master A.

Use the server-id and auto_increment_increment settings to avoid conflicts.

[mysqld]

server-id=1 log-bin=mysql-bin auto_increment_offset=1 auto_increment_increment=2

3.3 Load Data to Shards

Distribute your data to the appropriate shards using tools or custom scripts.

4. Configuring ProxySQL

ProxySQL is crucial for routing queries to the correct shard and managing replication. Follow these steps:

4.1 Add Shards to ProxySQL

Add the MySQL instances for each shard to ProxySQL:

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'shard1_master1', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'shard1_master2', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'shard2_master1', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'shard2_master2', 3306);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
    

4.2 Configure Query Rules

Create rules to route queries to the correct shard based on the sharding key:

INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (1, '^SELECT .* WHERE user_id <= 1000', 1);
INSERT INTO mysql_query_rules (rule_id, match_pattern, destination_hostgroup)
VALUES (2, '^SELECT .* WHERE user_id > 1000', 2);

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

4.3 Handle Write Conflicts

Use tools or application logic to handle potential conflicts in a master-master setup.

5. Monitoring and Maintenance

Monitor the setup for performance and replication lag:

• Use ProxySQL’s statistics tables for query performance metrics.
• Regularly check replication status using SHOW SLAVE STATUS\G;.
• Automate shard maintenance using backup and restore tools.

6. Best Practices

• Choose an appropriate sharding key to evenly distribute data.
• Implement application-level logic to route queries when possible.
• Use monitoring tools like ProxySQL stats and MySQL logs for insights.
• Regularly test backups and ensure shard consistency.

7. Conclusion

MySQL master-master sharding with ProxySQL is a powerful strategy for scaling databases in high-traffic environments. It ensures data distribution, redundancy, and efficient query handling, making it a suitable choice for complex applications requiring high availability and performance.