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 :

RouterBasic

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 :

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

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

RouterStarted

c) Connect a mysql client to one of router’s listening port

ClientConnect

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

About Ganapati Sabhahit

Ganapati Sabhahit has been working with MySQL Database team since May-2015. He is currently employed by Oracle, based in Bangalore. He is Senior Member Technical Staff and member of ComponentsQE team in MySQL,Oracle.Actively involved in testing of development and maintenance releases of MySQL Router.Also worked in group replication testing and bit of Fabric testing. Prior to Oracle,worked in Huawei Technologies involved in-memory database, backup testing.

7 thoughts on “Setting up MySQL Router : Basics

  1. after executing MySQL router script getting below error, kindly help me on this.

    [root@node3 router]# ./bin/mysqlrouter –config=/etc/sample-router.ini

    in log folder showing below error
    2017-05-09 05:59:43 ERROR [7f98e6b0f700] routing:read_write: Setting up TCP service using localhost:7002: Address already in use
    2017-05-09 05:59:43 ERROR [7f98e7510700] routing:read_only: Setting up TCP service using localhost:7001: Address already in use

    1. You can edit the bind_port configuration in your sample-router.ini file to some other port, which is not in use.
      The current ports configured(7001 and 7002) are already in use. So changing that ports configuration should work.

  2. Hi,

    We have InnoDB cluster in place for our Production environment. We would be glad to use MySQL Router. We could not find steps for installing the router for windows elsewhere.

    It will be really helpful if we can get the steps to install router on windows server 2012.

  3. Ganapati,

    Thanks for the reply.. We have 3 application servers & can we install router in all 3 or how can this be achieved. We would like to have our portal available irrespective of any server’s downtime.

  4. Hi Ganapathi,

    I have configured MySQL Router in one of my Application server and below are the connections written in my router’s mysqlrouter.conf file.

    [metadata_cache:clusternew]
    router_id=4
    bootstrap_server_addresses=mysql://192.8.17.39:3306,mysql://192.8.17.69:3306,mysql://192.8.17.68:3306
    user=mysql_router4_7cnds8xssu7h
    metadata_cluster=clusternew
    ttl=300

    [routing:clusternew_default_rw]
    bind_address=0.0.0.0
    bind_port=6446
    destinations=metadata-cache://clusternew/default?role=PRIMARY
    mode=read-write
    protocol=classic

    [routing:clusternew_default_x_rw]
    bind_address=0.0.0.0
    bind_port=6447
    destinations=metadata-cache://clusternew/default?role=PRIMARY
    mode=read-write
    protocol=x

    We would like to know what is the address to be mentioned in the application’s config file so that it will redirect the user request to available DB server.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter. * Time limit is exhausted. Please reload CAPTCHA.