Sunday, April 24, 2016

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

-- 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 respectively, the 'destinations' can be set as follows.

       destinations =,

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.

Saturday, April 16, 2016

Google Charts: a great way to draw interactive and realtime charts on JSON data requested from REST API

Data visualization is helpful not only  for data analysts but also for system administrators. Data analysts use data visualization to untangle correlated relationship between some features, while system administrators use data visualization to monitor the system status and users' status. Many plotting packages are developed to visualize data, such as ggplot2 in R and matplotlib in Python, which are very powerful to make elegant charts. But the charts made by those packages are STATIC. In some cases, we want to make charts based on users' interaction or refresh charts automatically in order to trace the latest updated data in the data set. Here Google Charts can play the role. Another benefit Google Charts provides is to showcase the work with charts on the web! Moreover, Google Charts make it easy to use AJAX to attach JSON data retrieved from a REST API to the chart data table.

In this post, I will give a complete example how to use Google Charts to draw a realtime chart, which reads data from a REST API and automatically refresh itself. In order to use Google Charts, the familiarity of the language HTML, JavaScript, and CSS will be required. I think the guide of Google Charts make it very easy for new learners to approach the technique. For people who feel hard following the guide, some tutorial resources have been provided at the end of the post to help catch up the required basic concepts.

The code of the example can be found in my GitHub. My working flow on this program is as follows.

  1. Prepare a REST API which provides JSON data to serve as chart data for visualization. A public GitHub API mentioned in this post is used. It contains some data about issues related to ggplot2 repository on GitHub. We can create our own REST API, as mentioned in my last post.
  2. Use AJAX to read JSON data from REST API to a JavaScript variable, which will be transformed to the format of Google Chart data table. 
  3. Set up Google Chart refresh effect by using the function setRefreshInterval()
  4. Set up Google Chart option animation
  5. Draw the chart. 
For people who haven't had experiences with HTML, CSS, JavaScript, and AJAX, I would recommend the following tutorial resources. Three weeks ago, I only knew a little bit about HTML and absolutely nothing about CSS, JavaScript, AJAX, or the concept of front end development. I referred to the following tutorials, grasped all of them in three weeks, and finished my task of user interface development from work. 
For people who want to learn more about REST API development, Python full-stack web development, and HTTP, the above resources will also be helpful. 

Last but not least, I would like to share a little bit about my motivation to learn the web development. After reading many posts and studying current successful business applications, my feeling is that machine learning and web development go hand by hand. Many machine learning systems are deployed on web applications. A complete web solution may contain some machine learning algorithms as a specific problem solution. I guess that makes sense by just thinking about what Amazon does.