SQL Zone is brought to you in partnership with:

I have been in the Software business for close to 30 years now, and I have 25+ years with SQL-based relational databases. Although I have used Unix even longer than that, I am pretty much an operating system agnostic. Over the years, I have work in many positions, from support engineer to sales engineer and consultant. Anders is a DZone MVB and is not an employee of DZone and has posted 50 posts at DZone. You can read more from them at their website. View Full User Profile

MaxScale for the Rest of Us, Part 2: The MariaDB Cluster

03.20.2014
| 3200 views |
  • submit to reddit

The first blog post in this series did a quick introduction to MaxScale, but now it is time start getting our hands dirty. We will get a more practical view on MaxScale and begin to put it to work. The following is based on a simple Proof of Concept that I did recently. The application is a mid-size web-based online shop where scalability is becoming an issue. They use PHP and with an old and rather inflexible framework, so even though changing how the database is used is possible, it's not easy and having database routing in the application code isn't a very good idea either.

The scalability issues affects reads, but as all traffic is directed to one database server, when this server gets slow, writes, like when entering an order, gets very slow, and this is unacceptable. So what was needed is a way to redirect reads to one or more slaves and to retain all writes in one server. That the slaves might be slightly delayed due to the asynchronous nature of MariaDB replication was wasn't determined to be a problem, but one task of this POC was to get a system where this could be tested. But before we come that far, we need to set up MaxScale.

This blogpost is really meant to show how to set up a MariaDB Cluster using MariaDB Replication. If you already know all this, then you might just have a quick look at this, and wait for part 3 of this series, where I configure MaxScale to run on the Cluster we are to setup here. Even if you know how to set up MariaDB and Replication and all that, have a look here for the server setup and IP addresses and stuff like that if you are to come along to part 3.

Setting up the servers

All servers we are going to use are running CetnOS 6.5, and this is set up a a Basic Server. Once this is done I log in a root and run a few extra things to make testing easier. First I update all the CentOS packages to the latest version

 # yum update 

Following this I set up all servers to use static IP. For this I need to set up two files:
/etc/sysconfig/network:

NETWORKING=yes
HOSTNAME=localhost.localdomain
GATEWAY=192.168.0.1

I guess you understand what is going on here and how up set this up for your own purposes, the only line affected is that I have added the gateway. The second networking file to set up will set up the network on the network adapter eth0:
/etc/sysconfig/network-scripts/ifcfg-eth0:

DEVICE=eth0
HWADDR=08:00:27:9F:C8:48
TYPE=Ethernet
UUID=5dba1546-197d-4a82-8606-7a9f05e9bb44
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=static
NETMASK=255.255.255.0
IPADDR=192.168.0.160

Here I have modified BOOTPROTO and NETMASK added IPADDR. Of course IPADDR is different for each server. To use these new network setting I need to restart CentOS networking:

 # /etc/init.d/network restart

Following this, there is only one more generic CentOS setting to be done, which is to add the MariaDB repository to the yum package manager configuration. This is by adding a MariaDB repository file:

 # vi /etc/yum.repos.d/MariaDB.repo

And then make this file look like this:

# MariaDB 5.5 CentOS repository list - created 2014-02-02 09:27 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Before we finish with the server setup, make sure that the Linux iptables firewall isn't running and that it isn't started on reboot by issuing the commands:

# /etc/init.d/iptables stop
# chkconfig iptables off 

This concludes the generic server setup, we are now ready to set up the first server whcih will act as the master.

Setting up the MariaDB master server

Following the generic server setup as outlined above, setting the IPADDR to 192.168.0.160 in my case, we are ready to do specific setup. First we need to install MariaDB server and client:

# yum install MariaDB-server
# yum install MariaDB-client

Then we need to set up MariaDB as a master before we start it, we do this by modifying the MariaDB server configuration file:

# vi /etc/my.cnf.d/server.cnf
And in the [mysqld] section add:
[mysqld]
server_id=1
log-bin

This concludes the initial master configuration, so now we can start the server to enable us to do some more basic configuration:

 # /etc/init.d/mysql start

We are going to add slaves on 192.168.0.162 and 192.168.0.163 and we are to run MaxScale on node 192.168.0.165, so we need to create a user that can access the master appropriately:

# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB-log MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.0.162' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.0.163' identified by 'repl';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on *.* to 'maxuser'@'192.168.0.165' identified by 'maxpwd';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+----------------------+----------+--------------+------------------+
| File  | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000001 |  245 |  |  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

This concludes the master configuration, we are now ready to start setting up the slaves.

Published at DZone with permission of Anders Karlsson, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)