cool hit counter How to use two servers to achieve master-slave synchronization and data read/write separation (II)_Intefrankly

How to use two servers to achieve master-slave synchronization and data read/write separation (II)


Master-slave synchronization enables read-write separation and quick application to real development projects. The last master-slave synchronization was mainly about how to configure master-slave synchronization on the server to achieve data tracking on both servers, which is equivalent to configuring the environment.

So with such a great environment, how do you waste it, so today I'll briefly explain how to put a configured master-slave synchronous environment to use and quickly apply it to our real-world development projects.

Implementation idea: master-slave synchronization, mainly to achieve the separation of data read and write, that is, the user's read (query) operations, using the master server's database, write (add, update, delete) operations using the slave server. So to achieve multiple data sources, you have to intercept the judgment and set different data sources before the Dao operation layer, so as to achieve the separation of database reading and writing, so the next operation is mainly to configure Dao's multiple data sources.

So let's get started, first showing the basic information of the code I'm going to demo and the basic information of the main database

Note: Here the author used the language java, framework for SSM framework (ps: springboot and other frameworks such as the same principle, as long as you can read this, other frameworks can also be used quickly)

master on the master database, there is a database mengxinshop, which has a table tb_shop, which has three data, as shown in the figure.

Corresponding AREA entity class.

Information about areaDao (only two methods)

The corresponding mybatis select statement is.

Brief information about my SSM framework

The above is what I did before the demo started (ps: if you want to follow along, get your environment like the above, SSM framework set up, and you can knock along next)

Here's how to start the configuration

1.Inheritance of AbstractRoutingDataSource

In the dao layer below the creation of a package split (to store the data separation of the configuration file), create the entity class DynamicDataSource class inherits AbstractRoutingDataSource, to implement the method, determineCurrentLookupKey. Add the code to return the following values.

A simple description: AbstractRoutingDataSource, translated is the abstract data routing, can be routed to different data sources, there is a method, that is, determineCurrentLookupKey, according to the field key of different decisions of different data sources.

2.Writing the DynamicDataSourceHolder tool class

Implement the DynamicDataSourceHolder tool class to fetch, set, clear fields, and determine the data source, with the following code, described below.

Connect the picture above

Only is the entire code of DynamicDataSourceHolder, is a tool class, the main role is to set the field to the thread, clean up the connection, get the field from the thread, to return it, and thus decide on a different data source.

3.Configuring the mybats interceptor

Configure mybats interceptor to intercept based on sql information to determine if the request is a read or write, thus setting different fields to the thread and returning for the first step of the determineCurrentLookupKey method to determine the database.

Creating InterceptorsDynamicDataSourceIntercept, Implementing the configuration of the interceptor(ps:Interceptor introduceibstis The interceptor of the), specifically as follows.

That's all the configuration for the mybatis interceptor, and the exact instructions are also on the diagram

Briefly, the interceptor intercepts mybatis sql statements, determines which database to use based on the intercepted sql statements, then calls the DynamicDataSourceHolder tool class, saves it to the thread, returns the key, and decides which database to use.

PS: If you think the above code is difficult to knock, please pay attention to the public number of this Mengxin, mengxinzero, reply: master-slave configuration, you can get all the above code.

Finally the configured Mybatis interceptor (DynamicDataSourceIntercept) is put into the core Mybatis configuration file mybatis-config.xml (don't forget)

as follows.

4.Configuring data sources for Spring configuration files

a. willdb.properties The original configuration of the data in, Adding ajdbc The information configuration of the, my as follows. memaster harmonyslave The username and password are the same in the, So it's only necessary to add aurl That's it.。

b. Find the place where the data source is configured in spring, modify the configuration of the original DataSource as an abstract data source, create master and slave data sources for inheritance, and configure the details as shown in Figure.

c. Configuring dynamic data sources

Note: The last configured bean will have its id set to dataSource (i.e. the data source id at the beginning), so that no other configuration in Spring needs to be modified, with the properties in the first bean being targetdatasources and the second being targetDataSource

All the above master-slave synchronization all configuration is completed, next let's test the effect

Here the author uses the Junit unit tests

1.Create a base class (baseTest) to load the various configuration files for spring, configured as follows.

2.Create the areaDaoTest test class, inherit from baseTest, and start testing with the following code.

Run it and test the following (if all configurations are correct).

The log prints out that the sql statement used is of type select and the database used is slave

ps: here the log information need to configure themselves, otherwise there is no output of these log information, only the results (I use logback, need that configuration, please follow this Mengxin public number, mengxinzero, reply logback, you can get the configuration and use method)

Again, look at the data from the query

The query returned 3, which is the same as the data record in the master database I showed above, which is indeed 3

Next let's test the write operation, here I add a piece of data to the database

Add test methods to this test class.

The test results were as follows.

You can see that the select statement is insert and the database used is master

Let's take another look at the data on the Master and Slave servers

You can see that the data is added to the master

Let's look at the data transformation on the slave server again

At this point, the data on the Slave server has been automatically synchronized. Isn't that amazing?

You will be able to use the method in your projects later, isn't it convenient.

Here are some additional questions that may be encountered.

1.Database connection failure

Possible causes of this.

a.Your account and password are wrong

b.mysql's port 3306 firewall is not closed

c. Not awardedmysql Remote connection privileges: useGRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY ' Your password.' WITH GRANT OPTION; Conducting authorizations。

You can use the local mysql tool to test the connection, if it connects, it means the problem is solved

2.Isolation level appears when testing write data

What is the reason for this, just read when there is no problem, write the problem, the reason is because the mysql driver package version and mysql conflict, such as my server mysql with 5.5, the driver package with 6.0 on the problem, directly replace the driver package for 5.1.32 on the line

Well, the above master-slave synchronization to achieve data separation of read and write to the actual development of the demo is written, can understand the above principles, you can apply to other frameworks, the method is the same. Hurry up and give it a try.

Special note: master-slave synchronization, all writes are performed in the master database, reads are performed in the slave database, please don't write operations to the slave database anymore. Failure to do so will result in master-slave confusion. (ps: if you follow this method to the letter, you can't usually get it wrong)


Recommended>>
1、Newegg users credit card information exposed for up to a month
2、120kmh will become history the first domestic no speed limit highway construction
3、Great Performance HP DL120Gen9 Server Hot Sale
4、Please keep your mouth shut on Microsoft platforms
5、84000 possibilities and you met me

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号