Skip to content

canal同步mysql

Jump to bottom
yangyp8110 edited this page Jan 17, 2018 · 1 revision

服务器

  • mysql master : 192.168.74.129
  • mysql slave : 192.168.74.131
  • canal server : 192.168.74.131

同步配置（如果未安装canal，参考canal安装）

mysql master配置（/etc/my.cnf）

# master
log-bin=mysql-binlog
binlog-format=ROW
server-id=1
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-do-db=test_db

mysql slave配置（/etc/my.cnf）

# slave
log-bin=mysql-binlog
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
replicate-do-db=test_db
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

canal server配置（/usr/local/install/canal/conf/example/instance.properties）

[root@yyp ~]# cat /usr/local/install/canal/conf/example/instance.properties 
#################################################
## mysql serverId
canal.instance.mysql.slaveId = 1234

# position info
canal.instance.master.address = 127.0.0.1:3306
canal.instance.master.journal.name = 
canal.instance.master.position = 
canal.instance.master.timestamp = 

#canal.instance.standby.address = 
#canal.instance.standby.journal.name =
#canal.instance.standby.position = 
#canal.instance.standby.timestamp = 

# username/password
canal.instance.dbUsername = canal
canal.instance.dbPassword = canal
canal.instance.defaultDatabaseName =
canal.instance.connectionCharset = UTF-8

# table regex
canal.instance.filter.regex = .*\\..*
# table black regex
canal.instance.filter.black.regex =  

#################################################
[root@yyp ~]#

数据同步

  • 启动mysql master
  • 启动mysql slave
  • 启动canal server
  • 运行官方示例，直接启动com.alibaba.otter.canal.example.SimpleCanalClientTest方法测试

同步测试

  • mysql master执行sql命令：
mysql> insert into person values(4,'test sync canal');
Query OK, 1 row affected (0.20 sec)

mysql>
  • 观察同步：

mysql master:

mysql> select * from person;
+-----+------------+
| pId | pName      |
+-----+------------+
|   1 | 123        |
|   4 | test sync canal |
+-----+------------+
2 rows in set (0.08 sec)

mysql>

mysql slave:

mysql> select * from person;
+-----+-----------------+
| pId | pName           |
+-----+-----------------+
|   1 | 123             |
|   4 | test sync canal |
+-----+-----------------+
2 rows in set (0.00 sec)

mysql>

canal:

****************************************************
* Batch Id: [2] ,count : [3] , memsize : [163] , Time : 2017-07-15 20:23:22
* Start : [mysql-binlog.000002:2326:1500121606000(2017-07-15 20:26:46)] 
* End : [mysql-binlog.000002:2513:1500121606000(2017-07-15 20:26:46)] 
****************************************************

================> binlog[mysql-binlog.000002:2326] , executeTime : 1500121606000 , delay : -203431ms
 BEGIN ----> Thread id: 17
----------------> binlog[mysql-binlog.000002:2456] , name[test_db,person] , eventType : INSERT , executeTime : 1500121606000 , delay : -203431ms
pId : 4    type=int(11)    update=true
pName : test sync canal    type=varchar(100)    update=true
----------------
 END ----> transaction id: 202
================> binlog[mysql-binlog.000002:2513] , executeTime : 1500121606000 , delay : -203431ms

扩展

上一步同步log

pId : 4    type=int(11)    update=true
pName : test sync canal    type=varchar(100)    update=true
  • canal伪装成mysql slave通过订阅binlog，解析，完成数据同步，稍加修改，可以格式化数据，做自己想做的事情。

其他

  • cancal 新建表

    create table tb_user(uId INT NOT NULL AUTO_INCREMENT,uName VARCHAR(100) NOT NULL, PRIMARY KEY(uId))ENGINE=InnoDB DEFAULT CHARSET=utf8;

****************************************************
* Batch Id: [6] ,count : [1] , memsize : [213] , Time : 2017-07-15 21:08:29
* Start : [mysql-binlog.000002:3519:1500124466000(2017-07-15 21:14:26)] 
* End : [mysql-binlog.000002:3519:1500124466000(2017-07-15 21:14:26)] 
****************************************************

----------------> binlog[mysql-binlog.000002:3519] , name[test_db,tb_user] , eventType : CREATE , executeTime : 1500124466000 , delay : -356637ms
 sql ----> create table tb_user(uId INT NOT NULL AUTO_INCREMENT,uName VARCHAR(100) NOT NULL, PRIMARY KEY(uId))ENGINE=InnoDB DEFAULT CHARSET=utf8
  • cancal 的 update

    update person set pName='test update' where pId = 4;

****************************************************
* Batch Id: [4] ,count : [3] , memsize : [210] , Time : 2017-07-15 21:04:51
* Start : [mysql-binlog.000002:2887:1500124251000(2017-07-15 21:10:51)] 
* End : [mysql-binlog.000002:3121:1500124251000(2017-07-15 21:10:51)] 
****************************************************

================> binlog[mysql-binlog.000002:2887] , executeTime : 1500124251000 , delay : -359806ms
 BEGIN ----> Thread id: 18
----------------> binlog[mysql-binlog.000002:3017] , name[test_db,person] , eventType : UPDATE , executeTime : 1500124251000 , delay : -359806ms
pId : 4    type=int(11)
pName : test update    type=varchar(100)    update=true
----------------
 END ----> transaction id: 217
================> binlog[mysql-binlog.000002:3121] , executeTime : 1500124251000 , delay : -359806ms
  • cancal 的 delete

    delete person where pId = 3;

****************************************************
* Batch Id: [3] ,count : [3] , memsize : [158] , Time : 2017-07-15 21:03:02
* Start : [mysql-binlog.000002:2609:1500124142000(2017-07-15 21:09:02)] 
* End : [mysql-binlog.000002:2791:1500124142000(2017-07-15 21:09:02)] 
****************************************************

================> binlog[mysql-binlog.000002:2609] , executeTime : 1500124142000 , delay : -359575ms
 BEGIN ----> Thread id: 18
----------------> binlog[mysql-binlog.000002:2739] , name[test_db,person] , eventType : DELETE , executeTime : 1500124142000 , delay : -359574ms
pId : 3    type=int(11)
pName : test canal    type=varchar(100)
----------------
 END ----> transaction id: 215
================> binlog[mysql-binlog.000002:2791] , executeTime : 1500124142000 , delay : -359573ms
Clone this wiki locally