Configuring MySQL-Router

So we start our First example with the config file used in Setting up MySQL Router sample-router.ini

1

2

3

4

5

6

7

8

9

10

11

12

13

14

[logger]

level=INFO

[routing:read_only]

bind_address=localhost

bind_port=7001

destinations=localhost:13002,localhost:13003,localhost:13004

mode=read-only

[routing:read_write]

bind_address=localhost

bind_port=7002

destinations=localhost:13005,localhost:13006

mode=read-write

About different mode options :[routing:read_only] :
If you connect a client to read-only routing service i.e. port 7001 , router will redirect the first connection to the first available server in the list, i.e. 13002. If you connect another client to the same bind_port, router will redirect the connection to the next available server in the list, i.e. 13003. another client request to same bind port will be redirected to next available server in the list , i.e 13004 and then router goes back to 13002.
So, when “mode = read-only” , client connection requests will be served in round-robin fashion. If next available server in the configured list in not available (may be its not running or its down), then following available server will be considered to serve the client requests.

[routing:read_write] :
If you connect a client to read-write routing service i.e. port 7002 , router will always redirect the connection to the first available server in the list, i.e. 13005. If you connect another client to the same bind_port, router will again redirect the connection to first available server in the list, i.e. 13005.
So, when “mode = read-write” , always first available server will be returned to the client. If the first available server in the configured list is not available (may be its not running or its down), then next available server in the list will be considered to serve the client requests.
About bind_address and bind_port :
Bind address is an optional parameter ,if you don’t set it 127.0.0.1 will be assumed. bind_port is a mandatory config parameter, not specifying this will throw an error during the router start. Also a single bind_port can be used for multiple bind_address values.
For Example :
bind_address = localhost
bind_port = 7002

Now we start our Second Example with the <tar-package>/share/doc/mysqlrouter/sample_mysqlrouter.ini file which is part of the downloaded package :

Fabric cache section : It takes address where the state store is running and the user for connecting to the fabric setup. When the router is started it prompts for the corresponding password.

Fabric routing section : In this, a fabric group is configured as destination. So based on one of the 2 possible modes “read-only” or “read-write” , it will redirect the connections to primary(read-write) or secondaries(read-only) respectively.

If “allow_primary_reads=yes” is appended to the destinations uri with read only mode, then primary also will be considered in the list of servers available to router for processing client requests. “allow_primary_reads” option does not make any sense if the configured mode is read-write.

So in the above example configurations, connecting a client to bind port 7002 , will return a connection to the server available in the group named “homepage_group”. In our example allow_primary_reads=yes, so it can return any server from the secondaries or primary. If only one slave is present in the group and “allow_primary_reads=no”, then the same server will be handling the client requests. Connecting a client to the port 7001 will always return the first server available in the destinations list. i.e. mysql-server1:3306. If this first server is not available then it will return mysql_server2.

About Ganapati Sabhahit

Ganapati Sabhahit has been working with MySQL Database team since May-2015. He is currently employed by Oracle, based in Bangalore. He is Senior Member Technical Staff and member of ComponentsQE team in MySQL,Oracle.Actively involved in testing of development and maintenance releases of MySQL Router.Also worked in group replication testing and bit of Fabric testing. Prior to Oracle,worked in Huawei Technologies involved in-memory database, backup testing.
View all posts by Ganapati Sabhahit →