Configuring MySQL-Router

MySQL Router

I assume you have read Setting up MySQL Router before reading this.

So we start our First example with the config file used in Setting up MySQL Router sample-router.ini

 About different mode options :
[routing:read_only] :
If you connect a client to read-only routing service i.e. port  7001 , router will redirect the first connection to the first available server in the list, i.e. 13002. If you connect another client to the same bind_port, router will redirect the connection to the next available server in the list, i.e. 13003. another client request to same bind port will be redirected to next available server in the list , i.e 13004 and then router goes back to 13002.
So, when “mode = read-only” , client connection requests will be served in round-robin fashion. If next available server in the configured list in not available (may be its not running or its down), then following available server will be considered to serve the client requests.

[routing:read_write] :
If you connect a client to read-write routing service i.e. port  7002 , router will always redirect the connection to the first available server in the list, i.e. 13005. If you connect another client to the same bind_port, router will again redirect the connection to first available server in the list, i.e. 13005.
So, when “mode = read-write” , always first available server will be returned to the client. If the first available server in the configured list is not available (may be its not running or its down), then next available server in the list will be considered to serve the client requests.
About bind_address and bind_port :
Bind address is an optional parameter ,if you don’t set it 127.0.0.1 will be assumed. bind_port is a mandatory config parameter, not specifying this will throw an error during the router start. Also a single bind_port can be used for multiple bind_address values.
For Example :
bind_address = localhost
bind_port = 7002

Now we start our Second Example with the <tar-package>/share/doc/mysqlrouter/sample_mysqlrouter.ini file which is part of the downloaded package :

Default and logger sections are optional which deals with router log file paths,plugin (.so)paths and log levels.
Fabric cache section : It takes address where the state store is running and the user for connecting to the fabric setup. When the router is started it prompts for the corresponding password.
PasswordRouter
Fabric routing section : In this, a fabric group is configured as destination. So based on one of the 2 possible modes “read-only” or “read-write” , it will redirect the connections to primary(read-write) or secondaries(read-only) respectively.
If “allow_primary_reads=yes” is appended to the destinations uri with read only mode, then primary also will be considered in the list of servers available to router for processing client requests. “allow_primary_reads” option does not make any sense if the configured mode is read-write.

So in the above example configurations, connecting a client to bind port 7002 , will return a connection to the server available in the group named “homepage_group”. In our example allow_primary_reads=yes, so it can return any server from the secondaries or primary. If only one slave is present in the group and “allow_primary_reads=no”, then the same server will be handling the client requests. Connecting a client to the port 7001 will always return the first server available in the destinations list. i.e. mysql-server1:3306. If this first server is not available then it will return mysql_server2.

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.

6 thoughts on “Configuring MySQL-Router”

  1. Pingback: Setting up MySQL Router : Basics | MySQL High Availability

  2. Hi, I’m configuring MySQL Router with fabric, and seems to work fine, my only question is, how can I start MySQL Router and leave running even when I log off from the server? The only way I can start it is with
    mysqlrouter –config=/etc/mysqlrouter/mysqlrouter.ini
    So I can put my password, but then the router will only works with that connection established, is there a workaround?
    Thanks

    1. Hi Mauricio,

      I would strongly encourage you to try out InnoDB clusters if you haven’t yet:
      – http://mysqlserverteam.com/mysql-innodb-cluster-new-5-7-17-preview-release-on-labs-mysql-com/
      – http://mysqlserverteam.com/mysql-innodb-cluster-a-hands-on-tutorial/

      That being said, this is how I had previously setup MySQL Router 2.0 to run as a daemon on my test machines–which are systemd (EL7) based–so that you have a working reference implementation. In this test setup, the username:password combination for Fabric was admin:admin


      [root@hanode1 ~]# cat /etc/mysqlrouter/mysqlrouter.ini
      #
      # MySQL Router configuration file
      #
      # Documentation is available at
      # http://dev.mysql.com/doc/mysql-router/en/

      [DEFAULT]
      logging_folder = /var/log/mysqlrouter/
      plugin_folder = /usr/lib64/mysqlrouter
      runtime_folder = /var/run/mysqlrouter
      config_folder = /etc/mysqlrouter

      [fabric_cache:lab]
      address = 192.168.1.91
      user = admin

      # Routing into my Fabric managed cluster
      # route to the current READ_WRITE / PRIMARY node
      [routing:lab]
      bind_address = 127.0.0.1:6600
      destinations = fabric+cache://lab/group/hagroup
      mode = read-write

      # Routing into my Group Replication cluster
      # route to the first available node
      [routing:grouprepl]
      bind_address = 127.0.0.1:7700
      destinations = 192.168.1.92:3306,192.168.1.93:3306,192.168.1.94:3306
      mode = read-write

      [logger]
      level = debug

      [root@hanode1 ~]# cat /usr/lib/systemd/system/mysqlrouter.service
      [Unit]
      Description=MySQL Router
      After=syslog.target
      After=network.target
      After=mysqlfabric.service

      [Service]
      Type=simple
      User=mysql
      Group=mysql

      PIDFile=/var/run/mysqlrouter/mysqlrouter.pid

      ExecStart=/bin/bash -c "echo 'admin' | mysqlrouter --config=/etc/mysqlrouter/mysqlrouter.ini"

      Restart=on-failure

      RestartPreventExitStatus=1

      PrivateTmp=true

      [Install]
      WantedBy=multi-user.target

      [root@hanode1 ~]#

      Best Regards,

      Matt

      1. Hi Matt. Thanks a lot, yesterday I’ve thought something similiar, starting it like mysqlrouter < pass.txt but this is better, because I'll be able to use the service. About InnoDB Cluster sadly we're still in MySQL 5.6, besides we use MyISAM tables occasionally.

        Thanks again for your answer.
        Mauricio

  3. Hi,

    Thanks for the article.

    MySQL router cannot figure out when server(s) was(were) recovered after restart/shutdown/etc.

    It gives “Can’t connect to MySQL server” even when all the servers already UP.

    Any idea?

    Thanks,
    John

  4. Thanks For this clarification, But i don’t know how i connect my Php Application to the router. As it defines two port one is for read and other is for read and write.
    Now the issue in i have developed an application and need to connect with Cluster but how.
    My application is written in PHP using Yii2 Application.

Leave a Reply