Monday, February 24, 2014

MaxScale for the rest of us - Part 4

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

4 comments:

ives said...

Anders. Cool stuff.

Given your experimentation so far, do you feel maxscale is a suitable mysql proxy?

Are there means of dynamically altering routing information or promoting / demoting masters?

jhon said...

Payday accommodation accounts can be managed responsibly and effectively. If college lending costs are evident, it is a baby amount to pay for such convenience. Abounding payday borrowers accept bidding achievement and abatement about accepting their account. It's accessible and simple to administer and afterwards ambience up their annual they can get banknote about instantly. If you accept a job and a cheque agreement card, again you too may be able to accessible an banknote beforehand account.
www.usacheckcashingstore.com/costa-mesa

jhon said...

This action additionally makes it easier for businesses to administer their account, added so if they are already ashore with their added burning responsibilities. Any merchant banknote beforehand aggregation of acceptable continuing will additionally acreage a acquiescent chump annual aggregation to adviser their audience through the accomplished process, and to advice them adjudge on a personalized, tailor-fit advantage that is advantageous for both parties.
Cash Advance Chicago

Shan Jonson said...


The big advantage of banknote envelopes is that, if you annual properly, you will consistently accept the banknote to pay a bill back it comes due. No added annoying about breadth the money will appear from, be it for mortgage or rent, utilities, car payments, repairs, taxes, arbitrary spending, etc.
auto title loans fresno online