要实现数据库A（Master: 123.123.123.123）到数据库B（Slave: 192.168.0.59）的异地同步，并忽略删除操作，使用Canal的方案如下：
一、整体架构
##Canal.Deliver和Canal.Adapter版本均为v1.1.7
MySQL-A (123.123.123.123)
│
↓ (binlog)
Canal Deliver/Server (订阅binlog和过滤DELETE事件，192.168.0.59)
│
↓ (解析事件)
Canal Client/Adapter (192.168.0.59)
│
↓ (INSERT/UPDATE操作)
MySQL-B (192.168.0.59)
二、详细步骤
2.1 配置MySQL Master (123.123.123.123)
-
开启binlog (修改
my.cnf):[mysqld] server-id=1 # 唯一标识，避免与目标库冲突 log-bin=mysql-bin # Binlog文件名前缀 binlog-format=ROW # 推荐使用ROW模式，记录行级变更 binlog-do-db=test_db # 仅同步指定数据库（可选） -
创建Canal专用用户:
CREATE USER 'canal'@'%' IDENTIFIED BY 'canal_password'; GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW ON *.* TO 'canal'@'%'; FLUSH PRIVILEGES; -
重启MySQL
systemctl restart mysqld -
MySQL-A全备份到MySQL-B
# 步骤就不写了
2.2 部署Canal Deployer(192.168.0.59)
-
下载Canal: 官网Release页
-
修改配置 (
conf/example/instance.properties):deployer]# grep -Ev "^#|^$|=$" conf/example/instance.properties canal.instance.mysql.slaveId=1234 canal.instance.gtidon=false canal.instance.master.address=123.123.123.123:3306 canal.instance.tsdb.enable=true canal.instance.dbUsername=canal canal.instance.dbPassword=canal_password canal.instance.connectionCharset = UTF-8 canal.instance.enableDruid=false canal.instance.filter.regex=.*\\..* canal.instance.filter.black.regex=mysql\\.slave_.* canal.instance.filter.dml.delete = true # 忽略删除的关键步骤 canal.mq.topic=example canal.mq.partition=0 -
启动Canal:
sh bin/startup.sh
2.3 部署Canal Adapter (192.168.0.59)
-
下载地址同2.2
-
修改Adapter配置 (
conf/application.yml):server: port: 8081 spring: jackson: date-format: yyyy-MM-dd HH:mm:ss time-zone: GMT+8 default-property-inclusion: non_null canal.conf: # 禁用远程配置 manager.enable: false # 强制使用本地配置 useLocalConfig: true mode: tcp flatMessage: true zookeeperHosts: # 如果使用ZK，需要配置 syncBatchSize: 1000 retries: 0 timeout: 30000 # 添加超时时间 accessKey: secretKey: filter: includes: .*\\..* # 所有表 excludes: .*\\.DELETE$ # 排除所有删除操作 consumerProperties: # 指向Canal Server地址 canal.tcp.server.host: 127.0.0.1:11111 canal.tcp.batch.size: 500 # 源数据库配置 (MySQL Master) srcDataSources: defaultDS: url: jdbc:mysql://123.123.123.123:3306?useSSL=false&serverTimezone=UTC username: canal password: canal_password # 适配器配置 canalAdapters: - instance: example # 添加实例名称，必须与Canal Server中的实例名匹配 groups: - groupId: g1 outerAdapters: - name: rdb key: mysql_target properties: jdbc.driverClassName: com.mysql.jdbc.Driver jdbc.url: jdbc:mysql://127.0.0.1:3306/test_db?useSSL=false&serverTimezone=UTC jdbc.username: root jdbc.password: root_password -
配置表映射规则 (
conf/rdb/mytest.yml):dataSourceKey: defaultDS destination: example groupId: g1 outerAdapterKey: mysql_target dbMapping: mirrorDb: true database: test_db # 指定数据库名 -
如果要同步指定表，配置表映射规则 (
conf/rdb/mytest.yml):dataSourceKey: defaultDS # 源数据源的key, 对应上面配置的srcDataSources中的值 destination: example # cannal的instance或者MQ的topic groupId: g1 # 对应MQ模式下的groupId, 只会同步对应groupId的数据 outerAdapterKey: mysql_target # adapter key, 对应上面配置outAdapters中的key concurrent: true # 是否按主键hash并行同步, 并行同步的表必须保证主键不会更改及主键不能为其他同步表的外键!! dbMapping: database: test_db # 源数据源的database/shcema table: db1_url1 # 源数据源表名 targetTable: db1_url1 # 目标数据的表名 targetPk: # 主键映射 id: id # 如果是复合主键可以换行映射多个 mapAll: true # 是否整表映射, 要求源表和目标表字段名一模一样 (如果targetColumns也配置了映射,则以targetColumns配置为准) # targetColumns: # 字段映射, 格式: 目标表字段: 源表字段, 如果字段名一样源表字段名可不填 # id: # name: # role_id: # c_time: # test1:
2.4 启动Canal Adapter
sh bin/startup.sh
2.5 验证
-
验证1：数据同步
## MySQL-A (123.123.123.123) mysql> SELECT type FROM test_db.db1_url1 WHERE id=13; +------+ | type | +------+ | 4 | +------+ 1 row in set (0.00 sec) ## MySQL-B (192.168.0.59) mysql> SELECT type FROM test_db.db1_url1 WHERE id=13; +------+ | type | +------+ | 4 | +------+ 1 row in set (0.00 sec) ## MySQL-A (123.123.123.123) mysql> UPDATE test_db.db1_url1 SET type = 14 WHERE id = 13; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT type FROM test_db.db1_url1 WHERE id=13; +------+ | type | +------+ | 14 | +------+ 1 row in set (0.00 sec) ## MySQL-B (192.168.0.59) mysql> SELECT type FROM test_db.db1_url1 WHERE id=13; +------+ | type | +------+ | 14 | +------+ 1 row in set (0.00 sec) # update后数据同步成功 -
验证2：删除
## MySQL-A (123.123.123.123) mysql> DELETE FROM test_db.db1_url1 WHERE id = 13; Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO test_db.db1_url1(id, type, `index`, is_open, icon_url) VALUES (15, 20, 21, 1,'http://www.baidu.com'); Query OK, 1 row affected (0.00 sec) ## MySQL-B (192.168.0.59) mysql> SELECT id,type FROM test_db.db1_url1 WHERE id = 13 OR id = 15; +----+------+ | id | type | +----+------+ | 13 | 14 | | 15 | 20 | +----+------+ 2 rows in set (0.00 sec) # 发现在数据库B同步插入了id为15的行，并且id为13的行未被删除，说明忽略删除配置是成功的
2.6 多库同步
我们一个MySQL数据库源中有多个库需要同步，配置如下
-
配置MySQL Master (123.123.123.123)
# 修改binlog日志同步数据库 [mysqld] server-id=1 # 唯一标识，避免与目标库冲突 log-bin=mysql-bin # Binlog文件名前缀 binlog-format=ROW # 推荐使用ROW模式，记录行级变更 binlog-do-db=test_db # 仅同步指定数据库（可选） binlog-do-db=test_db2 # 仅同步指定数据库（可选） # 重启mysql systemctl restart mysqld -
配置 canal deployer
-
修改配置 (
conf/example/instance.properties):# 配置conf/example/instance.properties deployer]# grep -Ev "^#|^$|=$" conf/example/instance.properties canal.instance.mysql.slaveId=1234 canal.instance.gtidon=false canal.instance.master.address=123.123.123.123:3306 canal.instance.tsdb.enable=true canal.instance.dbUsername=canal canal.instance.dbPassword=canal_password canal.instance.connectionCharset = UTF-8 canal.instance.enableDruid=false canal.instance.filter.regex=.*\\..* canal.instance.filter.black.regex=mysql\\.slave_.* canal.instance.multi.stream.enabled=true # 启用组播模式（支持多个消费者组） canal.instance.filter.dml.delete = true canal.mq.topic=example canal.mq.partition=0 # 重启deployer sh bin/restart.sh -
配置 canal adapter
-
修改Adapter配置 (
conf/application.yml):server: port: 8081 spring: jackson: date-format: yyyy-MM-dd HH:mm:ss time-zone: GMT+8 default-property-inclusion: non_null canal.conf: manager.enable: false # 禁用远程配置 useLocalConfig: true # 强制使用本地配置 mode: tcp flatMessage: true zookeeperHosts: # 如果使用ZK，需要配置 syncBatchSize: 1000 retries: 0 timeout: 30000 # 添加超时时间 accessKey: secretKey: filter: includes: .*\\..* # 所有表 excludes: .*\\.DELETE$ # 排除所有删除操作 consumerProperties: # 指向Canal Server地址 canal.tcp.server.host: 127.0.0.1:11111 canal.tcp.batch.size: 500 # 源数据库配置 (MySQL Master) srcDataSources: defaultDS: url: jdbc:mysql://123.123.123.123:3306?useSSL=false&serverTimezone=UTC username: canal password: canal_password # 适配器配置 canalAdapters: - instance: example groups: - groupId: g1 outerAdapters: - name: rdb key: mysql_target properties: jdbc.driverClassName: com.mysql.jdbc.Driver jdbc.url: jdbc:mysql://127.0.0.1:3306/test_db?useSSL=false&serverTimezone=UTC jdbc.username: root jdbc.password: root_password - instance: example groups: - groupId: g2 # 新订阅组ID outerAdapters: - name: rdb # 必须为rdb key: mysql_target2 # 新key properties: jdbc.driverClassName: com.mysql.jdbc.Driver jdbc.url: jdbc:mysql://127.0.0.1:3306/test_db2?useSSL=false&serverTimezone=UTC jdbc.username: root jdbc.password: root_password -
新增配置表映射规则(conf/rdb/mytest2.yml)
# 新增配置表映射规则 #] cp conf/rdb/mytest.yml conf/rdb/mytest2.yml # 修改配置规则 #] cat conf/rdb/mytest2.yml dataSourceKey: defaultDS destination: example groupId: g2 # application.yml新增的一致 outerAdapterKey: mysql_target2 # application.yml新增的一致 dbMapping: mirrorDb: true database: test_db2 # 指定同步数据库名 # 重启adapter #] sh bin/restart.sh -
验证
## MySQL-A (123.123.123.123)
mysql> DELETE FROM test_db2.db2_url1 WHERE id = 13;
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test_db2.db2_url1(id, type, `index`, is_open, icon_url) VALUES (15, 20, 21, 1,'http://www.baidu.com');
Query OK, 1 row affected (0.00 sec)
## MySQL-B (192.168.0.59)
mysql> SELECT id,type FROM test_db2.db2_url1 WHERE id = 13 OR id = 15;
+----+------+
| id | type |
+----+------+
| 13 | 14 |
| 15 | 20 |
+----+------+
2 rows in set (0.00 sec)
# 发现在数据库B同步插入了id为15的行，并且id为13的行未被删除，说明多库数据同步配置成功
扫一扫
3401
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
