Spring MVC, mybatis configuration read-write separation

The first method:
When the backend MySQL service group is master-master dual-sync, the front-end application uses the JDBC Connection library to use the LoadBalance method, as follows:
jdbc:mysql:loadbalance://dbnode_1:port,dbnode_2:port,dbnode_3:port,dbnode_n:port/dbname?user=xxxx; Password=xxxxxxx;loadbalanceblacklisttimeout=3000;&characterencoding=utf-8
The LoadBalance method has two load equalization algorithms, one is the random algorithm, the other is the shortest response time algorithm, which algorithm to use, how to specify which algorithm to use is not yet clear. The official web site also did not see the very clear said.

The second method:
When the backend MySQL service group is master-slave to a master n from the synchronous replication system, then the use of LoadBalance mode will be problematic, because if the INSERT, update and other write operations to change the number of the machine is troublesome, from the machine will not be changed into the main machine , so in this case it is necessary to be able to use the replication mode for this purpose in the form of "master reading, reading from only", as follows:
jdbc:mysql:replication://dbnode_1:port,dbnode_2:port,dbnode_3:port,dbnode_n:port/dbname?user=xxxx; Password=xxxxxxxx;&characterencoding=utf-8
The replication method can be very safe to write operations only to the host machine, and from the machine will only receive the read operation.

Environment

3 database machines, one master, two slave, respectively, slave1,slave2

2. Objectives to be achieved:

① making data written to master

② read data, read from slave1 and slave2

To realize the separation of reading and writing of the database

3. Principle:

Using the MySQL driver comes with replicationdriver to achieve, replicationdriver simply means there are two connection, a masterconnection, a slaveconnection;

When Setreadonly (true), the currentconnection=slaveconnection is set to Masterconnection

4. Implementation steps:

①jdbc.properties

[Java]View Plaincopy

1. # # Use Replicationdriver instead of driver
2. Jdbc.driverclassname=com.mysql.jdbc.replicationdriver
3. # # used is jdbc:mysql:replication://
4. Jdbc.url=jdbc:mysql:replication://master:3306,slave1:3306,slave2:3306/test2?characterencoding=utf8
5. Jdbc.username=root
6. jdbc.password=123456

② use spring's things to set read operations to Readonly=true

jdbc.connection.driver=com.mysql.jdbc.replicationdriverjdbc.connection.url=jdbc:mysql:replication:// 192.168.202.190,192.168.202.190/job?useunicode=true&characterencoding=utf-8

[Java]View Plaincopy

2. <!--configuration DataSource--
4. <bean class="Org.springframework.jdbc.datasource.DriverManagerDataSource"
6. id="DataSource" >
8. <property name="driverclassname" value="${jdbc.driverclassname}"/>
10. <property name="username" value="${jdbc.username}"/>
12. <property name="password" value="${jdbc.password}"/>
14. <property name="url" value="${jdbc.url}" ></property>
16. </bean>
20. <!--transaction configuration-->
22. <bean id="TransactionManager"
24. class="Org.springframework.jdbc.datasource.DataSourceTransactionManager" >
26. <property name="DataSource" ref="DataSource"/>
28. </bean>
32. <!--define transaction notifications--
34. <tx:advice id="Txadvice" transaction-manager="TransactionManager" >
36. <!--define method filtering rules--
38. <tx:attributes>
40. <!--all methods use transactions--
42. <tx:method name="*" propagation="REQUIRED"/>
44. <!--define all get start methods are read-only
46. <tx:method name="get*" read-only="true"/>
48. </tx:attributes>
50. </tx:advice>
54. <!--define AOP configuration--
56. <aop:config>
58. <!--define a pointcut--
60. <aop:pointcut expression="Execution (* com.most.service.*.* (..))" id="Services"/>
62. <!--notification of pointcuts and transactions, fitting-
64. <aop:advisor advice-ref="Txadvice" pointcut-ref="Services"/>
66. </aop:config>
70. <bean id="sqlsessionfactory" class="Org.mybatis.spring.SqlSessionFactoryBean" >
72. <property name="DataSource" ref="DataSource"/>
74. <property name="configlocation" value="/web-inf/conf/mybatis-config.xml"/>
76. </bean>

ReadOnly = True when all methods that begin with get are called

PS: This is just the realization of the read and write separation, master and slave data synchronization between the use of MySQL replication can

Last night to read the MySQL document, mainly about the copy of MySQL documentation, with the tool is MySQL help document + PowerWord.

Finally more about how to improve the MySQL database through replication to enhance the high availability and processing capacity, but has not been the actual experiment, but the overall idea is clear, the following ideas about the description, there is a chance to experiment again.

First, install and configure the MySQL database using the 1+N model on the server side. 1 is a master database, and N is one to multiple slave databases. The master database must have Binlog mode enabled, see the MySQL documentation for the configuration of both databases. When the configuration is complete, a 1+n replication cluster is created, and the modified data is automatically updated to n slave databases when the master is modified. One note, however, is that all modifications, including additions, deletions, modifications, and so on, must be on the master database so that the data can be reliably replicated to other slave servers. Also, because the slave server is used only as a query, choosing the MyISAM storage engine can improve the speed of queries.

And on the client side? How can the client program use these available servers in a balanced way? And the update operation can only be processed for master. Isn't this a very complex application-to-database operation logic?

It's not that complicated, but MySQL provides us with an interface to make these complex operations transparent. Here's a look at how a JDBC-based client works in a replicated cluster environment, and other programming languages refer to the MySQL documentation.

A driver class that is available in MySQL's latest JDBC driver package is com.mysql.jdbc.ReplicationDriver. This driver class allows multiple MySQL host addresses to be set in the URL, for example:

Jdbc:mysql://master1,slave1,slave2/dlog?autoreconnect=true&roundrobinloadbalance=true

in the above URL, there are three host addresses, respectively, Master1,slave1,slave2, where Replicationdriver Specifies that the first host is the master host address, and all that is left is the slave host. Another two parameters must be specified as true, which is autoreconnect and roundrobinloadbalance.

After using this driver and configuration, we will not be able to solve all the problems immediately, we also need to make some minor changes to the program.

We have to tell the driver, which statements are executed in the master database, and which statements are executed on the slave database.

Replicationdriver is the Connection object's ReadOnly property to determine whether the operation is an update operation. So we have to call Setreadonly when we execute an SQL statement to tell the driver whether the current operation is read-only. If you are using hibernate to manipulate the database, you can call the Query.setreadonly method.

The above is the whole idea of the approximate description, guess there must be some problems, for example, because the data replication process is asynchronous, it is also possible to perform an update operation, but the query can not be found when the situation occurs. Of course, these problems can only be solved in the actual process.

In addition to the latest version of the MySQL 5.1 has a cluster function, but after research found that this thing is simply a joke, I really do not understand how MySQL can do so irresponsible design, because this feature requires the database is the entire load into memory, that is, how big your data, How much memory to have, how to say? If you really big enough memory, you go to try it, anyway, this scheme has been rejected by me, I hope that MySQL can improve.

Spring MVC, mybatis configuration read-write separation