What is MySQL Router ?
The MySQL Router handles routing of clients requests to specific servers while providing additional benefits like load balancing and failover. Router will be managing the direct routing to servers sitting as a worker node in between the server and client ( user application ). Also router has the capability to use fabric to provide high availability.
So in this blog we will focus on using and setting up the MySQL Router in between client a and a set of servers.
Setting up the MySQL Router for standalone servers routing :
Step 1 : Downloading package ?
GA version of MySQL Router can be downloaded http://dev.mysql.com/downloads/router/
For example if you are using Linux 64bit operating system you can download router like this
Select platform : Linux Generic
Download : Linux – Generic (glibc 2.17) (x86, 64-bit), Compressed TAR Archive
Step 2 : Extracting the package :
tar -xvf mysql-router-*.tar.gz
Then you can see bin , share, include, lib and run folders
bin : This has the mysqlrouter executable
lib : This has the .so files which will be loaded as part of router
share : This has the doc folder, which has license, readme files and sample config files for router
include : This has the header files
run : “Empty folder”
About router config file…
logger section : Set logging level [FATAL, ERROR, WARNING, DEBUG,and INFO]. If not present INFO level will be used by default.
routing section : Define the address router will listen at and which servers it should manage, with one of two possible modes: “read-only” or “read-write”.If “bind_address” is not present 127.0.0.1 (localhost) will be used by default.
So in my config file I have defined two routing sections, on start of this router, it starts two routing services and listening on ports 7001 and 7002 for client connections.
Step 3 : Preparing a simple config file (sample-router.ini):
level = INFO
bind_address = localhost
bind_port = 7001
destinations = localhost:13002,localhost:13003,localhost:13004
mode = read-only
bind_address = localhost
bind_port = 7002
destinations = localhost:13005,localhost:13006
mode = read-write
Step 4 : Starting the router
a) Before starting the router, the 5 servers present in the config file should be running on required ports
b) ./bin/mysqlrouter –config=/etc/sample-router.ini
c) Connect a mysql client to one of router’s listening port
./bin/mysql --user=root --port 7001 --protocol=TCP
Connecting client to bind_port 7001 will redirect the connection to first server configured in the destinations. i.e. localhost:13002.
To learn more about configuring and different configurations available for router, please go through this post : Configuring-mysql-router