Read and write separation of MySQL based on MYCAT implementation

Mycat is equivalent to a scheduler, with database sharding function, itself does not store data, its function is to the back-end of the real database data, data division and data integration, easy to manage

How-mycat-read&&write Separation

Now let's make the database read/write separated by Mycat

other configuration files in this experiment, see mycat simple configuration and high availability of MySQL-based master-slave replication

define read, write nodes

Vim Schema.xml

<table name= "Employee" primarykey= "ID" datanode= "DN1,DN2"
rule= "Sharding-by-intfile"/>
<datahost name= "server33.lalala.com" maxcon= "+" mincon= "10"balance= "1"
44writetype= "0"Dbtype= "MySQL" dbdriver= "native"switchtype= "1"Slavethreshold= ">"
<!--can have multi write hosts--
47<writehostHost= "hostM1" url= "server33.lalala.com:3306" user= "Mycat"
password= "mycat+007" >
<!--can have multi read hosts--
50
51<readhostHost= "hostS1" url= "server55.lalala.com:3306" user= "Mycat"
password= "mycat+007" >
53</readHost><!--can have multi read hosts--
<!--<writehost host= "hostM2" url= "localhost:3316" user= "root" password= "123456"/>--
55</writeHost>
</dataHost>
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "1" slavethreshold= ">"

Parameter description:

Balance= "1″writetype=" 0″switchtype= "1″

Balance

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

2, balance=1 all Readhost and stand by writehost participate in load balancing of SELECT statements. Simply put, dual master dual slave mode (M1 à s1,m2 à s2, and M1 and M2 are mainly prepared for each other), normally, M1,S1,S2 participates in the complex equalization of SELECT statements.

3, balance=2 All read operations are randomly distributed on the readhost and Writehost

Writetype

Load balancing type, there are currently 3 types of values:
1, writetype= "0″, all write operations sent to the configuration of the first writehost.
2, writetype= "1″, all write operations are sent randomly to the configured writehost.
3, writetype= "2″, do not perform write operation.

Switchtype

1, switchtype=-1 means not automatically switch
2. Switchtype=1 default value, Auto switch
3, switchtype=2 based on MySQL master-slave synchronization state determines whether to switch

Create write-only users and read-only users

Vim Server.xml

<user name= "Mycat">
Bayi <property name= "password" >Mycat+007</property>
<property name= "Schemas" >JamesMycatSchema</property>
83
<!--table-level DML permissions Settings-
<!--
<privileges check= "false" >
<schema name= "TESTDB" dml= "0110" >
<table name= "tb01" dml= "0000" ></table>
<table name= "tb02" dml= "1111" ></table>
</schema>
</privileges>
---
</user>
94
<user name= "Mycat_r">
<property name= "Password" >cat</property>
<property name= "Schemas" >JamesMycatSchema</property>
98 <property name= "ReadOnly" >true</property>
99
</user>

Detection

close the Salve sql_ thread

(because we want to do the read and write separation, so the database synchronization, will cause interference to the experiment)

Mysql>stop slave sql_thread;

on the client login Mycat:inserting data: 5,mydog,10010

mysql-umycat-pmycat+007-h172.25.88.33-p8066

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8F/79/wKioL1jfnhKBKd_SAABBhY3AO1E512.png "title=" Screenshot from 2017-04-01 14-27-55.png "style=" Float:none; "alt=" Wkiol1jfnhkbkd_saabbhy3ao1e512.png "/>

After inserting and then reading, and not seeing the id=5 just inserted

650) this.width=650; "src=" Https://s5.51cto.com/wyfs02/M00/8F/79/wKioL1jfnhGSzjH4AABUc4AU51I822.png "title=" Screenshot from 2017-04-01 11-00-39.png "style=" Float:none; "alt=" Wkiol1jfnhgszjh4aabuc4au51i822.png "/>

In master (server33) MySQL database login, db1 database query to the data just inserted, so the write operation is performed in Master (SERVER33).

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M01/8F/7B/wKiom1jfnhLBncmAAABQFehi-Jc982.png "title=" Screenshot from 2017-04-01 14-29-04.png "style=" Float:none; "alt=" Wkiom1jfnhlbncmaaabqfehi-jc982.png "/>

Slave (SERVER44) SQL thread is closed, so slave (SERVER44) DB1 also has no id=5 data

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8F/7B/wKiom1jfoRizGf3BAAAs9sEPjCM580.png "title=" Screenshot from 2017-04-01 20-48-53.png "alt=" Wkiom1jforizgf3baaas9sepjcm580.png "/>

At this point the reading and writing separation is successful ~~~~~




This article from "12049878" blog, declined reprint!

Read-write separation of MySQL based on the Mycat implementation