canal同步mysql

開啟mysql bin-log

[mysqld]
log-bin=mysql-bin # 開啟 binlog
binlog-format=ROW # 選擇 ROW 模式
server_id=1 # 配置 MySQL replaction 需要定義，不要和 canal 的 slaveId 重復

創(chuàng)建canal賬號

CREATE USER canal IDENTIFIED BY 'canal';  
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
FLUSH PRIVILEGES;

下載

下載最新版deployer和adapter包（支持jdk8）
https://github.com/alibaba/canal/releases?page=1

配置deployer

conf/canal.properties不用動
編輯conf/example/instance.properties

這里改為源mysql地址

這里配置要監(jiān)聽的庫

啟動deployer：bin/startup.sh
監(jiān)控日志：tail -f /home/dev/canal/deployer/logs/canal/canal.log

2022-10-20 15:18:33.831 [canal-instance-scan-0] INFO  com.alibaba.otter.canal.deployer.CanalController - auto notify start example successful.
2022-10-20 15:18:33.831 [canal-instance-scan-0] INFO  com.alibaba.otter.canal.deployer.CanalController - auto notify reload example successful.
2022-10-20 15:18:45.309 [Thread-6] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## stop the canal server
2022-10-20 15:18:45.884 [Thread-6] INFO  com.alibaba.otter.canal.deployer.CanalController - ## stop the canal server[172.26.0.1(172.26.0.1):11111]
2022-10-20 15:18:45.909 [Thread-6] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## canal server is down.
2022-10-20 15:18:49.796 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## set default uncaught exception handler
2022-10-20 15:18:49.801 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## load canal configurations
2022-10-20 15:18:49.807 [main] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server.
2022-10-20 15:18:49.838 [main] INFO  com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.26.0.1(172.26.0.1):11111]
2022-10-20 15:18:51.344 [main] INFO  com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ......

配置adapter

編輯conf/application.yml

在defaultDS里配置源mysql，properties里配置目標mysql

編輯conf/rdb/mytest_user.yml

database為源庫，table為源表，targetTable為目標表

如果目標表是聯(lián)合主鍵，在targetPK下寫多個即可，但是要注意，源表中要有與之映射的字段。

如果使用的是mysql8，啟動報錯，將lib目錄下的mysql jar包替換為高版本即可
https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.29
替換后將權(quán)限改為：rwsrwsrwt

啟動adapter：bin/startup.sh
監(jiān)控日志：tail -f /home/dev/canal/adapter/logs/adapter/adapter.log

2022-10-20 15:30:02.064 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterLoader - Start adapter for canal-client mq topic: example-g1 succeed
2022-10-20 15:30:02.064 [Thread-4] INFO  c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - =============> Start to connect destination: example <=============
2022-10-20 15:30:02.064 [main] INFO  c.a.o.canal.adapter.launcher.loader.CanalAdapterService - ## the canal client adapters are running now ......
2022-10-20 15:30:02.070 [main] INFO  org.apache.coyote.http11.Http11NioProtocol - Starting ProtocolHandler ["http-nio-8081"]
2022-10-20 15:30:02.073 [main] INFO  org.apache.tomcat.util.net.NioSelectorPool - Using a shared selector for servlet write/read
2022-10-20 15:30:02.090 [main] INFO  o.s.boot.web.embedded.tomcat.TomcatWebServer - Tomcat started on port(s): 8081 (http) with context path ''
2022-10-20 15:30:02.092 [main] INFO  c.a.otter.canal.adapter.launcher.CanalAdapterApplication - Started CanalAdapterApplication in 2.491 seconds (JVM running for 2.859)
2022-10-20 15:30:02.112 [Thread-4] INFO  c.a.otter.canal.adapter.launcher.loader.AdapterProcessor - =============> Subscribe destination: example succeed <=============

如果報錯找不到庫，可以執(zhí)行如下sql

-- MySQL dump 10.13  Distrib 5.7.38, for Linux (x86_64)
--
-- Host: 192.168.55.127    Database: canal_manager
-- ------------------------------------------------------
-- Server version   8.0.31-0ubuntu0.20.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `canal_adapter_config`
--

DROP TABLE IF EXISTS `canal_adapter_config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_adapter_config` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `category` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `content` text NOT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `canal_adapter_config`
--

