Shulou(Shulou.com)06/02 Report--

It is believed that many inexperienced people have no idea about how to switch between master and slave data sources in SpringBoot. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

SpringBoot master-slave data source switch 1. Principle

With the help of the abstract class implementation of [org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource] of spring, the data source is routed and the route is selected through Aop.

two。 When configuring master-slave data source # dev server# multi-data source, when the master data source is masterspring.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/epoint?characterEncoding=utf8&allowMultiQueries=true&useSSL=false&autoReconnect=true&failOverReadOnly=falsespring.datasource.master.username=testspring.datasource.master.password=test# dev server# multi-data source Start error processing spring boot: error querying database for slavespring.datasource.slave.jdbc-url=jdbc:mysql://localhost:3306/epoint2?characterEncoding=utf8&allowMultiQueries=true&useSSL=false&autoReconnect=true&failOverReadOnly=falsespring.datasource.slave.username=testspring.datasource.slave.password=test from the data source. Cause: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required configure multiple data sources to start error reporting, error querying database. Cause: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required, the main reason is that in the process of configuring the data source, it is mainly written as: spring.datasource.url and spring.datasource.driverClassName. After the 2. 0 upgrade, it needs to be changed to: spring.datasource.jdbc-url and spring.datasource.driver-class-name! Change the configuration: spring.datasource.master.url-> spring.datasource.master.jdbc-url3. Get the data source type of the current thread / * describe: define the HandleDataSource class to get the data source type of the current thread * current user Maochao.zhu * current system 2020-9-15 * / public class HandleDataSource {public static final ThreadLocal holder = new ThreadLocal (); / * * bind the current thread data source * * @ param datasource * / public static void putDataSource (String datasource) {holder.set (datasource) } / * get the data source of the current thread * * @ return * / public static String getDataSource () {return holder.get ();}} 3.1 Thread conflict problem

Note: because the new database threading class conflicts with the existing multithreading class, it will cause the existing program to "stutter" or "crash". Therefore, the original timed task multithreading configuration is removed.

/ * describe: configure multithreaded timer * current user Maochao.zhu * current system 2020-1-20 * / @ Configuration@EnableSchedulingpublic class ScheduleConfig implements SchedulingConfigurer {@ Override public void configureTasks (ScheduledTaskRegistrar taskRegistrar) {Method [] methods = BatchProperties.Job.class.getMethods (); int defaultPoolSize = 3; int corePoolSize = 0 If (methods! = null & & methods.length > 0) {for (Method method: methods) {Scheduled annotation = method.getAnnotation (Scheduled.class); if (annotation! = null) {corePoolSize++;} if (defaultPoolSize > corePoolSize) corePoolSize = defaultPoolSize } taskRegistrar.setScheduler (Executors.newScheduledThreadPool (corePoolSize));} 4. Define the implementation class of routing data source / * describe: define the implementation class MyAbstractRoutingDataSource * current user Maochao.zhu * current system 2020-9-15 * / public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {private final Logger log = LoggerFactory.getLogger (this.getClass ()); @ Override protected Object determineCurrentLookupKey () {log.info ("# request data source: {}", HandleDataSource.getDataSource ()); return HandleDataSource.getDataSource () / / get the corresponding data source}} 5. Configure data source and routing / * describe: configure data source and routing configuration * current user Maochao.zhu * current system 2020-9-15 * / @ Configurationpublic class DataSourceConfig {/ / main data source @ Bean () @ ConfigurationProperties (prefix = "spring.datasource.master") public DataSource MasterDataSource () {return DataSourceBuilder.create () .build () } / / set the route from the data source @ Bean () @ ConfigurationProperties (prefix = "spring.datasource.slave") public DataSource SlaveDataSource () {return DataSourceBuilder.create () .build ();} / *, and decide which data source to use through the determineCurrentLookupKey in this class * / @ Bean public AbstractRoutingDataSource routingDataSource () {MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource () Map targetDataSources = new HashMap (2); / / stores the mapping targetDataSources.put ("master", MasterDataSource ()); targetDataSources.put ("slave", SlaveDataSource ()); proxy.setDefaultTargetDataSource (MasterDataSource ()); proxy.setTargetDataSources (targetDataSources); return proxy;} @ Bean (name = "SqlSessionFactory") @ Primary public SqlSessionFactory MasterSqlSessionFactory (DataSource routingDataSource) throws Exception {SqlSessionFactoryBean bean = new SqlSessionFactoryBean () Bean.setDataSource (routingDataSource); / / DataSource uses routing data source ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver (); try {bean.setMapperLocations (resolver.getResources ("classpath*:mapper/**/*.xml")); bean.setConfigLocation (resolver.getResource ("classpath:mybatis-config.xml")); return bean.getObject ();} catch (Exception e) {e.printStackTrace () Throw new RuntimeException (e);} @ Bean (name = "TransactionManager") @ Primary public DataSourceTransactionManager testTransactionManager (DataSource routingDataSource) {return new DataSourceTransactionManager (routingDataSource);} @ Bean (name = "SqlSessionTemplate") @ Primary public SqlSessionTemplate MasterSqlSessionTemplate (SqlSessionFactory sqlSessionFactory) throws Exception {return new SqlSessionTemplate (sqlSessionFactory);}} 5.1 launch permission shiro question

Note: after configuring the routing settings, the original apache.shiro permissions cannot be read. After troubleshooting, it is determined that the reason is the use of the configuration routing settings. The mybaits attribute originally configured in application.properties will not work, so you need to add a new configuration file (mybatis-config.xml) to read the configuration information. The new annotation support is added in the ShiroConfig configuration class.

/ * enable Shiro annotations (such as @ RequiresRoles,@RequiresPermissions), scan classes that use Shiro annotations with SpringAOP, and verify security logic if necessary * configure the following two bean (DefaultAdvisorAutoProxyCreator and AuthorizationAttributeSourceAdvisor) to achieve this feature * @ return * / @ Bean@ConditionalOnMissingBeanpublic DefaultAdvisorAutoProxyCreator defaultAdvisorAutoProxyCreator () {DefaultAdvisorAutoProxyCreator advisorAutoProxyCreator = new DefaultAdvisorAutoProxyCreator (); advisorAutoProxyCreator.setProxyTargetClass (true); return advisorAutoProxyCreator;} / * * enable shiro aop annotation support. * use proxy mode; therefore, code support needs to be enabled; * @ param securityManager * @ return * * / @ Beanpublic AuthorizationAttributeSourceAdvisor authorizationAttributeSourceAdvisor (SecurityManager securityManager) {AuthorizationAttributeSourceAdvisor authorizationAttributeSourceAdvisor = new AuthorizationAttributeSourceAdvisor (); authorizationAttributeSourceAdvisor.setSecurityManager (securityManager); return authorizationAttributeSourceAdvisor;} 6. Load mybatis configuration 7. Create a new annotation to annotate the data source used / * describe:DataSource annotation to annotate the data source to be used by the Mapper API * current user Maochao.zhu * current system 2020-9-15 * / @ Retention (RetentionPolicy.RUNTIME) @ Target (ElementType.METHOD) public @ interface DataSource {String value (); / / set the data source type} 8. Configure Aop/** * describe: configure Aop handoff routing * current user Maochao.zhu * current system 2020-9-15 * / @ Aspect@Componentpublic class DataSourceAspect {public Logger logger = LoggerFactory.getLogger (this.getClass ()) / * * before the dao layer method gets the datasource object, specify the current thread data source * / @ Pointcut ("execution (* com.cn.zx.dao..*.* (..)") / / pointcut as all mapper interfaces public void pointcut () {} @ Before ("pointcut ()") public void before (JoinPoint point) {System.out.println ("before") Object target = point.getTarget (); String method = point.getSignature () .getName (); Class [] classz = target.getClass () .getInterfaces (); / / get the interface of the target class, so @ DataSource needs to write on the interface Class [] parameterTypes = ((MethodSignature) point.getSignature ()) .getMethod () .getParameterTypes (); try {Method m = classz [0] .getMethod (method, parameterTypes) If (m! = null & & m.isAnnotationPresent (DataSource.class)) {DataSource data = m.getAnnotation (DataSource.class); System.out.println ("# user selects database type:" + data.value ()); HandleDataSource.putDataSource (data.value ()) / / put the data source in the current thread} logger.info ("execute interface method: {}. {}", point.getSignature (). GetDeclaringTypeName (), point.getSignature (). GetName ());} catch (Exception e) {e.printStackTrace ();} 9. Set the data source type

Use the annotation DataSource ("master/slave") on the corresponding mapper method to set the data source type

/ * call master data source master * @ param user * @ return * / @ DataSource ("master") Integer insertUser (User user); / * call slave data source slave * @ param user * @ return * / @ DataSource ("slave") List getUserList (User user); 10. Transaction failure problem

After adding the master-slave database, the transaction is invalid, and the reason is still being found. The preliminary positioning is: the execution order of transactions and aspects @ EnableTransactionManagement (order = 2) @ Order (2)

Resolve:

The SpringbootApplication startup class adds annotations to enable transaction annotations, and the database table engine is changed to innodb. If it is myisam, the transaction does not work @ EnableTransactionManagement

There are two ways to manage transactions: the first is programmatic transaction management, which requires canceling the automatic commit of the database, and writing transaction code by yourself. The second: declarative transaction management mode, spring uses the spring AOP feature to write annotations.

The characteristic of 10.1@Transactional annotations: add @ Transactional to the 1.service class tag (generally not recommended on the interface) to incorporate the entire class into spring transaction management. A transaction is opened when each business method executes, but these transactions are managed in the same way. And when a method in a service implementation class calls another method in this implementation class, both methods must declare a transaction before they can be managed as a transaction. 2.@Transactional annotations can only be applied to methods with public visibility. If applied to protected, private, or package visibility methods, there will be no error, but transaction settings will not work. 3. By default, spring does a transactional rollback of unchecked exceptions; if it is a checked exception, it does not roll back. 10.2checked exception

So what is a checked exception and what is a unchecked exception? in java, exceptions derived from Error or RuntimeException (such as null pointer, 1RuntimeException 0) are called unchecked exceptions, and other exceptions inherited from java.lang.Exception are collectively referred to as Checked Exception, such as IOException, TimeoutException, and so on. In general, exceptions such as null pointers that occur when you write code will be rolled back, files will be read and written, and there will be network problems, and spring will not be able to roll back.

The read-only transaction @ Transactional (propagation=Propagation.NOT_SUPPORTED,readOnly=true) read-only flag is applied only when the transaction starts, otherwise even the configuration will be ignored. Starting a transaction increases thread overhead, and the database is locked due to shared reads (depending on the database type and transaction isolation level). In general, there is no need to set up read-only transactions to add additional system overhead when only reading data. Transaction propagation mode Propagation enumerates a variety of transaction propagation modes, some of which are listed as follows: 1. REQUIRED (default mode): business methods need to run in a container. If the method is already in a transaction when it is running, join the transaction, otherwise create a new transaction yourself. 2. NOT_SUPPORTED: declare that the method does not require a transaction. If the method is not associated with a transaction, the container will not open the transaction for him, if the method is called in a transaction, the transaction will be suspended, and after the call ends, the original transaction will resume execution. 3. REQUIRESNEW: this method always initiates a new transaction for itself, regardless of whether there is a transaction or not. If the method is already running in a transaction, the original transaction is suspended and a new transaction is created. 4. MANDATORY: this method can only be executed in an existing transaction, and the business method cannot initiate its own transaction. If called without a transaction, the container throws an exception. 5. SUPPORTS: if the method is called within the scope of a transaction, the method becomes part of the transaction. If the method is called outside the scope of the transaction, the method is executed in an environment where there is no transaction. 6. NEVER: this method must not be executed within the scope of a transaction. Make an exception if you are here. The method executes normally only if the method is not associated with any transaction. 7. NESTED: if an active transaction exists, it runs in a nested transaction. If there is no active transaction, press the REQUIRED property to execute. It uses a separate transaction that has multiple Savepoints that can be rolled back. The rollback of the internal transaction does not affect the external transaction. It works only for DataSourceTransactionManager transaction managers. 10.5 fixed that Transactional comments will not be rolled back 1. Check to see if your method is public. two。 Whether your exception type is unchecked exception. Null pointer exception is a unchecked exception. What if I want to roll back the check exception? just indicate the exception type above. @ Transactional (rollbackFor= {Exception.class.RuntimeException.class}) is similar to norollbackFor, which customizes exceptions that are not rolled back. If the try catch operation has been performed in service, the transaction will not roll back 3. 0 because the exception has been caught. The database engine supports transactions. If it is mysql, note that the table uses an engine that supports transactions, such as innodb. If it is myisam, transactions do not work. 4. Whether the parsing of annotations is enabled in 4. 1 SpringMVC: open in 4. 2 pringboot: start classes add annotations: @ EnableTransactionManagement class methods add annotations: @ Transactional5. Whether spring scans your package, the following is the database engine settings of package 10.6 scanned under org.test.

Mysql Database engine innodb Settings

10.6.1 View supported engine show engines;10.6.2 View default engine show variables like 'default_storage_engine' 10.6.3 modify default engine modify mysql default database engine add default-storage-engine=INNODB under [mysqld] in configuration file my.ini if it cannot be started, find the skip-innodb entry and change it to # skip-innodb (otherwise the InnoDB service cannot be started) restart the Mysql server, and the setting takes effect. 10.6.4 modify the datasheet engine

If the database is named epoint, modify the database table engine from MyISAM-> InnoDB

10.6.5 query all table status SHOW TABLE STATUS FROM epoint;10.6.6 query modify table engine SQLSELECT GROUP_CONCAT (CONCAT ('ALTER TABLE', TABLE_NAME,' ENGINE=InnoDB;') SEPARATOR') FROM information_schema.TABLES AS t WHERE TABLE_SCHEMA = 'epoint' AND TABLE_TYPE =' BASE TABLE';10.6.7 get SQL execute ALTER TABLE branch ENGINE=InnoDB;10.6.8 query all table status SHOW TABLE STATUS FROM epoint

The engine that modifies all database tables ends with InnoDB

11. Synchronization of master-slave database

Master-slave database data synchronization problem location: 1. Program synchronization. two。 Database operation synchronization data because to read and write separation, it is necessary to set database permissions, the master database can read and write, the slave database can only read, so this method does not work, can only set master-slave data synchronization from the database.

1. View the installation path of mysql

View the installation path of mysql through the mysql command:

Select @ @ basedir as basePath from dual;SELECT @ @ basedir

Get the path: C:\ Program Files\ MySQL\ MySQL Server 5.7\

two。 Configure the master database

Find [mysqld] in the my.ini file and add the following configuration (you can write in as many databases as you need to synchronize. Master-slave synchronization will find the corresponding cluster database based on the library name to synchronize the data)

Server-id=1# master database and slave database need inconsistent log-bin=mysql-binbinlog-do-db=mstest# synchronization binlog-ignore-db=mysql# does not need synchronized database 2.1 restart MySql service, query master library status mysql > SHOW VARIABLES LIKE 'server_id' +-+-+ | Variable_name | Value | +-+-+ | server_id | 1 | +-+-+ 1 row in setmysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | mysql-bin.000007 | 5138 | epoint | +- -+ 1 row in set2.2 to create an account for slave database Assign all permissions grant all on *. * to 'user'@'%' identified by' user' 3. Configure slave database

Find [mysqld] in the my.ini file and add the following configuration (you can write in as many databases as you need to synchronize. Master-slave synchronization will find the corresponding cluster database based on the library name to synchronize the data)

Server-id=2# is inconsistent with the master library replicate-do-db=test# libraries that need to be synchronized 1replicate-ignore-db=mysql# does not need synchronized libraries 3.1 start the copy from library function STOP SLAVE; # stop the command change master to master_host='127.0.0.1',master_port=3306,master_user='slave',master_password='123',master_log_file='mysql-bin.000004',master_log_pos=717 of the copy from function

Description: corresponding to your own configuration, master_host: ip,master_port of the main library: the port of the main library, master_user: the account name established by the main library to the cluster library, master_password: account password about master_log_file and Position ('mysql-bin.000005' 98) is obtained by "show master status" in the configuration of the main library.

START SLAVE; # starts the copy from function RESET SLAVE; # resets the configuration of the copy from function, clears the master.info and relay-log.info files show slave status; (without semicolons), check

The Slave_IO_Running and Slave_SQL_Running attributes are turned on, indicating that they are enabled.

Slave_IO_Running: YesSlave_SQL_Running: Yes

End of configuring MySQL master-slave database

twelve。 Refer to blog information > master-slave Mysql database synchronization: https://blog.csdn.net/fengrenyuandefz/article/details/89420201> one computer with two MySQL: https://blog.csdn.net/weixin_41953055/article/details/79820221> server_uuid repeat: https://blog.csdn.net/sunbocong/article/details/81634296> mysql master-slave synchronization binlog-do-db replicate-do-db: https://blog.csdn.net/z69183787/article/details/70183284> mysql master-slave Database synchronization: https://blog.csdn.net/fengrenyuandefz/article/details/89420201 read the above content Have you mastered the method of switching between master and slave data sources in SpringBoot? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.