MySQL :: Configuring MySQL-Router

I assume you have read Setting up MySQL Router before reading this.

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

[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

[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 :

[DEFAULT]
logging_folder =
plugin_folder = /usr/local/lib/mysqlrouter
config_folder = /etc/mysql
runtime_folder = /var/run

[logger]
level = INFO


[fabric_cache]
address = your_fabric_node.example.com:32275
user =

[routing:basic_failover]
bind_port = 7001
mode = read-write
destinations = mysql-server1:3306,mysql-server2

[routing:homepage_reads_fabric]
bind_port = 7002
destinations = fabric+cache:///group/homepage_group?allow_primary_reads=yes
mode = read-only

[DEFAULT]

logging_folder =

plugin_folder = /usr/local/lib/mysqlrouter

config_folder = /etc/mysql

runtime_folder = /var/run

[logger]

level = INFO

[fabric_cache]

address = your_fabric_node.example.com:32275

user =

[routing:basic_failover]

bind_port = 7001

mode = read-write

destinations = mysql-server1:3306,mysql-server2

[routing:homepage_reads_fabric]

bind_port = 7002

destinations = fabric+cache:///group/homepage_group?allow_primary_reads=yes

mode = read-only

Default and logger sections are optional which deals with router log file paths,plugin (.so)paths and log levels.

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.