Using Canal to implement MySQL master/slave synchronization process steps _Mysql_ Script home
Script house Common server software
Quick navigation
Your location: Home page → Archive → Mysql→ Canal MySQL primary/secondary synchronization

Use Canal to implement the process steps of MySQL master/slave synchronization

Updated: April 29, 2024 09:20:25 Author: He Zhongying
This article mainly introduces how to use Canal to achieve the master-slave synchronization effect of MySQL. The article explains it in detail through the combination of code examples and graphics, which will be helpful to your study or work to some extent. If you need it, you can refer to it

This article describes how to use Canal to achieve the effect of MySQL master/slave synchronization

Start Canal

First, set the configuration of the target node (i.e. the monitored MySQL node) in the Canal server to start the Canal service.

在这里插入图片描述

Start the Canal server. In Windows, double-click the startup.bat file.

在这里插入图片描述

Create project

Create a Spring Boot project, pom.xml file as follows:

<? xml version="1.0" encoding="UTF-8"? > < project XMLNS = "http://maven.apache.org/POM/4.0.0" XMLNS: xsi = "http://www.w3.org/2001/XMLSchema-instance" Xsi: schemaLocation = "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" > <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.7.12</version> <relativePath/> </parent> hezy</groupId> <artifactId>canal_demo</artifactId> <version>1.0-SNAPSHOT</version> <properties> <maven.compiler.source>11</maven.compiler.source> <maven.compiler.target>11</maven.compiler.target> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> </properties> <dependencies> <! --lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <! --druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.2.8</version> </dependency> <! --mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.2</version> </dependency> <! --> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <scope>runtime</scope>  </dependency> <! --canal client --> <dependency> <groupId>top.javatool</groupId> <artifactId> Ccanal spring-boot-starter</artifactId> <version>1.2.1-RELEASE</version> </dependency> </dependencies> </project>

The application.yml file is as follows, where the database is configured to write to the slave node, and the account should have data read and write permission.

spring: # Database configuration datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql:// Secondary node MySQLIP:3306/test? useUnicode=true&characterEncoding=utf-8&useSSL=false username: root password: 123456 # 2.mybatis Configure mybatis: Configuration: # show SQL log configuration log - impl: org. Apache. Ibatis. Logging. Stdout. # StdOutImpl hump named configuration map - the underscore - to - camel - case: true # 3.canal Configure canal: # ip server: 127.0.0.1:11111 destination: example

Entity class object

import lombok.Data;

import java.io.Serializable;

@Data
public class User implements Serializable {

    private String id;

    private String username;

    private String password;
}

canal processing class

import com.hezy.mapper.UserMapper; import com.hezy.pojo.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import top.javatool.canal.client.annotation.CanalTable; import top.javatool.canal.client.handler.EntryHandler; @Component @CanalTable("i_user") public class UserHandler implements EntryHandler<User> { @Autowired private UserMapper userMapper; @Override public void insert(User user) {System.out.println(" New user: "+ user); userMapper.insertUser(user); } @Override public void update(User before, User after) {System.out.println(" Update user: "+ before +" -> "+ after);} @override public void update(user before, user after) {system.out.println (" Update user:" + before + "->" + after); userMapper.updateUserById(after); } @Override public void delete(User user) {System.out.println(" Delete user: "+ user);} @override public void delete(user user) {system.out.println (" Delete user:" + user); userMapper.deleteUserById(user.getId()); }}

Corresponding to write three Mapper methods for the User table (the table corresponding to the User entity class, that is, the i_user table) operations;

import com.hezy.pojo.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface UserMapper {


    @Insert("insert into i_user values (#{id}, #{username}, #{password})")
    void insertUser(User uesr);

    @Delete("delete from i_user where id = #{id}")
    void deleteUserById(String id);

    @Update("update i_user set username=#{username}, password=#{password} where id=#{id}")
    void updateUserById(User user);

}

Before starting the program, look at the table content of the two databases, the current is consistent, even if it is not consistent, before you want to synchronize, you should manually export/import data, so that its initial state data is consistent.

在这里插入图片描述

Start the program, the database begins to synchronize, view the console, and print the detection information in real time;

在这里插入图片描述

In this case, modify a piece of data in the i_user table of the primary node to view the console and secondary database contents.

在这里插入图片描述

You can see that this operation is detected by canal and updated via code to the slave library, the database configured in the code;

在这里插入图片描述

The contents of the i_user table in the secondary library are displayed. Data in the secondary library is synchronized successfully.

在这里插入图片描述

At this point, the MySQL master-slave synchronization using Canal is complete;

In addition

In addition, I have an idea, can this project package, into a jar package, when encountered short-term database synchronization scenarios, directly run this jar package can be.

For example, in daily development, we want to keep our local library synchronized with the library of the test environment, and directly modify the configuration of the test library, which may be troublesome to build the master and slave, we can use this way. You can even write a bat script, set an environment variable, and directly hit the CMD command to achieve synchronization between the two databases, which is very convenient.

Sum up

These are the details of implementing the master-slave synchronization effect of MySQL using Canal. For more information about Canal MySQL master-slave synchronization, please pay attention to other related articles of Script Home!

Articles you may be interested in:

Related article

Latest comments