Java uses spring to realize the sample code of read-write separation

In the recently launched projects, the database data is approaching saturation, the largest table data is close to 3000W, and there are several tables with millions of data, The project requires reading data The (select) time cannot exceed 0.05 seconds, but the actual situation does not meet the requirements. Explain establishes the index, and the use of redis and ehcache caching technology can not meet the requirements. Therefore, the use of read-write separation technology may need to consider the deployment of distributed database when the data volume is hundreds of millions or more in the future. However, at present, read-write separation + cache + index + table Partition + SQL optimization + load balancing can meet the query of 100 million data. Now let's take a look at the steps available for the pro test to separate reading and writing using spring:

1. Background

Our general application is "more reading and less writing" for the database, that is, there is a great pressure on the database to read data. One idea is to adopt the scheme of database cluster,

One of them is the main library, which is responsible for writing data. We call it: write library;

Others are from the library, which is responsible for reading data. We call it: reading the library;

Then, the requirements for us are:

1. The data of read library and write library are consistent; (this is a very important issue. The business logic should be handled at the service level, not at the Dao or mapper level.)

2. Write data must be written to the write library;

3. The data must be read to the library;

2. Programme

There are two solutions to the separation of reading and writing: application layer solution and middleware solution.

2.1. Application layer solution:

advantage:

1. It is convenient to switch multiple data sources, which is automatically completed by the program;

2. There is no need to introduce middleware;

3. Theoretically support any database;

Disadvantages:

1. It is completed by programmers, and the operation and maintenance cannot participate;

2. Unable to dynamically add data sources;

2.2. Middleware solution

Advantages and disadvantages:

advantage:

1. The source program can realize the separation of reading and writing without any change;

2. Adding data sources dynamically does not require restarting the program;

Disadvantages:

1. The program depends on middleware, which will make it difficult to switch the database;

2. The middleware is used as the transit agent, and the performance is reduced;

3. Implementation based on application layer using spring

3.1. principle

Before entering the service, use AOP to determine whether to use the write library or read library. The judgment basis can be determined according to the method name. For example, read the library starting with query, find, get, and others.

3.2. DynamicDataSource

3.3. DynamicDataSourceHolder

3.4. DataSourceAspect

3.5. Configure 2 data sources

3.5. 1. jdbc. properties

3.5. 2. Define connection pool

3.5. 3. Define datasource

3.6. Configure transaction management and dynamically switch data source aspects

3.6. 1. Define the transaction manager

3.6. 2. Define transaction policy

3.6. 3. Define section

4. Improve the aspect implementation and use transaction policy rule matching

In the previous implementation, we will match the method name instead of using the definition in the transaction policy. We use the rules in the transaction management policy.

4.1. Improved configuration

4.2. Improved implementation

5. Implementation of one master and multiple slaves

The "one master and many slaves" architecture is adopted in many actual use scenarios, so we now support this architecture. At present, we only need to modify the dynamicdatasource.

5.1. realization

6. MySQL master-slave replication

6.1. principle

The principle of MySQL master slave replication:

1. The master records the data changes in the binary log, that is, the file specified in the configuration file log bin (these records are called binary log events)

2. Slave copies the binary logevents of the master to its relay log

3. The slave redoes the events in the relay log and will change the data reflecting itself (data replay)

6.2. Matters needing attention in master-slave configuration

1. The versions of the master DB server and the slave DB server databases are consistent

2. The data of the master DB server is consistent with that of the slave DB server [here, the backup of the master can be restored on the slave, or the data directory of the master can be directly copied to the corresponding data directory of the slave]

3. The master DB server starts the binary log. The master DB server and the slave DB server_ ID must be unique

6.3. Main library configuration (similar under windows and Linux)

The IP address, user name and account configuration of some friends' master-slave database may not be very clear. The following is the master-slave configuration I tested. The IP is 127.0 0.1, I will write after I finish my example

A master-slave IP is an example of different configuration. You can use this example to more intuitively understand the configuration method.

In my Ini [mysqld] (the same is true from the Library):

(these commands entered in my.ini must have a line of space below, otherwise MySQL does not recognize them)

Execute SQL statement query status: show master status

You need to record the position value and set the synchronization start value in the slave library.

In addition, let me say that if you execute show master status in mysql, you will find that the configuration is in my The content in ini has no effect. The possible reason is that I didn't select my INI file, or you may not restart the service, which is most likely caused by the latter,

For the configuration to take effect, the MySQL service must be shut down and restarted.

How to shut down the service:

Open with the win key and enter services MSc call out service:

Start sqlyog again and find that the configuration has taken effect.

6.4. Create a synchronization user in the main library

6.5. Configure from library

In my Ini modification:

Execute the following SQL (using the root account of the slave):

The following is the master-slave configuration method for two computers with different IP addresses:

Operating system of main database: win7

Version of master database: 5.0

IP address of primary database: 192.168 one point one one one

From the operating system of the database: Linux

Version from data: 5.0

IP address of slave database: 192.168 one point one one two

After introducing the environment, let's talk about the configuration steps:

1, ensure that as like as two peas from the database.

For example, if the database of a in the main database has tables B, C and D, there should be a database of a and tables B, C and D engraved from the database

2. Create a synchronization account on the primary database.