Using MariaDB MaxScale as a Database Proxy

Configure MaxScale as a proxy layer

MariaDB MaxScale is a standalone proxy layer between the application and MariaDB servers. To ensure reliable operation, the configuration, available features, and known limitations must always match the version that is actually installed. The MaxScale 25.10 series is documented in the official documentation with its own release notes.

The application connects to a MaxScale listener rather than directly to a database server. A service consolidates the access logic, a router determines the backend, and filters can further process requests before they are forwarded. The data remains on the MariaDB backends; MaxScale itself does not become a database.

This separation is particularly helpful when an application should not know which server currently holds which role. For the basics of data models, SQL, and database operations, see the article MariaDB as an open-source database A useful addition. The proxy complements the server architecture, but it does not replace database permissions or a plan for replication and failover.

At Connection Routing When establishing a session, MaxScale selects a target; this backend remains responsible for the session. With statement routing, the router can route individual SQL statements differently based on their content. Which variant is appropriate therefore depends on transactions, session state, and the application’s ability to cleanly separate read and write paths on its own.

A Database Proxy This is not a blanket performance guarantee. It can centralize routing, decouple role changes between applications, or distribute appropriate read requests. However, additional network nodes, SQL analysis, and the specific backend topology influence its behavior. Scalability and capacity must therefore be tested with your own version, configuration, and load.

Monitoring Primary-Replica Topology Correctly

A typical target architecture consists of a write-capable primary and one or more read-only replicas. MaxScale is the application’s sole database endpoint, typically behind a fixed DNS name. Consequently, the application does not connect directly to individual hosts; the selection of an accessible and suitable backend takes place within the proxy layer.

For a primary-replica topology, the monitor determines mariadbmon the status of the backends and identifies their roles as Primary or Replica. Routers can use this information to select their destinations. The monitor must therefore be able to see the actual replication topology; incorrect mappings, unreplicated data, or unclear roles are not routing problems, but operational risks.

Distinguishing a role from its change is essential. With the appropriate configuration and permissions, mariadbmon can manage failovers, scheduled switchovers, and rejoins. A switchover is a deliberately planned role change for maintenance or operations; a failover is a response to a failure. Both processes require a clear topology, monitoring, alerting, and a documented incident or recovery process.

For monitoring-only tasks, the monitor account should be set up according to the principle Least Privilege are granted only the necessary permissions. The current documentation refers to this as REPLICA MONITOR. Additional permissions are required depending on the enabled functions—for example, FILE when checking storage space, or other specific permissions for topology scans and cluster operations. Blanket full permissions are not a substitute for these.

Before implementing automation, the team should determine who approves a role change, how the status of the replicas is verified, and how applications are notified of disruptions. MaxScale can adjust routing targets based on recognized roles. However, whether a switchover is technically permissible is determined by the high-availability strategy and its data, time, and responsibility constraints.

Understanding SQL Routing and Consistency Boundaries

The Router readwritesplit It routes write SQL statements to the primary and distributes appropriate read requests to replicas. This allows a primary-replica cluster to utilize its read capacity more effectively without requiring the application to assign each query to a server itself. The monitor's role information serves as the basis for this selection.

By default, this division does not guarantee that a read operation will see the new state immediately after every write operation. causal_reads is set to by default none. For example, if an application writes an order status and then reads it via an independent query, a replica may still return the previous value. A deliberately chosen primary read path or a shared transaction context are possible strategies, but they are not the only ones.

For Read-Your-Writes With Causal Reads enabled, readwritesplit can take preceding writes into account despite replica lag. The mode local ensures visibility for that same connection. global maintains the GTID status stored by the service across its connections; a global "happens-before" order of the reads is guaranteed only if all transactions fall within a single GTID domain. universal takes into account the cluster's transaction state as observed on the primary. However, the broader scopes increase the synchronization overhead and, consequently, potentially increase latency.

Causal reads use GTID information and wait for replication to complete in non-fast modes. If synchronization takes longer than causal_reads_timeout – 10 seconds by default – readwritesplit attempts to execute the query on the primary. In a read-only transaction, a timeout may instead be returned as an error because the query cannot be moved to the primary.

The Fast Modes fast, fast_global and fast_universal Do not wait for a replica and use the primary when necessary; the timeout does not apply there. They are therefore particularly well-suited for predominantly read-heavy workloads with few writes. Starting with MaxScale 24.02, MaxScale automatically sets up the necessary tracking for new backend connections; older versions require last_gtid in session_track_system_variables on all backends.

Within an open read-write transaction, `readwritesplit` routes read operations to the primary. This prevents statements from the same transaction from being executed on backends with different replication states. This conservative rule does not exempt you from setting transaction boundaries correctly from a technical standpoint and treating replication latency as a separate architectural constraint.

Even with stored procedures, UDF calls, and multi-statements, the decision is deliberately more cautious: In documented cases, such queries are routed to the primary. The reason is that their impact or context should not be treated like a simple read query. SQL routing is therefore not merely a search for the word SELECT, but follows rules for secure session and semantic handling.

A technically determined threshold value for Replica-Lag can exclude replicas from certain read operations, but does not replace any consistency requirements. The acceptable level of lag depends, for example, on whether an outdated catalog entry is tolerable or whether a payment status that has just been confirmed must be visible without fail. This decision belongs in the application logic and the service agreement, not in a general proxy default setting.

Configure Read-Write Split Safely

A read/write split configuration consists of separate object definitions for backends, the monitor, the service, and the listener. The following excerpt shows the structure of a file; it is not a template ready for immediate production use. The values in angle brackets are explicitly not passwords, but references to accounts and secrets that must be provided via the production environment’s secret management system. The monitor and service each require their own credentials for their respective tasks.

[db-primary]
type=server
address=db-primary.example.invalid
port=3306

[db-replica-1]
type=server
address=db-replica-1.example.invalid
port=3306

[db-replica-2]
type=server
address=db-replica-2.example.invalid
port=3306

[cluster-monitor]
type=monitor
module=mariadbmon
servers=db-primary,db-replica-1,db-replica-2
user=<monitor-account-from-secret-management>
password=<monitor-secret-reference>

[RW-Service]
type=service
router=readwritesplit
servers=db-primary,db-replica-1,db-replica-2
user=<service-account-from-secret-management>
password=<service-secret-reference>

[RW-Listener]
type=listener
service=RW-Service
protocol=MariaDBClient
port=3306

The server objects simply refer to accessible MariaDB backends; the monitor determines which instance is the primary or replica based on the topology. The monitor account is used for status and role queries. The separate service account is used to retrieve the user and permission information required for client authentication and authorization from the backends. Both accounts require only the permissions documented for their respective tasks. Recognizing Roles Loading user information and loading data are separate tasks.

The placeholders in the example must be resolved during deployment via a secure deployment channel; they are not intended to be used as literal configuration values. Plaintext passwords, actual certificate paths, or unverified permission packages should not be stored in freely readable files or repositories. For MariaDB protocol connections, MaxScale internally manages relevant user accounts and permissions from the system tables. If accounts or permissions are changed, outdated information can interfere with login or authorization.

Before deployment, the listener port, network paths, user mapping, and the actual router assignment must therefore be checked separately. Furthermore, automatic interventions by the monitor are not implicitly included in this minimal setup: failover, switchover, or rejoin functions require additional configuration and a tested operational concept. A seemingly brief configuration does not replace secret management or the verification of the backend topology.

Compare Router Models by Application

The choice of router is based on the application's behavior, not solely on the number of available replicas. Direct connections keep the setup simple but leave destination selection and role awareness to the application. Two separate endpoints with readconnroute are appropriate when the application deliberately manages read and write connections itself. Statement Routing Using `readwritesplit` is useful when a session needs to be routed to the primary or to replicas based on SQL.

readconnroute evaluates a target server when establishing a connection and does not analyze every subsequent SQL statement. If the selected backend fails, the session ends; the router does not automatically connect you to another server. While this is often reasonable for clearly separated, short-lived read connections, it may be unsuitable for stateful application workflows.

