Mind mapping
Wechat public account has been opened: [Java technology lovers], remember to pay attention to those who haven’t
Star: github.com/yehongzhi/l…
preface
In many projects, especially Internet projects, MySQL uses master-slave replication and read-write separation architecture.
Why a master-slave replication read-write separation architecture? How to do that? What are the disadvantages? Let’s begin our learning journey with these questions!
Why use master-slave replication, read-write separation
Master/slave replication and read/write separation are generally used together. The goal is simply to improve the concurrency performance of the database. You think, suppose it is a single machine, read and write on a single MySQL, performance must be not high. If you had three MySQL servers, one mater for writes and two Salves for reads, wouldn’t performance be vastly improved?
So master/slave replication and read/write separation are designed to support greater concurrency.
As the service volume expands, the I/O frequency is too high if MySQL is deployed on a single server. Using master/slave replication and read/write separation can improve database availability.
Principle of master-slave replication
① When the Master node performs insert, update, and delete operations, the operations are written to the binlog in sequence.
(2) The slave salve is connected to the master library, and the number of slave masters will be as many as the number of binlog dump threads.
③ When the Master node’s binlog changes, the binlog dump thread notifies all salve nodes and pushes the corresponding binlog content to slave nodes.
④ After receiving the binlog content, the I/O thread writes the content to the local relay log.
⑤ The SQL thread reads the relay log written by the I/O thread and performs operations on the slave database according to the relay log content.
How to implement master slave replication
Here I use three virtual machines (Linux) with IP addresses 104(Master), 106(Slave), and 107(Slave).
The expected effect is one master and two slave, as shown below:
The Master configuration
Mysql > select * from mysql;
mysql -u root -p
Copy the codeThen enter the password of the root user (if you forget the password, you can reset the password) and create a user:
192.168.0.106 is the IP address of the slave
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.106' identified by 'Java@1234';
192.168.0.107 is the IP address of the slave
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.107' identified by 'Java@1234';
// Refresh the configuration of the system permission table
FLUSH PRIVILEGES;
Copy the codeThe two created users are required during the configuration of the slave.
Mysql/mysql.cnf /etc/my.cnf
# open binlog
log-bin=mysql-bin
server-id=104
If you do not configure this parameter, synchronize all databases
binlog-do-db=test_db
# binlog Specifies the number of days that logs are retained. Logs that are older than 10 days are cleared
# Prevent disk space insufficiency due to large log files
expire-logs-days=10
Copy the codeAfter the configuration is complete, restart mysql:
service mysql restart
Copy the codeYou can run the show master status\G command. Query the current binlog information (useful later) :
Slave configuration
The Slave configuration is relatively simple. /etc/my.cnf/mysql.cnf/mysql.cnf/mysql.cnf/mysql.cnf
Mysql > select * from mysql
server-id=106
Copy the codeThen log in to the mysql server using the command line:
mysql -u root -p
Copy the codeThen enter your password to log in.
After logging in to mysql, enter the following command:
CHANGE MASTER TO MASTER_HOST='192.168.0.104',// host IP address MASTER_USER='root',// Previously created user account MASTER_PASSWORD='Java@1234',// The previously created user password MASTER_LOG_FILE='mysql-bin.000001',// The binlog name of the master host MASTER_LOG_POS=862,//binlog log offset master_port=3306; / / portCopy the codeThat’s not all. After the setup, you need to start:
Start slave;Copy the codeAfter starting, how to verify whether the startup is successful? Use the following command:
show slave status\G;
Copy the codeYou can see the following information (some key information is extracted) :
*************************** 1. row *************************** Slave_IO_State: Waiting for Master to send event Master_Host: 192.168.0.104 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 619 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 782 Relay_Master_Log_File: mysql-bin.000001 //binlog Log file name Slave_IO_Running: Slave_SQL_Running: Yes Master_Server_Id: 104 // Master service ID Master_UUID: 0ab6b3a6-e21d-11ea-aaa3-080027f8d623 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Auto_Position: 0Copy the codeThe configuration of the other slave is the same.
Test primary/secondary replication
SQL > execute SQL on master
CREATE TABLE `tb_commodity_info` (
`id` varchar(32) NOT NULL,
`commodity_name` varchar(512) DEFAULT NULL COMMENT 'Trade Name',
`commodity_price` varchar(36) DEFAULT '0' COMMENT 'Commodity price',
`number` int(10) DEFAULT '0' COMMENT 'Quantity of goods',
`description` varchar(2048) DEFAULT ' ' COMMENT 'Product Description'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Commodity Information Sheet';
Copy the codeThen we can see that the two slave machines also create the commodity information table:
Master slave replication is complete! Java technology enthusiasts have something
Reading and writing separation
After the master/slave replication is complete, read/write separation is required. The master writes data and the slave reads data. How do you do that?
There are many ways to achieve, my company used to use AOP way, judging by method name, method name has get, SELECT, query start with the slave connection, the other connection master database.
However, AOP is a bit cumbersome to implement the code, is there any ready-made framework, the answer is there.
Apache ShardingSphere is an ecosystem of open source distributed database middleware solutions, which is composed of JDBC and Proxy.
Shardingsphere-jdbc is positioned as 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.
Read and write separation can be implemented using ShardingSphere-JDBC.
SpringBoot+Mybatis+Mybatis-plus+ Druid +ShardingSphere-JDBC code implementation.
Project configuration
Version Description:
SpringBoot:2.01.. RELEASE the druid:1.122.
mybatis-spring-boot-starter:1.32.Mybatis - plus - the boot - starter:3.07.
sharding-jdbc-spring-boot-starter:4.11.
Copy the codeAdd sharding-JDBC maven configuration:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
Copy the codeThen add the configuration in application.yml:
This is the druid connection pool configuration. Other connection pool configurations may be different
spring:
shardingsphere:
datasource:
names: master,slave0,slave1
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: JDBC: mysql: / / 192.168.0.108:3306 / test_db? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: yehongzhi
password: YHZ@1234
slave0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: JDBC: mysql: / / 192.168.0.109:3306 / test_db? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: yehongzhi
password: YHZ@1234
slave1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: JDBC: mysql: / / 192.168.0.110:3306 / test_db? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: yehongzhi
password: YHZ@1234
props:
sql.show: true
masterslave:
load-balance-algorithm-type: round_robin
sharding:
master-slave-rules:
master:
master-data-source-name: master
slave-data-source-names: slave0,slave1
Copy the codeSharding. master-slave-rules specifies the master and slave libraries. Do not write the data incorrectly, otherwise the data will not be synchronized to the slave library.
Load-balance-algorithm-type indicates a routing policy, and round_robin indicates a polling policy.
After the project is started, the following information is displayed, indicating that the configuration is successful:
Write Controller interface:
/** * add item **@paramCommodityName commodityName *@paramCommodityPrice *@paramDescription Product price *@paramNumber Number of products *@returnBoolean Whether the value was added successfully *@authorJava technology enthusiast */
@PostMapping("/insert")
public boolean insertCommodityInfo(@RequestParam(name = "commodityName") String commodityName,
@RequestParam(name = "commodityPrice") String commodityPrice,
@RequestParam(name = "description") String description,
@RequestParam(name = "number") Integer number) throws Exception {
return commodityInfoService.insertCommodityInfo(commodityName, commodityPrice, description, number);
}
Copy the codeReady to test!
test
Open POSTMAN and add an item:
The console can see the following information:
To query data, use slave:
It’s that simple!
disadvantages
Although master-slave replication and read-write separation can greatly ensure the high availability of MySQL services and improve the overall performance, there are also many problems:
- The slave machine synchronizes data from the master through the binlog. If the network is delayed, the slave machine will have data delay. Then it is possible that the master may write data, but the slave may not read the data immediately.
One might ask, is there a transaction problem?
In fact, the framework is already there. If you look back at the screenshot, it says something like this:
Wechat public account has been opened: [Java technology lovers], remember to pay attention to those who did not follow oh ~
I am a Java technology enthusiast, the Java elite of Luo Ding, known as luo Ja Ying.
Adhere to the original, continue to output both breadth and depth of technical articles.
The code for all of the above examples is uploaded to Github:
Github.com/yehongzhi/m…
Your praise is the biggest motivation for my creation ~
Refusing to be a salt fish, I’m a programmer trying to be remembered. See you next time!!