Posted inMySQL

Deploying MaxScale as a Database Proxy: Automatic Read/Write Splitting for MySQL

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Quick Start: Spin up MaxScale in 5 Minutes with Docker

To see the results immediately without a complex installation, we’ll use Docker Compose. This approach allows you to quickly set up a Lab cluster consisting of 1 Master, 1 Slave, and 1 MaxScale to test the Read/Write Splitting feature.

Create a docker-compose.yml file with the following content:

version: '3.8'
services:
  mysql-master:
    image: mariadb:10.6
    environment:
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: testdb
    ports:
      - "3306:3306"

  mysql-slave:
    image: mariadb:10.6
    environment:
      MYSQL_ROOT_PASSWORD: root_password
    depends_on:
      - mysql-master

  maxscale:
    image: mariadb/maxscale:latest
    volumes:
      - ./maxscale.cnf:/etc/maxscale.cnf
    ports:
      - "6033:6033" # Port for application connections
      - "8989:8989" # GUI Dashboard
    depends_on:
      - mysql-master
      - mysql-slave

Activate the system with the command: docker-compose up -d. Now, you have an intelligent Proxy sitting in front of your Database. Any queries sent to port 6033 will be automatically routed by MaxScale to the appropriate node.

Why Choose MaxScale over ProxySQL?

Managing a Database Cluster often involves a difficult challenge: How can you ensure the application doesn’t need to track which IP is the Master (for writing) or the Slave (for reading)?

MaxScale is the answer from the MariaDB team itself. While it serves the same purpose as ProxySQL, MaxScale stands out with its transparent .cnf file configuration, eliminating the need for complex SQLite interface operations.

In an actual E-commerce project I managed with 50GB of data, splitting Read/Write traffic reduced the Master load by up to 70%. Instead of struggling with heavy reporting SELECT queries, the Master could focus solely on processing transactions. All query overhead was shifted to the Slaves.

Detailed MaxScale Configuration for MySQL Systems

First, create a user on the MySQL Master. This user allows MaxScale to monitor the health status of the nodes.

-- Run this command on the MySQL Master
CREATE USER 'maxuser'@'%' IDENTIFIED BY 'password123';
GRANT SELECT ON mysql.user TO 'maxuser'@'%';
GRANT SELECT ON mysql.db TO 'maxuser'@'%';
GRANT SELECT ON mysql.tables_priv TO 'maxuser'@'%';
GRANT SHOW DATABASES ON *.* TO 'maxuser'@'%';
GRANT REPLICATION CLIENT ON *.* TO 'maxuser'@'%';
FLUSH PRIVILEGES;

Next, configure the maxscale.cnf file to activate the routing engine:

[maxscale]
threads=auto

# 1. Define Database nodes
[server1]
type=server
address=mysql-master
port=3306
protocol=MariaDBBackend

[server2]
type=server
address=mysql-slave
port=3306
protocol=MariaDBBackend

# 2. Monitor: Check node status every 2 seconds
[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2
user=maxuser
password=password123
monitor_interval=2s

# 3. Service: Automatic Read/Write Splitting
[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2
user=maxuser
password=password123

# 4. Listener: Reception port from the App
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=6033

Internal Working Mechanism:

  • Monitor: Continuously checks the nodes. If the Master encounters an issue, MaxScale will detect it and block write traffic to protect data integrity.
  • Router (readwritesplit): Performs real-time SQL parsing. INSERT/UPDATE/DELETE commands are sent to the Master, while SELECT queries prioritize Slaves.
  • Listener: The single endpoint. The App only needs one Connection String pointing to port 6033.

Effectively Handling Replication Lag (Slave Lag)

The most frustrating issue is when you’ve just performed an INSERT on the Master, but a subsequent SELECT on the Slave returns nothing due to replication lag.

MaxScale handles this elegantly using a parameter in the [Read-Write-Service] section:

[Read-Write-Service]
type=service
router=readwritesplit
max_slave_replication_lag=5
...

With max_slave_replication_lag=5, if the Slave lags by more than 5 seconds, MaxScale will automatically route read queries back to the Master. This ensures users always see the latest data even when the system is experiencing replication overhead.

Real-world Operational Experience

After several Proxy deployments, I’ve gathered three important takeaways:

  1. Dashboard Security: The Web interface (port 8989) uses admin/mariadb by default. Change the password immediately or use a Firewall to block Internet access.
  2. Forcing Queries to the Master: For sensitive tasks like checking a wallet balance, use an SQL Hint: /* maxscale route to master */ SELECT ... to retrieve 100% accurate data.
  3. Leverage Debug Logs: If you see queries going the wrong way, enable log_debug=1. MaxScale will provide detailed explanations in the log file regarding its node selection.

Implementing MaxScale helps the system handle higher loads and frees developers from manual connection management. If you encounter any difficulties during configuration, feel free to leave a comment below!

Share:
Tags: