Setting Up MariaDB MaxScale for Load Balancing and Failover

MariaDB MaxScale is a database proxy that provides transparent load balancing, automatic failover, and query routing for MariaDB and MySQL deployments. It sits between your application and database servers, intelligently routing read queries to replicas and write queries to the primary. This guide covers installation, configuration, and production deployment.

MaxScale Architecture

MaxScale operates as a middleware proxy with a modular architecture:

• Listeners — accept client connections on specified ports
• Routers — route queries based on configurable rules
• Filters — transform or analyze queries in transit
• Monitors — track backend server health and replication status

Prerequisites

• MariaDB or MySQL replication already configured (one primary, one or more replicas)
• A separate server for MaxScale (2+ GB RAM recommended)
• Network access from MaxScale to all database servers on port 3306

Installation

# Ubuntu/Debian
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-maxscale-version=24.02
sudo apt install maxscale

# Rocky Linux / RHEL
sudo dnf install https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup
sudo dnf install maxscale

Create MaxScale User on Database Servers

MaxScale needs a monitoring user and a service user on all database servers:

-- Run on the PRIMARY server (replicates to replicas)
CREATE USER 'maxscale_monitor'@'maxscale-ip' IDENTIFIED BY 'MonitorPass123!';
GRANT REPLICATION CLIENT, SUPER, RELOAD ON *.* TO 'maxscale_monitor'@'maxscale-ip';

CREATE USER 'maxscale_service'@'maxscale-ip' IDENTIFIED BY 'ServicePass123!';
GRANT SELECT ON mysql.* TO 'maxscale_service'@'maxscale-ip';
GRANT SHOW DATABASES ON *.* TO 'maxscale_service'@'maxscale-ip';

FLUSH PRIVILEGES;

Configure MaxScale

# /etc/maxscale.cnf

[maxscale]
threads = auto
admin_host = 0.0.0.0
admin_port = 8989
admin_secure_gui = false

# Define backend servers
[primary]
type = server
address = 10.0.0.1
port = 3306
protocol = MariaDBBackend

[replica1]
type = server
address = 10.0.0.2
port = 3306
protocol = MariaDBBackend

[replica2]
type = server
address = 10.0.0.3
port = 3306
protocol = MariaDBBackend

# Monitor for replication topology
[MariaDB-Monitor]
type = monitor
module = mariadbmon
servers = primary, replica1, replica2
user = maxscale_monitor
password = MonitorPass123!
monitor_interval = 2000ms
auto_failover = true
auto_rejoin = true
enforce_read_only_slaves = true
failcount = 3
replication_user = repl_user
replication_password = ReplPass123!

# Read-Write Split Router
[ReadWriteSplit]
type = service
router = readwritesplit
servers = primary, replica1, replica2
user = maxscale_service
password = ServicePass123!
master_accept_reads = true
max_slave_replication_lag = 3s
transaction_replay = true
transaction_replay_max_size = 10Mi

# Listener for the service
[RWSplit-Listener]
type = listener
service = ReadWriteSplit
protocol = MariaDBClient
port = 4006
address = 0.0.0.0

Encrypt Passwords

# Generate encryption key
maxkeys /var/lib/maxscale/

# Encrypt passwords
maxpasswd /var/lib/maxscale/ MonitorPass123!
# Output: 7A3B2C1D4E5F...

# Replace plaintext passwords in maxscale.cnf with encrypted versions
# password = 7A3B2C1D4E5F...

Start and Verify

sudo systemctl start maxscale
sudo systemctl enable maxscale

# Check status
maxctrl list servers
maxctrl list services
maxctrl list monitors
maxctrl show monitor MariaDB-Monitor

Connect Applications Through MaxScale

# Connect via MaxScale instead of directly to MySQL
mysql -h maxscale-ip -P 4006 -u app_user -p

# Application connection string
mysql:host=maxscale-ip;port=4006;dbname=myapp

MaxScale transparently routes SELECT queries to replicas and INSERT/UPDATE/DELETE to the primary. Transactions always go to the primary.

Query Routing Rules

# MaxScale automatically routes:
# - SELECT queries → replicas (round-robin)
# - SELECT ... FOR UPDATE → primary
# - INSERT, UPDATE, DELETE → primary
# - BEGIN/START TRANSACTION → primary
# - All queries within a transaction → primary
# - Queries using temporary tables → primary
# - User-defined variables → primary

Automatic Failover

With auto_failover = true, MaxScale automatically promotes a replica to primary if the current primary fails:

1. Monitor detects primary is unresponsive (after failcount checks)
2. MaxScale selects the most up-to-date replica
3. Promotes it to primary and reconfigures other replicas
4. Application connections are transparently rerouted

# Manually trigger failover (for testing)
maxctrl call command mariadbmon failover MariaDB-Monitor

# Manually switch primary (graceful)
maxctrl call command mariadbmon switchover MariaDB-Monitor replica1 primary

# Rejoin a recovered server
maxctrl call command mariadbmon rejoin MariaDB-Monitor

Monitoring MaxScale

# REST API (port 8989)
curl -u admin:mariadb http://maxscale-ip:8989/v1/servers
curl -u admin:mariadb http://maxscale-ip:8989/v1/services/ReadWriteSplit

# MaxCtrl commands
maxctrl list sessions         # Active connections
maxctrl list filters          # Active filters
maxctrl show service ReadWriteSplit  # Service details with stats

Adding Query Filters

# Query logging filter
[QueryLog]
type = filter
module = qlafilter
filebase = /var/log/maxscale/queries
log_type = unified
flush = true

# Add filter to service
[ReadWriteSplit]
filters = QueryLog

Production Best Practices

• Deploy two MaxScale instances with keepalived for proxy high availability
• Enable transaction_replay for seamless failover during active transactions
• Set max_slave_replication_lag to avoid serving stale reads from lagging replicas
• Use encrypted passwords in the configuration file
• Monitor MaxScale itself with Prometheus using the REST API metrics endpoint
• Restrict MaxScale admin interface access to management networks only
• Test failover procedures regularly in staging before relying on them in production