9.【canal】canal从入门到放弃-mysql+canal+rocketmq实现数据库同步-canal安装

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: canal从入门到放弃-mysql+canal+rocketmq实现数据库同步-canal安装

前文如下:

8.【canal】canal从入门到放弃-mysql+canal+rocketmq实现数据库同步-mysql安装

2. canal安装

2.1 下载安装

(1)git下载canal,选择最新release版本 1.1.5

参考文档:

blog.csdn.net/A_nonym/art…

blog.csdn.net/weixin_4422…

下载地址: github.com/alibaba/can…

网络异常,图片无法展示
|

上传到linux虚拟机 ,查看解压

[anchu@localhost software]$ pwd/home/anchu/software[anchu@localhost software]$mkdir canal[anchu@localhost software]$ lscanal  canal.example-1.1.5.tar.gz  mysql-5.7.24-linux-glibc2.12-x86_64.tar.gzcanal.deployer-1.1.5.tar.gz  mysql-5.7.24[anchu@localhost software]$cd canal[anchu@localhost software]tar xzvf ../canal.deployer-1.1.5.tar.gz[anchu@localhost software]tar xzvf ../canal.example-1.1.5.tar.gz[anchu@localhost software]tar xzvf ../canal.admin-1.1.5.tar.gz[anchu@localhost canal]$ lsbin  conf  lib  logs  plugin
复制代码

(2)数据库配置

查看当前配置,可以看到binlog未开启,需要修改配置开启binlog

[anchu@localhost support-files]$ mysql -u root -P 3306 -h 192.168.120.110 -D test -pEnter password: Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 24Server version: 5.7.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%log_bin%';+---------------------------------+-------+| Variable_name                   | Value |+---------------------------------+-------+| log_bin                         | OFF   || log_bin_basename                |       || log_bin_index                   |       || log_bin_trust_function_creators | OFF   || log_bin_use_v1_row_events       | OFF   || sql_log_bin                     | ON    |+---------------------------------+-------+6 rows in set (0.03 sec)
mysql>
复制代码

修改需要被同步的数据库 /home/anchu/software/mysql-5.7.24/my.cfg配置,有则修改无则添加

#启动时,使用的copy到/home/anchu/software/mysql-5.7.24的my.cfg
vi /home/anchu/software/mysql-5.7.24/my.cfg
[mysqld]log-bin=mysql-bin # 开启 binlogbinlog-format=ROW # 选择 ROW 模式server_id=1 # 配置 MySQL replaction 需要定义,不要和 canal 的 slaveId 重复binlog-rows-query-log-events  = 1  #查看完整的sql语句
复制代码

重启mysql,使配置生效

[anchu@localhost support-files]$ jobs[1]+  Running                 /bin/sh /home/anchu/software/mysql-5.7.24/bin/mysqld_safe --defaults-file=/home/anchu/software/mysql-5.7.24/my.cnf 2>&1 > /dev/null &[anchu@localhost support-files]$ [anchu@localhost support-files]$ kill -9 %1[anchu@localhost support-files]$  /home/anchu/software/mysql-5.7.24/bin/mysqld_safe --defaults-file=/home/anchu/software/mysql-5.7.24/my.cnf 2>&1 > /dev/null &[1] 82914[anchu@localhost support-files]$ ps -ef |grep mysqlanchu     82914  80040  0 20:47 pts/1    00:00:00 /bin/sh /home/anchu/software/mysql-5.7.24/bin/mysqld_safe --defaults-file=/home/anchu/software/mysql-5.7.24/my.cnfanchu     83155  82914  3 20:47 pts/1    00:00:00 /home/anchu/software/mysql-5.7.24/bin/mysqld --defaults-file=/home/anchu/software/mysql-5.7.24/my.cnf --basedir=/home/anchu/software/mysql-5.7.24 --datadir=/home/anchu/software/mysql-5.7.24/data --plugin-dir=/home/anchu/software/mysql-5.7.24/lib/plugin --log-error=/home/anchu/software/mysql-5.7.24/data/mariadb.log --pid-file=/home/anchu/software/mysql-5.7.24/data/mariadb.pid --socket=/home/anchu/software/mysql-5.7.24/data/mysql.sock --port=3306
复制代码

重新连接mysql查看配置

[anchu@localhost support-files]$ mysql -u root -P 3306 -h 192.168.120.110 -D test -pEnter password: Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.24-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;+----------------+| Tables_in_test |+----------------+| test           |+----------------+1 row in set (0.00 sec)
mysql> show variables like '%log_bin%';+---------------------------------+--------------------------------------------------------+| Variable_name                   | Value                                                  |+---------------------------------+--------------------------------------------------------+| log_bin                         | ON                                                     || log_bin_basename                | /home/anchu/software/mysql-5.7.24/data/mysql-bin       || log_bin_index                   | /home/anchu/software/mysql-5.7.24/data/mysql-bin.index || log_bin_trust_function_creators | OFF                                                    || log_bin_use_v1_row_events       | OFF                                                    || sql_log_bin                     | ON                                                     |+---------------------------------+--------------------------------------------------------+6 rows in set (0.01 sec)
mysql>
复制代码

2.2 查看binlog日志目录及文件

[anchu@localhost support-files]$ ll /home/anchu/software/mysql-5.7.24/data/*bin*-rw-r-----. 1 anchu anchu 1431 Apr 26 23:23 /home/anchu/software/mysql-5.7.24/data/mysql-bin.000001-rw-r-----. 1 anchu anchu   19 Apr 26 20:47 /home/anchu/software/mysql-5.7.24/data/mysql-bin.index
复制代码

2.3 创建一个有相关权限的mysql slave账号,用户名canal,密码canal

# 创建账号CREATE USER canal IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'canal';# 给账号赋权限GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; # 刷新FLUSH PRIVILEGES;
复制代码

创建和测试canal账号

mysql> CREATE USER canal IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'canal';Query OK, 0 rows affected (0.02 sec)mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; Query OK, 0 rows affected (0.01 sec)mysql> FLUSH PRIVILEGES;Query OK, 0 rows affected (0.02 sec)
mysql> exitBye[anchu@localhost support-files]$ mysql -u canal -P 3306 -h 192.168.120.110 -D test -pEnter password: Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -AWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.7.24-log MySQL Community Server (GPL)mysql> exit
复制代码

(3)canal的工作原理

  • canal 模拟 MySQL slave 的交互协议,伪装自己为 MySQL slave ,向 MySQL master 发送dump 协议
  • MySQL master 收到 dump 请求,开始推送 binary log 给 slave (即 canal )
  • canal 解析 binary log 对象(原始为 byte 流)

(4)canal配置

修改instance 配置文件instance.properties
[anchu@localhost example]$cd  /home/anchu/software/canal/conf/example[anchu@localhost example]$ vi instance.properties
################################################### mysql serverId , v1.0.26+ will autoGen# canal.instance.mysql.slaveId=0
# enable gtid use true/falsecanal.instance.gtidon=false
# position info数据库实例地址,主数据库,注意端口canal.instance.master.address=192.168.120.110:3306canal.instance.master.journal.name=canal.instance.master.position=canal.instance.master.timestamp=canal.instance.master.gtid=
# rds oss binlogcanal.instance.rds.accesskey=canal.instance.rds.secretkey=canal.instance.rds.instanceId=
# table meta tsdb infocanal.instance.tsdb.enable=true#canal.instance.tsdb.url=jdbc:mysql://127.0.0.1:3306/canal_tsdb#canal.instance.tsdb.dbUsername=canal#canal.instance.tsdb.dbPassword=canal
#canal.instance.standby.address =#canal.instance.standby.journal.name =#canal.instance.standby.position =#canal.instance.standby.timestamp =#canal.instance.standby.gtid=
# username/password  数据库账号密码canal.instance.dbUsername=canalcanal.instance.dbPassword=canalcanal.instance.connectionCharset = UTF-8# enable druid Decrypt database passwordcanal.instance.enableDruid=false#canal.instance.pwdPublicKey=MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBALK4BUxdDltRRE5/zXpVEVPUgunvscYFtEip3pmLlhrWpacX7y7GCMo2/JM6LeHmiiNdH1FWgGCpUfircSwlWKUCAwEAAQ==
# table regex 白名单过滤canal.instance.filter.regex=.*\..*# table black regexcanal.instance.filter.black.regex=mysql\.slave_.*# table field filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)#canal.instance.filter.field=test1.t_product:id/subject/keywords,test2.t_company:id/name/contact/ch# table field black filter(format: schema1.tableName1:field1/field2,schema2.tableName2:field1/field2)#canal.instance.filter.black.field=test1.t_product:subject/product_image,test2.t_company:id/name/contact/ch
# mq config  定义主题canal.mq.topic=example# dynamic topic route by schema or table regex#canal.mq.dynamicTopic=mytest1.user,mytest2\..*,.*\..*#消息分区canal.mq.partition=0  # hash partition config#canal.mq.partitionsNum=3#canal.mq.partitionHash=test.table:id^name,.*\..*#canal.mq.dynamicTopicPartitionNum=test.*:4,mycanal:6#################################################
复制代码
修改canal  配置文件canal.properties,暂时不用MQ,不用注册中心,不用admin
[anchu@localhost conf]$ cd /home/anchu/software/canal/conf[anchu@localhost conf]$ vi canal.properties#主要改动#canal server的唯一标识,没有实际意义,但是我们建议同一个cluster上的不同节点,其ID尽可能唯一canal.id =110#canal server因为binding的本地IP地址,建议使用内网(唯一,集群可见,consumer可见)IP地址,比如“10.0.1.21”。#此IP主要为canalServer提供TCP服务而使用,将会被注册到ZK中,Consumer将与此IP建立连接。canal.ip =192.168.120.110# register ip to zookeepercanal.register.ip =#cannal server的TCP端口canal.port = 11111canal.metrics.pull.port = 11112# canal instance user/passwdcanal.user = canalcanal.passwd = canal
# canal admin config#canal.admin.manager = 127.0.0.1:8089canal.admin.port = 11110canal.admin.user = admincanal.admin.passwd = 4ACFE3202A5FF5CF467898FC58AAB1D615029441# admin auto register#canal.admin.register.auto = true#canal.admin.register.cluster =#canal.admin.register.name =
#zookeeper地址,可集群canal.zkServers =# flush data to zkcanal.zookeeper.flush.period = 1000canal.withoutNetty = false# tcp, kafka, rocketMQ, rabbitMQcanal.serverMode = tcp# flush meta cursor/parse position to file#canal将parse、position数据写入的本地文件目录 canal.file.data.dir = ${canal.conf.dir}canal.file.flush.period = 1000## memory store RingBuffer size, should be Math.pow(2,n)canal.instance.memory.buffer.size = 16384## memory store RingBuffer used memory unit size , default 1kbcanal.instance.memory.buffer.memunit = 1024 ## meory store gets mode used MEMSIZE or ITEMSIZEcanal.instance.memory.batch.mode = MEMSIZEcanal.instance.memory.rawEntry = true
# table meta tsdb infocanal.instance.tsdb.enable = truecanal.instance.tsdb.dir = ${canal.file.data.dir:../conf}/${canal.instance.destination:}canal.instance.tsdb.url = jdbc:h2:${canal.instance.tsdb.dir}/h2;CACHE_SIZE=1000;MODE=MYSQL;canal.instance.tsdb.dbUsername = canalcanal.instance.tsdb.dbPassword = canal# dump snapshot interval, default 24 hourcanal.instance.tsdb.snapshot.interval = 24# purge snapshot expire , default 360 hour(15 days)canal.instance.tsdb.snapshot.expire = 360
复制代码

(5)启动canal,测试

[anchu@localhost bin]$ pwd/home/anchu/software/canal/bin[anchu@localhost bin]$ ./canal-startup.sh cd to /home/anchu/software/canal/bin for workaround relative pathLOG CONFIGURATION : 
/home/anchu/software/canal/bin/../conf/logback.xml
#查看进程
[anchu@localhost bin]$ ps -ef |grep canalanchu     83236  80040  0 20:51 pts/1    00:00:00 mysql -u canal -P 3306 -h 192.168.120.110 -D test -panchu     84725      1  8 23:10 pts/2    00:00:04 /usr/bin/java -server -Xms2048m -Xmx3072m -Xmn1024m -XX:SurvivorRatio=2 -XX:PermSize=96m.........省略号...........
#停止canal
anchu@localhost canal]$ ./bin/canal-stop.sh localhost.localdomain: stopping canal 84725 ... Oook! cost:1
#查看日志
anchu@localhost canal]$ tail -f ../logs/canal/canal_stdout.log 
2022-04-26 23:20:22.419 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - connect MysqlConnection to /192.168.120.110:3306...2022-04-26 23:20:22.438 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - handshake initialization packet received, prepare the client authentication packet to send
2022-04-26 23:20:22.439 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - client authentication packet is sent out.
2022-04-26 23:20:22.440 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - disConnect MysqlConnection to /192.168.120.110:3306...2022-04-26 23:20:22.441 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - connect MysqlConnection to /192.168.120.110:3306...2022-04-26 23:20:22.444 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - handshake initialization packet received, prepare the client authentication packet to send
2022-04-26 23:20:22.445 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.alibaba.otter.canal.parse.driver.mysql.MysqlConnector - client authentication packet is sent out.
2022-04-26 23:20:22.540 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.a.otter.canal.parse.inbound.mysql.MysqlConnection - Register slave RegisterSlaveCommandPacket[reportHost=192.168.120.110,reportPort=47134,reportUser=canal,reportPasswd=canal,serverId=1778384897,command=21]2022-04-26 23:20:22.545 [destination = example , address = /192.168.120.110:3306 , EventParser] INFO  c.a.otter.canal.parse.inbound.mysql.MysqlConnection - COM_BINLOG_DUMP with position:BinlogDumpCommandPacket[binlogPosition=4,slaveServerId=1778384897,binlogFileName=mysql-bin.000001,command=18]
2022-04-26 23:20:22.546 [MultiStageCoprocessor-other-example-0] INFO  com.taobao.tddl.dbsync.binlog


相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
3月前
|
关系型数据库 应用服务中间件 nginx
Docker一键安装中间件(RocketMq、Nginx、MySql、Minio、Jenkins、Redis)
本系列脚本提供RocketMQ、Nginx、MySQL、MinIO、Jenkins和Redis的Docker一键安装与配置方案,适用于快速部署微服务基础环境。
|
14天前
|
安全 关系型数据库 MySQL
CentOS 7 yum 安装 MySQL教程
在CentOS 7上安装MySQL 8,其实流程很清晰。首先通过官方Yum仓库来安装服务,然后启动并设为开机自启。最重要的环节是首次安全设置:需要先从日志里找到临时密码来登录,再修改成你自己的密码,并为远程连接创建用户和授权。最后,也别忘了在服务器防火墙上放行3306端口,这样远程才能连上。
220 16
|
19天前
|
Ubuntu 安全 关系型数据库
安装与配置MySQL 8 on Ubuntu,包括权限授予、数据库备份及远程连接指南
以上步骤提供了在Ubuntu上从头开始设置、配置、授权、备份及恢复一个基础但完整的MySQL环境所需知识点。
209 7
|
2月前
|
Ubuntu 关系型数据库 MySQL
Ubuntu 22.04.1上安装MySQL 8.0及设置root密码的注意事项
这些是在Ubuntu 22.04.1 系统上安装MySQL 8.0 及设置root密码过程中必须考虑的关键点。正确的遵循这些步骤可确保MySQL的安装过程既顺利又安全。
509 20
|
3月前
|
关系型数据库 MySQL Linux
安装MySQL 5.7到红帽系RHEL8+系列上
本文介绍了在RHEL 8及以上系统中安装MySQL 5.7的两种方法:解压安装与RPM包安装。涵盖环境准备、目录配置、数据盘挂载、初始化及服务启动等关键步骤,适用于红帽系(8+)部署MySQL 5.7。
|
2月前
|
存储 关系型数据库 MySQL
在CentOS 8.x上安装Percona Xtrabackup工具备份MySQL数据步骤。
以上就是在CentOS8.x上通过Perconaxtabbackup工具对Mysql进行高效率、高可靠性、无锁定影响地实现在线快速全量及增加式数据库资料保存与恢复流程。通过以上流程可以有效地将Mysql相关资料按需求完成定期或不定期地保存与灾难恢复需求。
157 10
|
5月前
|
Java 关系型数据库 MySQL
在Linux平台上进行JDK、Tomcat、MySQL的安装并部署后端项目
现在,你可以通过访问http://Your_IP:Tomcat_Port/Your_Project访问你的项目了。如果一切顺利,你将看到那绚烂的胜利之光照耀在你的项目之上!
304 41
|
5月前
|
SQL 关系型数据库 MySQL
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!
这是一份详细的MySQL安装与配置教程,适合初学者快速上手。内容涵盖从下载到安装的每一步操作,包括选择版本、设置路径、配置端口及密码等。同时提供基础操作指南,如数据库管理、数据表增删改查、用户权限设置等。还介绍了备份恢复、图形化工具使用和性能优化技巧,帮助用户全面掌握MySQL的使用方法。附带常见问题解决方法,保姆级教学让你无忧入门!
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!
|
5月前
|
开发框架 Java 关系型数据库
在Linux系统中安装JDK、Tomcat、MySQL以及部署J2EE后端接口
校验时,浏览器输入:http://[your_server_IP]:8080/myapp。如果你看到你的应用的欢迎页面,恭喜你,一切都已就绪。
407 17
|
5月前
|
关系型数据库 MySQL Java
安装和配置JDK、Tomcat、MySQL环境,以及如何在Linux下更改后端端口。
遵循这些步骤,你可以顺利完成JDK、Tomcat、MySQL环境的安装和配置,并在Linux下更改后端端口。祝你顺利!
359 11

推荐镜像

更多