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
No comments:
Post a Comment