MySQL 17th article ~mycat read and write separation

A brief introduction Today let's talk about a function of mycat. Read and write separation
Second Frontier: One of the core functions of Mycat, the test of read and write separation
Three
1 requirements for read and write separations
1 direct the read demand of the business to the query from the library, reduce the pressure of the main library
2 direct the special reading requirements of the business to the main library to query
2 issues to be considered in reading and writing separation
1 Kicking out problematic slave libraries (from library down or copy stop) when there is a problem with the library
2 when a delay from the library kicks out the problematic slave library (single from library delay), but when it is normal from the library, the new read service is provided from the library node
3 When there is a problem with all the slave libraries that provide read from the library, the query points to the main library and, when restored from the library, provides a new read service from the library node
Four Test and configuration
Environmental configuration and preliminary testing
1 Modifying the Schemal.xml configuration file
Add within <writeHost> tags
<readhost host= "readM1" url= "1.1.1.2:3306" user= "Mycat" password= "Mycat" > </readHost>
2 logging on to management service port 9066
Reload @ @config Reload configuration file
3 Login Execution Service port 8086
1 Parsing data query statements
Explain select * from people
| Data_node | SQL |
| DB1 | SELECT * from people LIMIT 100 |
| DB2 | SELECT * from people LIMIT 100 |
Here we can also find that we search the entire table data automatically added limit 100
2 executing query statements and querying logs
1 SELECT * from people
2 Filter related logs to discover the following
threadid=28911, Charset=utf8, txisolation=3, Autocommit=true, Attachment=db1{select *
from people
WHERE ID > 151
LIMIT, Resphandler=singlenodehandler [Node=db1{select *
from people
WHERE ID > 151
LIMIT [], packetid=17], host=1.1.1.2, port=3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]
3 Select operation resolves to slave library, proof read/write separation complete
Two analog faults 1
1 from library stop slave
2 Observing Mycat Logs
Observation log found found MySQL master/slave Replication err!!
3 executing queries and filtering related logs
threadid=28911, Charset=utf8, txisolation=3, Autocommit=true, Attachment=db1{select *
from people
WHERE ID > 151
LIMIT, Resphandler=singlenodehandler [Node=db1{select *
from people
WHERE ID > 151
LIMIT [], packetid=17], host=1.1.1.1, port=3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]
This becomes the main library IP, the query succeeds
4 from library start slave
5 Execute the query again and filter the log to discover that slave continues to provide read operations
Three analog faults 2
1 from library stop slave
2 Setting the delay library from the library
3 from library start slave
4 Observing Mycat Logs
Observation log found found MySQL master/slave Replication delay!!
5 executing queries and filtering related logs
threadid=28911, Charset=utf8, txisolation=3, Autocommit=true, Attachment=db1{select *
from people
WHERE ID > 151
LIMIT, Resphandler=singlenodehandler [Node=db1{select *
from people
WHERE ID > 151
LIMIT [], packetid=17], host=1.1.1.1, port=3306, Statussync=null, Writequeue=0, Modifiedsqlexecuted=false]
This becomes the main library IP, the query succeeds
6 will return to normal from the library
7 Execute the query again and filter the log to discover that slave continues to provide read operations
Four special Needs
Requirements Note: Some SQL needs to be forced to take the main library
Sample Description:/* #mycat:d b_type=master*/SELECT * from people selecting a data source based on annotations
Specific: Starting from Mycat 1.6, for some special SQL statements, annotated implementation, the example here is just a simple single-table query, complex statements did not test
Five summary
Through testing, we can find that mycat perfectly supports the full functionality of the read and write requirements, which can be realized by annotations, which is only a test, for reference only.

That's what it is today.

MySQL 17th article ~mycat read and write separation