Recently the work task is light, free to learn learning technology, then to study how to achieve reading and writing separation. Here with the blog record process, on the one hand can be ready for the future view, but also can share to everyone (online information is really mostly copied to copy, but also do not take the format, look really uncomfortable).

Full code: github.com/FleyX/demo-…

1, the background

The most basic database in a project is also the most mainstream stand-alone database, read and write in a library. When the number of users gradually increases and the single-server database cannot meet the performance requirements, the system implements read and write separation (suitable for reading more than one database and reading more than one database). In general, a database cluster is created and primary/secondary backup is enabled to improve the read performance. Distributed databases are needed (we’ll probably learn how to do that later) when users can’t afford more read/write separation.

Under normal circumstances, to achieve read and write separation, the first step is to make a database cluster with one master and many slaves, and also need to carry out data synchronization. This article describes how to set up a single master multiple configuration with mysql, and the next article describes how to achieve read and write separation at the code level.

2. Set up a primary and multiple secondary database cluster

Multiple VMS are required for primary and secondary backup. I used Wmware to clone multiple instances. Note that directly cloning VMS will cause the same UUID for each database, so you need to change the UUID to different ONES. Modify method refer to this: click jump.

  • The main library configuration

    Create a user in the master database to read binary logs from the slave database. The SQL statement is as follows:

    mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456'; Mysql > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; Mysql > grant privileges; # refresh permissionCopy the code

    At the same time, modify the mysql configuration file to enable binary logging.

    [mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
Copy the code

    Then restart the database and run show master status; Statement to check the state of the master library as follows:

  • From library configuration

    Also add a few new configuration lines:

    [mysqld]
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
Copy the code

    Then restart the database and connect to the primary library using the following statement:

    CHANGE MASTER TO
         MASTER_HOST='192.168.226.5',
         MASTER_USER='root',
         MASTER_PASSWORD='123456',
         MASTER_LOG_FILE='master-bin.000003',
         MASTER_LOG_POS=154;
Copy the code

    Then run start slave; The following figure shows the normal situation: Slave_IO_Running and Slave_SQL_Running are both yes.

Multiple slave libraries can be opened using this step.

By default, all operations of the master database are backed up to the slave database. In fact, some libraries may need to be ignored. You can add the following configuration to the master database:

Binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema Other not synchronized binlog-do-db = gameCopy the code

3. Read and write separation at the code level

The code environment is SpringBoot + Mybatis + Druib connection pool. Multiple data sources need to be configured to separate read and write operations. Select the data source for write operations and the data source for read operations. There are two key points:

  • How to switch data sources
  • How do you choose the right data source for different methods

1) How to switch data sources

Usually springBoot uses its default configuration. You only need to define the connection properties in the configuration file, but now you need to configure it yourself. Spring supports multiple data sources. Multiple datasource in a HashMapTargetDataSource, through dertermineCurrentLookupKey obtain the key to which the data source to use. So our goal is very clear, establish multiple datasource into TargetDataSource, rewrite dertermineCurrentLookupKey method is used to determine which key at the same time.

2) How to select data source

Transactions are typically annotated at the Service layer, so the data source needs to be identified at the start of the Service method call. Is there a common way to do something before starting a method? I’m sure you’ve already figured that out. There are two ways to cut it:

  • Annotated, defines a read-only annotation that is used by the data annotated method read library
  • Method name, according to the method name write pointcut, such as getXXX use read library, setXXX use write library

3) Code writing

A. Write configuration files to configure the information of the two data sources

Only mandatory information, everything else has default Settings

