<property name="password">123456</property>
<property name="schemas">ITCAST</property> 

123456 ITCAST true ```

1. After configuration, restart MyCat service;

Description of attribute meaning:

checkSQLschema 
	When the value is set to true Time, If we execute the statement"select * from test01.user ;" Statement time, MyCat Will put schema Character removal , 
	It can avoid errors in back-end database execution ; 

balance 
	Load balancing type, At present, there are four values: 
	
	balance="0" : Do not enable the read-write separation mechanism , All read operations are sent to the currently available writeHost upper. 
	
	balance="1" : all-out readHost And stand by writeHost (Spare writeHost) All involved select Load balancing of statements,
	In short,Double master and double slave mode is adopted(M1 --> S1 , M2 --> S2, Normally, M2,S1,S2 All involved select Load balancing of statements.); 
	
	balance="2" : All read and write operations are performed at random writeHost , readHost Upper distribution 
	
	balance="3" : All read requests are randomly distributed to writeHost Corresponding readHost Upper execution, writeHost No reading pressure ;balance=3 Only in MyCat1.4 Effective after .

3. Verify read / write separation

Modify the value of balance and query the data changes in the logic table in MyCat;

4, MySQL dual master and dual slave setup

1. Architecture

A master master 1 is used to process all write requests, and its slave Slave1, another master 2 and its slave Slave2 are responsible for all read requests. When master1 host goes down, Master2 host is responsible for writing requests. Master1 and Master2 are standby machines for each other. The architecture diagram is as follows:

2. Dual master and dual slave configuration

The machines prepared are as follows:

1. Dual host configuration

Master1 configuration:

#Primary server unique ID 
server-id=1 

#Enable binary logging 
log-bin=mysql-bin 

# Set the database not to be copied (multiple databases can be set) 
# binlog-ignore-db=mysql 
# binlog-ignore-db=information_schema 

#Set the database to be replicated 
binlog-do-db=db02 
binlog-do-db=db03 
binlog-do-db=db04 

#Format logbin 
binlog_format=STATEMENT 

# In the slave database, the binary log file should also be updated when there is a write operation 
log-slave-updates

Master2 configuration:

#Primary server unique ID 
server-id=3 

#Enable binary logging 
log-bin=mysql-bin 

# Set the database not to be copied (multiple databases can be set) 
#binlog-ignore-db=mysql 
#binlog-ignore-db=information_schema 

#Set the database to be replicated 
binlog-do-db=db02 
binlog-do-db=db03
binlog-do-db=db04 

#Format logbin 
binlog_format=STATEMENT 

# In the slave database, the binary log file should also be updated when there is a write operation 
log-slave-updates

1. Dual slave configuration

Slave1 configuration:

#Unique ID of slave server 
server-id=2 

#Enable relay logging 
relay-log=mysql-relay

Salve2 configuration:

#Unique ID of slave server 
server-id=4 

#Enable relay logging 
relay-log=mysql-relay

1. Restart mysql service with dual hosts and dual slaves
2. Turn off the firewall for both host and slave computers
3. Establish an account on both hosts and authorize slave

#Execute the authorization command in the host MySQL 
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%' IDENTIFIED BY 'itcast'; 

flush privileges;

Query the status of Master1:

Query the status of Master2:

1. Configure the host to be replicated on the slave

Slave1 replicates Master1 and Slave2 replicates Master2

slave1 instruction:

CHANGE MASTER TO MASTER_HOST='192.168.192.157', 
MASTER_USER='itcast', 
MASTER_PASSWORD='itcast', 
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=409;

slave2 instruction:

CHANGE MASTER TO MASTER_HOST='192.168.192.159', 
MASTER_USER='itcast', 
MASTER_PASSWORD='itcast', 
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=409;

1. Start the replication function of two slave servers to view the running status of master-slave replication

start slave; 

show slave status\G;

1. The two hosts replicate with each other

Master2 replicates Master1, and Master1 replicates master2

Master1 execution command:

CHANGE MASTER TO MASTER_HOST='192.168.192.159', 
MASTER_USER='itcast', 
MASTER_PASSWORD='itcast', 
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=409;

Master2 execution command:

CHANGE MASTER TO MASTER_HOST='192.168.192.157', 
MASTER_USER='itcast', 
MASTER_PASSWORD='itcast', 
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=409;

1. Start the replication function of two master servers to view the running status of master-slave replication

start slave; 

show slave status\G;

1. verification

create database db03; 


use db03; 

create table user( 
	id int(11) not null auto_increment, 
	name varchar(50) not null, 
	sex varchar(1), 
	primary key (id) 
)engine=innodb default charset=utf8; 

insert into user(id,name,sex) values(null,'Tom','1'); 
insert into user(id,name,sex) values(null,'Trigger','0'); 
insert into user(id,name,sex) values(null,'Dawn','1'); 

insert into user(id,name,sex) values(null,'Jack Ma','1'); 
insert into user(id,name,sex) values(null,'Coco','0'); 
insert into user(id,name,sex) values(null,'Jerry','1');

Create database on Master1:

Create table on Master1:

1. Stop replication from service

stop slave;

1. Reconfigure master-slave relationship

stop slave; 
reset master;

5, MyCat dual master dual slave read / write separation

1. Configuration

Modify the balance attribute of < datahost >, and configure the type of read-write separation through this attribute;

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

	<schema name="ITCAST" checkSQLschema="true" sqlMaxLimit="100"> 
		<table name="user" dataNode="dn1" primaryKey="id"/>
	</schema> 
	
	<dataNode name="dn1" dataHost="localhost1" database="db03" /> 
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> 
		<heartbeat>select user()</heartbeat> 
		<writeHost host="hostM1" url="192.168.192.147:3306" user="root" password="itcast"> 
			<readHost host="hostS1" url="192.168.192.149:3306" user="root" password="itcast" /> 
		</writeHost> 
			
		<writeHost host="hostM2" url="192.168.192.150:3306" user="root" password="itcast"> 
			<readHost host="hostS2" url="192.168.192.151:3306" user="root" password="itcast" /> 
		</writeHost> 
	</dataHost> 
</mycat:schema>

1. balance

1: represents all readhosts and stand by writeHost to participate in the load balancing of the select statement. In short, in the dual master and dual slave mode (M1 - > S1, M2 - > S2, and M1 and M2 are both active and standby), under normal circumstances, M2, S1 and S2 all participate in the load balancing of the select statement;

1. writeType

0: all write operations are forwarded to the first writehost. If writehost1 hangs, it will switch to writeHost2;
1: all write operations are randomly sent to the configured writeHost;

1. switchType

-1: no automatic switching
1: default value, automatic switching
2: indicates whether to switch based on the master-slave synchronization status of MySQL. Heartbeat statement: show slave status

2. Read write separation verification

Query data: select * from user;

Insert data: insert into user(id,name,sex) values(null,'Dawn','1');

. switchType

-1: no automatic switching
1: default value, automatic switching
2: indicates whether to switch based on the master-slave synchronization status of MySQL. Heartbeat statement: show slave status

2. Read write separation verification

Query data: select * from user;

[external chain picture transferring... (img-Km7oCghu-1630197421259)]

Insert data: insert into user(id,name,sex) values(null,'Dawn','1');
[external chain picture transferring... (img-u9jrb3mN-1630197421260)]