查看 binlog
是否开启
show variables like '%log_bin%'
第一步:安装MySQL:(可以参考:https://my.oschina.net/amhuman/blog/1941540)
命令:
sudo docker run -it -d --restart unless-stopped -p 13306:3306 -h mysql --name mysql -e MYSQL_ROOT_PASSWORD=root alanpeng/mysql5.7-replication-docker
查看mysql 容器的id
sudo docker inspect [容器id]
开启MySQL 的binlog
(如果不是在docker中 vi /etc/mysql/my.cnf)
vi /etc/mysql/mysql.conf.d/mysqld.cnf
添加:
cd /etc/mysql
[mysqld] log-bin=mysql-bin #添加这一行就ok binlog-format=ROW #选择row模式 server_id=1 #配置mysql replaction需要定义,不能和canal的slaveId重复
#配置表名字不区分大小写
lower_case_table_names=1
更多配置:
vi /etc/my.cnf 在[mysqld]下面添加,然后重启服务 [mysqld] log_bin=/var/lib/mysql/bin-log log_bin_index=/var/lib/mysql/mysql-bin.index expire_logs_days=5 server_id=0002 binlog_format=ROW 注解: log_bin = /var/lib/mysql/bin-log , 开启 Binlog 并写明存放日志的位置 log_bin_index = /var/lib/mysql/mysql-bin.index , 指定索引文件的位置。 expire_logs_days = 5 ,删除超出这个变量保留期之前的全部日志被删除 server_id = 0002 , 指定一个集群内的 MySQL 服务器 ID,如果做数据库集群那么必须全局唯一,一般来说不推荐 指定 server_id 等于 1。 binlog_format = ROW,设置方面提到过的三种 Binlog 的日志模式。 更多选项: max_binlog_size ,binary log 最大的大小 binlog_cache_size ,当前的多少事务cache在内存中 binlog_cache_disk_use ,当前有多少事务暂存在磁盘上的,如果这个值有数值的话,就应该要注意调优了。 max_binlog_cache_size ,最大能有多少事务cache在内存中 binlog_do_db和binlog_ingore_db ,是一对控制对哪些数据库进行收集的选项。 默认是对所有db sync_binlog ,这个值控制cache的数据commit多少次才刷到磁盘上。默认是0,也就是让数据库自己决定同步的频率。如设置成1的话,则每commit一次就会将cache的数据同步到磁盘上,这样做最安全,但是性能最差。
解决 #sql 的模式//可以不操作
in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
方案一:修改mysqld
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
方案2: 参考:https://www.cnblogs.com/ai10999/p/11449436.html
重新更新这个数据,取消这个only_full_group_by
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; set @@session.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,
授权root 用户可以远程访问(运行sql)
grant all privileges on *.* to root@'%' identified by "root";
flush privileges;
查看 binlog
是否开启
show variables like '%log_bin%'
第二步:安装canal
2.1 命令:
sudo docker run -it -d --restart unless-stopped -p 11111:11111 canal/canal-server
2.2 进入容器
sudo docker exec -it [id]
2.3修改配置修改
应用参数:(参照:https://github.com/alibaba/canal/wiki/QuickStart)
vi 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 = .\..
#################################################
第三步:写一个java springboot项目,消费benlog
pom依赖:
<dependency> <groupId>com.alibaba.otter</groupId> <artifactId>canal.client</artifactId> <version>1.1.0</version> </dependency>
写一个Test 方法:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.icil.canal; import java.net.InetSocketAddress; import java.util.List; import org.springframework.core.env.SystemEnvironmentPropertySource; import com.alibaba.otter.canal.client.CanalConnector; import com.alibaba.otter.canal.client.CanalConnectors; import com.alibaba.otter.canal.common.utils.AddressUtils; import com.alibaba.otter.canal.protocol.CanalEntry.Column; import com.alibaba.otter.canal.protocol.CanalEntry.Entry; import com.alibaba.otter.canal.protocol.CanalEntry.EntryType; import com.alibaba.otter.canal.protocol.CanalEntry.EventType; import com.alibaba.otter.canal.protocol.CanalEntry.RowChange; import com.alibaba.otter.canal.protocol.CanalEntry.RowData; import com.alibaba.otter.canal.protocol.Message; public class SimpleCanalClientExample { public static void main(String args[]) { // 创建链接 // CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(AddressUtils.getHostIp(),11111), "example", "", ""); CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(AddressUtils.getHostIp(),11111), "seatest", "", ""); System.err.println(AddressUtils.getHostIp()); int batchSize = 1000; int emptyCount = 0; try { connector.connect(); connector.subscribe(".*\\..*"); connector.rollback(); int totalEmptyCount = 120; while (emptyCount < totalEmptyCount) { Message message = connector.getWithoutAck(batchSize); // 获取指定数量的数据 long batchId = message.getId(); int size = message.getEntries().size(); if (batchId == -1 || size == 0) { emptyCount++; System.out.println("empty count : " + emptyCount); try { Thread.sleep(1000); } catch (InterruptedException e) { } } else { emptyCount = 0; // System.out.printf("message[batchId=%s,size=%s] \n", batchId, size); //打印变换的数据 printEntry(message.getEntries()); } connector.ack(batchId); // 提交确认 // connector.rollback(batchId); // 处理失败, 回滚数据 } System.out.println("empty too many times, exit"); } finally { connector.disconnect(); } } //打印变化的数据 private static void printEntry(List<Entry> entrys) { for (Entry entry : entrys) { //表示在一个事务中的数据 if (entry.getEntryType() == EntryType.TRANSACTIONBEGIN || entry.getEntryType() == EntryType.TRANSACTIONEND) { continue; } RowChange rowChage = null; try { //获取每一行的数据 rowChage = RowChange.parseFrom(entry.getStoreValue()); } catch (Exception e) { throw new RuntimeException("ERROR ## parser of eromanga-event has an error , data:" + entry.toString(), e); } //获取时间类型// update insert delete EventType eventType = rowChage.getEventType(); System.out.println(String.format("================> binlog[%s:%s] , name[%s,%s] , eventType : %s", entry.getHeader().getLogfileName(), entry.getHeader().getLogfileOffset(), entry.getHeader().getSchemaName(), entry.getHeader().getTableName(), eventType)); for (RowData rowData : rowChage.getRowDatasList()) { if (eventType == EventType.DELETE) { printColumn(rowData.getBeforeColumnsList()); } else if (eventType == EventType.INSERT) { printColumn(rowData.getAfterColumnsList()); } else { System.out.println("----改变之前---> before"); printColumn(rowData.getBeforeColumnsList()); System.out.println("----修改之后---> after"); printColumn(rowData.getAfterColumnsList()); } } } } private static void printColumn(List<Column> columns) { for (Column column : columns) { System.out.println(column.getName() + " : " + column.getValue() + " update=" + column.getUpdated()); } } }
配置多个instans : (复制 example ,删除 h2.mv.db 和 meta,dat ,避免出现找不到 初始位置问题)