Choosing the Right MySQL Middleware: Features, Use Cases, and Business Requirements

1 Background

As frontline architects of internet services, database storage is essential, with MySQL being the most common component. Massive traffic scenarios demand capabilities beyond basic MySQL, such as read/write separation, sharding, permission control, monitoring, audit logging, and concurrency control, which often require middleware support.

2 Business Requirements Driven

From real‑world pain points, the following requirements and typical solutions are identified:

Connection pool management : Centralized Proxy provides pooling, limits, retries, and timeout protection.

Read/write separation : Proxy routes writes to the primary and reads to replicas.

Load balancing : Distribute read traffic across multiple replicas.

Sharding (database/table splitting) : Proxy hides sharding details, presenting a single‑table interface.

Failover : Automatic failover hides backend failures.

Permission control : User/IP based minimal permissions with automatic scaling for machine migrations.

Security : Proxy adds SQL injection protection, blacklists, and data encryption.

Log audit : Convenient audit logs with flow control and dynamic changes.

Monitoring & alerts : Metrics for connections, slow queries, latency, SQL statistics, and DB load.

Transactions : Support for single‑node and distributed transactions.

Cache : Configurable caching strategies based on query, user, or schema.

3 Common MySQL Middleware Overview

3.1 ProxySQL

Introduction: ProxySQL is a high‑performance C++‑based database proxy offering connection pooling, read/write splitting, load balancing, failover, SQL routing, multi‑protocol support, extensibility, security, monitoring, logging, and easy configuration.

Key Features

Connection pool management.

Read/write separation.

Load balancing.

Automatic failover.

SQL routing.

Support for multiple protocols (MySQL, PostgreSQL, Oracle, etc.).

Horizontal scalability.

SSL/TLS encryption.

Comprehensive monitoring and logging.

Simple configuration and management.

3.2 MaxScale

Introduction: MaxScale, developed by MariaDB, is an intelligent MySQL proxy that enhances high availability, scalability, and security while decoupling the proxy layer from the underlying database infrastructure.

Key Features (v10.3.1+)

Read/write splitting.

Automatic primary‑to‑replica switch.

Plugin support for authentication, routing, monitoring, and logging.

High availability via load‑balancing plugins.

SQL firewall and filtering.

Horizontal extensibility.

3.3 DBProxy

Introduction: DBProxy, created by Meituan’s DBA team, sits between applications and MySQL, abstracting read/write separation, sharding, and other complexities, allowing developers to focus on business logic.

Key Features

Read/write separation.

Load balancing.

Sharding support.

IP filtering.

SQL blacklist.

Smooth DBA‑driven DB node removal.

Replica traffic configuration.

Dynamic configuration loading.

3.4 MyCat

Introduction: Originating from Alibaba’s Cobar project, MyCat is a widely adopted open‑source middleware offering distributed architecture, SQL parsing/optimization, data sharding, read/write separation, failover, and rich monitoring.

Key Features

Distributed horizontal scaling.

SQL parsing and optimization.

Data sharding.

Read/write separation.

Automatic failover.

Comprehensive monitoring and management.

3.5 TDDL

Introduction: Developed by Alibaba, TDDL is a Java‑based distributed database system that provides transparent sharding, linear horizontal scalability, PB‑level storage, high‑performance transaction handling, and smooth online expansion.

Key Features

Linear horizontal scalability (up to 1.5M+ TPS).

PB‑scale storage for massive data.

Cost‑effective high‑performance solution.

Online data expansion without application changes.

Sharding based on split keys.

Transparent read/write separation using read‑only instances.

Mature operation and control system.

4 Summary

The article outlines the storage service demands of internet‑scale applications and reviews the capabilities of leading MySQL middleware solutions, setting the stage for deeper analysis of how each product addresses specific pain points.