In this article we will install MariaDB galera cluster with MaxScale proxy database by MariaDB corporation. MaxScale is inteligent proxy database that can route database statements from cluster to one server. But unlike HAproxy, MaxScale uses asynchronius I/O of the Linux kernel which should help with performance.
Cluster we are going to make will have read write split, meaning that all writes are done in master by MaxScale, and replicates them to all nodes as where they can be read.
In this article we use node-01 as the master and node-02 / node-03 as slaves. Node-04 will be our maxscale core machine.
Main Features of maxscale
a) If any database server fails, connection will be automatically created to another node
b) Connections can be dynamically added or removed from session
c) Maxscale will route client request to number of database servers
Installing the cluster
First thing is to set up your host file with hosnames and private ips of the all your hosts. This is needed so you could have your nodes communicate over private IPs and avoid the need for encryption of traffic. Here is my hosts file (/etc/hosts) on all 4 servers:
10.132.84.235 node-01 node-01 10.132.86.222 node-02 node-02 10.132.19.195 node-03 node-03 10.132.53.122 node-04 node-04
First three will be for Galera cluster, and fourth for MaxScale proxy.
Lets add key for MariaDB repository on first 3 servers
apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
Then we will add repostitory for x86 and POWER little endian architectures, the package will be installed according you your arch.
add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://nyc2.mirrors.digitalocean.com/mariadb/repo/10.2/ubuntu xenial main'
Update the sources list
And then install the mariadb
apt install mariadb-server rsync
Configuring and building the cluster
Next we need to edit configuration files and build the cluster. The node-01 will be the node for bootstrapping the cluster and other nodes will join to this one. So lets first edit configuration files on all three nodes.
There we need to find [galera] section and change these lines:
[galera] # Mandatory settings wsrep_on=ON wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_cluster_name="linoxide-cluster" wsrep_cluster_address="gcomm://node-01,node-02,node-03" binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 # # Allow server to accept connections on all interfaces. # bind-address=0.0.0.0 # wsrep_sst_method=rsync wsrep_node_address="10.132.84.235" wsrep_node_name="node-01"
The last two lines need to have address and hostname of current node, so this above is file from node-01. On every node those two lines need to be changed accordingly, while other lines can be same.
After this is done, we need to start the cluster. If the database server is running by any chance, stop it on all three nodes.
systemctl stop mysql
On first node run:
On the other two nodes
systemctl start mysql
Back on first node, we need to set the password, so we will run
After you ran that script, you can type this command on any node
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"
It should give you output similar to this
MaxScale Proxy installation and preparing the cluster
When we have cluster up and running, we can turn to node-04 to install MaxScale on it. MaxScale is only supported on x86_64 architecture for now. Lets download maxscale deb package with wget
Next we will install dependancy
apt install libcurl3
And then istall MaxScale
dpkg -i maxscale-2.0.1-2.ubuntu.xenial.x86_64.deb
Maxscale have been installed now we need to again enter the mysql prompt on our Galera cluster in order to make maxscale user and grant him enough privileges for maxscale to operate.
mysql -u root -p
And in mysql prompt type this line by line
CREATE USER 'maxscale'@'%' IDENTIFIED BY 'you-password-here'; Query OK, 0 rows affected (0.00 sec) GRANT SELECT ON mysql.db TO 'maxscale'@'%'; Query OK, 0 rows affected (0.01 sec) GRANT SELECT ON mysql.user TO 'maxscale'@'%'; Query OK, 0 rows affected (0.01 sec) GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%'; Query OK, 0 rows affected (0.01 sec)
Lets explain how this config file works. First part under [maxscale] will respectively set number of CPU threads to 4, turn off logging to /var/log/syslog, turn on logging to /var/log/maxscale, turn on log warning, log to memory and log notices, and turn of log info and log developer options for debugging the code.
Next important section is [Galera Monitor]. There the we need to concentrate on several lines. The line that say servers= need to be filled with names of servers. This is not hostname, this is how MaxScale names the servers in this config file further down. In our case we will set server1 to server3. User is username that we created in previous section, maxscale in our case. Password is whatever you set for your password for maxscale user. Galera Monotor will pick one node as master and others as slave (out of our three nodes). The node with lowest WSREP_LOCAL_INDEX will be selected as master. If cluster configuration changes, the new selection may happen and node with lower index will selected as master. If you don't want for master to change this way, you can use option disable_master_failback and set it to 1 like in our config file. This way master wont change even if new node with lower index joins the cluster.
Then we move to next important section which is [RW Split Router]. Here we again enter names for three servers and same user and password as the the [Galera Monitor] section.
Lastly, we need to edit those three servers, and enter names by which the MaxScale will see them, and ip addresses which will use to communicate with them. The names will be [server1][server2] and [server3], and for ip we will use private ip addresses to avoid having to encrypt the traffic.
Now on maxscale server, which is node-04, we are going to configure and start the maxscale proxy database. First lets set up the ufw to allow connections on needed ports.
ufw allow 6603 ufw allow 4442
Then backup the config file
mv /etc/maxscale.cnf /etc/maxscale.cnf.bk
After the file have been backed up and moved, lets make new file from scratch.
There, you can use this as skeleton for configuration, except off course you need to change the bold parts:
[maxscale] threads=4 syslog=0 maxlog=1 log_to_shm=1 log_warning=1 log_notice=1 log_info=0 log_debug=0 [Galera Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=maxscale passwd=you-password-here monitor_interval=2000 disable_master_failback=1 available_when_donor=1
type=filter module=qlafilter options=/tmp/QueryLog
type=filter module=regexfilter match=fetch replace=select [RW Split Router] type=service router=readwritesplit servers=server1,server2,server3 user=maxscale passwd=you-password-here max_slave_connections=100% max_slave_replication_lag=30 [CLI] type=service router=cli [RW Split Listener] type=listener service=RW Split Router protocol=MySQLClient port=3306 [CLI Listener] type=listener service=CLI protocol=maxscaled address=127.0.0.1 port=6603
type=server address=10.132.84.235 port=3306 protocol=MySQLBackend
type=server address=10.132.86.222 port=3306 protocol=MySQLBackend
type=server address=10.132.19.195 port=3306 protocol=MySQLBackend
After this have been saved, you can start the maxscale service
systemctl start maxscale.service
And test whether it is working
maxadmin -pmariadb list servers
We have successfully installed MaxScale proxy database as load balancer for our Galera cluster running on 3 Ubuntu 16.04 nodes with fourth node for MaxScale. MaxScale is good solution for large clusters, today we made smallest possible configuration but scaling out from here is possible. I hope that article was useful for introducing yourself with MaxScale configuration, than you for reading and have a good day.