Master-Master (multi-master) detection on monitor

[Art van Scheppingen]

Hi,

As we are now testing your all brand new MaxScale 1.0 beta I must say you did an awesome job guys! A lot has improved since the previous versions we tested and great to see more and more modules being integrated.

However I did find two things that gave us some trouble:
1. we are using master-master (multi-master) topology and using the monitor on these nodes reveals they are both slaves:
MaxScale> show servers
Server 0x1c13a10 (stg_services_node1)
    Server:                somehost1.spil
    Status:                       Slave, Running
    Protocol:            MySQLBackend
    Port:                3306
    Server Version:            5.5.38-35.2-log
    Node Id:            144
    Master Id:            145
    Slave Ids:
    Repl Depth:            3
    Number of connections:        0
    Current no. of conns:        0
    Current no. of operations:    0
Server 0x1c138d0 (stg_services_node2)
    Server:                somehost2.spil
    Status:                       Slave, Running
    Protocol:            MySQLBackend
    Port:                3306
    Server Version:            5.5.38-35.2-log
    Node Id:            145
    Master Id:            144
    Slave Ids:
    Repl Depth:            3
    Number of connections:        0
    Current no. of conns:        0
    Current no. of operations:    0
As you can see both have the master set to its peer and no slave ids.
I made sure the report-host is correctly set (I read that somewhere) so this is not the issue.

However if I restart one of the nodes the other one gets marked as a master for a short while (longer than the refresh of the monitor):
MaxScale> show servers
Server 0x1c13a10 (stg_services_node1)
    Server:                somehost1.spil
    Status:                       Slave, Running
    Protocol:            MySQLBackend
    Port:                3306
    Server Version:            5.5.38-35.2-log
    Node Id:            144
    Master Id:            145
    Slave Ids:
    Repl Depth:            1
    Number of connections:        0
    Current no. of conns:        0
    Current no. of operations:    0
Server 0x1c138d0 (stg_services_node2)
    Server:                somehost2.spil
    Status:                       Master, Running
    Protocol:            MySQLBackend
    Port:                3306
    Server Version:            5.5.38-35.2-log
    Node Id:            145
    Master Id:            -1
    Slave Ids:            144
    Repl Depth:            0
    Number of connections:        0
    Current no. of conns:        0
    Current no. of operations:    0
However after a few runs it "auto-corrects" itself back to the slave-slave situation. :(

For our crappy old HA solution (MMM) we do require a Master-Master topology and the easiest way to determine which server is master is by looking which one isn't set as read only:
(root@master) [(none)]> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

(root@slave) [(none)]> show global variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

Another option would be that we will use the floating ip address set by our HA solution, but to do so we absolutely need to have the connection to the writer not being monitored as it would end up with the same issue as described above.

Would it be possible to pick up this type of master-determination or make a "write only" connector that doesn't need monitoring?

Thanks!

Regards,
Art

--
Art van Scheppingen
Spil Games
http://engineering.spilgames.com

[Mark Riddoch]

Hi Art,

good to hear from you again.

The monitor we have is not really designed for multi-master, but it would be very easy to put a monitor together that would do what you want.

Mark

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Massimiliano Pinto]

Hi Art,

