The reason for using binlog

Recently, an old system needs to be rebuilt, and changes to orders from several services need to be synchronized in real time into the rebuilt table. There are two choices here,

  1. Send changes to the queue or service before each service’s mysql operation. This scenario requires modifying the code of multiple services and testing the impact on the original system, with additional development and testing costs.
  2. Alter table binlog; update table binlog; update table binlog; But you need to maintain a binlog synchronization service

This time, the binlog synchronization mode is selected. The binlog service can also be used to update the cache and synchronize the ES index of the new system. Compared with burying point, which only has a one-time effect, it has a higher cost performance.

Tools investigated: Canal and mysql-binlog-connector-java

1.canal

Canal is the widely used open source solution to implement the binlog synchronization service, which is stable and has rich functions.

However, when actually deploying the service, we encountered some problems:

  • Canal adopted a client-server model, requiring at least two clusters to be deployed. All of our projects are deployed using private cloud, so there are additional resources and maintenance overhead for stable operation.
    • It was later discovered that Canal Server could deliver information to Kafka. But our message queue is self-developed, so we can only try to change the source code.
  • Canal’s Server is a complete stand-alone package that cannot be nested directly with Springboot. We rely on SpringBoot for basic components such as monitoring, configuration center, etc.
  • Canal’s HA deployment uses ZooKeeper, and unfortunately we don’t have an available Zookeper cluster or the resources to redeploy one.
  • In single-machine deployment, the binlog postion that has been processed is saved in the file, and the private cloud Docker that we used is lost after the restart.

2.mysql-binlog-connector-java

The research also found another binlog synchronization tool, mysql-binlog-connector-java.

This is an open source binlog synchronization tool, the function is very simple, is to receive binlog information. It is easy to use in SpringBoot as a dependency JAR.

However, the contents of binlog are not formatted, which is very inconvenient to use. Of course there is no saving information and distributed deployment.

Self-developed tool package BinlogPortal

Based on these issues, we need a binlog synchronization tool with the following features:

  • Springboot can be used to load and run, with good scalability
    • In other words, as a JAR package, the open outbound interface can customize the way binlog information is handled
  • Binlog position can be saved and distributed using Redis

In order to meet these conditions, a self-developed tool binlogPortal is implemented by encapsulating mysql-binlog-connector-Java.

  • The binlogportal-spring-boot-starter package is provided for rapid deployment using Spring Boot
  • Use Redis to save binlog position information and restart from the last position
  • Structuring of inserts and updates is currently supported
  • Provides a default HTTP event handler. The event handler can be customized by implementing the IEventHandler interface
  • Redis is used as a distributed coordinator for multi-machine deployment to achieve high availability

Directions for use

Mysql configuration

  • Mysql needs binlog enabled and set to row mode
  • Add REPLICATION to the mysql account used to synchronize binlog, as shown in the following example:
CREATE USER binlogportal IDENTIFIED BY '123456';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlogportal'@The '%';
GRANT ALL PRIVILEGES ON *.* TO 'binlogportal'@The '%';
FLUSH PRIVILEGES;
Copy the code

Build the project with Spring Boot

  • Directly rely on binlogportal-spring-boot-starter
<dependency>
  <groupId>com.insistingon.binlogportal</groupId>
  <artifactId>binlogportal-spring-boot-starter</artifactId>
  <version>1.0.5</version>
</dependency>
Copy the code
  • Configure the initiator with Spring Boot’s application.yml
binlogportal:
  enable: true Whether to enable autoconfig
  distributed-enable: true # Whether to enable distributed deployment
  distributed-redis: When # distributed-enable is true, a Redis is provided as the distributed coordinator
    host: 127.0. 01.
    port: 6379
    auth:
  position-redis: Redis to save binlog position must be configured
    host: 127.0. 01.
    port: 6379
    auth:
  db-config: Select * from 'key' where key = 'key'
    d1:
      host: 0.0. 0. 0
      port: 3306
      user-name: binlogportal
      password: 123456
      handler-list: [logEventHandler] # The event handler used by the database, named spring bean name
  http-handler: Enable the HTTP event handler to send requests
    url-list: [http://127.0.0.1:8988/testit] # list of urls to send, HTTP parameters in a uniform format
    result-callback: httpCallBack IHttpCallback (bean name)
Copy the code
  • The Starter to start
    • After spring Boot AutoConfig is successfully started, the BinlogPortalStarter instance is injected into IOC
    • BinlogPortalStarter is used in the project by way of injection
    • BinlogPortalStarter. Start () for each mysql binlog library to create a thread processing
    • The following is an example of using CommandLineRunner to start a starter
@Slf4j
@Component
public class BinlogSync implements CommandLineRunner {
    @Resource
    BinlogPortalStarter binlogPortalStarter;

    public void run(String... args) throws Exception {
        try {
            binlogPortalStarter.start();
        } catch(BinlogPortalException e) { log.error(e.getMessage(), e); }}}Copy the code

Non-spring Boot projects

  • Non-spring Boot projects can use base packages
<dependency>
  <groupId>com.insistingon.binlogportal</groupId>
  <artifactId>binlogportal</artifactId>
  <version>1.0.5</version>
</dependency>
Copy the code
  • Implement configuration classes after dependencyBinlogPortalConfigandSyncConfigAnd run it in the Starter
public class TestClass{
 public static void main(String[] args) {
        SyncConfig syncConfig = new SyncConfig();
        syncConfig.setHost("0.0.0.0");
        syncConfig.setPort(3306);
        syncConfig.setUserName("binlogportal");
        syncConfig.setPassword("123456");

        BinlogPortalConfig binlogPortalConfig = new BinlogPortalConfig();
        binlogPortalConfig.addSyncConfig(syncConfig);

        RedisConfig redisConfig = new RedisConfig("127.0.0.1".6379);
        RedisPositionHandler redisPositionHandler = new RedisPositionHandler(redisConfig);
        binlogPortalConfig.setPositionHandler(redisPositionHandler);

        binlogPortalConfig.setDistributedHandler(new RedisDistributedHandler(redisConfig));

        BinlogPortalStarter binlogPortalStarter = new BinlogPortalStarter();
        binlogPortalStarter.setBinlogPortalConfig(binlogPortalConfig);
        try {
            binlogPortalStarter.start();
        } catch(BinlogPortalException e) { e.printStackTrace(); }}}Copy the code

2. Distributed deployment

The high availability implementation in the project is a distributed lock based on Redis.

Each instance loads the configuration of the entire database. Before creating a binlog connection, the redis lock is acquired, and the expiration time of the lock is periodically updated. All instances are re-locked periodically.

Binlog files and positions from the same mysql library are saved in Redis if an instance goes down. The newly acquired lock instance is initialized using the binlog information saved by the previous instance.

The project source code is available at Git. Project Git address: github.com/dothetrick/…

Click here to jump to the Git project location

The above content is a personal learning summary, if there is any inappropriate, welcome to point out in the comments