使用Canal实现MySQL主从同步的流程步骤_Mysql

说明：本文介绍如何使用canal实现mysql主从同步的效果

启动canal

首先，设置canal服务器里，目标节点（即监测的mysql节点）的配置，启动canal服务；

在这里插入图片描述

启动canal服务器，windows操作系统下，直接双击startup.bat文件即可；

在这里插入图片描述

创建项目

创建一个spring boot项目，pom.xml文件如下：

<?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>

    <groupid>com.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>

        <!--mysql驱动-->
        <dependency>
            <groupid>com.mysql</groupid>
            <artifactid>mysql-connector-j</artifactid>
            <scope>runtime</scope>
        </dependency>

        <!--canal客户端-->
        <dependency>
            <groupid>top.javatool</groupid>
            <artifactid>canal-spring-boot-starter</artifactid>
            <version>1.2.1-release</version>
        </dependency>
    </dependencies>

</project>

application.yml文件如下，这里的数据库配置写从节点的，且账户应该有数据读写权限；

server:
  port: 8080

# 1.数据源的配置
spring:
  # 数据库配置
  datasource:
    driver-class-name: com.mysql.cj.jdbc.driver
    url: jdbc:mysql://从节点mysqlip:3306/test?useunicode=true&characterencoding=utf-8&usessl=false
    username: root
    password: 123456

# 2.mybatis配置
mybatis:
  configuration:
    # 显示sql日志配置
    log-impl: org.apache.ibatis.logging.stdout.stdoutimpl
    # 驼峰命名配置
    map-underscore-to-camel-case: true

# 3.canal配置
canal:
  # canal服务端的ip
  server: 127.0.0.1:11111
  destination: example

实体类对象

import lombok.data;

import java.io.serializable;

@data
public class user implements serializable {

    private string id;

    private string username;

    private string password;
}

canal处理类

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("新增用户：" + user);
        usermapper.insertuser(user);
    }

    @override
    public void update(user before, user after) {
        system.out.println("更新用户：" + before + " -> " + after);
        usermapper.updateuserbyid(after);
    }

    @override
    public void delete(user user) {
        system.out.println("删除用户：" + user);
        usermapper.deleteuserbyid(user.getid());
    }
}

对应写三个针对user表（user实体类对应的表，即i_user表）操作的mapper方法；

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);

}

启动程序前，看下两个数据库的表内容，目前是一致的，即使不一致，在你想要进行同步前，也应该手动导出/导入数据，使其初始状态数据保持一致。

在这里插入图片描述