使用go-mysql-postgresql实现 MySQL实
MySQL to PG 的数据同步，可以通过canal 或者 bireme 来做，但是操作起来步骤都比较费事。
之前公司的同事，在go-mysql-elasticsearch的基础上，改了一下，将target从es改为了pg，工具名称叫做go-mysql-postgresql 。这个工具最大的好处就是一键部署使用，不依赖其它组件。
项目地址：https://github.com/frainmeng/go-mysql-elasticsearch
我实验的时候，看到当前最新版本为 ： go-mysql-postgresql3.0.0-linux-amd64.tar.gz
下面是我的配置操作笔记：
1、 在源MySQL上开设同步专用的账号
grant replication slave, replication client,process ,select on *.* to dts@'%' identified by 'dts';MySQL上面的表情况：use testdb;testdb >show create table t_order \G*************************** 1. row *************************** Table: t_orderCreate Table: CREATE TABLE `t_order` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `aid` int(10) unsigned NOT NULL, `uid` int(10) unsigned NOT NULL, `type` tinyint(3) unsigned NOT NULL, `status` tinyint(4) unsigned NOT NULL, `price` int(10) unsigned NOT NULL COMMENT '', `num` int(10) unsigned NOT NULL, `city` varchar(64) NOT NULL, `category` varchar(64) NOT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`)) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT=''1 row in set (0.00 sec)
2、在PG上创建相同的表
create database testdb ;\c testdb CREATE TABLE t_order ( id bigint NOT NULL, aid bigint NOT NULL, uid bigint NOT NULL, type bigint NOT NULL, status bigint NOT NULL, price bigint NOT NULL , num bigint NOT NULL, city varchar(64) NOT NULL, category varchar(64) NOT NULL, PRIMARY KEY (id)) ;CREATE USER dts REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'dts'; grant connect on database testdb to dts;grant usage on schema public to dts;grant select on all tables in schema public to dts; grant all on table t_order to dts;
go-mysql-postgresql 的部署：
将文件解压到 /var/lib/pgsql/go-mysql-postgresql 目录里面。
vim /var/lib/pgsql/go-mysql-postgresql/master.info 将准备同步的binlog信息写入文件中
bin_name = "mysql-bin.000167"
bin_pos = 13389413
cat /var/lib/pgsql/go-mysql-postgresql/river.toml
# 源端MySQL连接配置my_addr = "172.31.10.100:3306"my_user = "dts"my_pass = "dts"my_charset = "utf8"# 目的端PG连接配置pg_host = "192.168.2.4"pg_port = 5434pg_user = "dts"pg_pass = "dts"pg_dbname = "testdb"# 存放同步到的位移点的文件目录data_dir = "./var"# Inner Http status addressstat_addr = "192.168.2.4:12800"# statsd monitorstatsd_host = "127.0.0.1"statsd_port = 8125statsd_prefix = "dbsync"# 伪装成slave时候，配置的server-idserver_id = 1001flavor = "mysql"# minimal items to be inserted in one bulkbulk_size = 1# force flush the pending requests if we don't have enough items >= bulk_sizeflush_bulk_time = "500ms"# Ignore table without primary keyskip_no_pk_table = false# concurrency confconcurrent_size = 6concurrent_ack_win = 2048# MySQL data source[[source]]schema = "testdb"tables = ["t_order"]# 目标PG的连接配置[[target]]pg_name = "172.31.10.100_testdb_t_order"pg_host = "192.168.2.4"pg_port = 5434pg_user = "dts"pg_pass = "dts"pg_dbname = "testdb"# MySQL 数据到 PG 后的分发规则[[rule]]#mysql 库表的配置schema = "testdb"table = "t_order"# pg 库表的配置pg_schema = "public"pg_table = "t_order"# 下面这行很重要，标识了rule和target的绑定关系pg_name = "172.31.10.100_testdb_t_order"
启动：
sh start.sh 即可
日志大致类似这样的：
[2019/08/21 13:02:36] [info] pgclient.go:199 pg delete event execute success! Schema[public] Table[t_order], Id[166773984],result[{0xc000182b00 1}],reqId[503]
测试：
5k条记录， 走专线 从传输到写入到pg 用了33s2019-08-20 23:33:29.289 CST [112184] LOG: duration: 0.321 ms2019-08-20 23:34:02.769 CST [112184] LOG: duration: 0.085 ms2w记录， 走专线 从传输到写入到pg 用了 140s2019-08-20 23:35:20.216 CST [112189] LOG: duration: 0.347 ms2019-08-20 23:37:39.848 CST [85173] LOG: duration: 6.648 ms
最后补充：
我们在做异构数据同步的时候，使用go-mysql-postgresql之前，通常情况下还需要将mysql老的数据全量同步过来，然后才能使用 go-mysql-postgresql来消费binlog达到同步数据的目的。 全量同步数据的方法，可以参考上一篇blog，地址： https://blog.51cto.com/lee90/2436325
