Canal实例

最新推荐文章于 2024-09-12 09:45:00 发布

secretWHD

最新推荐文章于 2024-09-12 09:45:00 发布

阅读量609

点赞数 1

分类专栏： canal 文章标签： mysql 数据仓库 etl工程师 dba

本文链接：https://blog.csdn.net/secretWHD/article/details/119000059

版权

canal 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

使用canal监控源的变化，且canal只能监控mysql源，并且需要对方的mysql开启binlog日志

对方mysql提供给我们的登录账号需要 select,replication slave,replication client权限

binlog为什么需要这些权限：

Select权限代表允许从表中查看数据

Replication client权限代表允许执行“show master status”，“show slave status”，“show binary logs”命令

Replication slave权限代表允许slave主机通过此用户连接master以便建立主从复制关系

安装canal

1. 开启mysql binlog日志

注意：针对阿里云 RDS for MySQL , 默认打开了 binlog , 并且账号默认具有 binlog dump 权限 , 不需要任何权限或者 binlog 设置,可以直接跳过这一步

vim /etc/my.cnf 
# 添加 
[mysqld] 
log-bin=mysql-bin # 开启 binlog 
binlog-format=ROW # 选择 ROW 模式 
server-id=1 # 配置 MySQL replaction 需要定义，不要和 canal 的 slaveId 重复 

# 重启mysql 
service mysqld restart 

结果： 
mysql> show variables like '%log_bin%'; 
+---------------------------------+--------------------------------+ 
| Variable_name                   | Value                          | 
+---------------------------------+--------------------------------+ 
| log_bin                         | ON                             | 
| log_bin_basename                | /var/lib/mysql/mysql-bin       | 
| log_bin_index                   | /var/lib/mysql/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.00 sec) 

mysql> show master logs; 
+------------------+-----------+ 
| Log_name         | File_size | 
+------------------+-----------+ 
| mysql-bin.000001 | 1042      | 
+------------------+-----------+ 
1 row in set (0.00 sec)

下载canal

版本下载：https://github.com/alibaba/canal/releases

官方快速启动手册：https://github.com/alibaba/canal/wiki/QuickStart

安装解压canal

下载安装包： 
wget https://github.com/alibaba/canal/releases/download/canal-1.1.4/canal.deployer-1.1.4.tar.gz 

解压： 
    mkdir /tmp/canal \ 
    && tar zxvf canal.deployer-1.1.4.tar.gz -C /tmp/canal \ 
    && ll /tmp/canal 

结果： 
total 20 
drwxr-xr-x 2 root root 4096 Jul 21 14:05 bin 
drwxr-xr-x 5 root root 4096 Jul 21 13:56 conf 
drwxr-xr-x 2 root root 4096 Jul 21 13:56 lib 
drwxrwxrwx 4 root root 4096 Jul 21 14:05 logs 
drwxrwxrwx 2 root root 4096 Apr 19 16:15 plugin

配置canal

vim /tmp/canal/conf/example/instance.properties 

修改配置： 
    # 伪装为mysql的slave节点 
    canal.instance.mysql.slaveId = 1234 
    # 需要改成自己的数据库信息 
    canal.instance.master.address= server001:3306 
    # 需要改成自己的数据库信息 
    canal.instance.dbUsername= root 
    canal.instance.dbPassword= 123456

启动

sh /tmp/canal/bin/startup.sh 
tail -f /tmp/canal/logs/canal/canal.log 
结果： 
2021-07-21 14:24:30.496 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## start the canal server. 
2021-07-21 14:24:30.571 [main] INFO com.alibaba.otter.canal.deployer.CanalController - ## start the canal server[172.26.188.13(172.26.188.13):11111] 
2021-07-21 14:24:32.619 [main] INFO com.alibaba.otter.canal.deployer.CanalStarter - ## the canal server is running now ...... 

查看instance的日志 
tail -f logs/example/example.log 
结果： 
2021-07-21 14:24:32.316 [main] INFO c.a.otter.canal.instance.spring.CanalInstanceWithSpring - start CannalInstance for 1-example 
2021-07-21 14:24:32.346 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table filter : ^.*\..*$ 
2021-07-21 14:24:32.346 [main] WARN c.a.o.canal.parse.inbound.mysql.dbsync.LogEventConvert - --> init table black filter : ^mysql\.slave_.*$ 
2021-07-21 14:24:32.517 [main] INFO c.a.otter.canal.instance.core.AbstractCanalInstance - start successful.... 
2021-07-21 14:24:32.645 [destination = example , address = /ip:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> begin to find start position, it will be long time for reset or first position 
2021-07-21 14:24:32.645 [destination = example , address = /ip:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - prepare to find start position just show master status 
2021-07-21 14:24:33.444 [destination = example , address = /ip:3306 , EventParser] WARN c.a.o.c.p.inbound.mysql.rds.RdsBinlogEventParserProxy - ---> find start position successfully, EntryPosition[included=false,journalName=mysql-bin.000002,position=85059,serverId=1,gtid=,timestamp=1626848669000] cost : 754ms , the next step is binlog dump

常用命令

查看binlog文件的格式：
    show variables like 'binlog_format'; 
binlog文件列表：
    show binary logs; 
当前binlog文件：
    show master status; 
查看binlog文件内容(概要)：
    show binlog events in 'mysql-bin.000001'; show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 
查看binlog文件内容(详细) : 
    mysqlbinlog  --base64-output=decode-rows -v  /var/lib/mysql/mysql-bin.000003;

flush logs;     //生成新的binlog文件 
reset master;   //删除master的binlog 
reset slave;    //删除slave的中继日志

java测试canal

阿里云服务器记得开放11111端口

canalclient项目实例下载

canal监控库表的状态变化

创建表：create table emp3( id int null, name varchar(10) null ); 
================> binlog[mysql-bin.000003:2486] , name[flinkx,emp3] , eventType : CREATE ================》;isDdl: true,sql:create table emp3( id int null, name varchar(10) null ) 

修改表：alter table emp3 add column age int after name; 
================> binlog[mysql-bin.000003:2702] , name[flinkx,emp3] , eventType : ALTER ================》;isDdl: true,sql:alter table emp3 add column age int after name 

新增数据：insert into emp3 values(4,"hu",12); 
================> binlog[mysql-bin.000003:3018] , name[flinkx,emp3] , eventType : INSERT id : 4 update=true name : hu update=true age : 12 update=true 

修改数据: UPDATE emp3 SET age = 22 WHERE id = 4; 
================> binlog[mysql-bin.000003:3288] , name[flinkx,emp3] , eventType : UPDATE 
-------> before id : 4 update=false name : hu update=false age : 12 update=false 
-------> after id : 4 update=false name : hu update=false age : 22 update=true 

删除数据：DELETE FROM flinkx.emp3 WHERE id = 4; 
================> binlog[mysql-bin.000003:3571] , name[flinkx,emp3] , eventType : DELETE id : 4 update=false name : hu update=false age : 22 update=false 

删除表：drop table emp3; 
================> binlog[mysql-bin.000003:3714] , name[flinkx,emp3] , eventType : ERASE ================》;isDdl: true,sql:DROP TABLE `emp3` /* generated by server */

报错合集：

1. Could not find first log file name in binary log index file

ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file

解决：

需删除conf/example下的meta.dat文件，再重新启动