we are thinking about a new monitor tailored for the multi-master setup: the master assignment would be based on the 'readonly' var set ( it's set to OFF in the 'master').

I quickly coded into a new monitor this new rule and it's ok but we also need to think about replication topology detection, slave ids that may not apply and even correctly assign roles to all servers.

That's the server status of two servers monitored by the the new MultiMaster monitor:

Server 0x1a4cbc0 (server4)

Server: 127.0.0.1

Status:               Master, Slave, Running

Protocol: MySQLBackend

Port: 3309

Server Version: 5.5.35-MariaDB-log

Node Id: 4

Master Id: 29

Slave Ids:

Repl Depth: 5

Server 0x1a4cac0 (server5)

Server: 127.0.0.1

Status:               Slave, Running

Protocol: MySQLBackend

Port: 3310

Server Version: 10.0.11-MariaDB-log

Node Id: 29

Master Id: 4

Slave Ids:

Repl Depth: 5

This quick preview of new monitor code is derived from the mysql monitor where I only changed the rule for Master detection.

The following items are still to fix/rethink:

- Replication Depth; wrong detected or has sense in the MM setup.

- Slave Ids are not set: it may be not an issue.

- The Master has also the Slave status: that may confuse in the MM setup

but at least we have the Master you wanted.

We will share soon with you more details and you might test the new monitor in your setup, that may include two or three servers.

Massimiliano 

[Massimiliano Pinto]

Hi Art,

we are thinking about a new monitor tailored for the multi-master setup: the master assignment would be based on the 'readonly' var set ( it's set to OFF in the 'master').

I quickly coded into a new monitor this new rule and it's ok but we also need to think about replication topology detection, slave ids that may not apply and even correctly assign roles to all servers.

That's the server status of two servers monitored by the the new MultiMaster monitor:

Server 0x1a4cbc0 (server4)

Server: 127.0.0.1

Status:               Master, Slave, Running

Protocol: MySQLBackend

Port: 3309

Server Version: 5.5.35-MariaDB-log

Node Id: 4

Master Id: 29

Slave Ids:

Repl Depth: 5

Server 0x1a4cac0 (server5)

Server: 127.0.0.1

Status:               Slave, Running

Protocol: MySQLBackend

Port: 3310

Server Version: 10.0.11-MariaDB-log

Node Id: 29

Master Id: 4

Slave Ids:

Repl Depth: 5

This quick preview of new monitor code is derived from the mysql monitor where I only changed the rule for Master detection.

The following items are still to fix/rethink:

- Replication Depth; wrong detected or has sense in the MM setup.

- Slave Ids are not set: it may be not an issue.

- The Master has also the Slave status: that may confuse in the MM setup

but at least we have the Master you wanted.

We will share soon with you more details and you might test the new monitor in your setup, that may include two or three servers.

Massimiliano 

On 29 Aug 2014, at 16:19, Mark Riddoch <mark.r...@skysql.com> wrote:

[Art van Scheppingen]

Hi Mark and Massimilano,

Thanks for the quick replies (and development)!

I already talked to one of the developers at Spil what would be possible and he promised to help me out on Monday.

If you can share the change you made we can build the package and test it for you and see if it works with the Multi Master setups.
I agree it is not the ideal situation to determine which machine is the master, but for us it worked in the past five years. ;)

How do you do this with MHA? Is MHA following the Master-Slave paradigm?

Thanks!

Regards,
Art

[bashar....@gmail.com]

Hello,

Is there any updates regard master-master monitor module for maxscale.

i downloaded 1.0.5-stable and it seems doesn't support multi-master.

Regards,
Bash

[Massimiliano Pinto]

Hi,


Multi-Master monitor is not yet included in current MaxScale GA.


You can find it in the develop branch.


Massimiliano

[Bashar Shannak]

Thanks Massimiliano,

i will give it a try

Regards

Bash

[els...@gmail.com]

Hi

I compiled the 1.1.0 develop branch, but had no success with the multi-master setup. Log show, that there are no cluster members. Any idea why?

Kind Regards,
Sven

[Massimiliano Pinto]

Hi Sven,

 the multi master monitor was implemented in this setup & configuration:

---    two servers with circular replication, aka Multi Master setup

---    one MaxScale server

Here the monitor configuration

[Multi Master Monitor]

type=monitor

module=mmmon

servers=server2,server5

user=massi

passwd=massi

detect_stale_master=0

monitor_interval=8000

The MultiMaster monitor does:

- fetches SHOW SLAVES STATUS from all configured servers and gets Master_id, assigned to server->master_id and select @@server_id goes to server->node_id

- if both IO and SQL thread are running, replication is OK and the servers have the SLAVE bits on and replication depth to 1

- the SHOW GLOBAL VARIABLES LIKE 'read_only' statement is sent to all servers.

Rule for Master server assignment:

the server with value read_only value OFF that is also a Slave server takes the server bit MASTER, replication depth=0 and has the SERVER bit cleared.

The MMM external software you have may switch the 'read_only' variable in both server and this way MaxScale will see a new Master

Please note, if both server have  read_only value OFF MaxScale will see two master and no slaves at all.

This corner case should not happen of course: tell me if you are experimenting a such situation and we may think about it, if it makes sense.

There is a new option, that will be available also for MySQL monitor in the next days:

detect_stale_master that has 0 as default value.

If set to 1 it allows to keep the previous detected master and use it again when the replication setup is stopped or not working at all: restarting maxscale or monitor will end up in a scenario without master of course.

This case may be useful in order to keep the master server but it must be used at administrator risk, probably it's more delicate with classical MySQL replication with 1 master and 2 slaves!

With the Multi Master monitor you can use R/W split router and/or readconnection router with options 'slave' or 'master'

Now, let's see it in action!

Here the output of show servers, normal operation

Server 0xbc4760 (server4)

Server: 127.0.0.1

Status:                Master, Running

Protocol: MySQLBackend

Port: 3309

Server Version: 5.5.35-MariaDB-log

Node Id: 4

Master Id: 29

Repl Depth: 0

Server 0xbc4660 (server5)

Server: 127.0.0.1

Status:                Slave, Running

Protocol: MySQLBackend

Port: 3310

Server Version: 10.0.11-MariaDB-log

Node Id: 29

Master Id: 4

Repl Depth: 1

and the output when the replication has been stopped (STOP SLAVE issued in both servers) and detect_stale_master is set to 1:

Server 0xbc4760 (server4)

Server: 127.0.0.1

Status:                Master, Stale Status, Running

Protocol: MySQLBackend

Port: 3309

Server Version: 5.5.35-MariaDB-log

Node Id: 4

Master Id: -1

Repl Depth: 0

Server 0xbc4660 (server5)

Server: 127.0.0.1

Status:                Running

Protocol: MySQLBackend

Port: 3310

Server Version: 10.0.11-MariaDB-log

Node Id: 29

Master Id: -1

Repl Depth: 1

Without that option you will see all servers in Running state, without master at all.

Please note documentation is not yet available: you may follow this example and get back with your feedback, including maxscale configuration and backend details

Maybe you are already aware that a -DBUILD_MMMON=Y should be passed to cmake in order to compile mm_mon.c 

Massimiliano

[els...@gmail.com]

Hi Massimiliano

Thanks for the comprehnsive and quick response. I'll give it a try and come back with feedback next week.

Thanks

Sven

[els...@gmail.com]

Hi Massimiliano

It worked "sort of", bit not sure whether this correctly configured. IMagine the following scenario: 2 mysql servers (port 3307,3308) running in master-master mode (replicating to each other) maxscale is in front and should distribute and/or failover in case a backend goes down.

Here's what I configured to fare:

[MySQL Monitor]
type=monitor
module=mmmon
servers=server1,server2
user=xxx
passwd=xxx
monitor_interval=10000
detect_stale_master=1
...
[Read Connection Router]
type=service
router=readconnroute
servers=server1,server2
user=xxx
passwd=xxx
router_options=master
enable_root_user=1
localhost_match_wildcard_host=1
...
[Read Connection Listener]
type=listener
service=Read Connection Router
protocol=MySQLClient
port=4006
...
[server1]
type=server
address=127.0.0.1
port=3307
protocol=MySQLBackend

[server2]
type=server
address=127.0.0.1
port=3308
protocol=MySQLBackend

Both servers running I see:
Server 0x24d3150 (server1)

Server: 127.0.0.1
Status: Running
Protocol: MySQLBackend

Port: 3307
Server Version: 5.5.41-0ubuntu0.14.04.1-log
Node Id: 3307
Master Id: -1
Repl Depth: -1

Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0

Server 0x24d3040 (server2)

Server: 127.0.0.1
Status: Running
Protocol: MySQLBackend

Port: 3308
Server Version: 5.5.41-0ubuntu0.14.04.1-log
Node Id: 3308
Master Id: 3307
Repl Depth: -1

Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0

If one server goes down, the connect does no longer work. Do you see a problem in my config or is our MM setup wrong?

Thanks in advance

Sven

[Massimiliano Pinto]

Hi Sven,



"show servers” should report


Master, Running
Node Id
MasterId

Slave, Running
NodeId
MasterId


Is your replication setup working?

Do you have both SLQ and IO thread running?

You may even check if the monitor user=xxx is able to issue SHOW SLAVE STATUS.


I suggest connecting via mysql client to your backend from maxscale box, with the monitor user and run:

SHOW SLAVES STATUS

SHOW GLOBAL VARIABLES LIKE ‘read_only'

Thanks.



Massimiliano

[els...@gmail.com]

Hi Massimiliano

show server (shortened) looks like this, each pointing to the other as master.

MaxScale> show servers
Server 0x34bc150 (server1)
Status: Master, Running
Node Id: 3307
Master Id: 3308
Server 0x34bc040 (server2)
Status: Running

Node Id: 3308
Master Id: 3307

I use database "root" user for the monitor for testing to avoid permission problems.

read_only looks like this on both:

server1:
mysql> SHOW GLOBAL VARIABLES LIKE 'read_only' ;

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)

