Step-by-step installation and configuration of 3 node MariaDB Galera Cluster and MariaDB MaxScale on CentOS

This blog is aimed to help anyone who is interested in setting-up 3 node MariaDB Galera Cluster with MariaDB MaxScale for building highly available / reliable, fault-tolerant and self-healing MariaDB infrastructure operations.  We have not tried to explain MariaDB Galera Cluster and MariaDB MaxScale Architecture or internals in this post. MariaDB documentation on both MariaDB Galera Cluster and MariaDB MaxScale is neat and direct , You can read the same here – https://mariadb.com/kb/en/library/galera-cluster/   (MariaDB Galera Cluster) and https://mariadb.com/kb/en/mariadb-maxscale-20-setting-up-mariadb-maxscale/ …

If you use MaxScale to route queries from various servers to some MariaDB server(s), when viewing the processlist on the MariaDB server, you will see MaxScale’s host for any “host” information related to that connection or its queries.

When tracking down problematic queries, it can be helpful to know what originating host of that query.

MaxScale’s proxy protocol to the rescue.

The proxy protocol was introduced in MaxScale 2.2 and MariaDB 10.3.

To enable it, it is quite simple (essentially just 2 changes).

1. In MariaDB, you need to set the variable proxy_protocol_networks in your my.cnf file (you can specify comma-separated IP addresses and/or subnetworks, as well as localhost and ::1):

proxy-protocol-networks=::1, 192.168.0.0/16, localhost

This one, which I will use as-is, is the example from the …

So when it comes to routing your MySQL traffic several options exist.

• HAproxy
• MariaDB MaxScale
• NGINX
• MySQL Router
• ProxySQL
• You can even still get MySQL Proxy if you wanted it but it is EOF. 

Now I have seen HAproxy used more often with clients, it is pretty straight forward to set up. Percona has an example for those interested: 

• …

This post is just following up my previous blog post which describes how to setup 3-nodes MariaDB Galera Cluster with MySQL-Sandbox on single server.

Today, I’ll try to explain how we can setup MariaDB MaxScale over the Galera Cluster. Before I move ahead, I would like to explain about MaxScale little bit.

MariaDB MaxScale is a database proxy that enables horizontal database scaling while maintaining a fast response to client applications. You can implement MaxScale on either MySQL Replication or Galera cluster. With MySQL Replication, you can either use Read/Write Splitting or Connection routing and same with Galera Cluster. You can get more information here about this product.

…

Tab housekeeping but I also realise that people seem to have missed announcements, developments, etc. that have happened in the last couple of months (and boy have they been exciting). I think we definitely need something like the now-defunct MySQL Newsletter (and no, DB Weekly or NoSQL Weekly just don’t seem to cut it for me!).

MyRocks

During @scale (August 31), Yoshinori Matsunobu mentioned that MyRocks has been deployed in one region for 5% of its production workload at Facebook.

By October 4 at the …