10592 words, 475 lines! Let you have fun-MyCat-read and write separation!

1. The principle of MySQL master-slave replication

Replication refers to the primary database DDLand the DMLoperation log transmitted in binary from the library server, and then re-executes the library from these logs (also called redo), so that the data from the database and the main database synchronized.

MySQL supports one master database to replicate to multiple slave databases at the same time, and the slave database can also serve as the master database of other slave servers to achieve chain replication.

The principle of MySQL master-slave replication is as follows:

From the upper level, the copy is divided into three steps:

  • When the master main library commits the transaction, it will record the data changes as time events in the binary log file Binlog.
  • The master library pushes the log events in the binary log file Binlog to the relay log Relay Log of the slave library.
  • The slave redo events in the relay log and will change the data to reflect its own.

Advantages of MySQL replication:

  • If there is a problem with the master library, you can quickly switch to the slave library to provide services.
  • You can perform query operations on the slave library, update from the main library, realize read-write separation, and reduce the access pressure of the main library.
  • You can perform backups in the secondary library to avoid affecting the services of the primary library during the backup.

Two, MySQL one master and one slave build

Two machines prepared:

1.master

  1. In the master configuration file ( /usr/my.cnf), configure the following:
#mysql 服务ID,保证整个集群环境中唯一 
server-id=1 

#mysql binlog 日志的存储路径和文件名 
log-bin=/var/lib/mysql/mysqlbin 

#设置logbin格式 
binlog_format=STATEMENT 

#是否只读,1 代表只读, 0 代表读写 
read-only=0 

#忽略的数据, 指不需要同步的数据库 
#binlog-ignore-db=mysql 

#指定同步的数据库 
binlog-do-db=db01
  1. After the execution is complete, you need to restart Mysql:
service mysql restart ;
  1. Create an account for synchronizing data and perform authorization operations:
grant replication slave on *.* to 'itcast'@'192.168.192.158' identified by 'itcast'; 

flush privileges;
  1. View master status:
show master status;

Field meaning:

File : 从哪个日志文件开始推送日志文件 
Position : 从哪个位置开始推送日志 
Binlog_Ignore_DB : 指定不需要同步的数据库

2.slave

  1. In the slave configuration file /usr/my.cnf, configure the following:
#mysql服务端ID,唯一 
server-id=2 

#指定binlog日志 
log-bin=/var/lib/mysql/mysqlbin 

#启用中继日志 
relay-log=mysql-relay
  1. After the execution is complete, you need to restart Mysql:
service mysql restart;
  1. Execute the following commands:
change master to master_host= '192.168.192.157', master_user='itcast', 
master_password='itcast', master_log_file='mysqlbin.000001', master_log_pos=413;

Specify the IP address, user name, password of the main library corresponding to the current slave library, and the location from which log file starts to push logs synchronously.

  1. Turn on sync operation
start slave; 

show slave status;
  1. Stop sync operation
stop slave;

Reference material: 《开源数据库中间件MyCat实战笔记》
Quick start channel: 发送简信“MyCat资料”Free access

3. Verify master-slave synchronization

  1. Create a database in the main library, create a table, and insert data:
create database db01; 

user db01; 

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');
  1. Query data from the database to verify:

In the slave library, you can view the database you just created:

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

Three, MyCat one master and one slave read and write separation

1. Principle of read-write separation

Separation of read and write means to separate the read and write operations of the database to correspond to different database servers. The main database provides write operations and the slave database provides read operations, which can effectively reduce the pressure on a single database.

The above functions can be easily achieved through MyCat, which not only supports MySQL, but also supports Oracleand SQL Server.

MyCat controls the read-write separation and load balancing of the back-end database is controlled by the balance attribute of the datahost tag of the schema.xml file .

2. Read and write separation configuration

The configuration is as follows:

  1. Check whether MySQL's master-slave replication is running normally.
  2. Modify the conf/schema.xmlconfiguration of MyCat 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. modifyconf/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 the configuration is complete, restart the MyCat service;

Attribute meaning description:

checkSQLschema 
	当该值设置为true时, 如果我们执行语句"select * from test01.user ;" 语句时, MyCat则会 把schema字符去掉 , 
	可以避免后端数据库执行时报错 ; 

balance 
	负载均衡类型, 目前取值有4种: 
	
	balance="0" : 不开启读写分离机制 , 所有读操作都发送到当前可用的writeHost上. 
	
	balance="1" : 全部的readHost 与 stand by writeHost (备用的writeHost) 都参与 select 语句的负载均衡,
	简而言之,就是采用双主双从模式(M1 --> S1 , M2 --> S2, 正常情况下, M2,S1,S2 都参与 select 语句的负载均衡。); 
	
	balance="2" : 所有的读写操作都随机在writeHost , readHost上分发 
	
	balance="3" : 所有的读请求随机分发到writeHost对应的readHost上执行, writeHost不负担 读压力 ;balance=3 只在MyCat1.4 之后生效 .

