Mysql Server automatically appends into writer hostgroup, while it is only configured to be in reader hostgroup

[Iacovos Mpotsaris]

Hello,

I have set up a proxysql instance, with 3 mysql servers.

The setup is configured with mysql_replication_hostgroups, since the one of the nodes (writer) is part of a galera cluster, and the other 2 (reader1, reader2) are async (slave) nodes.

What I am trying to achieve is:

All writes go to the writer node, all reads go to the reader1 node.

If writer  node fails, do not accept write operations, only reads.

If reader1 node fails, read from reader2.

That's it.

The writer node is  is in the writer hostgoup (2).

The other two nodes, are in the reader hostgroup (3).

# select hostname, hostgroup_id, weight, status from mysql_servers;

+------------------------------+--------------+---------+--------+ | hostname | hostgroup_id | weight | status | +------------------------------+--------------+---------+--------+ | writer | 2 | 1000000 | ONLINE | | reader1 | 3 | 10000 | ONLINE | | reader2 | 3 | 100 | ONLINE | +------------------------------+--------------+---------+--------+

Now, the problem is, that in the proxysql runtime, reader2 node somehow gets appended in the writer hostgroup (2), an I do not understand why:

# select hostname, hostgroup_id, weight, status from runtime_mysql_servers; +------------------------------+--------------+---------+--------+ | hostname | hostgroup_id | weight | status | +------------------------------+--------------+---------+--------+ | writer | 2 | 1000000 | ONLINE | | reader2 | 2 | 100 | ONLINE | | reader1 | 3 | 10000 | ONLINE | | reader2 | 3 | 100 | ONLINE | +------------------------------+--------------+---------+--------+

I stop the writer, run a hostgroup 2 command (e.g. create database), and the database is created on reader2 node.

Note that I cannot set the reader2 node in read_only, as it's being used by other applications / purposes etc.

Any advise will be appreciated

Thank you

[René Cannaò]

Hi,

You probably configured proxysql to set in the writer hostgroup the servers with read_only=0 , and proxysql did what has been configured to do

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e09c5ac9-ed51-48cd-aee7-3c89846d7116n%40googlegroups.com.

[Iacovos Mpotsaris]

Could you kindly advise on how to alter that behavior?

Thank you in advance.

[Alexander Maleev]

Hi, 

Since you can't use read_only check in this particular case, you should not use mysql_replication_hostgroups table. 

So the following is simple possible solution:

1. Clean mysql_replication_hostgroups
2. Set acceptable weights in mysql_servers for readers
3. Set read/write split in mysql_query_rules to redirect read/write traffic to defined reader/writer groups accordingly (example in "Generic Read/Write split using regex" on this documentation page: https://proxysql.com/documentation/proxysql-read-write-split-howto)

Kind regards,

Alex

[Iacovos Mpotsaris]

Hello Alex, 

I was under the false impression that using a replication group configuration was mandatory; thank you for clarifying.

Greatly appreciated,

Iacovos

You received this message because you are subscribed to a topic in the Google Groups "proxysql" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/proxysql/txHHJyHIlYo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/277c1d3f-b6e0-4308-bf2b-6c4c1d8d5815n%40googlegroups.com.