galera | Synchronous multi-master replication library | Build Tool library

Yes, the readwritesplit module only uses connections that have the same rank. This means that if both server1 and server2 fail, the readwritesplit service will use server3 as long as it is up.

When either of the two other servers comes back up, the galeramon monitor will shift the Master label from server3 to one of the other nodes. At that point readwritesplit will discard the connection to server3 and reconnect to the nodes with the higher rank, if possible.

I have the answer. The issue was, that I needed to add mode tcp to both the frontend and backend of the database. Now it's working as expected, if there will be any additional issues, I will comment on this answer. There are more things that depend on this.

So looking at the Illuminate\Database\Connectors\ConnectionFactory class, your question includes the createPdoResolverWithHosts() method; you can see this is called by createPdoResolver() when a hosts key exists in the config. But if that array key is missing, createPdoResolverWithoutHosts() is called instead, which directly calls the createConnector() method. That method returns an instance of the appropriate database connector class. Before it does that, however, it checks for any bound connectors:

The application only needs to know the IP address of the VIP in this architecture. The app connects to that VIP using a MySQL connector, as if it is a MySQL Server.

The "requests" are then stateful TCP/IP connections using the MySQL protocol, just as if the app were connected directly to a MySQL node.

This is not a series of stateless http requests. You might be assuming that HAProxy is only for load-balancing http requests. In fact, HAProxy can be used for other protocols than http.

If you want to access from the local host (i.e. 127.0.0.1), you either need two separate users ('user'@'127.0.0.1' and 'user'@'10.1.0.%') or a grant that covers them both ('user'@'%'). A more detailed description of this can be found in the MaxScale tutorial.

You can make authentication handling with MaxScale significantly simpler by using the proxy_protocol feature in MaxScale. This would allow you to define users only once with the exact IP address they come from and let the proxy-protocol-networks variable in MariaDB control which MaxScale instances are allowed to act as proxies.

The error 'user'@'127.0.0.1' means that the client was attempting to connect from the local host, i.e. by connecting to the loopback address. This isn't covered by the 10.1.0.% host which explains why it failed.

Having converted the user to 'user'@'127.0.0.1', the local login between the client and MaxScale worked but then the backend server rejected the authentication as the MaxScale host no longer matches the grant's network (127.0.0.1).

Using the % host causes all addresses to match which explains why it solved the problem.

Usually you can connect to any number of MaxScale instances as long as certain features are enabled to guarantee that all MaxScale instances pick the same server where they send writes to.

If you are using a Galera cluster, this can be done in a safe and conflict-free manner by enabling the root_node_as_master parameter. It uses the Galera cluster itself to select which node it uses for writes. This allows your applications to connect to either of the MaxScale instances.

Even without this parameter it would not cause any problems with the database itself but due to the way Galera works, you increase the likelihood of running into a conflict when you COMMIT your transaction if you write to multiple nodes.

If you use MaxScale with a cluster that uses asynchronous replication, you still can do this as long as you configure your mariadbmon monitor to use cooperative_monitoring_locks. This causes the MaxScale instances to communicate via the database about which servers they see and which of them they chose for writes.

An added benefit of cooperative_monitoring_locks is that you can enable the automated cluster management parameters auto_failover and auto_rejoin without having to worry about two MaxScales attempting to change the replication configuration at the same time: cooperative_monitoring_locks makes sure only one MaxScale does it.

By default, mysqld will read an option file in a few default locations (e.g. /etc/my.cnf). These are documented: https://mariadb.com/kb/en/configuring-mariadb-with-option-files/#default-option-file-locations

In addition, the my.cnf file may contain a directive to load others:

Rules for memory allocation.

• Do not allocate so much RAM that swapping will occur. Swapping is terrible for MySQL/MariaDB performance.
• Do adjust innodb_buffer_pool_size such that most of RAM is in use during normal time and even for spikes in activity. (I often say "set it to 70% of available RAM", but you are asking for more details.)
• Do not bother changing other settings; they add to the complexity of "getting it right".

There are 3 situations (based on innodb_buffer_pool_size and dataset size):

• Tiny dataset -- buffer_pool is bigger than necessary --> wasting some of RAM, but so what; it is not useful for anything else. And it give you some room for growth.
• Medium-sized dataset -- Most activity is done in RAM; the system will run nicely.
• Big dataset -- The system may be I/O-bound. Adding RAM is a costly and brute force solution. However, some software techniques (eg, better indexes) may help, such as this for WordPress and WooCommerce.

So I seem to have fixed the issue. Turns out to be an issue with the OS (Arch Linux) not docker, I found this out by running the same files on a Windows system.

For any arch users I've listed my steps out at https://bbs.archlinux.org/viewtopic.php?pid=1973008#p1973008