Maxscale is writing on slave with router_options=master (slave/master replication) and listeners stopped

Question

I've configured on 2 servers(srv50/51), one of them is Master and the second one is slave,

Here the configuration of my configuration file /etc/maxscale.cnf :

[Read-Only Service]
type=service
router=readconnroute
servers=server50, server51
user=YYYYYYYYYYYYY
passwd=XXXXXXXXXXXXXX
router_options=slave

[Write-Only Service]
type=service
router=readconnroute
servers=server50, server51
user=YYYYYYYYYYYYY
passwd=XXXXXXXXXXXXXX
router_options=master

[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008

[Write-Only Listener]
type=listener
service=Write-Only Service
protocol=MySQLClient
port=4009

As i understool the router_options look who is the master and send the writing query to the master

Maxscale (via maxadmin) seems to discover the 2 serveur and understand witch one is the Master :

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server51           | 192.168.0.51    |  3306 |           0 | Slave, Running
server50           | 192.168.0.50    |  3306 |           0 | Master, Running
-------------------+-----------------+-------+-------------+--------------------

But even if I connect in Mysql in local on my Maxscale Write-Only Listener port (4009), Listener are in Stopped mode, is it normal ?

MaxScale> list listeners
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name         | Protocol Module    | Address         | Port  | State
---------------------+--------------------+-----------------+-------+--------
Read-Only Service    | MySQLClient        | *               |  4008 | Stopped
Write-Only Service   | MySQLClient        | *               |  4009 | Stopped
MaxAdmin Service     | maxscaled          | *               |  6603 | Running
---------------------+--------------------+-----------------+-------+--------

I've try to create a database in srv51 (slave), and it was created only on srv51, not in srv50.

Is something wrong in my configuration ? It's strange because it's not my first cluster, and on other cluster all write go to the master (but listeners are Running). Do i don't understand well the meaning of "router_options=master" ? How to start listeners ? I prefere to keep the 51 in Write list to detect topology change

===== UPDATE =====

After watching Log file /var/log/maxscale/maxscale1.log I found that my monitor user didn't have the correct password :

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server50, server51
user=MONITOR
passwd=MONITOR_PASS
monitor_interval=10000

I corrected password for user and restarted maxscale, Now everything is running :

MaxScale> list listeners
Listeners.
---------------------+--------------------+-----------------+-------+--------
Service Name         | Protocol Module    | Address         | Port  | State
---------------------+--------------------+-----------------+-------+--------
Read-Only Service    | MySQLClient        | *               |  4008 | Running
Write-Only Service   | MySQLClient        | *               |  4009 | Running
MaxAdmin Service     | maxscaled          | *               |  6603 | Running
---------------------+--------------------+-----------------+-------+--------

But write query are still done on Slave and not on Master

score 0 · Accepted Answer · answered Nov 29 '16 at 12:04

Thanks to MariaDb support, I was trying to connect like this :

mysql -h localhost --port=4009 -u USER -p

But Maxscale & Mysql were installed in the same server, even if Mysql bind port 3306, when you specify 'localhost', the connection is done on Mysql port 3306 and not in Maxscale port 4009, the port is ignore !!

The solution is to connect like this :

mysql -h 127.0.0.1 --port=4009 -u USER -p

or like this :

mysql -h localhost --protocol=tcp --port=4009 -u USER -p

I've try both solution and they works.

The solution about the listener not Running is on update of the question.

score 0 · Answer 2 · answered Sep 11 '17 at 05:40

If writes are done on the slaves, the simplest explanation would be that you're executing writes on the wrong port or your configuration is wrong. To diagnose these problems, enable the info log level by adding log_info=true under the [maxscale] section.

If enabling the info log and inspecting the log files does not provide any clues, I'd suggest opening a bug report on the Maxscale Jira.

Maxscale is writing on slave with router_options=master (slave/master replication) and listeners stopped

2 Answers2