MariaDB Galera Cluster is a synchronous multi-master database cluster solution that provides MariaDB high availability and data consistency. MariaDB MaxScale is a database proxy that can be used to improve the performance, scalability, and security of MariaDB Galera Cluster. Codership (the makers of Galera Cluster) have a long term partnership with MariaDB Corporation, including joint development, roadmap planning, and supporting customers for continued success.
Using MariaDB MaxScale with MariaDB Galera Cluster offers several key benefits, including improved read throughput, deadlock avoidance on commit, and high availability guarantees. This means that your database will be more resilient to failures and will be able to handle more traffic.
Let’s start by configuring a simple 3-node MariaDB Galera Cluster. We did this on Ubuntu 24.04 LTS with MariaDB Server 11.4. Instructions are available on the MariaDB download size and also here for posterity:
sudo apt-get install apt-transport-https curl sudo mkdir -p /etc/apt/keyrings sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp' In /etc/apt/sources.list.d/mariadb.sources: # MariaDB 11.4 repository list - created 2024-12-19 08:26 UTC # https://mariadb.org/download/ X-Repolib-Name: MariaDB Types: deb # deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details. # URIs: https://deb.mariadb.org/11.4/ubuntu URIs: https://sg-mirrors.vhost.vn/mariadb/repo/11.4/ubuntu Suites: noble Components: main main/debug Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp sudo apt-get update sudo apt-get install mariadb-server
Once all that is installed, it is time to configure the first node. You can read Getting Started with MariaDB Galera Cluster, or just follow along here.
Edit /etc/mysql/mariadb.conf.d/60-galera.cnf and add:
binlog_format=ROW default-storage-engine=innodb innodb_autoinc_lock_mode=2 bind-address=0.0.0.0 # Galera Provider Configuration wsrep_on=ON wsrep_provider=/usr/lib/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="galera" wsrep_cluster_address="gcomm://152.42.240.131,188.166.241.27,188.166.240.202" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="152.42.240.131"
You can now start node 1 with galera_new_cluster. Starting nodes 2 and 3 are simple – just do it via service mysqld restart. At this point you should have a functioning 3-node MariaDB Galera Cluster.
MariaDB [(none)]> show status like 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.001 sec)
On your 4th machine, let’s install MariaDB MaxScale. We can do it via: curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash and once the repository is added it is as simple as apt install maxscale. We configure MariaDB MaxScale in /etc/maxscale.cnf. Here is a simple configuration to define three servers, one for each node in the MariaDB Galera Cluster. It also defines a Galera Monitor that will monitor the health of the cluster. The Read-Write-Service routes read and write queries to the appropriate nodes using the readwritesplit router, ensuring that write operations are directed to the master node while read operations are distributed among the slave nodes for enhanced performance. The max_slave_connections=100% parameter ensures that all available slaves are utilized for read queries. Finally, the configuration defines a listener for the Read-Write-Service on the standard MySQL port (3306).
[maxscale] threads=auto log_augmentation=1 ms_timestamp=1 syslog=1 logdir=/var/log/maxscale [server1] type=server address=152.42.240.131 port=3306 protocol=MariaDBBackend [server2] type=server address=188.166.241.27 port=3306 protocol=MariaDBBackend [server3] type=server address=188.166.240.202 port=3306 protocol=MariaDBBackend [Galera-Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=maxscale password=MyNewPass1! monitor_interval=2000ms [Read-Write-Service] type=service router=readwritesplit servers=server1,server2,server3 user=maxscale password=MyNewPass1! max_slave_connections=10 master_failure_mode=fail_on_write disable_sescmd_history=1 connection_keepalive=300s [Read-Write-Listener] type=listener service=Read-Write-Service protocol=MariaDBClient port=3306
On any node in the MariaDB Galera Cluster, let’s create a MariaDB MaxScale user.
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'MyNewPass1!'; GRANT SELECT ON mysql.user TO 'maxscale'@'%'; GRANT SELECT ON mysql.db TO 'maxscale'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%'; GRANT SELECT, RELOAD, PROCESS, SHOW DATABASES, REPLICATION CLIENT, SUPER ON *.* TO 'maxscale'@'68.183.236.109' IDENTIFIED BY 'MyNewPass1!'; FLUSH PRIVILEGES;
Now you can start MariaDB MaxScale: systemctl restart maxscale. And you should be able to see it via maxctrl:
maxctrl list servers ┌─────────┬─────────────────┬──────┬─────────────┬─────────────────────────┬──────┬────────────────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ Monitor │ ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server1 │ 152.42.240.131 │ 3306 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │ ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server2 │ 188.166.241.27 │ 3306 │ 0 │ Master, Synced, Running │ │ Galera-Monitor │ ├─────────┼─────────────────┼──────┼─────────────┼─────────────────────────┼──────┼────────────────┤ │ server3 │ 188.166.240.202 │ 3306 │ 0 │ Slave, Synced, Running │ │ Galera-Monitor │ └─────────┴─────────────────┴──────┴─────────────┴─────────────────────────┴──────┴────────────────┘
Now you can access your MariaDB Galera Cluster via MariaDB MaxScale: mariadb -h 68.183.236.109 -P 3306 -u root -p
This is blog 1 of a series of many of why you might want to use MariaDB MaxScale with MariaDB Galera Cluster. We will also address what new MariaDB Server features benefit Galera Cluster users. This particular example only focused on the readwritesplit router, which helps us distribute effcitively all the read-write queries across all available “secondary” nodes.