preface

This paper introduces the principle of master-slave synchronization and demonstrates the actual operation. Based on the above, this paper studies the separation of reading and writing based on mycat database and table

MyCat

Example architecture diagram:

192.168.43.110: database test01 and database test02 have student tables respectively

192.168.43.111: database test01 and database test02 have student tables respectively

192.168.43.216: install mycat and JDK

Installation and configuration (JDK is required)

1. Download MyCat
2. Decompression: tar -xvzf mycat-server-1.6.6.1-release-20181031195535-linux tar. gz
3. Enter the conf directory and modify the three configuration files server xml，schema.xml，rule.xml

file	effect
server.xml	Specify MyCat user account and password, logical database, etc
schema.xml	Set sub database and sub table, read-write separation configuration
rule.xml	Some rules of sub table

server.xml:

• Only one user is left, the account password is root/root, and the logical database is specified as TESTDB (modification point)
• The noted privileges are to do some DML permission control for the table (note)

	<user name="root" defaultAccount="true">
		<property name="password">root</property>
		<property name="schemas">TESTDB</property>
		
		<!-- Table level DML Permission setting -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<!-- <user name="user"> -->
		<!-- <property name="password">user</property> -->
		<!-- <property name="schemas">TESTDB</property> -->
		<!-- <property name="readOnly">true</property> -->
	<!-- </user> -->

schema.xml:
Explanation:
1. Divide the TESTDB logical library into two physical libraries test01 and test02.
2. The student table is divided into tables according to the rule that the rule is mod long. This rule is evenly distributed according to the id and configured in rule XML inside
3. Read write separation, 192.168.43.110 master write, 192.168.43.111 slave node read

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
		<!--name: Sub table
			dataNode: Position of the split data slice  dn1,dn2
			rule:Split rule myrule (The rule of splitting is called myrule)
		-->
		<table name="student" dataNode="dn1,dn2" rule="mod-long" />

	</schema>


	<dataNode name="dn1" dataHost="localhost1" database="test01" />
	<dataNode name="dn2" dataHost="localhost1" database="test02" />

	<!--  dataHost: Set the host address of read-write separation-->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
	
		<writeHost host="hostM1" url="192.168.43.110:3306" user="root"
				   password="123456">
			<readHost host="hostS2" url="192.168.43.111:3306" user="root" password="123456" />
		</writeHost>

	</dataHost>

</mycat:schema>

Description of some parameters:

datahost:

• balance: load balancing of read requests
  0: do not enable read / write separation. All read operations are sent to writehost
  1: All readhost and stand by writehost participate in the read operation
  2: Read requests are randomly sent to readhost and writehost
  3: The read request is randomly sent to readhost in writehost (writehost does not participate in the read request) -- recommended
• writeType: load balancing of write requests
  0: the write request is sent to schema The first writehost in XML. When the first writehost hangs up, it will automatically switch to writehost. The switching record will be recorded in conf / dnindex Properties -- recommended
  1: Write requests are randomly sent to all wirtehost s
• switchType: whether to allow the "read operation" to automatically switch between readhost and writehost (solve the delay problem: when there is a network delay in reading data from readhost, automatically read data from writehost)
  -1: No
  1: Default, allowed
  2: Automatically select whether to switch according to "master-slave synchronization status".
  Heartbeat will be sent continuously between master and slave. When the heartbeat detection mechanism sends IO delay, readhost will automatically switch to writehost;
  Otherwise, do not switch. The heartbeat must be set to show slave status -- recommended

rule.xml
Modify the rule of mod long in the rule, count=2, because the configured test01 and TEST02 have two nodes

	<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>

	<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">2</property>
	</function>

Start MyCat

Enter the bin directory:

../mycat start  --start-up
../mycat stop  --stop it
../mycat restart    --restart
../mycat status    --View status

Sign in:
You can't log in directly at the node 192.168.43.216 where mycat is located. You need to operate mysql remotely on a computer with mysql software installed
We log in to mycat at 192.168.43.110 or 192.168.43.111, and the port is 8066:

mysql  -uroot -proot -h192.168.43.216 -P8066

We can see that the database is a logical database TESTDB,

Check the log: if there is an error, check the log
Under logs Directory: wrapper Log file

Test effect

Take a look at the structure of the database, that is, the picture at the beginning:
192.168.43.110: database test01 and database test02 have student tables respectively
192.168.43.111: database test01 and database test02 have student tables respectively

Log in with the logical library TESTDB, view the student table, and insert a piece of data:
insert into student(id,name) values(1,'testmycat1');
Note: mycat is a strong syntax, such as insert into student values(1,testmycat1); (no)


We can see that the logical library TESTDB we use inserts a piece of data into the physical library test02 of the master (192.168.43.110) and synchronizes it to the test02 Library of the slave node of the slave (192.168.43.111) through master-slave synchronization

Insert another piece of data:
insert into student(id,name) values(2,'testmycat2');

This time, we can see that we have synchronized the data from the main database (test110.168.01) to 192.43 (test110.43) through test168.01

It is based on rule The average score rule configured in the XML file determines the student table of which library to enter. Each time the id is added by 1, it is alternately entered into test01 and test02 libraries, and then synchronized to the slave library