Hostgroups with read/write split in config file (with High Availability for the write node). :-)

[spyros...@gmail.com]

Greetings!

I am beginning to get a grasp of proxySQL and I am trying to configure it through the cfg file so that I have a better and wider view of the variables.

I would like to have it all in a config file if it is possible so I can move it around my test clusters.

What I want to achieve is having a Galera Cluster of three nodes (10.0.0.1, 10.0.0.2, 10.0.0.3) and a proxysql node 

that will let me write to only one of the galera nodes and read with load balancing from all the nodes.

If the node that accepts the writes fails, a new node (one of the remaining 2) should take it's part.

I believe this is what severalnines has as a default config when you install proxysql through cluster-control (like in the image I attached). PS. I would like to do this myself rather than automagically through cluster-control.

So how do I achieve this?

I defined the admin_variables, 

the mysql_variables,

the mysql_users 

and some query rules:

#defines MySQL Query Rules

mysql_query_rules:

(

# {

# rule_id=1

# active=1

# match_pattern="^SELECT .* FOR UPDATE$"

# destination_hostgroup=0

# apply=1

# },

# {

# rule_id=2

# active=1

# match_pattern="^SELECT"

# destination_hostgroup=1

# apply=1

# }

{

rule_id=100

active=1

match_pattern="^SELECT .* FOR UPDATE"

destination_hostgroup=10

apply=1

},

{

rule_id=200

active=1

match_pattern="^SELECT .*"

destination_hostgroup=10

apply=1

},

{

rule_id=300

active=1

match_pattern=".*"

destination_hostgroup=10

apply=1

},

)

 

Then there is the mysql_servers dictionary:

# defines all the MySQL servers

mysql_servers =

(

# {

# address = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

# port = 3306           # no default, required . If port is 0 , address is interpred as a Unix Socket Domain

# hostgroup = 0         # no default, required

# status = "ONLINE"     # default: ONLINE

# weight = 1            # default: 1

# compression = 0       # default: 0

#   max_replication_lag = 10  # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned

# },

# {

# address = "/var/lib/mysql/mysql.sock"

# port = 0

# hostgroup = 0

# },

# {

# address="127.0.0.1"

# port=21891

# hostgroup=0

# max_connections=200

# },

# { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },

# { address="127.0.0.1" , port=21892 , hostgroup=1 },

# { address="127.0.0.1" , port=21893 , hostgroup=1 }

# { address="127.0.0.2" , port=3306 , hostgroup=1 },

# { address="127.0.0.3" , port=3306 , hostgroup=1 },

# { address="127.0.0.4" , port=3306 , hostgroup=1 },

# { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }

        { address="10.0.0.1" , port=3306 , hostgroup=10 },

{ address="10.0.0.2" , port=3306 , hostgroup=10 },

{ address="10.0.0.3" , port=3306 , hostgroup=10 }

{ address="10.0.0.1" , port=3306 , hostgroup=20 },

{ address="10.0.0.2" , port=3306 , hostgroup=20 },

{ address="10.0.0.3" , port=3306 , hostgroup=20 },

)

I believe the above are NOT enough to define read/write hostgroups and/or my rules are lacking.

1. What about the mysql_replication_hostgroups . table?
   
2. Can this table be defined into the cfg file?
   
3. Is it a dictionary like all the others?
4. Should it be configured like this:

replication_hostgroups:(

   writer_hostgroup=10

   reader_hostgroup=20

)

 

5. Where should the read/write split be defined?

 

6. Is it one or multiple directives in different config parts? 

Thank you! 

[René Cannaò]

Hi,

About questions #1 , #2 , #3 , #4 , and I believe aso #6, there is an example here:
https://github.com/sysown/proxysql/blob/v1.4.3/etc/proxysql.cnf#L141-L153

About #5 , read/write split is implemented defining rules. I suggested to read this:
http://proxysql.com/blog/configure-read-write-split

Thanks

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Spyros Vlachos]

Wow! This is exactly what I was looking for. 

I believe this config file should have a link in the github documentation wiki.

Thank you!

[spyros...@gmail.com]

So I came up with the following that I believe does what I want. 

I am not entirely sure about the "mysql_replication_hostgroups" option and if it is needed in galera (i believe not).

datadir="/var/lib/proxysql"

admin_variables=

{

admin_credentials="admin:uberpass" mysql_ifaces="127.0.0.1:6032;10.0.0.14:6032;/tmp/proxysql_admin.sock"

}

mysql_variables=

{ connect_retries_on_failure=10

connect_timeout_server=10000

default_query_timeout=36000000

max_connections=2048

monitor_connect_interval=60000

monitor_connect_timeout=200

monitor_username="proxysql-monitor"

monitor_password="uberpass"

monitor_ping_interval=30000

monitor_replication_lag_timeout=1000

query_retries_on_failure=1000

server_capabilities=45578

server_version="5.1.30"

}

mysql_servers =

( { address="10.0.0.12" , port=3306 , hostgroup=10, max_connections=100, max_replication_lag=10 },

{ address="10.0.0.13" , port=3306 , hostgroup=10, max_connections=100, max_replication_lag=10 },

{ address="10.0.0.15" , port=3306 , hostgroup=10, max_connections=100, max_replication_lag=10 },

{ address="10.0.0.12" , port=3306 , hostgroup=20, max_connections=100, max_replication_lag=10 },

{ address="10.0.0.13" , port=3306 , hostgroup=20, max_connections=100, max_replication_lag=10 },

{ address="10.0.0.15" , port=3306 , hostgroup=20, max_connections=100, max_replication_lag=10 }

)

mysql_users:

(

{

username = "admin"

password = "admin"

default_hostgroup = 10

max_connections = 10000

default_schema="information_schema"

active = 1

}

)

mysql_query_rules:

(

{

rule_id=100

active=1

match_pattern=".*@.*"

destination_hostgroup=10

apply=1

},

{

rule_id=200

active=1

match_pattern="^SELECT.*"

destination_hostgroup=20

apply=0

},

{

rule_id=300

active=1

match_pattern="^SELECT.*FOR UPDATE"

destination_hostgroup=10

apply=1

}

)

scheduler=

(

   {

      id=1

      active=1

      interval_ms=1000

      filename="/var/lib/proxysql/proxysql_galera_checker.sh"    

      arg1="10"    

      arg2="20"

      arg3="1"

      arg4="1"

      arg5="/var/lib/proxysql/proxysql_galera_checker.log"

   }

)

mysql_replication_hostgroups=

(

        {

                writer_hostgroup=10

                reader_hostgroup=20                

        }       

)

1) Do the settings' values seem alright?

2) Do I need mysql_replication_hostgroups in Galera?

Thank you!

[spyros...@gmail.com]

Actually my config doesn't seem to enable writing to another node if the node that is for writes, fails.

Is there something I missed?

Thank you again!

On Monday, September 18, 2017 at 3:39:56 PM UTC+3, spyros...@gmail.com wrote:

[spyros...@gmail.com]

OK! 

It seems like changing the scheduler-> interval_ms to 2000 from 1000 

allows the automatic change of the write node.

Though why is that?

Thank you!

On Monday, September 18, 2017 at 3:39:56 PM UTC+3, spyros...@gmail.com wrote: