SoFunction
Updated on 2025-04-07

How to use Spring Boot and Canal to implement MySQL database synchronization

Preface

In distributed systems, data synchronization is a common requirement. For example, we might need to synchronize the data from the master library to multiple slave libraries in real time, or synchronize the data from one database cluster to another. This article uses a practical case to introduce how to use Spring Boot and Canal to achieve data synchronization between MySQL databases.

1. Background

Suppose we have the following database schema:

  • Two main libraries: db_1 and db_2.
  • Each master library corresponds to two slave libraries: db_1_bk_1, db_1_bk_2 and db_2_bk_1, db_2_bk_2.
  • Our goals are:
  • Synchronize the data of db_1 to db_1_bk_1 and db_1_bk_2.
  • Synchronize the data of db_2 to db_2_bk_1 and db_2_bk_2.

2. Introduction to Canal

Canal is an incremental data subscription and distribution tool based on MySQL Binlog by Alibaba. It simulates MySQL slave nodes, captures the Binlog log of the master library in real time, and pushes data change events to downstream consumers. Canal supports a variety of downstream adapters such as Kafka, RabbitMQ and direct consumption.

3. Main library database configuration

1. Main library configuration

In order for Canal to parse Binlog logs normally, the main library needs to perform the following configuration:

  • Turn on Binlog log: Make sure that the main library has Binlog log enabled and set to ROW mode.
  • Configure server-id: Set a unique server-id for each main library.
  • Create a Canal user and grant permissions: Create a user for Canal to use and grant the necessary permissions.

Edit the configuration file (or) of the main library and add the following:

[mysqld]
# Enable Binlog loglog-bin=mysql-bin
# Set the Binlog format to ROW modebinlog-format=ROW
# Set a unique server-idserver-id=1

Notice:

  • If you have multiple master libraries, the server-id of each master must be unique.
  • After modifying the configuration, the MySQL service needs to be restarted to make the configuration take effect.

2. Create a Canal user and grant permissions

Canal requires a MySQL user with Binlog permission. Here are the steps to create a user and grant permissions:

# Log in to MySQLmysql -u root -p
# Create a userCREATE USER 'canal'@'%' IDENTIFIED BY 'canal';
# Grant permissionsGRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal';
# Refresh permissionsFLUSH PRIVILEGES;

illustrate:

  • The canal user needs sufficient permissions to read Binlog data, but does not need to write to the database.
  • If your MySQL version is newer(), you may need to use the ALTER USER command to set the password:
ALTER USER 'canal'@'%' IDENTIFIED BY 'canal';

4. Configure Canal Server

Canal Server is the core component of Canal, which is responsible for connecting to the main library and parsing Binlog data. We need to configure one Canal instance for each main library.

Server configuration file

In the Canal Server configuration directory, create two instance configuration files: conf/db_1/ and conf/db_2/.
conf/db_1/：

# Address and port of the main library=db_1_ip:3306
# Canal Username and Password for the main library=canal
=canal
# The table regular expression that needs to be synchronized, which means that all tables in the db_1 database are synchronized=db_1\\..*

conf/db_2/：

# Address and port of the main library=db_2_ip:3306
# Canal Username and Password for the main library=canal
=canal
# The table regular expression that needs to be synchronized, which means that all tables in the db_2 database are synchronized=db_2\\..*

2. Start Canal Server

Start Canal Server with the following command:

nohup sh bin/ start &

Notice:

  • Make sure the Binlog location and file name of the main library are correct. If you are not sure, you can view it through the SHOW MASTER STATUS; command.
  • If the main library has been running for a while, you need to specify the starting location of the Binlog to avoid repeated synchronization of old data.

5. Develop Spring Boot Client

As Canal's message consumer, the Spring Boot client is responsible for receiving data change events and syncing them to the target slave library.

1. Introduce dependencies

In Spring Boot ProjectIn the file, introduce Canal client dependencies:

<dependency>
    <groupId></groupId>
    <artifactId></artifactId>
    <version>1.1.8</version>
</dependency>

2. Configure the Canal client

existIn the file, configure the address of Canal Server:

canal:
  : canal_server_ip
  : 11111

3. Implement data synchronization logic

Create a Canal client service class that receives and processes data change events.
：

@Service
public class CanalClientService {
    private final CanalConnector canalConnector;
    public CanalClientService(@Value("${}") String canalServerIp, @Value("${}") int canalServerPort) {
         = (new InetSocketAddress(canalServerIp, canalServerPort), "example", "", "");
    }
    @PostConstruct
    public void start() {
        ();
        ("db_1..*, db_2..*"); // Subscribe to all tables for db_1 and db_2        new Thread(this::process).start();
    }
    private void process() {
        while (true) {
            Message message = (100);
            long batchId = ();
            if (batchId == -1 || ().isEmpty()) {
                continue;
            }
            for (Entry entry : ()) {
                handleData(entry);
            }
            (batchId);
        }
    }
    private void handleData(Entry entry) {
        String schemaName = ().getSchemaName(); // Database name        String tableName = ().getTableName();  // Table name        EventType eventType = ().getEventType(); // Data change type        ("Schema: " + schemaName + ", Table: " + tableName + ", Type: " + eventType);
        // Synchronize to the corresponding slave library according to the source database        if ("db_1".equals(schemaName)) {
            syncToBackupDbs(entry, "db_1_bk_1", "db_1_bk_2");
        } else if ("db_2".equals(schemaName)) {
            syncToBackupDbs(entry, "db_2_bk_1", "db_2_bk_2");
        }
    }
    private void syncToBackupDbs(Entry entry, String... backupDbs) {
        // Synchronize to the slave library according to the event type        if (().getEventType() == ) {
            for (String db : backupDbs) {
                syncInsert(entry, db);
            }
        } else if (().getEventType() == ) {
            for (String db : backupDbs) {
                syncUpdate(entry, db);
            }
        } else if (().getEventType() == ) {
            for (String db : backupDbs) {
                syncDelete(entry, db);
            }
        }
    }
    private void syncInsert(Entry entry, String backupDb) {
        // Use MyBatis to insert data into the corresponding slave library        ("INSERT into " + backupDb);
    }
    private void syncUpdate(Entry entry, String backupDb) {
        // Use MyBatis to update the data to the corresponding slave library        ("UPDATE into " + backupDb);
    }
    private void syncDelete(Entry entry, String backupDb) {
        // Use MyBatis to delete data from the corresponding slave library        ("DELETE from " + backupDb);
    }
}

6. Start and test

  • Start Canal Server.
  • Launch the Spring Boot app.
  • Insert, update, or delete data in the main library db_1 or db_2.
  • Observe whether the libraries db_1_bk_1, db_1_bk_2, db_2_bk_1 and db_2_bk_2 are synchronized successfully.

7. Things to note

  • Data consistency: Ensure that the data from the slave library is consistent with the master library. Conflicts can be avoided through transactions or lock mechanisms.
  • Performance optimization: If the data volume is large, it is recommended to combine middleware (such as Kafka) for buffering and load balancing.
  • Error handling: During the synchronization process, network exceptions, database connection exceptions, etc. need to be handled.
  • Canal Server High Availability: In a production environment, it is recommended to deploy a cluster of Canal Server to improve system availability.

8. Summary

Through Spring Boot and Canal, we can achieve efficient data synchronization between MySQL databases. Canal provides powerful Binlog parsing capabilities, while Spring Boot provides a flexible development framework that combines the two to easily cope with complex distributed data synchronization needs. I hope this article will be helpful to you. If you have any questions, please leave a message in the comment area.

This is the end of this article about using Spring Boot and Canal to implement MySQL database synchronization. For more related Spring Boot MySQL database synchronization content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!