Mycat realization of MySQL read/write separation

MySQL middleware before the advent, for MySQL master and slave cluster, if you want to implement its read and write separation, is generally implemented in the terminal, this brings a problem, that is, the database and program coupling is too high, if my database address changes, then I will also make corresponding changes to the terminal If the database is accidentally hung out, it also means that the program is not available, which is unacceptable to many applications.

The introduction of MySQL middleware is a good way to decouple the terminal and the database, so that the terminal only needs to focus on the address of the database middleware, without knowing how the underlying database provides the service.

As the hottest MySQL middleware, Mycat realizes that the read and write separation of MySQL master-slave cluster should be justified, and its configuration is quite simple.

Here, I use three instances to compose MySQL master-slave cluster, to verify the Mycat read and write separation function, in fact, a master from can be satisfied, the reason used three, is to verify the Mycat shard function.

The cluster consists of the following:

Role Host name host IP

Master Mysql-server1 192.168.244.145

Slave Mysql-server2 192.168.244.146

Slave Mysql-server3 192.168.244.144

Here, the test is done using the Travelrecord table.

First edit the Mycat configuration file Schema.xml, the configuration information about Datahost is as follows:

<datahost name= "Localhost1"  maxcon= " mincon="  balance= "1"                  writetype= "0"  dbType= "MySQL " dbdriver=" native " switchtype="-1 "  slavethreshold=" >                 

In this, there are two parameters to note, balance and switchtype.

Among them, balance refers to the load balancer type, the current value has 4 kinds:

1. balance= "0", does not open the read and write separation mechanism, all read operations are sent to the currently available writehost.

2. Balance= "1", all readhost and stand by writehost participate in the load balancing of the SELECT statement, simply speaking, when the dual master dual slave mode (M1->S1,M2->S2, and M1 and M2 are mainly prepared), under normal circumstances, M2,S1,S2 all participate in load balancing of the SELECT statement.

3. Balance= "2", all read operations are randomly distributed on writehost, Readhost.

4. Balance= "3", all read requests randomly distributed to wiriterhost corresponding readhost execution, Writerhost does not bear reading pressure

Switchtype refers to the mode of switching, the current value also has 4 kinds:

1. switchtype= '-1 ' means no automatic switching

2. switchtype= ' 1 ' default value, indicating automatic switching

3. Switchtype= ' 2 ' is based on the state of MySQL master-slave synchronization to decide whether to switch, heartbeat statement for show slave status

4. Switchtype= ' 3 ' based on the switch mechanism of MySQL galary cluster (for cluster) (1.4.1), the heartbeat statement is show status like ' wsrep% '.


Therefore, the balance= "1" in the configuration file means that hostS1 and hostS2 as stand by Writehost will participate in load balancing of the SELECT statement, which enables the master-slave read-write separation, switchtype= '- 1 ' means that when the Lord is hung up, no automatic switching is made, i.e. hostS1 and hostS2 are not promoted and still provide only read functionality. This avoids the possibility of writing data into Slave, after all, a simple MySQL master-slave cluster does not allow data to be read into slave unless it is configured with dual master.


Verifying read-Write separation

Here to verify that

Create a Travelrecord table
CREATE TABLE Travelrecord (ID bigint NOT NULL primary key,user_id varchar, traveldate DATE, fee decimal,days int);
Inserting data
mysql> INSERT INTO Travelrecord (id,user_id,traveldate,fee,days) VALUES (1,@ @hostname, 20160101,100,10); Query OK, 1 row affected, 1 Warning (0.02 sec) mysql> insert into Travelrecord (id,user_id,traveldate,fee,days) VALUES (5 000001,@ @hostname, 20160102,100,10); Query OK, 1 row affected, 1 warning (0.01 sec)

In this case, a trickery method is used, that is, the host name of the current instance is inserted into the user_id, which can visually observe whether the read-write is detached and the Mycat shard function. The reason for this is that my current MySQL version -5.6.26 defaults to statement-based replication, which would be undesirable if row-based replication was used.

