X

MySQL and MySQL Community information

  • October 27, 2015

Concurrent connections to a MySQL router from connector/python.

Guest Author

An application always needs support of concurrent connections to mysql servers and always requires high available system for a loss less data environment.

The MySQL Router is a new building block for high availability solutions. MySQL Router simplifies development of high availability applications by intelligently routing queries to MySQL Servers for increased performance, robustness, and uptime. MySQL Router definitely helps in supporting HA and concurrent connections to servers without any change in the application level coding irrespective of the servers in a simple topology or in a fabric distributed server.

In a practical application environment, the data is fetched from the read-only servers where data is written to read-write servers. So Router is pre-defined to support similar server setup. Also it is required connect to the available/next available server when there is failover and Provide proper logs to the DBA regarding the failover system.

Round-Robin mode: read-only will use a simple round-robin method to go through the list of MySQL Servers. If one server is not available, next server will be tried. When no server is available, the routing is aborted.MySQL Server which could not be contacted are quarantined. They will be regularly checked whether they are again available. When they are, they will be put back into the available servers.

Read-write mode: will try the first MySQL Server. When it can not connect, it will try the next in the list. If the list runs out, the routing is aborted.The first successful server contacted will be saved in memory as first to try for next incoming connections. Note however that this state will not be saved and on next restart of Router the list it tried the way it is was configured in the configuration file.

Concurrent connection setup using MySQL Router:
Please refer to http://dev.mysql.com/doc/mysql-router/en/mysql-router-installation.html to install MySQL Router.

Define the server setup for your application. for example:
-> You can have a one master and multiple slave topology (m1-> sl1,sl2,sl3)
-> You can have a multiple masters setups where application connects to each server for different data.    (server1->sl1,sl2,sl3,server2->sl4,sl5,sl6,server3->sl7,sl8,sl9)                                                        

Define a configuration file for router as below.

#[DEFAULT]
#logging_folder =
#plugin_folder = /usr/local/lib/mysqlrouter
#config_folder = /etc/mysql
#runtime_folder = /var/run

#[logger]
#level = INFO

#[routing:basic_failover1]
# To be more transparent, use MySQL Server port 3306
#bind_port = 7011
#mode = read-write
#destinations = m1:3306 (you can define the host:port for the masters here)

#[routing:basic_failover2]
#bind_port = 7012
#mode = read-write
#destinations = m2:3306 (you can define the host:port for the masters here)

#[routing:basic_failover3]
#bind_port = 7013
#mode = read-write
#destinations = m3:3306 (you can define the host:port for the masters here)

Masters are configured to write data from the application.

#[routing:read_from_slaves]
#bind_port = 7002
#destinations = sl1:port1,sl2:port2,sl3:port3 (you can define the host:port for the masters here)
#mode = read-only

#[routing:read_from_slaves2]
#bind_port = 7003
#destinations = sl4:port4,sl5:port5,sl6:port6 (you can define the host:port for the masters here)
#mode = read-only

#[routing:read_from_slaves3]
#bind_port = 7004
#destinations = sl7:port7,sl8:port8,sl9:port9 (you can define the host:port for the masters here)
#mode = read-only

We can use port 7002 to get data from first topology, 7003 and 7004 to get data from 2nd and 3rd topologies respectively.

Run Router as mysqlrouter -c /path/to/your/mysqlrouter.ini

How can we configure it in connector python ?

It is very simple to configure conn/Python job for a corresponding routing connection. If you are passing the host and port to conn/Python, you need to pass the corresponding binding port of the router.

Example:

    connection = mysql.connector.Connect(host=options.hostname,user="root",passwd ="",port=options.portvalue)  #here port value is 7002 or 7003 or 7004
    cursor = connection.cursor()
    cursor.execute("use test")
        cursor.execute("select * from t1; ")     
        rows = cursor.fetchall()
        for row in rows:
            print row[0]

It is going to connect to the available slave from the destinations. It is round-robin process to get the slave server details. The first server will connect to first router connection, 2nd to second and so on. Once there is no servers available, it will start from the first one.At a failover, if the first is not available, it will move on to 2nd, third,etc.

It depends on the application developer to add corresponding router binding information, to get a connection to required server.In case of Masters, it always connect to one server to write the data. If there is a failover the next master server will be taken for a connection.Application developer can run concurrent connections using thread programming to read data from slaves using the same router port and it increases the performance of data extraction.

Note: As part of testing we have verified 1000 concurrent connections using MySQL Router.

Summary:

MySQl Router is easy to configure and easy to use and it helps in providing high availability and load balancing to an application.


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.