使用canal.deployer-1.1.7和canal.adapter-1.1.7实现mysql数据同步

1、下载地址 

 

--查看是否开启bin_log日志，value = on表示开启
SHOW VARIABLES LIKE 'log_bin';
-- 查看bin_log日志文件
SHOW BINARY LOGS;
--查看bin_log写入状态
SHOW MASTER STATUS;
--查看bin_log存储格式 = row
SHOW VARIABLES LIKE 'binlog_format';
--查看数据库服务id
SHOW VARIABLES LIKE 'server_id';

 

下载下面两个即可

2、解压下载好的文件

 3、修改 canal.deployer的配置文件 canal.deployer-1.1.7\conf\example\instance.properties，并进入到 canal.deployer-1.1.7\bin 目录下启动程序,instance.properties配置文件修改如下内容

# position info   #这里修改为数据源库的地址 账号密码 以及同步的表
canal.instance.master.address=127.0.0.1:3306
canal.instance.dbUsername=root
canal.instance.dbPassword=zsj381599113QQ
canal.instance.filter.regex=mytest.prj_bridge,mytest.prj_health_rule

然后执行启动命令

4、使用 canal.adapter 将 数据源数据同步至 目标数据库

修改 canal.adapter-1.1.7\conf\application.yml 配置文件 

server:port: 8081
spring:jackson:date-format: yyyy-MM-dd HH:mm:sstime-zone: GMT+8default-property-inclusion: non_nullcanal.conf:mode: tcp #tcp kafka rocketMQ rabbitMQflatMessage: truezookeeperHosts:syncBatchSize: 1000retries: -1timeout:accessKey:secretKey:consumerProperties:#  canal.deployer的地址和端口canal.tcp.server.host: 127.0.0.1:11111canal.tcp.batch.size: 500srcDataSources:defaultDS: # 对应canal.adapter-1.1.7\conf\rdb下的配置文件中的dataSourceKey的value值#数据源url: jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=trueusername: rootpassword: zsj381599113QQcanalAdapters:- instance: example # example对应路径canal.deployer-1.1.7\conf\example 和canal.adapter-1.1.7\conf\rdb下的配置文件中的 destination的value值groups:- groupId: g1    # g1对应canal.adapter-1.1.7\conf\rdb下的配置文件中的 groupId的value值   outerAdapters:- name: rdb  #rdb对应路径canal.adapter-1.1.7\conf\rdbkey: mysql1  # mysql1 对应 canal.adapter-1.1.7\conf\rdb下的配置文件中的outerAdapterKey的value值 properties:#目标数据库信息jdbc.driverClassName: com.mysql.jdbc.Driverjdbc.url: jdbc:mysql://127.0.0.1:3306/mytest2?useUnicode=truejdbc.username: rootjdbc.password: zsj381599113QQdruid.stat.enable: falsedruid.stat.slowSqlMillis: 1000

4、修改 canal.adapter-1.1.7\conf\rdb下的mysql1.yml的配置内容

dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql1
concurrent: true
dbMapping:#源数据库 database: mytest#源数据库表table: prj_bridge#目标数据库表targetTable: prj_bridgetargetPk:id: id#表字段一样全部映射  mapAll: truecommitBatch: 100 # 批量提交的大小

5、win下面canal.adapter-1.1.7\bin的startup.bat文件改成如下，不然会报错

@echo off
@if not "%ECHO%" == ""  echo %ECHO%
@if "%OS%" == "Windows_NT"  setlocalset ENV_PATH=.\
if "%OS%" == "Windows_NT" set ENV_PATH=%~dp0%set conf_dir=%ENV_PATH%\..\confset CLASSPATH=%conf_dir%
set CLASSPATH=%CLASSPATH%;%conf_dir%\..\lib\*set JAVA_MEM_OPTS= -Xms128m -Xmx512m -XX:PermSize=128m
set JAVA_OPTS_EXT= -Djava.awt.headless=true -Djava.net.preferIPv4Stack=true -Dapplication.codeset=UTF-8 -Dfile.encoding=UTF-8
set ADAPTER_OPTS= -DappName=canal-adapterset JAVA_OPTS= %JAVA_MEM_OPTS% %JAVA_OPTS_EXT% %ADAPTER_OPTS%set CMD_STR= java %JAVA_OPTS% -classpath "%CLASSPATH%" com.alibaba.otter.canal.adapter.launcher.CanalAdapterApplication
echo start cmd : %CMD_STR%java %JAVA_OPTS% -classpath "%CLASSPATH%" com.alibaba.otter.canal.adapter.launcher.CanalAdapterApplication

然后启动canal.adapter 即可实现数据同步

如果需要同步多张表 canal.adapter-1.1.7\conf\application.yml配置如下 

server:port: 8081
spring:jackson:date-format: yyyy-MM-dd HH:mm:sstime-zone: GMT+8default-property-inclusion: non_nullcanal.conf:mode: tcp #tcp kafka rocketMQ rabbitMQflatMessage: truezookeeperHosts:syncBatchSize: 1000retries: -1timeout:accessKey:secretKey:consumerProperties:#  canal.deployer的地址和端口canal.tcp.server.host: 127.0.0.1:11111canal.tcp.batch.size: 500srcDataSources:defaultDS: # 对应canal.adapter-1.1.7\conf\rdb下的配置文件中的dataSourceKey的value值#数据源url: jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=trueusername: rootpassword: zsj381599113QQcanalAdapters:- instance: example # example对应路径canal.deployer-1.1.7\conf\example 和canal.adapter-1.1.7\conf\rdb下的配置文件中的 destination的value值groups:- groupId: g1    # g1对应canal.adapter-1.1.7\conf\rdb下的配置文件中的 groupId的value值   outerAdapters:- name: rdb  #rdb对应路径canal.adapter-1.1.7\conf\rdbkey: mysql1  # mysql1 对应 canal.adapter-1.1.7\conf\rdb下的配置文件中的outerAdapterKey的value值 properties:#目标数据库信息jdbc.driverClassName: com.mysql.jdbc.Driverjdbc.url: jdbc:mysql://127.0.0.1:3306/mytest2?useUnicode=truejdbc.username: rootjdbc.password: zsj381599113QQdruid.stat.enable: falsedruid.stat.slowSqlMillis: 1000- name: rdb  #rdb对应路径canal.adapter-1.1.7\conf\rdbkey: mysql2  # mysql1 对应 canal.adapter-1.1.7\conf\rdb下的配置文件中的outerAdapterKey的value值 properties:#目标数据库信息jdbc.driverClassName: com.mysql.jdbc.Driverjdbc.url: jdbc:mysql://127.0.0.1:3306/mytest2?useUnicode=truejdbc.username: rootjdbc.password: zsj381599113QQdruid.stat.enable: falsedruid.stat.slowSqlMillis: 1000

canal.adapter-1.1.7\conf\rdb目录下需要同步几张表的数据就有几个yml配置文件，此次实验同步两张表，两个配置文件如下  mysql1.yml

dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql1
concurrent: true
dbMapping:#源数据库 database: mytest#源数据库表table: prj_bridge#目标数据库表targetTable: prj_bridgetargetPk:id: id#表字段一样全部映射  mapAll: truecommitBatch: 100 # 批量提交的大小

mysql2.yml

dataSourceKey: defaultDS
destination: example
groupId: g1
outerAdapterKey: mysql2
concurrent: true
dbMapping:#源数据库 database: mytest#源数据库表table: prj_health_rule#目标数据库表targetTable: prj_health_ruletargetPk:id: id#表字段一样全部映射  mapAll: truecommitBatch: 100 # 批量提交的大小