load balancer

Table of contents

• Overview
• Preparations
• Activate MaxScale configuration synchronisation
• Change MaxScale parameters
• Add new slave and make MaxScale known
• Remove old slave and make MaxScale known
• How is the configuration synchronised?

Inhaltsverzeichnis

• Übersicht
• Vorbereitungen
• MaxScale Konfigurations-Synchronisation aktivieren
• MaxScale Parameter ändern
• Neuer Slave hinzufügen und MaxScale bekannt machen
• Alter Slave entfernen und MaxScale bekannt machen

Table of contents

• Overview
• Preparation of the shards (MariaDB database instances)
• Create test data
• Creating roles and users
• MaxScale Monitor User
• MaxScale Admin User
• Create application role and accounts

Inhaltsverzeichnis

• Übersicht
• Vorbereitung der Shards (MariaDB Datenbank Instanzen)
• Testdaten erstellen
• Rollen und User erstellen
• MaxScale Monitor User
• MaxScale Admin User
• Applikationsrolle und Accounts erstellen

Currently we are in the testing phase of the next release of our Ops Center (focmm).

For this how-to we were working with a MariaDB 10.6.5 database server and the newest MariaDB MaxScale 6.3.0 (aka 2.6) load balancer/proxy.

As a starting point we had a working MariaDB Master/Master replication with one active Master (M_a) and one passive Master (M_p). Whereas the passive Master was set to read_only. As a replication user we used the user replication.

Recently we had the case that a customer claimed that MariaDB 10.3 Binary Log is using 150% more space on disk than MySQL 5.7 Binary Log. Because I never observed something similar, but to be honest, I did not look to intensively for this situation, we had to do some clarifications.

First we checked the usual variables which could be candidates for such a behaviour:

There are some pretty sophisticated Load Balancers for Galera Clusters setups out in the market (ProxySQL, MaxScale, HAproxy, ...). They have many different exotic features. You can nearly do everything with them. But this comes at the cost of complexity. Non of them is simple any more.

After properly installing and testing a Galera Cluster we see that the set-up is not finished yet. It needs something in front of the Galera Cluster that balances the load over all nodes.

Recently we had to set-up a 3-node Galera Cluster with a Load Balancer in front of it. Because Galera Cluster nodes (mysqld) still reply to TCP requests on port 3306 when they are expelled from the Cluster it is not sufficient to just leave it to the Load Balancer to check the port if a Galera node is properly running or not.

Table of Contents

• Building the Galera Load Balancer
• Starting the Galera Load Balancer
• Querying the Galera Load Balancer
• Draining nodes with Galera Load Balancer
• Removing nodes from and adding nodes to Galera Load Balancer