MaxScale Installation and Configuration
In our many years of experience working with databases, one of the most important things we’ve seen is how to connect to the database layer easily, while allowing for maintenance or unplanned failures of the database servers. Changing database connections manually requires human intervention and therefore lots of room for error. MariaDB MaxScale enables seamless switchovers for maintenance, as well as auto-failover and rejoin. MaxScale also has numerous features that improve usability, such as read-write splitting. We always recommend MaxScale as the database proxy layer. MariaDB MaxScale is an advanced database proxy for MariaDB Enterprise Server, powering its enterprise high availability, scalability, security, and integration services.
So now you’ve decided to try MaxScale. This guide will explain how to install, configure, and use the basics of MariaDB MaxScale.
This installation guide assumes that you already have a MariaDB database cluster using standard replication.
DB1 10.0.2.100. #Primary
DB2 10.0.2.101 #Replica
DB3 10.0.2.102 #Replica
Installation
The first step is to download the MaxScale package from the MariaDB Downloads page:
Select your operating system and download either the RPM or the DEB package.
- For RHEL/CentOS variants, use yum to install the downloaded RPM.
- For SLES, use zypper.
- For Debian/Ubuntu systems, install the package with dpkg -i followed by apt-get install to install the dependencies.
You can also use the MariaDB package repository to install MaxScale by first configuring the repository and then installing the maxscale package via your package manager. These instructions will install the latest version of MaxScale.
Configure the MariaDB Enterprise Repository using the following procedure:
- Retrieve your Customer Download Token.
- Install the prerequisites for downloading the software from the Web.
- Install on CentOS / RHEL / Rocky Linux (YUM):
$ sudo yum install wget
- Install on Debian / Ubuntu (APT):
$ sudo apt install wget apt-transport-https
- Install on SLES (ZYpp):
$ sudo zypper install wget
- Install on CentOS / RHEL / Rocky Linux (YUM):
- Configure the MariaDB Enterprise Repository using the mariadb_es_repo_setup script and provide the Customer Download Token using the –token option:
$ wget https://dlm.mariadb.com/enterprise-release-helpers/mariadb_es_repo_setup$ echo "f8eb9c1b59ccfd979d27e39798d2f2a98447dd29e2149ce92bf606aab4493ad9 mariadb_es_repo_setup" | sha256sum -c -$ chmod +x mariadb_es_repo_setup$ sudo ./mariadb_es_repo_setup --token="CUSTOMER_DOWNLOAD_TOKEN" --apply
To learn how to configure your system to install a specific product or version from the MariaDB Enterprise Repository, see the available mariadb_es_repo_setup command-line options.
Next, verify that you have network connectivity from your MaxScale node to the MariaDB nodes.
# nc -vz 10.0.2.100 3306 Ncat: Version 7.50 ( https://nmap.org/ncat ) Ncat: Connected to 10.0.2.161:3306. Ncat: 0 bytes sent, 0 bytes received in 0.01 seconds. # nc -vz 10.0.2.101 3306 Ncat: Version 7.50 ( https://nmap.org/ncat ) Ncat: Connected to 10.0.2.233:3306. Ncat: 0 bytes sent, 0 bytes received in 0.01 seconds. # nc -vz 10.0.2.103 3306 Ncat: Version 7.50 ( https://nmap.org/ncat ) Ncat: Connected to 10.0.2.237:3306. Ncat: 0 bytes sent, 0 bytes received in 0.01 seconds.
Configuration
One of the first things you need to do is to create the MaxScale user on your database nodes. Run the following GRANT statements on the primary database node:
MariaDB [(none)]> CREATE USER maxuser@'10.0.2.%' IDENTIFIED BY 'somepassword'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SUPER, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES ON *.* TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.db TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.user TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.roles_mapping TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.tables_priv TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.columns_priv TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.proxies_priv TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> GRANT SELECT ON mysql.procs_priv TO maxuser@'10.0.2.%'; Query OK, 0 rows affected (0.000 sec)
Now go back to the MaxScale node and create your server objects in the MaxScale config file:
# vi /etc/maxscale.cnf [DB1] type=server address=10.0.2.100 port=3306 protocol=MariaDBBackend [DB2] type=server address=10.0.2.101 port=3306 protocol=MariaDBBackend [DB3] type=server address=10.0.2.102 port=3306 protocol=MariaDBBackend
Now create the MariaDB Monitor in your MaxScale config file. Monitor sections are used to define the monitoring module that watches a set of servers. Each server can only be monitored by one monitor. A monitor module is capable of monitoring the state of a particular kind of cluster and making that state available to the routers of MaxScale:
# vi /etc/maxscale.cnf [MariaDB-Monitor] type=monitor module=mariadbmon servers=DB1,DB2,DB3 user=maxuser password=somepassword monitor_interval=5000 replication_user=repuser replication_password=somepassword backend_connect_timeout=2 backend_write_timeout=2 backend_read_timeout=2 backend_connect_attempts=1 master_conditions=connected_slave,running_slave auto_failover=1 auto_rejoin=1 failcount=2 switchover_timeout=20 failover_timeout=20
At this point, you’ll want to start MaxScale and verify that it is monitoring your database nodes:
# systemctl start maxscale # systemctl enable maxscale # maxctrl list servers ┌─────────┬────────────┬──────┬─────────────┬─────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB1 │ 10.0.2.100 │ 3306 │ 0 │ Master, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB2 │ 10.0.2.101 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB3 │ 10.0.2.102 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ └─────────┴────────────┴──────┴─────────────┴─────────────────┴────────────┘
Next, setup a router. A router module is capable of routing requests to backend servers according to the characteristics of a request and/or the algorithm the router implements.
Examples of routers are readconnroute that provides connection routing, that is, the server is chosen according to specified rules when the session is created and all requests are subsequently routed to that server, and readwritesplit that provides statement routing, that is, each individual request is routed to the most appropriate server.
# vi /etc/maxscale.cnf [Read-Write-Service] type=service router=readwritesplit servers=DB1,DB2,DB3 user=maxuser password=somepassword max_sescmd_history = 1500 causal_reads = 1 causal_reads_timeout =1 0s transaction_replay = 1 transaction_replay_max_size = 1Mi delayed_retry = 1 master_reconnection = 1 master_failure_mode = fail_on_write [Read-Write-Listener] type=listener service=Read-Write-Service protocol=MariaDBClient port=4006
Applications will connect using the MaxScale node’s IP address and port 4006.
Make sure to restart MaxScale after making configuration changes:
# systemctl restart maxscale
Your config file should look something like this:
[DB1] type=server address=10.0.2.100 port=3306 protocol=MariaDBBackend [DB2] type=server address=10.0.2.101 port=3306 protocol=MariaDBBackend [DB3] type=server address=10.0.2.102 port=3306 protocol=MariaDBBackend [MariaDB-Monitor] type=monitor module=mariadbmon servers=DB1,DB2,DB3 user=maxuser password=somepassword monitor_interval=5000 replication_user=repuser replication_password=somepassword backend_connect_timeout=2 backend_write_timeout=2 backend_read_timeout=2 backend_connect_attempts=1 master_conditions=connected_slave,running_slave auto_failover=1 auto_rejoin=1 failcount=2 switchover_timeout=20 failover_timeout=20 [Read-Write-Service] type=service router=readwritesplit servers=DB1,DB2,DB3 user=maxuser password=somepassword max_sescmd_history = 1500 causal_reads = 1 causal_reads_timeout =1 0s transaction_replay = 1 transaction_replay_max_size = 1Mi delayed_retry = 1 master_reconnection = 1 master_failure_mode = fail_on_write [Read-Write-Listener] type=listener service=Read-Write-Service protocol=MariaDBClient
Using MaxScale Features
Failover and Switchover
You can run the command for a planned switchover. This will promote a replica to primary and demote the current primary to a replica:
# maxctrl call command mariadbmon switchover MariaDB-Monitor DB2 DB1 OK
DB2 will now be the primary server.
# maxctrl list servers ┌─────────┬────────────┬──────┬─────────────┬─────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB1 │ 10.0.2.100 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB2 │ 10.0.2.101 │ 3306 │ 0 │ Master, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB3 │ 10.0.2.102 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ └─────────┴────────────┴──────┴─────────────┴─────────────────┴────────────┘
MaxScale also has automatic failover and rejoin. To test this, stop the MariaDB service on DB2:
# systemctl stop mariadb
Check the cluster status from the MaxScale node. DB2 shows as down, and DB1 has been promoted:
# maxctrl list servers ┌─────────┬────────────┬──────┬─────────────┬─────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB1 │ 10.0.2.100 │ 3306 │ 0 │ Master, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB2 │ 10.0.2.101 │ 3306 │ 0 │ Down │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB3 │ 10.0.2.102 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ └─────────┴────────────┴──────┴─────────────┴─────────────────┴────────────┘
Start DB2 again:
# systemctl start mariadb
Check the cluster status from the MaxScale node. The cluster is back to the original settings:
# maxctrl list servers ┌─────────┬────────────┬──────┬─────────────┬─────────────────┬────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB1 │ 10.0.2.100 │ 3306 │ 0 │ Master, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB2 │ 10.0.2.101 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ ├─────────┼────────────┼──────┼─────────────┼─────────────────┼────────────┤ │ DB3 │ 10.0.2.102 │ 3306 │ 0 │ Slave, Running │ 0-1-275790 │ └─────────┴────────────┴──────┴─────────────┴─────────────────┴────────────┘
Connectors and Cooperative Monitoring
MaxScale itself can be setup in a High Availability fashion with two MaxScale nodes. Starting with MariaDB Connector/Python 1.1, when MariaDB Connector/Python is built with MariaDB Connector/C 3.3, the connector supports connection failover when auto_reconnect is enabled and the connection string contains a comma-separated list of multiple server addresses.
The following code example connects with connection failover enabled:
# Module Import
import mariadb
import sys
# Instantiate Connection
try:
conn = mariadb.connect(
host="192.0.2.1,192.0.2.0,198.51.100.0",
port=3306,
user="db_user",
password="USER_PASSWORD")
conn.auto_reconnect = True
except mariadb.Error as e:
print(f"Error connecting to the database: {e}")
sys.exit(1)
# Use Connection
# ...
# Close Connection
conn.close()
MaxScale must be set up for “Cooperative monitoring.” Check out this video for a demonstration on this topic.
When multiple MaxScale instances are used in a highly available deployment, MariaDB Monitor needs to ensure that only one MaxScale instance performs automatic failover operations at a given time. It does this by using cooperative locks on the back-end servers.
For example, add this to the monitor section of the config file:
# vi /etc/maxscale.cnf [MariaDB-Monitor] type = monitor module. = mariadbmon ... cooperative_monitoring_locks = majority_of_running
MaxScale GUI
MaxScale has a GUI with many easy to use, intuitive features. Full setup is beyond the scope of this article, but you can learn more here. The MaxScale GUI provides an alternative to using the MaxCtrl command-line utility or directly editing the config file and makes live and dynamic changes to MaxScale in-flight accessible in more operating environments than before.
Conclusion
Following these short steps, you are now ready to use MariaDB MaxScale. Your database layer maintenance will now be easier. You’ll have automatic failover, which means fewer pages. And you can balance your reads and writes across the cluster. Download and try MariaDB MaxScale!
