Shulou(Shulou.com)06/01 Report--

Before the emergence of MySQL middleware, for the MySQL master-slave cluster, if you want to achieve its read-write separation, it is usually implemented on the program side, which brings a problem, that is, the coupling between the database and the program is too high. If the address of my database changes, then my program side has to modify it accordingly. If the database accidentally hangs up, it also means that the program is unavailable, which for many applications. It's not acceptable.

The introduction of MySQL middleware can decouple the program side from the database, so that the program side only needs to pay attention to the address of the database middleware without knowing how the underlying database provides services.

As the current hot MySQL middleware, it is necessary for MyCAT to separate the read and write of MySQL master-slave cluster, and its configuration is also quite simple.

Here, I use three instances to form a MySQL master-slave cluster to verify the read-write separation function of MyCAT. In fact, one master and one slave can be satisfied. The reason for using three is to verify the sharding function of MyCAT.

The cluster is composed as follows:

Role hostname 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 Travelrecord table is still used for testing.

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

Select user ()

Here, there are two parameters to note, balance and switchType.

Among them, balance refers to the type of load balancer. Currently, there are four values:

1. Balance= "0". The read-write separation mechanism is not enabled, and 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 select statements. To put it simply, when the double master and double slave mode (M1-> S1 less M2-> S2, and M1 and M2 are the master and standby of each other), under normal circumstances, M2Magin S1 S2 participates in the 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, there are 4 values:

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

2. Default value of switchType='1', which means automatic switching

3. SwitchType='2' decides whether to switch based on the status of MySQL master-slave synchronization, and the heartbeat statement is show slave status.

4. SwitchType='3' 's MySQL galary cluster-based handover mechanism (suitable for clusters) (1.4.1), and 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 the load balancing of select statements, which realizes the read-write separation of master and slave, and switchType='-1' means that when the master hangs up, there is no automatic switching, that is, hostS1 and hostS2 will not be promoted to master, but only provide read function. This avoids the possibility of writing data into slave; after all, a pure MySQL master-slave cluster does not allow data to be read into slave unless dual master is configured.

Verify read-write separation

Let's verify it.

Create a Travelrecord table

Create table travelrecord (id bigint not null primary key,user_id varchar, traveldate DATE, fee decimal,days int)

Insert data

Mysql > insert into travelrecord (id,user_id,traveldate,fee,days) values; Query OK, 1 row affected, 1 warning (0.02 sec) mysql > insert into travelrecord (id,user_id,traveldate,fee,days) values; Query OK, 1 row affected, 1 warning (0.01 sec)

Here, a tricky method is used, that is, the hostname of the current instance is inserted into user_id, so that you can visually observe whether read and write are separated and the sharding function of MyCAT. The reason for this is that my current version of MySQL-5.6.26 is statement-based replication by default, which is not desirable if it is row-based replication.

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 | 2016 | 10 | +-+ rows in set (0.01 sec) mysql > select * from travelrecord +-+ | id | user_id | traveldate | fee | days | + -- + | 5000001 | mysql-server3 | 2016-01-02 | 2016 | 10 | 1 | mysql-server2 | 2016-01-01 | 100 | 10 | +-+ rows in set (0.02sec) mysql > select * from travelrecord +-+ | id | user_id | traveldate | fee | days | + -- + | 5000001 | mysql-server3 | 2016-01-02 | 2016 | 10 | 1 | mysql-server3 | 2016-01-01 | 100 | 10 | +-+ rows in set (0.01 sec) mysql > select * from travelrecord +-+ | id | user_id | traveldate | fee | days | + -- + | 5000001 | mysql-server3 | 2016-01-02 | 2016 | 10 | 1 | mysql-server3 | 2016-01-01 | 100 | 10 | +-+ 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 | +-+

From the above output, we can draw the following two points:

First, the configuration has achieved the separation of read and write, and the read data has no master node.

Second, the random distribution of MyCAT is not based on statement, that is, one select statement queries one node, and another select statement queries another node. It is distributed for slices, and the results of the same select statement are returned by different dataNode.

Not only that, information about read-write separation can also be obtained from the MyCAT log, of course, provided that the log level of MyCAT is debug. Log related information is as follows:

Verify that mater is dead, and slave can also provide read function.

For the MySQL master-slave cluster, our requirement is that master is down, and slave can also provide read function.

Let's test it.

First of all, shut down the main library artificially

[root@mysql-server1 ~] # / etc/init.d/mysqld stop

Log in to MyCAT

[root@mysql-server1] # mysql- utest-ptest-h227.0.0.1-P8066-DTESTDB

Insert data

Mysql > insert into travelrecord (id,user_id,traveldate,fee,days) values; 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 | 2016 | 10 | +-+ rows in set (0.02 sec)

It is visible that the data cannot be inserted, but the read data is not affected.

At this point, the MyCAT implementation of MySQL read-write separation deployment test is complete.

Summary:

1. In fact, the readHost node was configured at the beginning, which is as follows:

Select user ()

But there is a problem with this approach, that is, after the master is down, slave cannot provide services, which violates the original intention of the MySQL master-slave cluster.

two。 If the transaction mode, set autocommit=0, is turned on, the read within the transaction is the master node, not the slave node.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.