MySQL Router: A High Availability Solution for MySQL

Nowadays more and more companies start to use cloud computing system and services. From the perspective of cloud service users, they want their service level guaranteed. Take the database cloud service as the example. The users expect the database on the cloud available whenever they use it. But there can always be some reason that may cause the database not available, such as the network failure and database exceptional shutdown. So a big problem for cloud service providers is how to guarantee the service level declared in the service level agreement. High available solutions can solve that problem. "High Availability" is a fundamental and important concept in the cloud computing. It means the service/server on the cloud is available/up at a high rate, like 99.99% time of a given year.

In this post, I would like to introduce one high availability solution for MySQL -- MySQL Router. First, I will describe what it is. And then I will show how to use it.

What is MySQL Router?

MySQL Router is a middleware between MySQL client and MySQL server, which redirects a query from the MySQL client to a MySQL server. What we usually do is to use a MySQL client directly to connect to a MySQL server. So why do we want this middleware? The reason is, with MySQL Router, we can set up more than one backend MySQL servers. When one server is down, MySQL Router can automatically redirect the query to another available server, which helps guarantee the service level.

How to use MySQL Router?

1. Set up MySQL backend servers.

What I did is to launch two virtual machines with Ubuntu Linux system and install a MySQL server on each virtual machine. Another setup scenario is introduced in MySQL Router tutorial. In real practice, the MySQL backend servers are set in the replication topology.

Command of installing MySQL server on Ubuntu:
      shell>  sudo apt-get install mysql-server

2. Set up MySQL Router.

-- Download the MySQL APT repository.
shell> wget http://dev.mysql.com/get/mysql-apt-config_0.7.2-1_all.deb

-- Install the MySQL APT repository.
shell> sudo  dpkg -i mysql-apt-config_0.7.2-1_all.deb

-- Update the APT repository.
shell> sudo apt-get update

-- Install MySQL Router.
shell> sudo apt-get install mysql-router

-- Install MySQL Utilities.
shell> sudo apt-get install mysql-utilities

3. Configure MySQL Router.

The configuration is under /etc/mysqlrouter/mysqlrouter.ini.

The configuration instructions can be found here. The most important part is the option 'destinations' in the [routing] section. Its format is host_ip:port. For example, if there are two MySQL servers on the host 192.168.25.200/201 respectively, the 'destinations' can be set as follows.

       destinations = 192.168.25.200:3306, 192.168.25.201:3306

4. Start MySQL Router.

      shell> sudo mysqlrouter -c /etc/mysqlrouter/mysqlrouter.ini

5. Set up MySQL client to connect to MySQL Router.

A python script will be used here as MySQL client. Based on the documentation, the client must be executed on the same machine where the MySQL Router is running.

(About 'cox' statement: -- change the user and password to your own in the 'cnx' statement.
 -- By default, the host and port of MySQL Router are 'localhost' and 7001,   unless they are configured by the option 'bind_address' in mysqlrouter.ini. )
   
      shell> python
      >>> import mysql.connector
      >>> cnx = mysql.connector.connect(host='localhost', port=7001, user='root', password='secret')
      >>> cur = cnx.cursor()
      >>> cur.execute("SHOW DATABASES")
      >>> print cur.fetchall()

6. Play with MySQL Router.

-- Create different databases on each MySQL server under the same user specified in the 'cnx' statement.
-- Execute the code in Step 5. And check the output.
-- Stop a MySQL server, re-execute code from 'cnx', and examine the difference.

More details can be found in the part of Testing the Router.