Querying data
mysql> select * from travelrecord;+---------+---------------+------------+------+------ +| id    | user_id       | traveldate  | fee  | days |+---------+---------------+------------+------+------+|      1 | mysql-server2 | 2016-01-01 |  100 |    10 | |  5000001 | mysql-server3 | 2016-01-02 |  100 |    10 |+---------+---------------+------------+------+------+rows in set  (0.01 sec) MySQL > select * from travelrecord;+---------+---------------+------------+------+------+|  id    | user_id       | traveldate  | fee  | days |+---------+---------------+------------+------+------+|  5000001 | mysql-server3 | 2016-01-02 |  100 |   10 | |      1 | mysql-server2 | 2016-01-01 |  100 |    10 |+---------+---------------+------------+------+------+rows in set  ( 0.02&NBSP;SEC) mysql> select * from travelrecord;+---------+---------------+------------ +------+------+| id    | user_id       |  traveldate | fee  | days |+---------+---------------+------------+------+- -----+| 5000001 | mysql-server3 | 2016-01-02 |  100 |    10 | |      1 | mysql-server3 | 2016-01-01 |  100 |    10 |+---------+---------------+------------+------+------+rows in set  ( 0.01&nbSP;SEC) mysql> select * from travelrecord;+---------+---------------+------------+------ +------+| id    | user_id       |  traveldate | fee  | days |+---------+---------------+------------+------+------+ | 5000001 | mysql-server3 | 2016-01-02 |  100 |    10 | |      1 | mysql-server3 | 2016-01-01 |  100 |    10 |+---------+---------------+------------+------+------+rows in set  ( 0.01&NBSP;SEC) mysql> select * from travelrecord;+---------+---------------+------------ +------+------+| id    | user_id       |  traveldate | fee  | days |+---------+---------------+------------+------+- -----+|     1 | mysql-server2 | 2016-01-01 |  100 |    10 | |  5000001 | mysql-server2 | 2016-01-02 |  100 |    10 |+---------+---------------+------------+------+------+

From the above output, the following two points can be drawn:

First, the configuration has been implemented read and write separation, read out the data does not have the master node.

Second, the random distribution of mycat is not based on statement, that is, a SELECT statement queries one of the nodes, and the other SELECT statement queries another node. It is distributed against the slices, and the result of the same SELECT statement is returned with a different datanode.

In addition, information about read and write separations can be obtained from the Mycat log, assuming that the Mycat log level is Debug. Log related information is as follows:
Verify that the Mater is hung, slave can also provide read function

For MySQL master-slave cluster, our requirement is master hang up, slave also can provide read function.

Let's test it here.

First, artificially shutting down the main library

[[email protected] ~]#/etc/init.d/mysqld stop

Login Mycat

[Email protected] ~]# Mysql-utest-ptest-h127.0.0.1-p8066-dtestdb

Inserting data
mysql> INSERT INTO Travelrecord (id,user_id,traveldate,fee,days) VALUES (10000001,@ @hostname, 20160103,100,10); ERROR 1184 (HY000): Connection refusedmysql> select * from travelrecord;+---------+---------------+------------+--- ---+------+| ID | user_id | TravelDate | Fee |       Days |+---------+---------------+------------+------+------+| 1 | Mysql-server2 |  2016-01-01 |   100 | 10 | | 5000001 | Mysql-server3 |  2016-01-02 |   100 | |+---------+---------------+------------+------+------+rows in Set (0.02 sec)
Visible cannot insert data, but does not affect reading data.

At this point, mycat implementation of MySQL read-write separation deployment test completed.


Summarize:

1. In fact, the first configuration is the Readhost node, configured as follows:
<datahost name= "Localhost1"  maxcon= " mincon="  balance= "1"                  writetype= "0"  dbType= "MySQL " dbdriver=" native " switchtype="-1 "  slavethreshold=" >                 
But this way there is a problem, that is, master hangs, Slave also can not provide services, and this violates the original purpose of MySQL master and slave cluster.

2. If transaction mode is turned on, set autocommit=0, the master node is read within the transaction, not from the node.





Mycat realization of MySQL read/write separation