server2:
mysql> SHOW GLOBAL VARIABLES LIKE 'read_only' ;

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)

same for "show slave status":
server 1:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 677
Relay_Log_File: mysqld-relay-bin.000013
Relay_Log_Pos: 823
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 677
Relay_Log_Space: 980
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3308

1 row in set (0.00 sec)

and server2:
ysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000005
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 679
Relay_Log_Space: 3100
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno:
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3307

1 row in set (0.00 sec)

From that I see, that on server2 Slave_SQL_Running is NO. Could this cause the problem?

Thanks for your outstanding support!

Sven

[Massimiliano Pinto]

Hi Sven,


now it’s clear:


1) in server2 Slave_SQL_Running is not running:

STOP SLAVE;
START SLAVE;

If still have issues:

STOP SLAVE;
RESET SLAVE ALL;
CHANGE MASTER ….
START SLAVE;


2) You should enable read_only=ON in one server at least, that will set the Slave role (now you have only the Master)


The read_only= ON | OFF is meant to be done by an external application: MaxScale MM monitor only gets the value,



I guess in a few steps you should get your setup working.



Massimiliano

[els...@gmail.com]

Thanks Massimiliano. The steps succeed and I see now 2 masters, which reflects my setup. The listener works, I can do SQL commands. Good!

