MaxScale’s read-write split functionality is often misunderstood as a simple request router, but its true power lies in its ability to dynamically reconfigure MariaDB cluster topology without downtime.
Here’s MaxScale’s read-write split in action. Imagine a primary MariaDB instance for writes and two read replicas for reads.
[maxscale]
; Basic configuration
logdir = /var/log/maxscale
; ... other global settings
[ReadWriteSplit]
type = ReadWriteSplit
# The service that connects to the primary for writes
router_options = "primary=Primary, dc-filter=true"
# The service that connects to the replicas for reads
# dc-filter=true means only send read traffic to replicas in the same datacenter
# read_only_service=Secondary
; read_only_service=Secondary2
[Primary]
type = service
router = mysqlrouter
servers = server1
; Use a persistent connection for the primary to avoid overhead
; The default is 60 seconds, increasing it can help
; connection_max_age = 1800
; MaxScale will retry connections to the primary if it fails
; retry_connect = true
; retry_interval = 5
[Secondary]
type = service
router = mysqlrouter
servers = server2, server3
; For read replicas, we can be more aggressive with connection pooling
; connection_max_age = 300
; Server definitions
[server1]
type = server
address = 192.168.1.10
port = 3306
[server2]
type = server
address = 192.168.1.11
port = 3306
[server3]
type = server
address = 192.168.1.12
port = 3306
; Listener for client connections
[RW_Listener]
type = listener
service = ReadWriteSplit
protocol = mysql
port = 4000
When a client connects to RW_Listener on port 4000, MaxScale inspects the incoming SQL. If it’s a write operation (INSERT, UPDATE, DELETE, etc.), it directs the query to the Primary service, which in turn connects to server1. If it’s a read operation (SELECT), MaxScale distributes the query across the Secondary service, connecting to server2 and server3 based on its internal load balancing algorithm. The dc-filter=true option ensures that read traffic is preferentially sent to read replicas within the same datacenter, minimizing network latency.
The core problem this solves is the performance bottleneck of sending all traffic, read and write, to a single database instance. By splitting traffic, read-heavy workloads can be offloaded to replicas, freeing up the primary to handle writes more efficiently. MaxScale’s ReadWriteSplit router is highly configurable, allowing you to define specific rules for query routing beyond just read/write. You can route queries based on schema, user, or even the SQL statement itself using regular expressions.
What most people miss is how MaxScale handles schema changes and replication lag. When a primary fails and a replica is promoted, MaxScale doesn’t just update its internal configuration; it can actively re-establish the Primary service to point to the new primary and reconfigure the other servers as read replicas. This dynamic re-configuration, often facilitated by external monitoring tools or MaxScale’s own health checks, allows for near-zero downtime failovers. Furthermore, while MaxScale doesn’t inherently fix replication lag, its ability to monitor replica health and potentially pause read traffic to lagging replicas (via custom scripts or advanced configurations) helps prevent stale data from being served to applications.
The next step is understanding how to integrate MaxScale with high-availability solutions like Galera Cluster for synchronous replication, and how MaxScale’s monitoring capabilities can automate failover detection.