Implementation steps for SpringBoot+MyBatis-Flex to configure ProxySQL

🎯 Target

Connect ProxySQL in Spring Boot.
Use MyBatis-Flex to access the database.
Configure read-write separation and delay detection.

🔧Step 1: Ensure that ProxySQL and MySQL master-slave synchronization are correctly configured

First, make sure you have correctly configured ProxySQL and MySQL master-slave synchronization.

ProxySQL default configuration

Hostgroup ID	describe
10	Main library (write operation)
20	From the library (read operation)

ProxySQL data port

Port: 6033 (default data interface port)

🔧Step 2: Introducing dependencies in Spring Boot project

Introducing MyBatis-Flex dependencies

existAdd the following dependencies to:

<dependency>
    <groupId></groupId>
    <artifactId>mybatis-flex-spring-boot-starter</artifactId>
    <version>1.4.3</version>
</dependency>

🔧Step 3: Configure

existConfigure the data source of ProxySQL in the file.

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:6033/db_order_plus?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai
    username: root
    password: root
  mybatis-flex:
    mapper-locations: classpath:mapper/**/*
    configuration:
      map-underscore-to-camel-case: true

🔧 Step 4: Configure read and write separation of data sources

Configure read and write separation rules in ProxySQL:

Execute the following SQL in the ProxySQL management interface

INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT.*', 20),
       (2, 1, '^INSERT.*|^UPDATE.*|^DELETE.*', 10);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

🔧Step 5: Create MyBatis-Flex's Mapper and Entity Class

1. Create entity class

existCreate an entity class in the package, for example：

package ;

public class Order {
    private Long id;
    private String orderName;

    // Getters and Setters
}

2. Create a Mapper interface

existCreate a Mapper interface in the package:

package ;

import ;
import ;

public interface OrderMapper {
    
    @Select("SELECT * FROM orders WHERE id = #{id}")
    Order selectOrderById(Long id);
}

3. Create a Mapper XML file

existsrc/main/resources/mapperCreated in the directory：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-////DTD Mapper 3.0//EN"
    "/dtd/">

<mapper namespace="">
    <select  resultType="">
        SELECT * FROM orders WHERE id = #{id}
    </select>
</mapper>

🔧Step 6: Create Service and Controller

1. Create Service

existCreate one in the packageOrderService：

package ;

import ;
import ;
import ;

@Service
public class OrderService {

    private final OrderMapper orderMapper;

    public OrderService(OrderMapper orderMapper) {
         = orderMapper;
    }

    public Order getOrderById(Long id) {
        return (id);
    }
}

2. Create Controller

existCreate one in the packageOrderController：

package ;

import ;
import ;
import ;
import ;
import ;

@RestController
public class OrderController {

    private final OrderService orderService;

    public OrderController(OrderService orderService) {
         = orderService;
    }

    @GetMapping("/orders/{id}")
    public Order getOrderById(@PathVariable Long id) {
        return (id);
    }
}

🔧 Step 7: Test Read and Write Separation

Start Spring Boot project:

mvn spring-boot:run

Test write operation (INSERT/UPDATE/DELETE)

Through Navicat or other tools, write operations to the database to ensure that these operations are routed to the main library.

Test Read Operation (SELECT)

Visithttp://localhost:8080/orders/{id}, verify that the read operation is routed to the slave library.

🔧 Step 8: Configure delay detection

Enable Delay Detection in ProxySQL:

SET mysql-monitor_replication_lag_interval_ms = 1000;

INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, max_replication_lag)
VALUES (10, 20, 'seconds_behind_master', 5);

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

✅ Summary

step	describe
Install MyBatis-Flex	Introducing MyBatis-Flex in the project
Configure data source	existData source configured in ProxySQL
Configure read and write separation rules	Configuring read and write separation rules in ProxySQL
Create entity classes, Mappers, Services, Controllers	Implement database access logic
Enable Delay Detection	Enable Delay Detection in ProxySQL

This is the article about the implementation steps of SpringBoot+MyBatis-Flex configuration ProxySQL. For more related SpringBoot MyBatis-Flex configuration ProxySQL content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!