MaxScale> show servers
Server 0x34bc150 (server1)

Server: 127.0.0.1
Status: Master, Running

Protocol: MySQLBackend
Port: 3307
Server Version: 5.5.41-0ubuntu0.14.04.1-log

Node Id: 3307
Master Id: 3308

Repl Depth: 0

Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0

Server 0x34bc040 (server2)
Server: 127.0.0.1
Status: Master, Running

Protocol: MySQLBackend
Port: 3308
Server Version: 5.5.41-0ubuntu0.14.04.1-log

Node Id: 3308
Master Id: 3307

Repl Depth: 0

Number of connections: 0
Current no. of conns: 0
Current no. of operations: 0

Failover situation:

Shutting down one server - I end up in Running/Down situation, but the failover didn't happen. SQL commands are refused with connect error. Is this caused by the ready_only variable setting?

MaxScale> show servers
Server 0x34bc150 (server1)

Status: Running
Node Id: 3307
Master Id: -1
Server 0x34bc040 (server2)
Status: Down
Node Id: -1
Master Id: 3307

Sven

[Massimiliano Pinto]

Hi Sven,


the MM maxscale setup requires Master and Slave, states set by read_only variable value.


The setup with two master was not a test case for this monitor development.


The failover also depends on the variable value, that will change value from server1 to server2.

If you have the detect_stale_master=1 you should keep the former master even if the replication is no longer working.


Would you mind giving a try with the variable set to ON in one server?



Massimiliano

[els...@gmail.com]

Hi Massimiliano

Thanks again for the quick reply!
If I set the read_only variable on one server (server1), the status goes to:

MaxScale> show servers
Server 0x1c87150 (server1)
Status: Slave, Running
Node Id: 3307
Master Id: 3308
Server 0x1c87040 (server2)
Status: Master, Running

Node Id: 3308
Master Id: 3307

But if I bring server1 down: I end up with
MaxScale> show servers
Server 0x1c87150 (server1)

Status: Down
Node Id: -1

Master Id: 3308
Server 0x1c87040 (server2)
Status: Running
Node Id: 3308
Master Id: -1

SQL Listener is no longer working...

mysql -u root -P 4006 -p -h 127.0.0.1
Enter password:
ERROR 1045 (28000): failed to create new session

log says:
2015-03-31 12:06:48 Error: there are no cluster members
2015-03-31 12:08:02 Error : Failed to create new routing session. Couldn't find eligible candidate server. Freeing allocated resources.
2015-03-31 12:08:02 Error : Failed to create Read Connection Router session.
2015-03-31 12:08:03 Error : Failed to create new routing session. Couldn't find eligible candidate server. Freeing allocated resources.
2015-03-31 12:08:03 Error : Failed to create Read Connection Router session.
2015-03-31 12:08:48 Error : Monitor was unable to connect to server 127.0.0.1:3307 : "Can't connect to MySQL server on '127.0.0.1' (111)"
2015-03-31 12:08:48 Error : Monitor was unable to connect to server 127.0.0.1:3307 : "Can't connect to MySQL server on '127.0.0.1' (111)"

Sven

[Massimiliano Pinto]

Hi Sven,


the setup is now ok with Master, Slave.


If you stop mysql or shutdown node1(the master), the "external application” should detect it and set read_only=OFF in node2 (Now it’s a slave with read_only=ON).

If you don’t have the external application you should do it manually, basically you do the failover manually.


Please give a try: you should see node1 down and server2 as master and you should be able to get in via mysql client


Massimiliano

[els...@gmail.com]

Hi Massimiliano

I followed th eprocedure, but the monitor just says Running, even I set the read_only to off on the remaining server (server2)

MaxScale> show servers
Server 0x2ded150 (server1)

Status: Down
Node Id: -1

Master Id: -1
Server 0x2ded040 (server2)

Status: Running
Node Id: 3308
Master Id: -1

Therefore, the connection with the client fails.

Sven

[Massimiliano Pinto]

Hi Sven,


currently MM monitor detects failure in replication (broken replication stopped replication) and detect_stale_master=1 option keeps previous may keep previous master.


Stopping mysql or shutdown the server avoid MM monitor to detect the replication:

Example:

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

MM monitor needs both server being slaves and 1 is master via read_only=OFF.


The scenario with a down mysql on a node was not included in the first development.

The MM monitor is still not GA, we’ll add more test cases and code modification for the down state.



We’ll get back to you soon.



Massimiliano

[els...@gmail.com]

Thanks Massimiliano!