If you have followed this series of blogs (Introduction in part 1, Setting up the cluster in part 2 and Installing and configuring MaxScale in part 3), you should now have MaxScale up and running on a Cluster using MariaDB Replication. But as I said when I left off in Part 3, there is more to it.
To begin with, let's look at how the replication system works. Let's insert some more data through MaxScale, this should end up with the master and the replication system will handle making this available on the slaves. So on the Client machine (192.168.0.167), logged in a root, do:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(2)"
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(3)"
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(4)"
We should now have 4 rows in the table t1, let's check:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT c1 FROM t1"
+------+
| c1 |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
Whoa! That was cool. Or? Maybe not I guess. For example, where is this data coming from? Let's use something that we can use to tell us which server we are selecting from. Remember that the variable server_id is different in all servers (it has to be), it is 1 (on the master). 11 and 12 (on the two slaves respectively) so if we select that, we should see what server the data is coming from, right? Let's check it out:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
+-------------+------+
| @@server_id | c1 |
+-------------+------+
| 11 | 1 |
| 11 | 2 |
| 11 | 3 |
| 11 | 4 |
+-------------+------+
So this was data coming from the first slave. We know that we are supposed to have load balancing in place here, so if we try the same thing again, data should come from the other slave. But don't take my word for it, let's try it:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
+-------------+------+
| @@server_id | c1 |
+-------------+------+
| 12 | 1 |
| 12 | 2 |
| 12 | 3 |
| 12 | 4 |
+-------------+------+
Hey, load balancing seems to work! Coolness, dude! Our INSERTs above were clearly excuted on the master and replicated to the two slaves, right? Can we check that! Yeah, let's try again, but let's first remove all the rows in our test table:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "DELETE FROM t1"
And now let's insert a row again, but use the server_id variable instead:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(@@server_id)"
Let's check what happened now:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
+-------------+------+
| @@server_id | c1 |
+-------------+------+
| 12 | 12 |
+-------------+------+
What! Have we gone collectively nuts (or more so than usual at least), or? What happened here? Let's try it again:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
+-------------+------+
| @@server_id | c1 |
+-------------+------+
| 11 | 11 |
+-------------+------+
Ouch! What's wrong! Well, what is going on here is that we are using statement based replication, which is the default, so on the master, c1 has the value 1, but executing INSERT INTO t1 VALUES(@@server_id) on the first slave will pick up the local value for @@server_id (which is 11) and insert that into the table. To fix this we have to use Row Based Replication (RBR). We can set this as a global variable, but to make it permanent, which is what we want to do, really, we have to set it in the appropriate config file on the master:
# vi /etc/my.cnf.d/server.cnf
and then adjust the mysqld section so it looks like this:
[mysqld]
server_id=1
log-bin
binlog_format=MIXED
And then we restart the master server (this is also executed on the master, mind you):
# /etc/init.d/mysql restart
Now, let's try again, from the client:# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "DELETE FROM t1"
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "INSERT INTO t1 VALUES(@@server_id)"
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
+-------------+------+
| @@server_id | c1 |
+-------------+------+
| 11 | 1 |
+-------------+------+
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "SELECT @@server_id, c1 FROM t1"
+-------------+------+
| @@server_id | c1 |
+-------------+------+
| 12 | 1 |
+-------------+------+
That makes more sense, right. It got scary there for a while. And note that this isn't specific to MaxScale or anything, it's just how MariaDB Replication works.
With this we now have a Cluster of MariaDB servers set up where we use MaxScale as a Load Balancer with read-write split. In the next blog in this series, I plan to have a look at performance.
Cheers
/Karlsson
Monday, February 24, 2014
Tuesday, February 4, 2014
MaxScale for the rest of us - Part 3
This third post in this series of blogs about MaxScale is finally getting where you want to go: Install and configure MaxScale. The first blog in this series was an overview of what MaxScale is and the second about how to set up a Cluster of MariaDB servers, using MariaDB Replication, for MaxScale to access. But now it's time to introduce MaxScale.
If you skipped the second post as you already know how to set up MariaDB with Replication and all that, be remineded that I will use the same Linux server setup as outlined there even for the MaxScale server and for a client to do some testing, and I recommend you stick with that for now (for MariaDB itself you can use any relevant setup you want, MaxSCale doesn't really care, but MaxScale is pretty new and has still not been tested on that many platforms, so try to stick to the CentOS 6.5 setup I propose.
# cd /usr/local
# mkdir skysql
# cd skysql
# curl https://downloads.skysql.com/files/SkySQL/MaxScale/maxscale.preview.0.4.tar.gz > maxscale.preview.0.4.tar.gz
# tar xvfz maxscale.preview.0.4.tar.gz
# cd maxscale
Now we have MaxScale downloaded, but for maxscale to work, the MAXSCALE_HOME environment variable has to be set up, and to achieve this we, let's create a shell-script that starts MaxScale for us.
# vi maxstart.sh
Then set up this script like this:
#!/bin/bash
#
export MAXSCALE_HOME=/usr/local/skysql/maxscale/MaxScale
$MAXSCALE_HOME/../bin/maxscale
Once we have that script, let's make it executable:
# chmod +x maxstart.sh
# vi /usr/local/skysql/maxscale/MaxScale/etc/MaxScale.cnf
In this file, each module has it's own section, as well as each server and there is also a section for MaxScale itself. Let's begin with MaxScale which has just one variables that controls the # of threads MaxScale uses:
[maxscale]
threads=1
Following this, we set up the servers we are to manage here, of which there are three. For every configuration section, except the one for MaxScale core, we have to tell what type of entity we are defining here, in this case it is server:
[srv1]
type=server
address=192.168.0.160
port=3306
protocol=MySQLBackend
[srv2]
type=server
address=192.168.0.162
port=3306
protocol=MySQLBackend
[srv3]
type=server
address=192.168.0.163
port=3306
protocol=MySQLBackend
As you can see, we define the ip address of the server and the port that MariaDB runs on. In addition we define which protocol module to use, and in this case there is not much else than MySQLBackend to choose from. As you can see, we do not define the master or slave properties of the servers, instead we let MaxScale figure that out for us by using a Monitor module, so now is the time to define that.
For the monitor to work, it will connect to the respective MySQL servers so we need to define a username and password for this. In the previous post I created a user for this called maxuser using the password maxpwd. Also, to simplify matters I created this user with full privileges, and this really isn't recommended practice for production use. Last, with MaxScale you have the option to obfuscate the passwords used in the configuration file for added security, but I am not using that feature here as I want to keep things simple. So, this is how we define this monitor:
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd
This should be pretty straightforward, right? The module variable is a reference to the monitoring module that we are to use. With this in place, we now needs to set up the actual router, and there are two sections for this, one for the listener and one for the router. The router we are about to use is the read-write split router, which manages routing writes to the master and reads to the slaves. Let's start with the router that is the central point here:
[RWSplitRouter]
type=service
router=readwritesplit
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd
The reason we need to have a username and password even for the router is that this needs to authenticate users connecting to MaxScale itself, and to do that it needs to connect to the server it manages, connect to the mysql database and get the user authorization data from there, which is why we need an account that can access the mysql database.
Now we are close to getting started with MaxScale, what is left is to set up a listener for the router we defined above:
[RWSplitListener]
type=listener
service=RWSplitRouter
protocol=MySQLClient
port=3306
That concludes the configuration of MaxScale!
Now we should be able to connect to MaxScale and test that it works. We do this by setting up a MariaDB Client server. This is set up just like our previous servers, only that we only install the MariaDB client. So set up a server like before, set IPADDR to 192.168.0.167 and run, as root:
# yum install MariaDB-client
Now we are real close! The rest of this post assumes that you are connected to the MariaDB Client on 192.168.0.167
I here assume that you have created the t1 table that we used to test replication in the previous post, if not, create it and populate it now:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "create table t1(c1 int)"
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "insert into t1 values(1)"
Now we have a table to test with, so let's see what happens, and let's check that our SELECTs are being routed to our two slaves. We can do that by using the @@server_id variable, which is different one these two of course:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
+------+-------------+
| c1 | @@server_id |
+------+-------------+
| 1 | 12 |
+------+-------------+
Cool, this ended up with one of the slaves, so if I try it again, it shoudl end up with the other slave, right:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
+------+-------------+
| c1 | @@server_id |
+------+-------------+
| 1 | 11 |
+------+-------------+
Whoa!, it actually seems to work! Also, not that if you executed the create table above, then this got routed to the master! We are all set, right? Well, no, there are a few things left to do. And if you didn't get the MaxScale to work, like seeing this in the error log /usr/local/skysql/maxscale/MaxScale/log/skygw_err1.log:
Couldn't find suitable Slave from 3 candidates.
Or if you can't connect to MaxScale, then the most likely issue is that you didn't stop iptables:
# /etc/init.d/iptables stop
But hopefully things work now, but that it works doesn't mean we are finished, there are some things with this configuration that needs fixing, both on the servers and in MaxScale itself, and there is also one thing to watch for in the replication setup. So the next post in the series will be about fine-tuning the MaxScale installation and the MariaDB Cluster it is accessing.
/Karlsson
If you skipped the second post as you already know how to set up MariaDB with Replication and all that, be remineded that I will use the same Linux server setup as outlined there even for the MaxScale server and for a client to do some testing, and I recommend you stick with that for now (for MariaDB itself you can use any relevant setup you want, MaxSCale doesn't really care, but MaxScale is pretty new and has still not been tested on that many platforms, so try to stick to the CentOS 6.5 setup I propose.
Installing MaxScale
Start by setting up a CentOS 6.5 server as outlined in the previous blog in this series and then log in a root. Set up the server to run on IPADDR 192.168.0.165. When logged in as root, create an installation directory for MaxScale, download it and install it:# cd /usr/local
# mkdir skysql
# cd skysql
# curl https://downloads.skysql.com/files/SkySQL/MaxScale/maxscale.preview.0.4.tar.gz > maxscale.preview.0.4.tar.gz
# tar xvfz maxscale.preview.0.4.tar.gz
# cd maxscale
Now we have MaxScale downloaded, but for maxscale to work, the MAXSCALE_HOME environment variable has to be set up, and to achieve this we, let's create a shell-script that starts MaxScale for us.
# vi maxstart.sh
Then set up this script like this:
#!/bin/bash
#
export MAXSCALE_HOME=/usr/local/skysql/maxscale/MaxScale
$MAXSCALE_HOME/../bin/maxscale
Once we have that script, let's make it executable:
# chmod +x maxstart.sh
Configuring MaxScale
The next step then is to configure MaxScale. The default configuration file for MaxScale is called MaxScale.cnf and is located in the etc directory under where MAXSCALE_HOME is located. In this case we will edit:# vi /usr/local/skysql/maxscale/MaxScale/etc/MaxScale.cnf
In this file, each module has it's own section, as well as each server and there is also a section for MaxScale itself. Let's begin with MaxScale which has just one variables that controls the # of threads MaxScale uses:
[maxscale]
threads=1
Following this, we set up the servers we are to manage here, of which there are three. For every configuration section, except the one for MaxScale core, we have to tell what type of entity we are defining here, in this case it is server:
[srv1]
type=server
address=192.168.0.160
port=3306
protocol=MySQLBackend
[srv2]
type=server
address=192.168.0.162
port=3306
protocol=MySQLBackend
[srv3]
type=server
address=192.168.0.163
port=3306
protocol=MySQLBackend
As you can see, we define the ip address of the server and the port that MariaDB runs on. In addition we define which protocol module to use, and in this case there is not much else than MySQLBackend to choose from. As you can see, we do not define the master or slave properties of the servers, instead we let MaxScale figure that out for us by using a Monitor module, so now is the time to define that.
For the monitor to work, it will connect to the respective MySQL servers so we need to define a username and password for this. In the previous post I created a user for this called maxuser using the password maxpwd. Also, to simplify matters I created this user with full privileges, and this really isn't recommended practice for production use. Last, with MaxScale you have the option to obfuscate the passwords used in the configuration file for added security, but I am not using that feature here as I want to keep things simple. So, this is how we define this monitor:
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd
This should be pretty straightforward, right? The module variable is a reference to the monitoring module that we are to use. With this in place, we now needs to set up the actual router, and there are two sections for this, one for the listener and one for the router. The router we are about to use is the read-write split router, which manages routing writes to the master and reads to the slaves. Let's start with the router that is the central point here:
[RWSplitRouter]
type=service
router=readwritesplit
servers=srv1,srv2,srv3
user=maxuser
passwd=maxpwd
The reason we need to have a username and password even for the router is that this needs to authenticate users connecting to MaxScale itself, and to do that it needs to connect to the server it manages, connect to the mysql database and get the user authorization data from there, which is why we need an account that can access the mysql database.
Now we are close to getting started with MaxScale, what is left is to set up a listener for the router we defined above:
[RWSplitListener]
type=listener
service=RWSplitRouter
protocol=MySQLClient
port=3306
That concludes the configuration of MaxScale!
Testing MaxScale
# ./maxstart.shNow we should be able to connect to MaxScale and test that it works. We do this by setting up a MariaDB Client server. This is set up just like our previous servers, only that we only install the MariaDB client. So set up a server like before, set IPADDR to 192.168.0.167 and run, as root:
# yum install MariaDB-client
Now we are real close! The rest of this post assumes that you are connected to the MariaDB Client on 192.168.0.167
I here assume that you have created the t1 table that we used to test replication in the previous post, if not, create it and populate it now:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "create table t1(c1 int)"
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "insert into t1 values(1)"
Now we have a table to test with, so let's see what happens, and let's check that our SELECTs are being routed to our two slaves. We can do that by using the @@server_id variable, which is different one these two of course:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
+------+-------------+
| c1 | @@server_id |
+------+-------------+
| 1 | 12 |
+------+-------------+
Cool, this ended up with one of the slaves, so if I try it again, it shoudl end up with the other slave, right:
# mysql -h 192.168.0.165 -u maxuser -pmaxpwd test -e "select c1, @@server_id from t1"
+------+-------------+
| c1 | @@server_id |
+------+-------------+
| 1 | 11 |
+------+-------------+
Whoa!, it actually seems to work! Also, not that if you executed the create table above, then this got routed to the master! We are all set, right? Well, no, there are a few things left to do. And if you didn't get the MaxScale to work, like seeing this in the error log /usr/local/skysql/maxscale/MaxScale/log/skygw_err1.log:
Couldn't find suitable Slave from 3 candidates.
Or if you can't connect to MaxScale, then the most likely issue is that you didn't stop iptables:
# /etc/init.d/iptables stop
But hopefully things work now, but that it works doesn't mean we are finished, there are some things with this configuration that needs fixing, both on the servers and in MaxScale itself, and there is also one thing to watch for in the replication setup. So the next post in the series will be about fine-tuning the MaxScale installation and the MariaDB Cluster it is accessing.
/Karlsson
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.
# 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=1Before 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.
# 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.
# 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
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=1Before 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
Subscribe to:
Posts (Atom)