Get started with MariaDB MaxScale, the advanced database proxy. These guides provide a quick path to installing, configuring, and leveraging key features.
Quickstart guide for MariaDB MaxScale
MariaDB MaxScale is an advanced, open-source database proxy, router, and load balancer designed to enhance the scalability, high availability, and security of your MariaDB deployments. It acts as an intelligent intermediary between your applications and your MariaDB servers, abstracting the underlying database topology.
MaxScale is not a database itself, but a sophisticated gateway that understands the MariaDB protocol. It intercepts client connections and routes them to the appropriate backend MariaDB servers based on configured rules, health checks, and workload types. This allows for flexible and dynamic management of database traffic without requiring changes to the application code.
MariaDB MaxScale provides several key functionalities that contribute to optimizing MariaDB environments:
Intelligent Routing and Load Balancing:
ReadWriteSplit Router: This is a primary feature that automatically distinguishes between read (SELECT) and write (INSERT, UPDATE, DELETE, DDL) statements. It intelligently routes all write statements to the designated primary server and distributes read statements across multiple replica servers, significantly improving read scalability and reducing the load on the primary.
Other Routers: MaxScale offers various routers for different use cases, such as routing to specific databases, connection-based routing, or custom routing logic.
High Availability and Automated Failover:
MaxScale constantly monitors the health of your MariaDB servers. In a replication setup (e.g., primary-replica or Galera Cluster), if the primary server fails, MaxScale can automatically detect the failure, promote a healthy replica to become the new primary, and seamlessly redirect client connections to the newly promoted server. This minimizes downtime and ensures continuous operation.
Seamless Server Maintenance:
MaxScale provides built-in mechanisms to place backend servers into maintenance mode without interrupting connected applications or clients. This allows administrators to perform tasks like patching, upgrades, or reconfigurations on individual servers while MaxScale intelligently redirects traffic away from them. Management can be done via the maxctrl
command-line interface, MaxGUI (web interface), or REST API.
Security and Traffic Control:
MaxScale can implement granular security policies and traffic controls for database connections and queries. This includes features like firewalling, query filtering, and authentication proxying.
QLAfilter (Query Log Anonymizer Filter): This filter can be used to create an audit trail by logging all queries, with options to anonymize sensitive data, aiding in security audits and performance analysis.
Protocol Compatibility: MaxScale is designed to be compatible with standard MariaDB and MySQL client protocols, making it transparent to most applications.
By abstracting the database topology and intelligently managing connections, MariaDB MaxScale helps organizations achieve higher availability, better performance, and enhanced security for their MariaDB deployments.
MariaDB MaxScale installation quickstart guide
MariaDB MaxScale is an advanced, open-source database proxy that provides intelligent routing, load balancing, high availability, and security features for your MariaDB and MySQL deployments. It acts as an intermediary, forwarding database statements to one or more backend database servers based on configured rules and server roles, all transparently to your applications.
To understand MaxScale, familiarize yourself with these core components:
Servers: These are your backend MariaDB or MySQL instances that MaxScale will manage traffic to.
Monitors: Plugins that observe the health and state of your backend servers (e.g., primary, replica, down).
Routers: Plugins that determine how client queries are directed to backend servers (e.g., readwritesplit
router for directing writes to a primary and reads to replicas).
Services: Define a combination of a router and a set of servers, along with any filters.
Listeners: Define how clients connect to MaxScale (port, protocol) and which service they connect to.
Filters: Optional components that can inspect, modify, or log queries as they pass through MaxScale (e.g., qlafilter
for auditing).
MariaDB MaxScale is typically installed from the official MariaDB repositories.
a. Add MariaDB Repository:
Use the MariaDB Repository Configuration Tool (search "MariaDB Repository Generator") to get specific instructions for your OS and MaxScale version.
Example for Debian/Ubuntu (MaxScale 23.08):
sudo apt update
sudo apt install dirmngr software-properties-common apt-transport-https ca-certificates curl -y
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
sudo apt update
b. Install MaxScale:
sudo apt install maxscale -y # For Debian/Ubuntu
# sudo yum install maxscale -y # For RHEL/CentOS
maxscale.cnf
)MaxScale's configuration is primarily done in its main configuration file, typically /etc/maxscale.cnf
.
a. Define Servers:
Add a section for each of your backend MariaDB servers.
[server1]
type=server
address=192.168.1.101 # IP address or hostname of your first MariaDB server
port=3306
protocol=MariaDBBackend
[server2]
type=server
address=192.168.1.102 # IP address or hostname of your second MariaDB server
port=3306
protocol=MariaDBBackend
b. Define a Monitor:
This section tells MaxScale how to monitor your backend servers' health and roles.
[MariaDB-Monitor]
type=monitor
module=mariadbmon # Standard MariaDB monitoring module
servers=server1,server2 # List of servers to monitor
user=maxscale_monitor # A MariaDB user with REPLICATION SLAVE privilege
password=monitor_password
monitor_interval=5000 # Check every 5 seconds
Important: Create the maxscale_monitor
user on your backend MariaDB servers with appropriate privileges (e.g., GRANT REPLICATION SLAVE ON *.* TO 'maxscale_monitor'@'%' IDENTIFIED BY 'monitor_password';
).
c. Define a Service (e.g., Read-Write Split):
This configures how MaxScale routes queries. The readwritesplit router is very common for replication setups.
[Read-Write-Service]
type=service
router=readwritesplit # Use the read-write split router
servers=server1,server2 # Servers available for this service
user=maxscale_user # A MariaDB user your applications will use to connect THROUGH MaxScale
password=application_password
# router_options= ... (optional router-specific settings)
Important: Create the maxscale_user
on your backend MariaDB servers with the privileges your application needs.
d. Define a Listener:
This specifies the port and protocol MaxScale will listen on for incoming client connections and which service to direct them to.
[Read-Write-Listener]
type=listener
service=Read-Write-Service # Link to the service defined above
protocol=MariaDBClient # MaxScale listens as a MariaDB client
port=3307 # MaxScale will listen on port 3307 for applications
e. Basic MaxScale Configuration (at the top of maxscale.cnf
):
[maxscale]
threads=4 # Number of worker threads
# Other global settings
After configuring maxscale.cnf
, start and enable the MaxScale service.
sudo systemctl start maxscale
sudo systemctl enable maxscale
sudo systemctl status maxscale # Check status
Once MaxScale is running, configure your applications to connect to MaxScale's listener port (e.g., localhost:3307
) instead of directly to a MariaDB server.
Example (Connect with mariadb
client):
mariadb -h 127.0.0.1 -P 3307 -u maxscale_user -p
maxscale_user
should be the user defined in your MaxScale [Read-Write-Service]
section.
Verify Read-Write Split (if configured):
Connect to MaxScale (127.0.0.1:3307
).
Execute a WRITE
query (e.g., INSERT INTO your_table ...
). This should be routed to the primary server.
Execute a READ
query (e.g., SELECT * FROM your_table
). This should be load-balanced across your replica servers.
You can use maxctrl show sessions
or maxctrl show servers
to observe routing in action.
Quickstart guide for MariaDB MaxScale authentication modules
MariaDB MaxScale incorporates robust authentication modules to manage client access and ensure secure communication with your backend MariaDB servers. Understanding these modules is crucial for securing your database deployments when using MaxScale.
MaxScale's authentication modules (often referred to as "authenticator plugins") are components that handle client authentication. They determine how incoming clients verify their identity to MaxScale and, in turn, how MaxScale authenticates itself to the backend MariaDB servers. This process is similar to how authentication works directly with MariaDB Server using the MySQL protocol.
MaxScale employs a User Account Manager (UAM) for services that use a MariaDB protocol listener.
The UAM is responsible for storing and managing user account information.
It typically queries the mysql
database on your backend MariaDB servers (usually the primary) to retrieve user account details.
Using this information, the UAM authenticates connecting clients, verifies their passwords, and checks their database access rights.
The user
and password
settings within your MaxScale service configuration define the credentials MaxScale uses to fetch these user accounts from the backend databases.
MaxScale supports various authentication schemes through different plugins:
Standard MySQL Password: This is the most common authentication method, verifying user credentials against those stored in the backend MariaDB server's mysql.user
table (or similar).
GSSAPI (Generic Security Service Application Programming Interface): Provides secure authentication methods, often used in enterprise environments with Kerberos or similar systems.
PAM (Pluggable Authentication Modules): Allows MaxScale to integrate with PAM, enabling authentication against external systems like Unix system users, LDAP, or Active Directory.
Authentication options are primarily defined within the listener configuration of your MaxScale service in the maxscale.cnf
file.
a. Specifying the Authenticator:
The authenticator parameter specifies which authentication plugin to use for a particular listener.
Example maxscale.cnf
snippet (simplified):
Ini, TOML
[my_service]
type=service
router=readwritesplit
servers=server1,server2
user=maxscale_user # User MaxScale uses to connect to backend MariaDB for UAM
password=maxscale_password
[my_listener]
type=listener
service=my_service
protocol=MariaDBClient
port=3306
authenticator=MariaDBAuth # Example: Use the standard MariaDB password authentication
# authenticator=GSSAPIAuth # Or GSSAPI authentication
# authenticator=PAMAuth # Or PAM authentication
b. Authenticator Options (authenticator_options):
Additional settings can be passed to the authenticator plugin using authenticator_options. These are comma-separated key-value pairs.
Common authenticator_options
:
skip_authentication=true
: (Use with extreme caution, typically only for development/testing). This option bypasses password checks for connecting clients. Clients will still need a valid username in the backend database, but their password will not be verified.
match_host=false
: Disables host matching for user accounts. By default, MariaDB (and thus MaxScale's UAM) matches user accounts based on both username and host (e.g., 'user'@'localhost'
). Setting this to false
means only the username needs to match.
lower_case_table_names=true/false
: Controls how database names are matched during authentication, similar to the lower_case_table_names
system variable in MariaDB Server.
Example with options:
Ini, TOML
[my_listener]
# ... other settings ...
authenticator=MariaDBAuth
authenticator_options=skip_authentication=true,match_host=false
By configuring these authentication modules, you can control how clients connect to your MariaDB through MaxScale, enforce security policies, and integrate with existing authentication infrastructure.
MariaDB MaxScale limitations quickstart guide
While MariaDB MaxScale is a powerful tool for managing MariaDB deployments, it's essential to be aware of its limitations to ensure proper configuration and avoid unexpected behavior. This guide highlights key considerations when deploying and using MaxScale.
Limitation: Older versions of MaxScale (2.1.2 and earlier) had a strict line length limit of 1024 characters in the configuration file (maxscale.cnf
).
Consideration: Ensure your MaxScale configuration lines do not exceed this limit for older versions. Modern versions (e.g., 2.3.0 and later) have significantly increased this limit (to 16,777,216 characters), making it less of a concern for current deployments. Always check the documentation for your specific MaxScale version.
Limitation: MaxScale operates under the assumption that certain MariaDB Server configuration parameters are set to their default values. A critical example is the assumption that autocommit
is enabled for new connections.
Consideration: If your backend MariaDB servers deviate from default autocommit
settings or other assumed defaults, it could lead to unexpected behavior in how MaxScale manages connections and routes queries, particularly with transaction handling. Always verify that your MariaDB server configurations align with MaxScale's expectations as outlined in its documentation.
Limitation: MaxScale uses a custom SQL parser to deduce transaction boundaries. This parser may not fully comprehend or correctly classify highly complex SQL statements or certain edge cases. This can lead to a mismatch between MaxScale's understanding of a connection's transaction state and the actual state on the backend database.
Specific Issue with XA Transactions: If a START TRANSACTION
command fails internally on the backend due to an already open XA transaction (e.g., using XA START
), MaxScale's parser might not correctly detect this failure. It could mistakenly assume a new transaction has started, leading to discrepancies in transaction state awareness, especially with the readwritesplit
router.
Consideration: Be cautious with overly complex SQL or when using XA transactions in conjunction with MaxScale's transaction-aware routers. Thoroughly test your application's transaction logic through MaxScale to identify and mitigate any potential inconsistencies.
Limitation: The ETL (Extract, Transform, Load) feature within MaxScale relies on the MariaDB Connector/ODBC driver for its functionality.
Consideration: To ensure stability and avoid potential crashes or memory leaks when using MaxScale's ETL capabilities, it is highly recommended to use MariaDB Connector/ODBC driver version 3.1.18 or later. Always ensure all components are compatible.
Query Classification: MaxScale's ability to route queries correctly relies on its query classification. If queries are ambiguous or use non-standard SQL, they might be misrouted.
Prepared Statements: While supported, complex interactions with prepared statements can sometimes expose nuances in parsing or routing.
Protocol Specifics: MaxScale aims for broad compatibility, but subtle differences in client or server protocol implementations might arise.
Monitoring and Filters: Be aware that specific monitor or filter modules might have their own inherent limitations or performance impacts.
Understanding these limitations helps in designing a robust MaxScale deployment and troubleshooting potential issues effectively.