MyCAT implements MySQL read/write splitting and mycatmysql read/write

MyCAT implements MySQL read/write splitting and mycatmysql read/write

Before the emergence of MySQL middleware, For MySQL master-slave clusters, If You Want To implement read/write separation, it is generally implemented at the program end, which leads to a problem, that is, the coupling between databases and programs is too high, if the address of my database has changed, I need to modify the address of my program. If the database fails accidentally, it also means that the program is unavailable, this is unacceptable for many applications.

The introduction of MySQL middleware can well decouple the program end and the database. In this way, the program end only needs to pay attention to the address of the database middleware, without knowing how the underlying database provides services.

As the popular MySQL middleware, MyCAT achieves read/write splitting of MySQL Master/Slave clusters, and its configuration is quite simple.

Here, I use three instances to form a MySQL master-slave cluster to verify the read/write splitting function of MyCAT. In fact, one master and one slave can meet the requirements, to verify the sharding function of MyCAT.

The cluster composition is as follows:

Host IP address of the role Host Name

Master mysql-server1 192.168.244.145

Slave mysql-server2 192.168.244.146

Slave mysql-server3 192.168.244.144

Here, we still use the Travelrecord table for testing.

First, edit the schema. xml configuration file of MyCAT. The dataHost configuration information is as follows:

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"                writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">                
Here, two parameters are worth noting: balance and switchType.
Among them, balance refers to the load balancing type. Currently, there are four types of values:
1. balance = "0". read/write splitting is not enabled. 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 put, when the dual-master and dual-slave mode (M1-> S1, m2-> S2, and M1 and M2 are both active and standby). Normally, M2, S1, and S2 are involved in load balancing of select statements.
3. balance = "2", all read operations are randomly distributed on writeHost and readhost.
4. balance = "3": all read requests are randomly distributed to the readhost corresponding to wiriterHost for execution. writerHost does not bear the read pressure.
SwitchType refers to the switching mode. Currently, four values are available:
1. switchType = '-1' indicates no automatic switch.
2. switchType = '1' default value, indicating automatic switch
3. switchType = '2' determines whether to switch based on the MySQL master-slave synchronization status. The heartbeat statement is show slave status.
4. switchType = '3' Based on the MySQL galary cluster switching mechanism (applicable to clusters) (1.4.1), the heartbeat statement is show status like 'wsrep % '.
 
Therefore, the balance = "1" in this configuration file means that hostS1 and hostS2, as stand by writeHost, will be involved in the load balancing of the select statement, which realizes the master-slave read/write splitting, switchType = '-1' means that when the master fails, automatic failover is not performed, that is, hostS1 and hostS2 are not promoted to the master, and only read functions are provided. This avoids the possibility of reading data into slave. After all, a simple MySQL master-slave cluster does not allow reading data into slave unless it is configured with a dual master.
 
Verify read/write splitting
Let's verify it,
Create a Travelrecord table
create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Insert 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(5000001,@@hostname,20160102,100,10);Query OK, 1 row affected, 1 warning (0.01 sec)
Here, a clever method is used to insert the Host Name of the current instance to the user_id, so that you can intuitively observe whether the read/write splitting and the sharding function of MyCAT are performed. This is because my current MySQL version-5.6.26 is statement-based replication by default. If it is row-based replication, this method will not be available.
Query 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 |+---------+---------------+------------+------+------+2 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 |+---------+---------------+------------+------+------+2 rows in set (0.02 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 |+---------+---------------+------------+------+------+2 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-server3 | 2016-01-01 |  100 |   10 |+---------+---------------+------------+------+------+2 rows in set (0.01 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 |+---------+---------------+------------+------+------+
The following two points can be obtained from the output:
1. read/write splitting has been implemented for this configuration, and the read data does not have a master node.
2. Random Distribution of MyCAT is not based on statement, that is, one select statement queries one node, and the other select statement queries another node. It distributes slices. The results of the same select statement are returned by different dataNode statements.
In addition, you can also obtain information about read/write splitting from the MyCAT log, provided that the Log Level of MyCAT is debug. The log information is as follows:

 
Verify that the mater is down and slave can also provide the READ function
For MySQL master-slave clusters, our requirement is that the master node is down, and slave can also provide the READ function.
Let's test it.
First, manually shut down the master database.
[Root @ mysql-server1 ~] #/Etc/init. d/mysqld stop
Log on to MyCAT
[Root @ mysql-server1 ~] # Mysql-utest-ptest-h127.0.0.1-P8066-DTESTDB
Insert 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 |   10 |+---------+---------------+------------+------+------+2 rows in set (0.02 sec)
Data cannot be inserted, but data reading is not affected.
So far, MyCAT has completed the MySQL read/write splitting deployment test.
 
Summary:
1. In fact, the readHost node is configured at the beginning. The configuration is as follows:
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"                writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">                
But there is a problem with this method, that is, after the master node is down, slave cannot provide services, which violates the original intention of the MySQL master-slave cluster.
2. If the transaction mode is enabled, that is, set autocommit = 0, the read in the transaction goes through the master node rather than the slave node.