MySQL master-slave replication and how to build a master-slave replication environment
MySQL high availability primary/secondary replication
In order to reduce the load on each MySQL host, MySQL can also be separated from read and write. In fact, master/slave replication and read and write separation are usually used together.
This article will talk about MySQL read-write separation. With the help of some database middleware, easy to achieve thief, a look will!
MySQL > select * from user;
The basic principle of MySQL read/write separation is to let the master database handle the write operation and the slave database handle the read operation. The master synchronizes the change of the write operation to the slave nodes.
MySQL read/write separation can improve system performance:
- The number of physical servers increases, and the processing capacity of machines increases. Trade hardware for performance.
- The slave can be configured with the MyiASM engine to improve query performance and save system overhead.
- The master writes data concurrently, while the slave recovers data asynchronously through the binlog sent by the master library.
- The slave can set some parameters separately to improve its read performance.
- Increase redundancy and improve availability.
How to achieve read/write separation
MySQL Proxy is not recommended to be used:
In the meantime, it recommends using the MySQL Router, which not only has little functionality, but also requires you to specify different ports for reading/writing in your application code, something no one would use in a real production environment.
In fact, there are many good MySQL middleware, such as ShardingSphere-JDBC, MyCat, Amoeba, etc., which are good choices.
Here, we use JDBC of Apache open source project ShardingSphere to achieve read and write separation of MySQL.
ShardingSphere-JDBC
ShardingSphere’s JDBC component, called Sharding-JDBC, is a lightweight Java framework that provides additional services in Java’s JDBC layer. It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.
This means that we do not need to install any additional software in the project, directly introduce Jar package dependency, can realize the database sub-table, read and write separation, etc.
To put it bluntly, Sharding-JDBC is JDBC that contains read and write separation of database and table, so we can directly use Sharding-JDBC as normal JDBC.
Sharding-jdbc implements the core concept of read and write separation
The main library
The database used to add, update, and delete data operations. Currently, only single master libraries are supported.
From the library
Query the database used by the data operation. Multiple slave libraries are supported.
We use the MySQL database architecture of one master and two slaves to achieve master/slave replication and read/write separation.
Master-slave synchronization
The operation of asynchronously synchronizing data from a master library to a slave library. Due to the asynchronous nature of master-slave synchronization, data from the slave and master libraries may be inconsistent for a short time.
Load Balancing Policy
If there are multiple slave libraries, you can use load balancing policies to funnel query requests to different slave libraries.
MySQL read and write separation code based on Sharding-JDBC
1. Master/slave replication host configuration:
| The serial number | The host name | The IP address | MySQL | A virtual machine OS |
|---|---|---|---|---|
| 1 | mysql-master | 192.168.2.158 | 5.7 | CnetOS 7.8 |
| 2 | mysql-slave-node01 | 192.168.2.159 | 5.7 | CnetOS 7.8 |
| 3 | mysql-slave-node02 | 192.168.2.157 | 5.7 | CnetOS 7.8 |
The primary and secondary databases are shardingsphere_demo:
log-bin=master-bin
binlog-format=ROW
server-id=1
binlog-do-db=shardingsphere_demo
Copy the codeThe shardingsphere_demo library has a table called Laogong:
create table laogong(
id int.
name varchar(20),
age int
);
Copy the code2. Create a SpringBoot project and import the Jar package
Jar dependencies introduced into ShardingSphere:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
Copy the codeDruid connection pool, Mybatis, mysql driver, not shown here.
3. Configuration file
Configuration is at the heart of sharding-JDBC and is the only module in Sharding-JDBC that deals with application developers.
The configuration module is also the sharding-JDBC portal, through which you can quickly and clearly understand the functionality provided by Sharding-JDBC.
Configuring Read/Write Separation
Based on the previous information about the primary and secondary replication hosts, configure the following hosts:
spring:
shardingsphere:
Configure data sources
datasource:
# data source name
names: master,s1,s2
MySQL master data source
master:
Database connection pool
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: JDBC: mysql: / / 192.168.2.158:3306 / shardingsphere_demo? serverTimezone=UTC
username: root
password: 123456
# Two slave data sources
s1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: JDBC: mysql: / / 192.168.2.159:3306 / shardingsphere_demo? serverTimezone=UTC
username: root
password: 123456
s2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: JDBC: mysql: / / 192.168.2.157:3306 / shardingsphere_demo? serverTimezone=UTC
username: root
password: 123456
masterslave:
load-balance-algorithm-type: round_robin
name: ms
master-data-source-name: master
slave-data-source-names: s1,s2
# Other attributes
props:
# enable SQL display
sql.show: true
Copy the code4. Create entity classes and Mapper classes
Entity class code is not posted here, insult intelligence
The Mapper class:
@Repository
@Mapper
public interface LaogongMapper {
@Insert("insert into laogong(id, name, age) values(#{id}, #{name}, #{age})")
public void addLaogong(Laogong laogong);
@Select("select * from laogong where id=#{id}")
public Laogong queryLaogong(Integer id);
}
Copy the codetest
Test writing data
Insert 5 entries into laogong table:
@Test
public void testMSInsert(a){
for (int i = 1; i <= 5; i++) {
Laogong laogong = new Laogong();
laogong.setId(i);
laogong.setName("xblzer" + i);
laogong.setAge(new Random().nextInt(30));
laogongMapper.addLaogong(laogong);
}
}
Copy the codeRunning results:
As you can see, the inserted SQL statement writes all data to the master database.
Test read data
Select * from library where id=1; select * from library where id=1;
@Test
public void testMSQuery(a){
for (int i = 0; i < 10; i++) {
Laogong laogong = laogongMapper.queryLaogong(1);
System.out.println(laogong);
}
}
Copy the codeVerification of results:
Only S1 and S2 are read from the library.
Through the above two read and write data test, can be available, through sharding-JDBC really convenient to help us achieve read and write separation! This is the time when we can say really delicious!
The last
Sharding-jdbc-based MySQL read-write separation is really easy to use, and ShardingSphere became a top project of the Apache Software Foundation on April 16, 2020, so ShardingSphere will soon catch on.
In our project, we have used this to do MySQL sub-table read and write separation. This article only mentions read/write separation, but there are many other features sharding-JDBC can implement:
- Data sharding (sub-library & sub-table)
- Reading and writing separation
- Distributed transaction
- Distributed governance
- Data encryption
- .
ShardingSphere has formed an ecosystem and its functions are still being improved.
The end of this navigation, above.
PS. The complete code address of the project involved in this article:
https://github.com/xblzer/JavaJourney/tree/master/code/shardingsphere
The first public line 100 li ER, welcome the old iron attention reading correction. GitHub github.com/xblzer/Java…