X

MySQL and MySQL Community information

  • October 28, 2015

Multiple MysqL Routers setup for an application

Guest Author
The MySQL Router is a new building block for high availability solutions. MySQL Router simplifies development of high availability applications by intelligently routing queries to MySQL Servers for increased performance, robustness, and uptime. MySQL Router also helps in load balancing of read or write transactions to a database with support of multiple router connections to multiple clients.

There are various use cases where you can use multiple router connections.
- Write to a DB can be routed using one router and read can be happened by multiple router connections to have a better performance and stability
- If client and servers are in specific region , we can use multiple routers in the specific region
- Different warehouse can be handled by multiple routers considering the load.

These are few examples usecases. You can define your own usecase and check if it suites your environment. Also you can use this in your existing application

Multiple Router setup:
Below is a simple example of how to create multiple router setup for a set of warehouses.
Define MySQL Router configuration files using below template. Note that the router binding port should be different.

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

#[logger]
#level = INFO

#[routing:read_from_servers1_warehouse1]
#bind_port = 7002
#destinations = sl1:port1,sl2:port2,sl3:port3 (you can define the host:port for the masters here)
#mode = read-only

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

#[logger]
#level = INFO

#[routing:read_from_servers2_warehouse2]
#bind_port = 7003
#destinations = sl4:port4,sl5:port5,sl6:port6 (you can define the host:port for the masters here)
#mode = read-only

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

#[logger]
#level = INFO

#[routing:read_from_servers3_warehouse3]
#bind_port = 7004
#destinations = sl7:port7,sl8:port8,sl9:port9 (you can define the host:port for the masters here)
#mode = read-only

Please refer to http://dev.mysql.com/doc/mysql-router/en/mysql-router-installation.html  to install MySQL Router

You can define your log files path in the ini file. Run Routers in the background as

nohup mysqlrouter -c config1.ini &
nohup mysqlrouter -c config2.ini &
nohup mysqlrouter -c config3.ini &

This can be done in different machines,regions,etc depending on the use cases.The corresponding router ports can be used in application programs to connect to the servers. So you can connect many no of clients to a router port to get the information and with support of high availability.

Summary:
Multiple routers setup is required in simple to complex applications for a better performance, load balancing and stability.






Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.