mysql:
  datasource:
    # Number of read libraries
    num: 1
    type-aliases-package: com.example.dxfl.dao
    mapper-locations: classpath:/mapper/*.xml
    config-location: classpath:/mybatis-config.xml
    write:
      url: JDBC: mysql: / / 192.168.226.5:3306 / test? useUnicode=true&characterEncoding=utf-8&useSSL=true
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    read:
      url: JDBC: mysql: / / 192.168.226.6:3306 / test? useUnicode=true&characterEncoding=utf-8&useSSL=true
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
Copy the code

B. Write DbContextHolder

This class sets the database category, with a ThreadLocal that holds whether each thread is using a read or write library. The code is as follows:

ThreadLocal is used to store whether or not the current thread is in read mode (use the start READ_ONLY annotation to set the mode to read mode before the operation starts, and clear the data after the operation ends to avoid memory leaks). It is also intended to be in read mode for subsequent write operations on the thread@author fxb
 * @dateThe 2018-08-31 * /
public class DbContextHolder {

    private static Logger log = LoggerFactory.getLogger(DbContextHolder.class);
    public static final String WRITE = "write";
    public static final String READ = "read";

    private static ThreadLocal<String> contextHolder= new ThreadLocal<>();

    public static void setDbType(String dbType) {
        if (dbType == null) {
            log.error("DbType is empty");
            throw new NullPointerException();
        }
        log.info("Set dbType to {}",dbType);
        contextHolder.set(dbType);
    }

    public static String getDbType(a) {
        return contextHolder.get() == null ? WRITE : contextHolder.get();
    }

    public static void clearDbType(a) { contextHolder.remove(); }}Copy the code

C, rewrite determineCurrentLookupKey method

Spring uses this method to determine which database to use when starting a database operation, so we call the getDbType() method of DbContextHolder above to get the current operation category and load balance the library as follows:

public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {

    @Value("${mysql.datasource.num}")
    private int num;

    private final Logger log = LoggerFactory.getLogger(this.getClass());

    @Override
    protected Object determineCurrentLookupKey(a) {
        String typeKey = DbContextHolder.getDbType();
        if (typeKey == DbContextHolder.WRITE) {
            log.info("Use the write library.");
            return typeKey;
        }
        // Use a random number to determine which library to use
        int sum = NumberUtil.getRandom(1, num);
        log.info("Use read library {}", sum);
        returnDbContextHolder.READ + sum; }}Copy the code

D. Write configuration classes

Instead of using springBoot’s default configuration for read/write separation, we need to configure it manually. The data source is generated automatically using @configurProperties:

	/** * write data source **@PrimaryIndicates that this Bean is considered first when there are more than one similar Bean candidate. * When configuring multiple data sources, note that there must be a primary data source, using@PrimaryFlag the Bean */
    @Primary
    @Bean
    @ConfigurationProperties(prefix = "mysql.datasource.write")
    public DataSource writeDataSource(a) {
        return new DruidDataSource();
    }
Copy the code

The Bean name is read+ serial number.

And then set up the data source, use before we write MyAbstractRoutingDataSource classes

	/ * * * set up the data source routing, through which data source * / determineCurrentLookupKey decided to use in the class
    @Bean
    public AbstractRoutingDataSource routingDataSource(a) {
        MyAbstractRoutingDataSource proxy = new MyAbstractRoutingDataSource();
        Map<Object, Object> targetDataSources = new HashMap<>(2);
        targetDataSources.put(DbContextHolder.WRITE, writeDataSource());
        targetDataSources.put(DbContextHolder.READ+"1", read1());
        proxy.setDefaultTargetDataSource(writeDataSource());
        proxy.setTargetDataSources(targetDataSources);
        return proxy;
    }
Copy the code

Then you need to set up the sqlSessionFactory

	/** * Multiple data sources need to set sqlSessionFactory */
    @Bean
    public SqlSessionFactory sqlSessionFactory(a) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(routingDataSource());
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        // The location of the entity class
        bean.setTypeAliasesPackage(typeAliasesPackage);
        // Configure mybatis XML
        bean.setMapperLocations(resolver.getResources(mapperLocation));
        bean.setConfigLocation(resolver.getResource(configLocation));
        return bean.getObject();
    }
Copy the code

Finally, you must configure the transaction, otherwise the transaction will not take effect

	/** * sets up a transaction that needs to know which data source is currently being used */
    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager(a) {
        return new DataSourceTransactionManager(routingDataSource());
    }
Copy the code

4) Select the data source

Multiple data sources are configured, but what about selecting data sources at the code level? Here are two ways:

A, annotated

First, define a read-only annotation, which is used by the annotation method in the read library, and the others in the write library. This method can be used if the project is mid-way through the transformation to read/write separation. There is no need to modify the business code, just add an annotation to the read-only Service method.

@Target({ElementType.METHOD,ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ReadOnly {
}
Copy the code

Then write a section to switch the data use which kinds of data sources, rewrite the getOrder guarantee this aspect priority above affairs aspect priority, add @ EnableTransactionManagement at startup class (order = 10), in order to code is as follows:

@Aspect
@Component
public class ReadOnlyInterceptor implements Ordered {
    private static final Logger log= LoggerFactory.getLogger(ReadOnlyInterceptor.class);

    @Around("@annotation(readOnly)")
    public Object setRead(ProceedingJoinPoint joinPoint,ReadOnly readOnly) throws Throwable{
        try{
            DbContextHolder.setDbType(DbContextHolder.READ);
            return joinPoint.proceed();
        }finally {
            // Clear DbType to avoid memory leaks and, more importantly, to avoid subsequent operations on this thread
            DbContextHolder.clearDbType();
            log.info("Remove threadLocal"); }}@Override
    public int getOrder(a) {
        return 0; }}Copy the code

B. Method name formula

SetXXX is set to write, getXXX is set to read, and setXXX is set to read.

4, test,

Write the code to see what happens. Here’s a screenshot of the run:

Read/write separation is only a temporary solution for database expansion, and cannot be a permanent solution. With the further increase of load, only one library for writing is certainly not enough, and the single-table database is capped, mysql can maintain good query performance with up to ten million levels of data. It will eventually become a separate database and separate table architecture. Depots table can look at this article: www.tapme.top/blog/detail…

Original published in this article: www.tapme.top/blog/detail…