Monday, February 3, 2014

MaxScale for the rest of us - Part 2

The first blogpost 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.

Setting up the MariaDB slave servers

The slave servers will be set up on nodes 192.168.0.162 and 192.168.0.163. To begin with, these have the same basic setup as the master above, except that the IPADDR is different of course. Then we install MariaDB server and client:
# yum install MariaDB-server
# yum install MariaDB-client
Then we need to do some basic server configuration, in this case we need to set up the server_id of the slaves, they will be set to 11 and 12 respectively for this test:
# vi /etc/my.cnf.d/server.cnf
And on 192.168.0.162 we set the [mysqld] section to:
[mysqld]
server_id=11

And on 192.168.0.163 to:
[mysqld]
server_id=12

With that done, we can start MariaDB on both servers:
# /etc/init.d/mysql start
and then we configure these servers as slaves of the master we configured above, execute the following of both 192.168.0.162 and 192.168.0.163:
# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB 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)]> change master to master_host = '192.168.0.160', master_user = 'repl', master_password = 'repl', master_log_file = 'localhost-bin.000001', master_log_pos = 245;
Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> slave start;
Query OK, 0 rows affected (0.00 sec)


Now we have a master server and two slaves set up. Before we finish this up and move on to setting up MaxScale itself, let's test that replication work. On the master, do this:
# mysql test
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
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 [test]> create table t1(c1 int);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

This means we now have a table, in the test database, that should be replicated to the two slaves. Run this command on the two slaves to check;
# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.35-MariaDB 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 [test]> select * from t1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [test]> exit
Bye

This concludes the setup of the master/slave cluster that we are to use with MaxScale, in the next blog in this series, that is exactly what we are to do: Set up MaxScale, finally!


/Karlsson

No comments: