Canal的基本使用

  |   0 评论   |   3,502 浏览

Canal简介

Canal是Alibaba开源的数据库同步组件,能够实现MySQL的实时同步

工作原理

  1. canal模拟mysql slave的交互协议,伪装自己为mysql slave,向mysql master发送dump协议

  2. mysql master收到dump请求,开始推送binary log给slave(也就是canal)

  3. canal解析binary log对象(原始为byte流)

MySQL初始化

修改binlog格式

canal的原理是基于mysql binlog技术,所以这里一定需要开启mysql的binlog写入功能,建议配置binlog模式为row.

vim /etc/my.cnf
log-bin=mysql-bin #添加这一行就ok
binlog-format=ROW #选择row模式
server_id=1 #配置mysql replaction需要定义,不能和canal的slaveId重复
service mysqld restart

验证

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

创建账号并授权

canal的原理是模拟自己为mysql slave,所以这里一定需要做为mysql slave的相关权限

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' identified BY 'canal';
Query OK, 0 rows affected (0.00 sec)

Deployer部署

下载

访问:https://github.com/alibaba/canal/releases

wget 
https://github.com/alibaba/canal/releases/download/canal-1.0.26-preview-3/canal.deployer-1.0.26-SNAPSHOT.tar.gz


解压

mkdir -p /usr/local/canal-deployer-1.0.26
tar zxvf canal.deployer-1.0.26-SNAPSHOT.tar.gz -C /usr/local/canal-deployer-1.0.26

查看一下目录结构

[root@nn2 canal-deployer-1.0.26]# ll
总用量 16
drwxr-xr-x 2 root root 4096 6月   7 18:12 bin
drwxr-xr-x 4 root root 4096 6月   7 18:12 conf
drwxr-xr-x 2 root root 4096 6月   7 18:12 lib
drwxrwxrwx 2 root root 4096 6月   7 09:51 logs

修改配置文件

vim conf/example/instance.properties
## mysql serverId
canal.instance.mysql.slaveId=100
# position info
canal.instance.master.address=127.0.0.1:3306
## enable gtid use true/false
canal.instance.gtidon=false
#
## table meta tsdb info
canal.instance.tsdb.enable=false
# username/password
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
canal.instance.connectionCharset=UTF-8
# table regex
canal.instance.filter.regex=.*\\..*
# table black regex
canal.instance.filter.black.regex=

image.png

启动canal-deployer

bin/startup.sh && tail -f logs/canal/canal.log

正常会有下面的输出

2018-06-07 19:17:34.304 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - prepare to find start position just last position {"identity":{"slaveId":-1,"sourceAddress":{"address":"localhost","port":3306}},"postion":{"gtid":"","included":false,"journalName":"mysql-bin.000002","position":1810,"serverId":1,"timestamp":1528370073000}}
2018-06-07 19:17:34.333 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - find start position : EntryPosition[included=false,journalName=mysql-bin.000002,position=1810,serverId=1,gtid=,timestamp=1528370073000]
2018-06-07 19:17:34.342 [main] INFO  com.alibaba.otter.canal.deployer.CanalLauncher - ## the canal server is running now ......

测试Client

导入代码

git clone https://github.com/alibaba/canal.git


并导入Idea中

运行com.alibaba.otter.canal.example.SimpleCanalClientTest

记得修改Deployer的IP

image.png

在MySQL中测试一下

CREATE TABLE `cxy_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)

查看控制台输出

正常会有以下输出

****************************************************
* Batch Id: [1] ,count : [1] , memsize : [1612] , Time : 2018-06-07 19:10:03
* Start : [mysql-bin.000002:198:1528369491000(2018-06-07 19:04:51)] 
* End : [mysql-bin.000002:198:1528369491000(2018-06-07 19:04:51)] 
****************************************************
----------------> binlog[mysql-bin.000002:1810] , name[test,cxy_order] , eventType : CREATE , executeTime : 1528370073000(2018-06-07 19:14:33) , gtid : () , delay : -181958 ms
 sql ----> CREATE TABLE `cxy_order` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)

可能遇到的错误

Timeout occurred, failed to read 4 bytes in 25000 milliseconds

2018-06-07 19:17:59.759 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.o.canal.parse.inbound.mysql.dbsync.DirectLogFetcher - Socket timeout expired, closing connection
java.net.SocketTimeoutException: Timeout occurred, failed to read 4 bytes in 25000 milliseconds.
at com.alibaba.otter.canal.parse.driver.mysql.socket.BioSocketChannel.read(BioSocketChannel.java:90) ~[canal.parse.driver-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch0(DirectLogFetcher.java:174) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:80) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:146) [canal.parse-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:227) [canal.parse-1.0.26-SNAPSHOT.jar:na]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_112]
2018-06-07 19:17:59.760 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - dump address localhost/127.0.0.1:3306 has an error, retrying. caused by java.net.SocketTimeoutException: Timeout occurred, failed to read 4 bytes in 25000 milliseconds.
at com.alibaba.otter.canal.parse.driver.mysql.socket.BioSocketChannel.read(BioSocketChannel.java:90) ~[canal.parse.driver-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch0(DirectLogFetcher.java:174) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:80) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:146) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:227) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
at java.lang.Thread.run(Thread.java:745) [na:1.8.0_112]
2018-06-07 19:17:59.766 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.net.SocketTimeoutException: Timeout occurred, failed to read 4 bytes in 25000 milliseconds.at com.alibaba.otter.canal.parse.driver.mysql.socket.BioSocketChannel.read(BioSocketChannel.java:90)
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch0(DirectLogFetcher.java:174)
at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:80)
at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:146)
at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:227)
at java.lang.Thread.run(Thread.java:745)

出现上面的错不要惊慌,说明你的数据库不够繁忙,在周期内没有产生数据,只要有

## the canal server is running now ......

的日志数据就说明启动成功了

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

2018-06-07 18:53:21.738 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - prepare to find start position cxy:4:null
2018-06-07 18:53:21.739 [destination = example , address = /127.0.0.1:3306 , EventParser] WARN  c.a.otter.canal.parse.inbound.mysql.MysqlEventParser - find start position : EntryPosition[included=false,
journalName=cxy,position=4,serverId=<null>,gtid=,timestamp=<null>]2018-06-07 18:53:21.743 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR c.a.o.canal.parse.inbound.mysql.dbsync.DirectLogFetcher - I/O error while reading from client socket
java.io.IOException: Received error packet: errno = 1236, sqlstate = HY000 errmsg = Could not find first log file name in binary log index file
        at com.alibaba.otter.canal.parse.inbound.mysql.dbsync.DirectLogFetcher.fetch(DirectLogFetcher.java:105) ~[canal.parse-1.0.26-SNAPSHOT.jar:na]
        at com.alibaba.otter.canal.parse.inbound.mysql.MysqlConnection.dump(MysqlConnection.java:146) [canal.parse-1.0.26-SNAPSHOT.jar:na]
        at com.alibaba.otter.canal.parse.inbound.AbstractEventParser$3.run(AbstractEventParser.java:227) [canal.parse-1.0.26-SNAPSHOT.jar:na]
        at java.lang.Thread.run(Thread.java:745) [na:1.8.0_112]

解决办法

[root@nn2 ~]# ll /hadoop/1/mysqldata/mysql-bin.*
-rw-rw---- 1 mysql mysql 5141051 6月   7 18:49 /hadoop/1/mysqldata/mysql-bin.000001
-rw-rw---- 1 mysql mysql      19 6月   7 17:54 /hadoop/1/mysqldata/mysql-bin.index
[root@nn2 ~]# service mysqld restart
停止 mysqld:                                              [确定]
正在启动 mysqld:                                          [确定]
[root@nn2 ~]# ll /hadoop/1/mysqldata/mysql-bin.*
-rw-rw---- 1 mysql mysql 5141070 6月   7 18:52 /hadoop/1/mysqldata/mysql-bin.000001
-rw-rw---- 1 mysql mysql     106 6月   7 18:52 /hadoop/1/mysqldata/mysql-bin.000002
-rw-rw---- 1 mysql mysql      38 6月   7 18:52 /hadoop/1/mysqldata/mysql-bin.index


读后有收获可以支付宝请作者喝咖啡