The database data in the recently launched project is approaching saturation. The largest table data is close to 3000W, and there are several tables with millions of data. The project requires that the data reading time cannot exceed 0.05 seconds, but the actual situation does not meet the requirements. Explain indexing and using redis and ehcache cache technology can no longer meet the requirements. Therefore, we started to use read and write separation technology. Maybe when the data volume exceeds 100 million or more in the future, we need to consider the deployment of distributed databases. However, at present, read and write separation + cache + index + table partition + sql optimization + load balancing can meet the query work of 100 million data volumes. Let’s take a look at the steps to use spring to achieve read and write separation:
1. Background
Our general application is to "read more and write less" for databases, which means that the pressure on the database to read data is relatively high. One idea is to use a database cluster solution.
One of them is the main library, which is responsible for writing data, which we call: writing library;
The others are all from the library, which is responsible for reading data, which we call: reading library;
So, the requirements for us are:
1. The data of the read library and the write library are consistent; (This is a very important issue. The processing of business logic should be processed in the service layer, and not at the dao or mapper level)
2. When writing data, you must write it to the writing library;
3. You must go to the reading library to read the data;
2. Plan
There are two solutions to solve the separation of read and write: application-layer solution and middleware solution.
2.1. Application layer solution:
advantage:
1. Multiple data sources are easy to switch and are automatically completed by the program;
2. No middleware is required;
3. In theory, support any database;
shortcoming:
1. Completed by programmers, and operation and maintenance are not involved;
2. Dynamically increase data sources cannot be achieved;
2.2. Middleware solution
Pros and cons:
advantage:
1. The source program can achieve read and write separation without any changes;
2. Dynamically adding data sources does not require restarting the program;
shortcoming:
1. Programs rely on middleware, which makes it difficult to switch databases;
2. The middleware is used as a transit agent, and the performance has declined;
3. Use Spring to implement based on application layer
3.1. Principle
Before entering Service, use AOP to make a judgment, whether to use a write library or a read library, the judgment basis can be judged based on the method name, such as the one starting with query, find, get, etc., and other write library.
3.2. DynamicDataSource
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;/** * Define dynamic data sources and implement AbstractRoutingDataSource provided by integration Spring. You only need to implement the determineCurrentLookupKey method * * Since DynamicDataSource is a singleton and thread-insecure, ThreadLocal is used to ensure thread-safety, which is completed by DynamicDataSourceHolder. * * @author zhijun * */public class DynamicDataSource extends AbstractRoutingDataSource{ @Override protected Object determineCurrentLookupKey() { // Use DynamicDataSourceHolder to ensure thread safety, and get the data source key in the current thread return DynamicDataSourceHolder.getDataSourceKey(); }} 3.3. DynamicDataSourceHolder
/** * * Use ThreadLocal technology to record the key of the data source in the current thread * * @author zhijun * */public class DynamicDataSourceHolder { //Write the data source corresponding to the library private static final String MASTER = "master"; //Read the data source corresponding to the library private static final String SLAVE = "slave"; //Use ThreadLocal to record the data source of the current thread private static final ThreadLocal<String> holder = new ThreadLocal<String>(); /** * Set the data source key * @param key */ public static void putDataSourceKey(String key) { holder.set(key); } /** * Get the data source key * @return */ public static String getDataSourceKey() { return holder.get(); } /** * Markup write library*/ public static void markMaster(){ putDataSourceKey(MASTER); } /** * Markup read library*/ public static void markSlave(){ putDataSourceKey(SLAVE); }} 3.4. DataSourceAspect
import org.apache.commons.lang3.StringUtils;import org.aspectj.lang.JoinPoint;/** * Define the AOP section of the data source, and judge whether it is time to read the library or write the library through the method name of the Service * @author zhijun * */public class DataSourceAspect { /** * Execute before entering the Service method * * @param point section object */ public void before(JoinPoint point) { // Get the currently executed method name String methodName = point.getSignature().getName(); if (isSlave(methodName)) { // Mark as read library DynamicDataSourceHolder.markSlave(); } else { // Mark as write library DynamicDataSourceHolder.markMaster(); } } /** * Determine whether it is a read library* * @param methodName * @return */ private Boolean isSlave(String methodName) { // Method name starts with query, find, get, return StringUtils.startsWithAny(methodName, "query", "find", "get"); }}3.5. Configure 2 data sources
3.5.1. jdbc.properties
jdbc.master.driver=com.mysql.jdbc.Driverjdbc.master.url=jdbc:mysql://127.0.0.1:3306/mybatis_1128?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=truejdbc.master.username=rootjdbc.master.password=123456jd bc.slave01.driver=com.mysql.jdbc.Driverjdbc.slave01.url=jdbc:mysql://127.0.0.1:3307/mybatis_1128?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=truejdbc.slave01.username=rootjdbc.slave01.password=123456
3.5.2. Define connection pool
<!-- Configure connection pool--> <bean id="masterDataSource" destroy-method="close"> <!-- Database driver--> <property name="driverClass" value="${jdbc.master.driver}" /> <!-- The corresponding driver's jdbcUrl --> <property name="jdbcUrl" value="${jdbc.master.url}" /> <!-- The username of the database--> <property name="username" value="${jdbc.master.username}" /> <!-- The password of the database--> <property name="password" value="${jdbc.master.password}" /> <!-- Check the interval time of idle connections in the database connection pool. The unit is a fraction. The default value is 240. If you want to cancel, set to 0. --> <property name="idleConnectionTestPeriod" value="60" /> <!-- The maximum number of connections that are not used in the connection pool. The unit is a fraction. The default value is 60. If you want to survive forever, set to 0. --> <property name="idleMaxAge" value="30" /> <!-- The maximum number of connections per partition--> <property name="maxConnectionsPerPartition" value="150" /> <!-- The minimum number of connections per partition--> <property name="maxConnectionsPerPartition" value="150" /> <!-- The minimum number of connections per partition--> <property name="maxConnectionsPerPartition" value="150" /> <!-- The minimum number of connections per partition--> <property name="minConnectionsPerPartition" value="5" /> </bean> <!-- Configure connection pool--> <bean id="slave01DataSource" destroy-method="close"> <!-- Database driver--> <property name="driverClass" value="${jdbc.slave01.driver}" /> <!-- jdbcUrl for the corresponding driver --> <property name="jdbcUrl" value="${jdbc.slave01.url}" /> <!-- Database username--> <property name="username" value="${jdbc.slave01.username}" /> <!-- The password of the database --> <property name="password" value="${jdbc.slave01.password}" /> <!-- Check the interval time of idle connections in the database connection pool. The unit is a fraction. The default value is 240. If you want to cancel, set to 0. --> <property name="idleConnectionTestPeriod" value="60" /> <!-- The maximum survival time of unused links in the connection pool. The unit is a fraction. The default value is 60. If you want to survive forever, set to 0. --> <property name="idleMaxAge" value="30" /> <!-- The maximum number of connections per partition--> <property name="maxConnectionsPerPartition" value="150" /> <!-- Minimum number of connections per partition --> <property name="minConnectionsPerPartition" value="5" /> </bean> 3.5.3. Define DataSource
<!-- Define the data source and use the data source you implement --> <bean id="dataSource"> <!-- Set multiple data sources --> <property name="targetDataSources"> <map key-type="java.lang.String"> <!-- This key needs to be consistent with the key in the program --> <entry key="master" value-ref="masterDataSource"/> <entry key="slave" value-ref="slave01DataSource"/> </map> </property> <!-- Set the default data source, here the default write library --> <property name="defaultTargetDataSource" ref="masterDataSource"/> </bean>
3.6. Configure transaction management and dynamically switch data source surfaces
3.6.1. Defining the Transaction Manager
<!-- Definition Transaction Manager--> <bean id="transactionManager" > <property name="dataSource" ref="dataSource" /> </bean>
3.6.2. Define transaction policies
<!-- Define transaction policy--> <tx:advice id="txAdvice" transaction-manager="transactionManager"> <tx:attributes> <!-- Define query methods are read-only-> <tx:method name="query*" read-only="true" /> <tx:method name="find*" read-only="true" /> <tx:method name="get*" read-only="true" /> <!-- The main library performs operations, and the transaction propagation behavior is defined as the default behavior--> <tx:method name="save*" propagation="REQUIRED" /> <tx:method name="update*" propagation="REQUIRED" /> <tx:method name="delete*" propagation="REQUIRED" /> <!--Other methods use default transaction policy--> <tx:method name="*" /> </tx:attributes> </tx:advice>
3.6.3. Define the facet
<!-- Define AOP section processor--> <bean id="dataSourceAspect" /> <aop:config> <!-- Define sections, all methods of all services--> <aop:pointcut id="txPointcut" expression="execution(* xx.xxx.xxxxxx.service.*.*(..))" /> <!-- Apply transaction policies to Service section--> <aop:advisor advice-ref="txAdvice" pointcut-ref="txPointcut"/> <!-- Apply the section to a custom section processor, -9999 ensures that the section has the highest priority execution-> <aop:aspect ref="dataSourceAspect" order="-9999"> <aop:before method="before" pointcut-ref="txPointcut" /> </aop:aspect> </aop:config>
4. Improve the implementation of the section 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, and we will use the rule matching in the transaction management policy.
4.1. Improved configuration
<!-- Define AOP section processor--> <bean id="dataSourceAspect"> <!-- Specify transaction policy--> <property name="txAdvice" ref="txAdvice"/> <!-- Specify the prefix of the slave method (not required) --> <property name="slaveMethodStart" value="query,find,get"/> </bean>
4.2. Improved implementation
import java.lang.reflect.Field;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.apache.commons.lang3.StringUtils;import org.aspectj.lang.JoinPoint;import org.springframework.transaction.interceptor.NameMatchTransactionAttributeSource;import org.springframework.transaction.interceptor.TransactionAttribute;import org.springframework.transaction.interceptor.TransactionAttributeSource;import org.springframework.transaction.interceptor.TransactionInterceptor;import org.springframework.util.PatternMatchUtils;import org.springframework.util.ReflectionUtils;/** * Defines the AOP section of the data source, which controls whether to use Master or Slave. * * If a transaction policy is configured in transaction management, the method of marking ReadOnly in the configured transaction policy is to use Slave, and the other uses Master. * * If there is no policy to configure transaction management, the principle of method name matching is adopted, and Slave is used as starting with query, find, and get, and other methods are used as Master. * * @author zhijun * */public class DataSourceAspect { private List<String> slaveMethodPattern = new ArrayList<String>(); private static final String[] defaultSlaveMethodStart = new String[]{ "query", "find", "get" }; private String[] slaveMethodStart; /** * Read policies in transaction management* * @param txAdvice * @throws Exception */ @SuppressWarnings("unchecked") public void setTxAdvice(TransactionInterceptor txAdvice) throws Exception { if (txAdvice == null) { // The transaction management policy is not configured return; } // Obtain policy configuration information from txAdvice TransactionAttributeSource transactionAttributeSource = txAdvice.getTransactionAttributeSource(); if (!(transactionAttributeSource instanceof NameMatchTransactionAttributeSource)) { return; } //Use reflection technology to obtain the nameMap attribute value in the NameMatchTransactionAttributeSource object NameMatchTransactionAttributeSource matchTransactionAttributeSource = (NameMatchTransactionAttributeSource) transactionAttributeSource; Field nameMapField = ReflectionUtils.findField(NameMatchTransactionAttributeSource.class, "nameMap"); nameMapField.setAccessible(true); //Set this field to access //Get the value of nameMap<String, TransactionAttribute> map = (Map<String, TransactionAttribute>) nameMapField.get(matchTransactionAttributeSource); //TransactionAttribute> entry : map.entrySet()) { if (!entry.getValue().isReadOnly()) {//After judgment, the ReadOnly policy is defined before adding it to slaveMethodPattern continue; } slaveMethodPattern.add(entry.getKey()); } } /** * Execute before entering the Service method * @param point Face object*/ public void before(JoinPoint point) { // Get the currently executed method name String methodName = point.getSignature().getName(); boolean isSlave = false; if (slaveMethodPattern.isEmpty()) { // There is no transaction policy configured in the current Spring container, and the method name matching method isSlave = isSlave(methodName); } else { // Use policy rules to match for (String mappedName : slaveMethodPattern) { if (isMatch(methodName, mappedName)) { isSlave = true; break; } } } } if (isSlave) { // Mark as read library DynamicDataSourceHolder.markSlave(); } else { // Mark as write library DynamicDataSourceHolder.markMaster(); } } /** * Determine whether it is a read library* * @param methodName * @return */ private Boolean isSlave(String methodName) { // Method name starts with query, find, get return StringUtils.startsWithAny(methodName, getSlaveMethodStart()); } /** * Wildcard matching * * Return if the given method name matches the mapped name. * <p> * The default implementation checks for "xxx*", "*xxx" and "*xxx*" matches, as well as direct * equality. Can be overridden in subclasses. * * @param methodName the method name of the class * @param mappedName the name in the descriptor * @return if the names match * @see org.springframework.util.PatternMatchUtils#simpleMatch(String, String) */ protected boolean isMatch(String methodName, String mappedName) { return PatternMatchUtils.simpleMatch(mappedName, methodName); } /** * The method name prefix of the user specified slave* @param slaveMethodStart */ public void setSlaveMethodStart(String[] slaveMethodStart) { this.slaveMethodStart = slaveMethodStart; } public String[] getSlaveMethodStart() { if(this.slaveMethodStart == null){ // Not specified, use the default return defaultSlaveMethodStart; } return slaveMethodStart; } }5. Implementation of one master and multiple slaves
In many practical usage scenarios, we use the "one master, multiple slave" architecture, so we now support this architecture, and currently only need to modify the DynamicDataSource.
5.1. Implementation
import java.lang.reflect.Field;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.concurrent.atomic.AtomicInteger;import javax.sql.DataSource;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;import org.springframework.util.ReflectionUtils;/** * Define dynamic data sources and implement AbstractRoutingDataSource provided by Spring through integration, you only need to implement the determineCurrentLookupKey method * * Since DynamicDataSource is a singleton and thread-insecure, ThreadLocal is used to ensure thread-safety, which is completed by DynamicDataSourceHolder. * * @author zhijun * */public class DynamicDataSource extends AbstractRoutingDataSource { private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSource.class); private Integer slaveCount; // Poll count, initially -1, AtomicInteger is thread-safe private AtomicInteger counter = new AtomicInteger(-1); // Record the key private List<Object> slaveDataSources = new ArrayList<Object>(0); @Override protected Object determineCurrentLookupKey() { // Use DynamicDataSourceHolder to ensure thread safety and get the data source key in the current thread if (DynamicDataSourceHolder.isMaster()) { Object key = DynamicDataSourceHolder.getDataSourceKey(); if (LOGGER.isDebugEnabled()) { LOGGER.debug("The key of the current DataSource is: " + key); } return key; } Object key = getSlaveKey(); if (LOGGER.isDebugEnabled()) { LOGGER.debug("The key of the current DataSource is: " + key); } return key; } @SuppressWarnings("unchecked") @Override public void afterPropertiesSet() { super.afterPropertiesSet(); // Since the resolvedDataSources property of the parent class is a private subclass that cannot be obtained, you need to use reflection to obtain Field field = ReflectionUtils.findField(AbstractRoutingDataSource.class, "resolvedDataSources"); field.setAccessible(true); // Set accessibility try { Map<Object, DataSource> resolvedDataSources = (Map<Object, DataSource>) field.get(this); // The amount of data in the read library is equal to the total number of data sources minus the number of write libraries this.slaveCount = resolvedDataSources.size() - 1; for (Map.Entry<Object, DataSource> entry : resolvedDataSources.entrySet()) { if (DynamicDataSourceHolder.MASTER.equals(entry.getKey())) { continue; } slaveDataSources.add(entry.getKey()); } } catch (Exception e) { LOGGER.error("afterPropertiesSet error! ", e); } } /** * Polling algorithm implementation* * @return */ public Object getSlaveKey() { // The resulting subscripts are: 0, 1, 2, 3... Integer index = counter.incrementAndGet() % slaveCount; if (counter.get() > 9999) { // To avoid exceeding the Integer range counter.set(-1); // Restore} return slaveDataSources.get(index); }}6. MySQL master-slave replication
6.1. Principle
The principle of copying mysql master (called master) slave (called slave):
1. The master records the data changes into the binary log, that is, the file specified by the configuration file log-bin (these records are called binary log events, binary log events)
2. Slave copy master's binary logvents to its relay log (relay log)
3. The slave reworks events in the relay log will change the data that reflects itself (data replays)
6.2. What should be paid attention to when MACH configuration
1. The versions of the primary DB server and the slave DB server database are the same
2. The database data of the master DB server and the slave DB server are the same [here you can restore the master's backup on the slave, or you can directly copy the master's data directory to the corresponding slave's data directory]
3. The main DB server enables binary logs, and the main DB server and the slave DB server server must be unique.
6.3. Main library configuration (similar to Windows, Linux)
Some friends may not have very clear IP address, username and account configuration of the master and slave database. The following is the master and slave configuration I tested. The IPs are all 127.0.0.1. After I finished my example, I will write it.
A master-slave IP is an example of different configurations. You can use this example to understand the configuration method more intuitively.
Modify under my.ini [mysqld] (as well from the library):
#Enable master-slave replication, the configuration of the main library log-bin= mysql3306-bin#Specify the main library serveridserver-id=101#Specify the synchronized database. If not specified, all databases are synchronized binlog-do-db=mybatis_1128
(The commands entered in my.ini must have a line of space below, otherwise MySQL will not recognize it)
Execute SQL statement query status: SHOW MASTER STATUS
The Position value needs to be recorded, and the synchronization start value needs to be set in the library.
Let me say one more thing. If you execute SHOW MASTER STATUS on mysql and find that the content configured in my.ini has not worked. It may be that you did not choose the my.ini file, or it may be that you did not restart the service. It is very likely that it is caused by the latter.
To make the configuration take effect, you must turn off the MySQL service and restart it.
How to close the service:
Open the win key, enter services.msc to call up the service:
Start SQLyog again and find that the configuration has taken effect.
6.4. Create a synchronous user in the main library
#Authorized user slave01 uses 123456 password to log in mysqlgrant replication slave on *.* to 'slave01'@'127.0.0.1' identified by '123456';flush privileges;
6.5. Configuration from the library
Modify in my.ini:
#Specify serverid, as long as it is not repeated, there is only one configuration from the library, and the others are operated in the SQL statement server-id=102
The following executes SQL (executes using the slave's root account):
CHANGEMATERTOmater_hot='127.0.0.1',//The host's ip address material_uer='lave01',//The host's user (the account just created on the host through ql)mater_paword='123456',mater_port=3306,mater_log_file='myql3306-bin.000006',//Filemate_log_po=1120;//Poition
#Start slave synchronization START SLAVE;#View synchronization status SHOW SLAVE STATUS;
Here are the master and slave configuration methods for two different IP computers:
The operating system where the main database resides: win7
Version of the primary database: 5.0
The IP address of the main database: 192.168.1.111
From the operating system where the database resides: linux
From the version of the data: 5.0
IP address from database: 192.168.1.112
After introducing the environment, let’s talk about the configuration steps:
1. Make sure that the master database is exactly the same as the slave database.
For example: the database of a in the main database has tables b, c, and d, so the database of a and tables b, c, and d should be engraved with a mold.
2. Create a synchronous account on the main database.
The code copy is as follows:
GRANT REPLICATION SLAVE,FILE ON *.* TO 'mstest'@'192.168.1.112' IDENTIFIED BY '123456';
192.168.1.112: It is the IP address that runs using the user
mstest: is the newly created username
123456: It is the password of the newly created username
The detailed explanation of the above command is best done on Baidu. If you write too much, it will make it more unclear.
3. Configure my.ini of the main database (because it is under window, it is my.ini not my.cnf).
[mysqld]server-id=1log-bin=logbinlog-do-db=mstest //To synchronize mstest database, if you want to synchronize multiple databases, add a few more binlog-do-db=database name binlog-ignore-db=mysql //To ignore the database
4. Configure my.cnf from the database.
[mysqld]server-id=2master-host=192.168.1.111master-user=mstest //Step 1. Create the username of the account master-password=123456 //Step 1. Create the password of the account master-port=3306master-connect-retry=60replicate-do-db=mstest //To synchronize the mstest database, to synchronize multiple databases, add a few more replicate-do-db=database name replicate-ignore-db=mysql //The database to be ignored
5. Verify whether it is successful
Enter mysql and enter the command: show slave status/G. The following image will be displayed. If both slave_io_running and slave_sql_running are yes, it means that the synchronization can be successfully
6. Test synchronous data.
Enter the main database and enter the command: insert into one(name) values('beijing');
Then enter the input command from the database: select * from one;
If data is retrieved from the database at this time, it means that the synchronization has been successful and the master and slave will be implemented.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.