3. Verify the separation of read and write

The modified balancevalue, query the data changes in the logical table in MyCat;

Fourth, MySQL dual-master dual-slave construction

1. Architecture

A host computer Master1for processing all write requests it from the machine Slave1and another host Master2as well as its slave Slave2responsible for all read requests. When Master1the host goes down, Master2the host is responsible for writing the request Master1, Master2mutual backup machine. The architecture diagram is as follows:

2. Dual master dual slave configuration

The prepared machines are as follows:

  1. Dual host configuration

Master1 configuration:

#主服务器唯一ID 
server-id=1 

#启用二进制日志 
log-bin=mysql-bin 

# 设置不要复制的数据库(可设置多个) 
# binlog-ignore-db=mysql 
# binlog-ignore-db=information_schema 

#设置需要复制的数据库 
binlog-do-db=db02 
binlog-do-db=db03 
binlog-do-db=db04 

#设置logbin格式 
binlog_format=STATEMENT 

# 在作为从数据库的时候,有写入操作也要更新二进制日志文件 
log-slave-updates

Master2 configuration:

#主服务器唯一ID 
server-id=3 

#启用二进制日志 
log-bin=mysql-bin 

# 设置不要复制的数据库(可设置多个) 
#binlog-ignore-db=mysql 
#binlog-ignore-db=information_schema 

#设置需要复制的数据库 
binlog-do-db=db02 
binlog-do-db=db03
binlog-do-db=db04 

#设置logbin格式 
binlog_format=STATEMENT 

# 在作为从数据库的时候,有写入操作也要更新二进制日志文件 
log-slave-updates
  1. Dual slave configuration

Slave1 configuration:

#从服务器唯一ID 
server-id=2 

#启用中继日志 
relay-log=mysql-relay

Salve2 configuration:

#从服务器唯一ID 
server-id=4 

#启用中继日志 
relay-log=mysql-relay
  1. Restart the mysql service with dual masters and dual slaves
  2. Turn off the firewall for both the host and the slave
  3. Create an account on the two hosts and authorize the slave
#在主机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

Slave1Copy Master1, Slave2CopyMaster2

slave1 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;

slave2 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;
  1. Start the replication function of two slave servers and check the running status of master-slave replication
start slave; 

show slave status\G;
  1. Two hosts replicate each other

Master2Copy Master1, Master1CopyMaster2

Master1 executes instructions:

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 executes instructions:

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 the two master servers and check the running status of the 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 a database on Master1:

Create a table on Master1:

  1. Stop copying features from the service
stop slave;
  1. Reconfigure the master-slave relationship
stop slave; 
reset master;

Five, MyCat dual-master dual-slave read and write separation

1. Configuration

Modify <dataHost>the balance property , this property by separate read and write type of configuration;

<?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: Representative all readHostthe stand by writeHostparticipation selectstatement load balancing, simply, when the case of a double master-slave mode bis (M1-> S1, M2-> S2 , and M1 and M2 mutual backup), normal, M2, Sl , S2 is involved in the load balancing of select statements ;

  1. writeType

0: All write operations are forwarded to the first station writeHost, and when writeHost1hung up, it will be switched to writeHost2;
1: All write operations are randomly sent to the configured one writeHost;

  1. switchType

-1: No automatic switching
1: Default value, automatic switching
2: Indicates whether to switch based on the master-slave synchronization state of MySQL, heartbeat statement:show slave status

2. Read and write separation verification

Query data : select * from user;

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

3. Usability verification

Close Master1, and then execute the written SQL statement, and query the current write operation through the log, which server is operating;


Reference materials: all 《开源数据库中间件MyCat实战笔记》
work is forwarded to the first station writeHost, and when it is writeHost1hung up, it will be switched to writeHost2;
1: all write operations are randomly sent to the configured one writeHost;
  1. switchType

-1: No automatic switching
1: Default value, automatic switching
2: Indicates whether to switch based on the master-slave synchronization state of MySQL, heartbeat statement:show slave status

2. Read and write separation verification

Query data : select * from user;

[External link image is being transferred...(img-c5Oqnc7F-1622703628089)]

Insert data: insert into user(id,name,sex) values(null,'Dawn','1');
[External link image is being transferred...(img-dw2DsXx6-1622703628090)]

3. Usability verification

Close Master1, and then execute the written SQL statement, and query the current write operation through the log, which server is operating;


Reference materials:《开源数据库中间件MyCat实战笔记》