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:
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:
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.
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.cnfAnd 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.