Shulou(Shulou.com)06/02 Report--

How to achieve master-slave replication and read-write separation in MySQL? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Mind map

The article has been included in Github selections, welcome to Star: https://github.com/yehongzhi/learningSummary

Preface

In many projects, especially Internet projects, the architecture of master-slave replication and read-write separation will be adopted when using MySQL.

Why should we adopt the architecture of master-slave replication read-write separation? How to achieve it? What are the shortcomings? Let's start this learning journey with these questions.

Why use master-slave replication and read-write separation

Master-slave replication and read-write separation are generally used together. The goal is simple, which is to improve the concurrency performance of the database. You think, suppose it is a stand-alone machine, reading and writing are done on a MySQL, the performance must not be high. If there were three MySQL, one mater only for write operations and two salve for read operations, wouldn't the performance be greatly improved?

So master-slave replication, read-write separation is for the database to support greater concurrency.

With the expansion of business, if it is a stand-alone deployment of MySQL, it will lead to high frequency of Icano. Master-slave replication and read-write separation can improve the availability of the database.

The principle of master-slave replication

① when the Master node performs insert, update, and delete operations, it is written to the binlog sequentially.

② salve connects to the master master library from the library and creates as many binlog dump threads as there are slave in Master.

③ when the binlog of the Master node changes, the binlog dump thread notifies all salve nodes and pushes the corresponding binlog content to the slave node.

After the ④ I _ binlog O thread receives the content, it writes the content to the local relay-log.

The ⑤ SQL thread reads the relay-log written by the Ibank O thread and performs the corresponding operation on the slave database according to the contents of the relay-log.

How to realize master-slave replication

I demonstrate here with three virtual machines (Linux). The IP is 104,106 (Slave) and 107 (Slave), respectively.

The expected effect is one master and two slaves, as shown in the following figure:

Master configuration

Use the command line to enter mysql:

Mysql-u root-p

Then enter the password of the root user (if you forget the password, check the reset password online), and then create the user:

/ / 192.168.0.106 is the IP of the slave slave

GRANT REPLICATION SLAVE ON *. * to 'root'@'192.168.0.106' identified by' Java@1234'

/ / 192.168.0.107 is the IP of the slave slave

GRANT REPLICATION SLAVE ON *. * to 'root'@'192.168.0.107' identified by' Java@1234'

/ / refresh the configuration of the system permissions table

FLUSH PRIVILEGES

The two users created will be used when configuring the slave slave.

Next, find the configuration file / etc/my.cnf for mysql and add the following configuration:

# enable binlog

Log-bin=mysql-bin

Server-id=104

# databases that need to be synchronized. If not configured, synchronize all databases

Binlog-do-db=test_db

# number of days that binlog logs are retained, and logs that are cleared for more than 10 days

# prevent log files from being too large, resulting in insufficient disk space

Expire-logs-days=10

After the configuration is complete, restart mysql:

Service mysql restart

You can view the information of the current binlog log from the command line show master status\ G; (useful later):

Slave configuration

Slave configuration is relatively simple. The slave must also be a MySQL server, so as with Master, find the / etc/my.cnf configuration file and add the following configuration:

# do not repeat with other mysql service id

Server-id=106

Then log in to the mysql server using the command line:

Mysql-u root-p

Then enter the password to log in.

After entering mysql, enter the following command:

CHANGE MASTER TO

MASTER_HOST='192.168.0.104',// host IP

User accounts previously created by MASTER_USER='root',//

User password previously created by MASTER_PASSWORD='Java@1234',//

Binlog log name of the MASTER_LOG_FILE='mysql-bin.000001',//master host

MASTER_LOG_POS=862,//binlog log offset

Master_port=3306;// port

It's not over yet. You need to start it after setting it up:

# start the slave service

Start slave

After the startup, how to verify whether the startup is successful? Use the following command:

Show slave status\ G

You can see the following information (extracted some key information):

* * 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: Yes / / Slave_ IO thread and SQL thread are all running

Slave_SQL_Running: Yes

Master_Server_Id: 104 / / Service id of master host

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: 0

The configuration of another slave slave is the same, so I won't repeat it.

Test master-slave replication

Execute sql on the master host:

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'

`promotion`varchar (2048) DEFAULT''COMMENT' Product description'

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' Commodity Information Table'

Then we can see that the two slave slaves synchronize and create the product information table:

The master-slave copy is complete! Java technology enthusiasts have something.

Separation of reading and writing

After the master-slave replication is completed, we also need to achieve read-write separation, master is responsible for writing data, and two slave are responsible for reading data. How can it be realized?

There are many ways to achieve. In the past, our company used to use AOP. Judging by the method name, those with the beginning of get, select and query in the method name connect to slave, while others connect to master database.

But the code is a bit tedious to implement through AOP. Is there any ready-made framework? the answer is yes.

Apache ShardingSphere is an ecological circle 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 at the JDBC layer of Java. It uses a client-side directly connected database and provides services in the form of jar packages without additional deployment and dependency. It can be understood as an enhanced version of the JDBC driver, fully compatible with JDBC and various ORM frameworks.

Read-write separation can be implemented using ShardingSphere-JDBC.

Let's demonstrate the SpringBoot+Mybatis+Mybatis-plus+druid+ShardingSphere-JDBC code implementation.

Project configuration

Version Notes:

SpringBoot:2.0.1.RELEASE

Druid:1.1.22

Mybatis-spring-boot-starter:1.3.2

Mybatis-plus-boot-starter:3.0.7

Sharding-jdbc-spring-boot-starter:4.1.1

Add maven configuration for sharding-jdbc:

Org.apache.shardingsphere

Sharding-jdbc-spring-boot-starter

4.1.1

Then add the configuration in application.yml:

# this is a configuration that uses druid connection pooling. Other connection pooling 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

Sharding.master-slave-rules is to mark the master library and slave library, do not write wrong, otherwise write data to the slave library, it will lead to non-synchronization.

Load-balance-algorithm-type is a routing policy, and round_robin represents a polling policy.

When you start the project, you can see the following message, which indicates that the configuration is successful:

Write the Controller interface:

/ * *

* add goods

*

* @ param commodityName commodity name

* @ param commodityPrice commodity price

* @ param description commodity price

* @ param number goods quantity

Whether the * @ return boolean was added successfully

* @ author java 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)

}

Ready, start the test!

test

Open POSTMAN and add goods:

The console can see the following information:

If you query the data, use slave:

It's that simple!

Shortcoming

Although master-slave replication and read-write separation can greatly ensure the high availability and improve the overall performance of MySQL services, there are also many problems:

The slave synchronizes data from the master through the binlog log. If there is a delay in the network, the slave will have a data delay. Then it is possible that after master writes the data, the slave read data may not be read immediately.

Some people may ask, is there any business problem?

In fact, this framework has already been thought of. When we look back at the previous screenshot, there is a sentence like this:

This is the answer to the question about how to achieve master-slave replication and read-write separation in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.