Spring------MySQL Read-write separation

1. Why Read and write separation

A large number of javaweb applications do io-intensive tasks, the database pressure is large, need to shunt

A lot of application scenarios, is read more write less, database read more pressure

A very natural idea is to use a master-slave database cluster: One is the main repository, which is responsible for writing the data, and the other is from the library, which is responsible for reading the data. Master-Slave library data synchronization.

MySQL native support master-slave replication

The principle of MySQL master (called master) copy from (called slave):
1. Master records data changes to the binary log (bin log), which are called binary logs events
2. Slave copies the binary log events of master to its trunk logs (relay log)
3, slave redo the event in the trunk log, will change the reflection of its own data (data replay)

There are roughly two ways to solve read-write separations:

1) Specify the database in the application layer to read/write separately

The benefit is that the data source switches easily without introducing other components. However, you cannot add data sources dynamically.

2) use middleware to solve

The benefit is that the source program does not need to make any changes and can add data sources dynamically. However, the middleware will bring some performance loss.

Currently there are Mysql-proxy, Mycat, Altas, etc.

2. mysql Master-slave configuration

Main Library Configuration

Modify My.ini:
#开启主从复制, the configuration of the main library
Log-bin = Mysql3306-bin
#指定主库serverid
Server-id=101
#指定同步的数据库, synchronize all databases if you do not specify
Binlog-do-db=mydb

Execute the following sql:
SHOW MASTER STATUS;

Record the position value, you need to set the synchronization starting value from the library.

#授权从库用户slave01使用123456密码登录本库
Grant Replication Slave on * * to ' slave01 ' @ ' 127.0.0.1 ' identified by ' 123456 ';
Flush privileges;

Configure from Library

Modify My.ini:

#指定serverid
server-id=102

Execute the following sql:

Change MASTER to
Master_host= ' 127.0.0.1 ',
Master_user= ' Slave01 ',
Master_password= ' 123456 ',
master_port=3306,
Master_log_file= ' mysql3306-bin.000006 ', #设置主库时记下的Position
master_log_pos=1120;

#启动slave同步
START SLAVE;

#查看同步状态 slave_io_running and slave_sql_running are yes to indicate that the synchronization was successful
SHOW SLAVE STATUS;

3. Spring Dynamic Data source +AOP for read-write separation

The application-level read-write separation scheme is used here.

Using AOP to determine whether to use a write or read library before executing the service method
Can be based on the method of the famous judge, for example, query, find, get and so on in the beginning of the library, the other Go write library

Slice class:

/** * If the policy for a transaction is configured in spring, the ReadOnly method is marked with the slave from the library, and the other uses the Master Library master. * If the transaction policy is not configured, the method name matching is used, the method that starts with query, find, get is used slave, the other uses master.  */public class Datasourceaspect {private list<string> Slavemethodpattern = new arraylist<string> ();  The wildcard method name that holds the ReadOnly property is private static final string[] Defaultslavemethodstartwith = new string[]{"Query", "find", "get"    };  Private string[] Slavemethodstartwith;  Method name saved with Slavemethodstartwith attribute header//inject public void Settxadvice (Transactioninterceptor txadvice) throws Exception        {if (Txadvice = = null) {//No transaction policy is configured to return; }//Get policy configuration information from Txadvice transactionattributesource Transactionattributesource = Txadvice.gettransactionattribu        Tesource (); if (! (        Transactionattributesource instanceof Namematchtransactionattributesource)) {return; }//Use the reflection technique to get the Namemap property value in the Namematchtransactionattributesource object Namematchtransactionattributesource MatchTran SactionattrIbutesource = (namematchtransactionattributesource) Transactionattributesource;        Field Namemapfield = Reflectionutils.findfield (Namematchtransactionattributesource.class, "NameMap"); Namemapfield.setaccessible (TRUE); Set this field to access//Get the value of Namemap map<string, transactionattribute> Map = (map<string, transactionattribute&        gt;) Namemapfield.get (Matchtransactionattributesource); Traverse Namemap for (map.entry<string, transactionattribute> entry:map.entrySet ()) {if (!entry.getv            Alue (). IsReadOnly ()) {//The policy defining readonly is added to Slavemethodpattern continue;        } slavemethodpattern.add (Entry.getkey ()); }}//slice before method public void before (Joinpoint point) {//Get to the currently executing method name String MethodName = point.g        Etsignature (). GetName ();        Boolean isslave = false; if (Slavemethodpattern.isempty ()) {//does not have a Read-only attribute configured with method name matching Isslave = Isslavebymethodname(MethodName); } else {//config Read-only property with wildcard match for (String Mappedname:slavemethodpattern) {if (Isslavebyconfigwildcard (MethodName, Mappedname))                    {Isslave = true;                Break        }}} if (Isslave) {//marked as Read library Dynamicdatasource.markmaster (true);        } else {//marked as Write library Dynamicdatasource.markmaster (false);        }}//matches the method name that starts with the specified name, configures the Slavemethodstartwith property, or uses the default private Boolean isslavebymethodname (String methodName) {    Return Stringutils.startswithany (MethodName, Getslavemethodstartwith ()); }//Match the method name with the wildcard "xxx*", "*xxx" and "*xxx*", derived from the method name configured with the ReadOnly attribute protected Boolean Isslavebyconfigwildcard (String method    Name, String mappedname) {return Patternmatchutils.simplematch (Mappedname, methodName); }//Inject public void Setslavemethodstartwith (string[] slavemethodstartwith) {This.slaveMethodstartwith = Slavemethodstartwith; } public string[] Getslavemethodstartwith () {if (This.slavemethodstartwith = = null) {//Not configured Slavemeth        Odstartwith property, using the default return defaultslavemethodstartwith;    } return Slavemethodstartwith; }}

Spring's Routingdatasource

/** * Using spring's Dynamic Data source, you need to implement Abstractroutingdatasource * by Determinecurrentlookupkey method to get the identification key to determine the choice of read/write data source * Token is clearly a number of cases, So introduced threadlocal save */public class Dynamicdatasource extends Abstractroutingdatasource {//Read library total private Integer slave      Count;    Read Library polling count, initially 1, this class is a singleton, Atomicinteger thread safety private Atomicinteger counter = new Atomicinteger (-1);  Memory Read Library identification key SL1VE01, SLAVE02 ...        Write library recognition key is master private list<object> slavedatasources = new arraylist<object> ();        Write library/Read library token for current thread private static final threadlocal<boolean> Tokenholder = new threadlocal<> ();    public static void Markmaster (Boolean ismaster) {tokenholder.set (ismaster); } @Override protected Object Determinecurrentlookupkey () {if (Tokenholder.get ()) {return ' ma   Ster ";        Write Library}//poll read the library, get the subscript is: 0, 1, 2 ...        Integer index = counter.incrementandget ()% Slavecount;          if (Counter.get () > 99999) {//To avoid exceeding the integer range  Counter.set (-1);    } return Slavedatasources.get (index);        } @Override public void Afterpropertiesset () {super.afterpropertiesset (); The Resolveddatasources property of the parent class is private, and you need to use reflection to get field field = Reflectionutils.findfield (Abstractroutingdatasource.cla        SS, "Resolveddatasources"); Field.setaccessible (TRUE); Set accessible try {map<object, datasource> resolveddatasources = (map<object, datasource>) field            . get (this);            Number of Read libraries equals datasource total number of write-down libraries This.slavecount = Resolveddatasources.size ()-1; For (Map.entry<object, datasource> entry:resolvedDataSources.entrySet ()) {if ("Master". Equals (Entr                Y.getkey ())) {continue;            } slavedatasources.add (Entry.getkey ());        }} catch (Exception e) {e.printstacktrace (); }    }}

Spring configuration file

<!--define transaction policies--<tx:advice id= "Txadvice" transaction-manager= "TransactionManager" > <tx:attributes > <!--all methods starting with query are read-only-<tx:method name= "query*" read-only= "true"/> <!--re Adonly Properties-<!--Other methods use the default transaction policy--<tx:method name= "*"/> </tx:attributes&gt    ;        </tx:advice> <!--defining an AOP slicing processor--<bean class= "Com.zx.DataSourceAspect" id= "Datasourceaspect" > <!--injection transaction policy--<property name= "Txadvice" ref= "Txadvice"/> <!--specifies the prefix of the Slave method (not required)--&G        T <property name= "Slavemethodstartwith" value= "Query,find,get"/> </bean> <aop:config> &LT;AOP :p ointcut id= "mypointcut" expression= "Execution (* com.zx.service.*.* (..))"/> <!--apply facets to a custom slice processor, 9999 guarantees that the cut Face priority--<aop:aspect ref= "Datasourceaspect" order= " -9999" > <aop:before method= "before" p Ointcut-ref= "Mypointcut"/> </aop:aspect> </aop:config> <!--define a data source that inherits spring's dynamic Data source and &LT Bean id= "DataSource" class= "Com.zx.DynamicDataSource" > <!--set multiple data sources--<property name= "TargetD Atasources "> <map key-type=" java.lang.String "> <!--key and DETERMINECURRENTLOOKUPK for these settings The EY method gets the key compared to the select data source according to match--<entry key= "master" value-ref= "Masterdatasource"/> <!--value-re F point to Data Source-<entry key= "slave01" value-ref= "Slave01datasource"/> <entry key= "Slav            E02 "value-ref=" Slave02datasource "/> <entry key=" slave03 "value-ref=" Slave03datasource "/> </map> </property> <!--set the default data source, which defaults to write-<property name= "Defaulttargetdat Asource "ref=" Masterdatasource "/> </bean>

Spring------MySQL Read-write separation