MySQL Router and High Availability

Introduction

High availability of MySQL servers can be achieved through 'MySQL Fabric' which is basically a system for managing a farm of servers. To deploy Fabric , there has been the requirement to have connectors which are Fabric aware. The requests for supporting basic connectors are frequent and this is where MySQL Router comes into the picture. With Router, connectors no longer need to be Fabric aware. They can communicate with Fabric through Router, just as they communicate with standalone MySQL servers.

In this blog, we shall focus on how exactly we can integrate Fabric with Router.

Fabric Cache

MySQL Router includes the 'Fabric Cache' plugin which essentially caches high-availability group and shard information from Fabric. This is similar to how a Fabric-aware connector would cache such information. This Router plugin thus maintains the connection between a client application and Fabric.

Hands On

Let us start by setting up high-availability groups of mysql servers with the help of Fabric :

a) MySQL Fabric is currently shipped as part of MySQL Utilities, which can be downloaded from : https://dev.mysql.com/downloads/utilities .

b) Make sure that a set of mysql servers is running. (In this example we assume the servers are running on the local machine, and server ports start at 13002)

c) Configure the fabric.cfg file with the proper username and password values to start. Make sure the storage section is pointed to a valid mysql server :  

  [storage]   
   address = localhost:13002  

d) Start Fabric as :

 mysqlfabric manage setup  
 mysqlfabric manage start  

e) Create a high availability group of mysql servers, and promote one server to be the primary :

 mysqlfabric group create my_group1  
 mysqlfabric group add my_group1 localhost:13003  
 mysqlfabric group add my_group1 localhost:13004  
 mysqlfabric group add my_group1 localhost:13005  
 mysqlfabric group promote my_group1 –slave_id=localhost:13003  

f) Execute a group lookup_servers command to make sure that the HA group is setup properly.

Now we can proceed to setup MySQL Router : 

a) Download MySQL Router from http://dev.mysql.com/downloads/router/ and install.

b) Create a configuration file for router , say as follows :

 [fabric_cache]   
 address = 127.0.0.1:32275   
 user = admin   
   
 [routing:fabric_read_write]   
 bind_address = 127.0.0.1:7002   
 destinations = fabric+cache:///group/my_group1  
 mode = read-write   
   
 [routing:fabric_read_only]   
 bind_address = 127.0.0.1:7003   
 destinations = fabric+cache:///group/my_group1  
 mode = read-only   

Here, the address 127.0.0.1:32275 and user admin correspond to [protocol.mysql] section of the fabric config file.

Two routing services 'fabric_read_write' and 'fabric_read_only' have been defined. Client applications which use bind_address=127.0.0.1:7002 would be connected to the primary server (which is read_write) of the HA group : 'my_group1' and those which use bind_address=127.0.0.1:7003 would be connected to the secondary servers (which are read_only) of the HA group : 'my_group1' in a round-robin fashion.

c) Now as the Router is installed and the config file is ready, we are good to start it and connect to the Fabric group.

Start Router :

 ./mysqlrouter --config=<path to router config file>  

d) Now, we use a mysql client to connect to the bind_address of the router, which would make the connection to the Fabric group.

 ./mysql --host=127.0.0.1 --port=7002 (Will be routed to the primary server of my_group1)

 ./mysql --host=127.0.0.1 --port=7003 (Will be routed to one secondary server of my_group1) 

 ./mysql --host=127.0.0.1 --port=7003 (Will be routed to the other secondary server of my_group1) 

In this way, client applications can communicate with a highly-available farm of mysql servers using Router, without Fabric aware connectors.

To learn more about MySQL High-availability solution, visit : http://mysqlhighavailability.com/

To learn more about MySQL Router, visit : http://using-mysqlrouter.blogspot.in/