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

catalogue

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:

Example of how MySQL creates database names with special characters

This article mainly introduces MySQL to create a database name method with special characters, the article gives a detailed example code, the need for friends can refer to learn, let's take a look.
2017-03-03
This article takes you to understand the underlying query logic of MySQL from simple to deep

This article mainly gives you a detailed introduction to the MySQL underlying query logic, the article has a detailed code example and graphic introduction, has a certain reference value, interested students can read for reference
2023-06-06
Note sharing is configured in MySQL 5.5 primary/Secondary synchronization

This article mainly introduces MySQL 5.5 master/slave synchronization Settings note sharing, need friends can refer to the next
2014-05-05
Create View Delete and modify MySQL table operations

This article mainly introduces the operation of MySQL table to create view delete and modify the relevant information,MySQL is the most commonly used database, in the database operation is basically added, deleted and changed operation, referred to as CRUD, the article through the code is very detailed, the need of friends can refer to the next
2024-01-01
About SQL cast() function parsing

This article focuses on parsing SQL's cast() function, which is used to explicitly convert an expression of one data type to another. The parameter of the CAST() function is an expression, which includes the source value and the target data type separated by the AS keyword, and the friend who needs it can refer to it
2023-04-04
MySQL stored procedures in the basic functions and triggers related to learning tutorials

This article mainly introduces the MySQL stored procedure of the basic functions and triggers related to learning tutorials, including trigger creation and deletion and other basic operations, need friends can refer to the next
2015-11-11
Resolve deadlocks caused by MySQL shared locks

This article mainly introduces the causes and solutions of the deadlock problem caused by MySQL shared lock, and introduces it in detail through code examples and graphics, which is helpful to everyone's study or work, and needs friends to refer to it
2023-11-11
Mysql varchar length setting method

This article mainly introduces the relevant information about the varchar length setting method in Mysql, this article also brings you the change of valar type and the difference between char() and varchar(), very good, with reference value, the need of friends can refer to the next
2016-07-07
Solution to garbled characters when importing data to the MYSQL database

I am using the last method, the previous three methods to solve the MYSQL import data garbled methods have not tried, Dongguan SEO recommends that you directly use the fourth method to deal with the MYSQL import Chinese data garbled problems.
2011-01-01
mysql startup error :The server quit without updating PID file

Whether it is in The installation or running MySQL, it is very likely to encounter errors, the following article mainly introduces you about mysql startup error :The server quit without updating PID file several solutions, need friends can refer to
2022-08-08