Setting up MySQL Router : Basics

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 :

MySQL Router can be downloaded from
GA release version 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.

More information on configurations can be found in my upcoming post : Configuring-mysql-router

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

 [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  

 

Step 4 : Starting the router

Here we go... all set to start 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 router configurations : Configuring-mysql-router 

To learn more about mysql-high-availability : http://mysqlhighavailability.com/