Special caution is warranted for a service intended to serve as a read endpoint with router_options=slave. This option gives preference to servers with the replica role; however, if all replicas are down and the primary is available, `readconnroute` can fall back to the primary. Furthermore, this option does not enforce write protection. The application must send read-only SQL statements, or an additional `ReadOnly` filter must prevent write access. Routing destination and role preference are therefore neither a permission boundary nor a substitute for restrictive database permissions.

Read-write split, on the other hand, reduces the number of target decisions in the application code but requires a precise examination of the semantics. Appropriate reads can be distributed across replicas, while write operations go to the primary. For workflows with strict timeliness requirements, it remains crucial whether a query can wait for replicated data after a write or must intentionally use the primary.

Planning Sessions, Schedule, and Transition

A database session consists of more than just a single query. SET, USE, CHANGE USER and prepared statement operations change their context. `readwritesplit` replicates relevant session commands to potential target servers so that statements that are later routed can be executed with the appropriate session state. This distinguishes the router from simple TCP load balancing, but it does not eliminate every edge case.

This limit is particularly important for prepared statements involving temporary tables. If such a statement is prepared on the primary, replicas may not necessarily be able to execute it. The application should therefore test temporary objects, prepared statements, and transaction flows using the intended routing model. Session state is not a secondary condition, but rather determines whether an SQL distribution is appropriate from both a business and technical perspective.

For a read-heavy application, the team can define a technically specified latency threshold as an exclusion criterion for replicas. However, max_replication_lag However, this does not guarantee that a previous write on a replica is already visible. For critical subsequent reads, depending on the workflow, a deliberate primary path, a shared write context, or enabled causal reads may be considered.

With causal reads, the scope must be chosen deliberately. local preserves causality within a connection. global uses a service-wide GTID, but guarantees the global "happens-before" order of reads only if all transactions are within a single GTID domain. universal Takes into account the cluster's transaction state as observed on the primary. Non-fast modes synchronize using GTIDs; if a replica does not catch up within the configured timeout, the query is always repeated on the primary, provided the transaction mode allows it.

This feature is version-dependent. Starting with MaxScale 24.02, MaxScale adds last_gtid for new backend connections; in older versions, you had to session_track_system_variables must be set accordingly on the MariaDB backends. The fast modes eliminate waiting times, but if replicas have not yet caught up, they may redirect reads to the primary and are therefore not generally suitable for write-intensive workloads.

A planned switchover and an automatic failover must also be treated separately. During a switchover, a role change is prepared in a controlled manner; during a failover, operations respond to a failure condition. mariadbmon can monitor roles and backend states and, if configured accordingly, can support these processes. A replicated data state, a clear topology, alerting, and an incident response process remain prerequisites.

In MaxScale 25.10, you can discover-replicas Identify servers that replicate directly to the current primary and add them to Monitor and MaxScale. This feature is not compatible with configuration synchronization. Before using this feature, the team must therefore determine whether topology changes will be detected automatically or controlled via a synchronized configuration; doing both at the same time is not a valid option in this context.

Securing TLS, Permissions, and Filters

With MaxScale, TLS must be configured on two separate paths: from the application to the listener and from the proxy to the MariaDB backends. Both connections require their own settings for encryption and certificate validation; a secure client path does not automatically encrypt downstream traffic. The configuration must therefore reflect the security requirements and the chain of trust for both segments.

TLS Enabled ssl=true encryption in the respective listener or server configuration. If a client expects a server certificate, the listener requires the certificate and private key. An account with REQUIRE SSL Cannot log in via a listener without TLS. Certificate files and keys belong in a secure operating environment, not in publicly accessible sample or repository configurations.

Also the Concept of Rights The following remains crucial on the backends: MaxScale mediates connections but does not replace specific database accounts. Service accounts are granted only the permissions required for their tasks. For login and authorization, MaxScale relies on user accounts and privileges from the MariaDB system tables; therefore, changes to these accounts must be made available promptly in the internal user management system.

The firewall filter can check SQL before forwarding it based on learned or defined rules. It supports idle, learning, monitoring, and enforcement modes; in enforcement mode, depending on the settings, unauthorized statement patterns are either returned with an error or the connection is terminated. The learning phase must cover representative, permitted application workflows to ensure that the filter does not block regular queries.