LOCK TABLES `canal_adapter_config` WRITE;
/*!40000 ALTER TABLE `canal_adapter_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_adapter_config` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `canal_cluster`
--

DROP TABLE IF EXISTS `canal_cluster`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_cluster` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(63) NOT NULL,
  `zk_hosts` varchar(255) NOT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `canal_cluster`
--

LOCK TABLES `canal_cluster` WRITE;
/*!40000 ALTER TABLE `canal_cluster` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_cluster` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `canal_config`
--

DROP TABLE IF EXISTS `canal_config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_config` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint DEFAULT NULL,
  `server_id` bigint DEFAULT NULL,
  `name` varchar(45) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `content` text NOT NULL,
  `content_md5` varchar(128) NOT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sid_UNIQUE` (`server_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `canal_config`
--

LOCK TABLES `canal_config` WRITE;
/*!40000 ALTER TABLE `canal_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_config` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `canal_instance_config`
--

DROP TABLE IF EXISTS `canal_instance_config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_instance_config` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint DEFAULT NULL,
  `server_id` bigint DEFAULT NULL,
  `name` varchar(45) NOT NULL,
  `status` varchar(45) DEFAULT NULL,
  `content` text NOT NULL,
  `content_md5` varchar(128) DEFAULT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `canal_instance_config`
--

LOCK TABLES `canal_instance_config` WRITE;
/*!40000 ALTER TABLE `canal_instance_config` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_instance_config` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `canal_node_server`
--

DROP TABLE IF EXISTS `canal_node_server`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_node_server` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `cluster_id` bigint DEFAULT NULL,
  `name` varchar(63) NOT NULL,
  `ip` varchar(63) NOT NULL,
  `admin_port` int DEFAULT NULL,
  `tcp_port` int DEFAULT NULL,
  `metric_port` int DEFAULT NULL,
  `status` varchar(45) DEFAULT NULL,
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `canal_node_server`
--

LOCK TABLES `canal_node_server` WRITE;
/*!40000 ALTER TABLE `canal_node_server` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_node_server` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `canal_user`
--

DROP TABLE IF EXISTS `canal_user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `canal_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(31) NOT NULL,
  `password` varchar(128) NOT NULL,
  `name` varchar(31) NOT NULL,
  `roles` varchar(31) NOT NULL,
  `introduction` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `canal_user`
--

LOCK TABLES `canal_user` WRITE;
/*!40000 ALTER TABLE `canal_user` DISABLE KEYS */;
/*!40000 ALTER TABLE `canal_user` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-11-10 17:21:16

測試

向源mysql的mytest.user中插入一條數(shù)據(jù)：insert into user(name) value("6666666666");
adapter中有如下日志打印

2022-10-20 15:56:01.476 [pool-7-thread-1] INFO  c.a.o.canal.client.adapter.logger.LoggerAdapterExample - DML: {"data":[{"id":12,"name":"6666666666"}],"database":"mytest","destination":"example","es":1666252561000,"groupId":"g1","isDdl":false,"old":null,"pkNames":["id"],"sql":"","table":"user","ts":1666252561475,"type":"INSERT"}
2022-10-20 15:56:01.523 [pool-1-thread-1] DEBUG c.a.o.canal.client.adapter.rdb.service.RdbSyncService - DML: {"data":{"id":12,"name":"6666666666"},"database":"mytest","destination":"example","old":null,"table":"user","type":"INSERT"}

此時目標mysql的mytest.user已經(jīng)同步

mysql> select * from user;
+----+------------+
| id | name       |
+----+------------+
| 11 | ccc        |
| 12 | 6666666666 |
+----+------------+
2 rows in set (0.00 sec)

也可以手動全量同步，如果只是修改了yml，其他都為默認，只需要修改以下命令中的price_config-bj（表映射文件）即可

curl "localhost:8081/etl/rdb/mysql1/price_config-bj.yml" -X POST
{"succeeded":true,"resultMessage":"導入RDB 數(shù)據(jù)：11 條"}root@ts-OptiPlex-7070:/home/ts/canal/adapter/conf# curl "localhost:8081/etl/rdb/mysql1/price_config-zj.yml" -X POST

附錄

mysql8的docker-compose

version: '3'
services:
  mysql:
    privileged: true
    restart: always
    image: mysql:8.0.29
    container_name: mysql
    ports:
      - 3306:3306
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_PASSWORD: 123456
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
    volumes:
      - ./conf:/etc/mysql/conf.d
      - ./logs:/logs
      - ./data:/var/lib/mysql

完整的mysql配置文件

###### [mysql]配置模塊 ######
[mysql]
# 設(shè)置MySQL客戶端默認字符集
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock

###### [mysqld]配置模塊 ######
[mysqld]
port=3306
#user=mysql
#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# MySQL8 的密碼認證插件
default_authentication_plugin=mysql_native_password

# 禁用符號鏈接以防止各種安全風險
symbolic-links=0

# 允許最大連接數(shù)
max_connections=1000

# 服務端使用的字符集默認為8比特編碼的latin1字符集
character-set-server=utf8mb4

# 創(chuàng)建新表時將使用的默認存儲引擎
default-storage-engine=INNODB

# 表名存儲在磁盤是小寫的，但是比較的時候是不區(qū)分大小寫
lower_case_table_names=0
max_allowed_packet=16M 

# 設(shè)置時區(qū)
#default-time_zone='+8:00'

# binlog 配置
# 二進制文件存放路徑，非必須，mysql8后默認存放/var/lib/mysql
log-bin=mysql-bin
# 日志存儲方式
binlog_format=row
# 監(jiān)控的庫，如果不寫，對所有庫開啟監(jiān)控
#binlog-do-db=test
#binlog-do-db=test2

expire-logs-days = 90
max-binlog-size = 500M

# 集群id
server-id=1

###### [client]配置模塊 ######
[client]
default-character-set=utf8mb4

參考
https://www.cnblogs.com/whitelittle/p/16598857.html

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容