From the library, you can view the database you just created:

In this database, query the data in the user table:

3, MyCat master slave read / write separation

1. Read write separation principle

Read write separation is simply to separate the read and write operations of the database to correspond to different database servers. The master database provides write operations and the slave database provides read operations, which can effectively reduce the pressure of a single database.

The above functions can be easily realized through MyCat. It can support not only MySQL, but also Oracle and SQL Server.

MyCat controls the read-write separation and load balancing of the background database The XML file is controlled by the balance attribute of the datahost tag.

2. Read write separation configuration

The configuration is as follows:

1. Check whether the master - slave replication of MySQL is running normally
2. Modify the conf / schema of MyCat The XML configuration is as follows:

<?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="db01" /> 
	
	<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.157:3306" user="root" password="itcast"> 
			<readHost host="hostS1" url="192.168.192.158:3306" user="root" password="itcast" /> 
		</writeHost> 
	</dataHost> 
</mycat:schema>

1. Modify conf / server xml

<user name="root" defaultAccount="true"> 
	<property name="password">123456</property> 
	<property name="schemas">ITCAST</property> 
</user> 

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

<user name="user"> 
	<property name="password">123456</property> 
	<property name="schemas">ITCAST</property> 
	<property name="readOnly">true</property> 
</user>

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 

#Set logbin format 
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 

#Set logbin format 
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;

Finally, attach a brain map prepared before the interview:

Before the interview, you must brush the questions. In order to facilitate your review, I share a collection of personal interviews

• Java core knowledge sorting

• Spring family bucket (Practical Series)

Step 3: Brush questions

Since it's an interview, I have to brush questions. In fact, I can't go anywhere after the Spring Festival. I brush a lot of interview questions myself, so I can know what knowledge points and high-frequency questions will be asked in the interview process. Therefore, brushing questions is a very important point in the early preparation of the interview.

The following is my private interview question bank:

Many people lament that "learning is useless". In fact, the useless theory arises because what they want does not match what they have learned, which means that they have not learned enough. Whether studying or working, you should have initiative, so if you have a big factory dream, you should try your best to realize it.

CodeChina open source project: [analysis of Java interview questions of front-line large manufacturers + core summary learning notes + latest explanation Video]

Only in the interview can we know what knowledge points will be asked and what high-frequency questions will be asked in the interview process. Therefore, brushing questions is a very important point in the preparation process of the interview.

The following is my private interview question bank:

[external chain picture transferring... (IMG phvfohu2-1630543968296)]

Many people lament that "learning is useless". In fact, the useless theory arises because what they want does not match what they have learned, which means that they have not learned enough. Whether studying or working, you should have initiative, so if you have a big factory dream, you should try your best to realize it.

CodeChina open source project: [analysis of Java interview questions of front-line large manufacturers + core summary learning notes + latest explanation Video]

Finally, I wish you good health and get your favorite offer smoothly!