The filter is an additional layer of control for expected SQL patterns; it is not a substitute for access permissions. Restrictive database permissions, secure credentials, and verified application code remain necessary because the filter only evaluates incoming requests based on its rules. Claims regarding other filters should be verified separately based on their respective documentation.

Systematically Check the Configuration and for Errors

In the event of connection or routing errors, following a set sequence of checks—rather than making changes on a hunch—is helpful. First, check the monitor status: Are all backends reachable, and are the primary and replica roles recognized as expected? Only then can you meaningfully evaluate routing decisions. A misidentified or unreachable node is not merely a listener problem.

Next come the listener and TLS: the port, protocol, certificate, key, and the requirements of the accounts being used must all match. Next, verify that the user account management system is aware of the current backend accounts and permissions. Especially after changes to users or permissions, outdated account data can prevent logins, even though the account is set up correctly in MariaDB itself.

Next, you assign the service, router, and the designated servers. For read/write splitting, you also need to specify the replica lag, causal_reads and the application's transaction limits to the checklist. Without causal reads enabled, a read performed outside the required write context on a replica may return an older state; a lag threshold alone does not guarantee the visibility of a previous write.

Also, place a single Configuration Source fixed. MaxScale can persist changes at runtime; upon restart, the persisted configuration can override static files. If a team edits some files and others maxctrl If this runtime configuration is used, the change history, review, and backup of this runtime configuration must be documented in a binding manner.

You should treat maintenance windows for schema changes as a separate operational area: dependencies, locking behavior, and replication must be checked in advance and should not be left to the proxy. For planning controlled changes to large MariaDB tables, see the article on Schema changes without downtime additional context. MaxScale can then control connections and routing based on the backend roles, but does not execute any database DDL strategy.

Warning: You should first test changes to roles, certificates, user permissions, and routing in a similar environment. For production interventions, this includes a rollback procedure, monitoring of application errors, and clearly defined responsibilities. This makes it possible to determine whether a malfunction stems from the proxy configuration, the backend, or client behavior.

Use MaxScale Appropriately or Leave It Out

For a single MariaDB server with no requirements for routing, failover, or SQL filtering, direct connections are usually the simpler option. However, the application must then handle any subsequent target changes on its own. MaxScale is worthwhile if the additional endpoint fulfills a specific task, such as handling role changes, filtering access requests, or distributing reads to replicas in a controlled manner.

If the application can deliberately choose its own read and write paths, separate endpoints with readconnroute often understandable. This router selects a backend when establishing a connection, but it does not analyze every SQL statement and does not transfer an interrupted session to another server. In the case of router_options=slave Replicas are preferred; if all replicas are down and the primary is still available, the router can use the primary. This option also does not make the endpoint read-only. To achieve this, a suitable read-only filter, restrictive backend permissions, or strictly controlled application SQL are required.

For primary-replica workloads whose ORM and transaction model are compatible, readwritesplit The application determines the SQL destination for each statement. By default, causal_reads=none However, replication lag remains visible. After critical writes, the application can use a primary read path or an appropriate shared transaction context; alternatively, causal reads can be specifically configured for the required visibility scope.

Causal Reads distinguish between visibility within the same session, a service-managed global GTID count, and the cluster's state as observed on the primary. local applies to the same connection. In the case of global The global "happens-before" order of reads is guaranteed only if all transactions belong to a single GTID domain; therefore, this mode should not be interpreted as an unrestricted, service-wide consistency guarantee. universal Checks the observable state on the primary before reads. Synchronization modes can introduce additional latency; fast variants do not wait, but can shift the load more heavily to the primary during writes.

Borderline cases should be subjected to targeted testing with the actual driver and data model before implementation. Temporary tables and statements that rely on them may not be available on replicas if the preparation takes place on the primary. Stored procedures, UDF calls, multi-statements, and session state also influence whether the router conservatively routes requests to the primary.

Transaction Replay does not guarantee lossless transactions. If a connection is interrupted during COMMIT ...it can be left open whether the commit was already successful. Risky replay configurations may execute write operations twice; server-dependent results such as NOW() or @@server_id can also trigger checksum discrepancies. Therefore, evaluate its use based on consistency requirements, error handling, and the repeatability of writes—not on blanket performance promises.