Shulou(Shulou.com)06/03 Report--

MySQL to PG data synchronization, can be done through canal or birem, but the operation steps are more troublesome.

My colleagues at the company changed target from es to pg based on go-mysql-elasticsearch, and the tool name was go-mysql-postgresql. The biggest advantage of this tool is that it is deployed with one click and does not depend on other components.

Project address: github.com/frainmeng/go-mysql-elasticsearch

When I was experimenting, I saw that the latest version was go-mysql-postgresql3.0.0-linux-amd64.tar.gz

Here are my configuration notes:

1. Open a synchronization dedicated account on source MySQL

grant replication slave, replication client,process ,select on *.* to dts@'%' identified by 'dts'; MySQL above emoji: use testdb;testdb >show create table t_order \G*************************** 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)

Create the same table on 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 deployment:

Unzip the file to/var/lib/pgsql/go-mysql-postgresql directory.

The requested URL/var/lib/pgsql/go-mysql-postgresql/master.info was not found on this server.

bin_name = "mysql-bin.000167"

bin_pos = 13389413

cat /var/lib/pgsql/go-mysql-postgresql/river.toml

#Source MySQL connection configuration my_addr = "172.31.10.100:3306"my_user = "dts"my_pass = "dts"my_charset = "utf8"#Destination PG connection configuration pg_host = "192.168.2.4"pg_port = 5434pg_user = "dts"pg_pass = "dts"pg_dbname = "testdb"#File directory 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"#When masquerading as slave, configured server-idserver_id = 1001flavor = "mysql"# minimal items to be inserted in one bulk_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#currency concurrency_size = 6concurrent_ack_win = 2048# MySQL data source[[source]]schema = "testdb"tables = ["t_order"]#target PG connection configuration [[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 data distribution rules after PG [[rule]]#mysql library table configuration schema = "testdb"table = "t_order"#pg_schema = "public"pg_table = "t_order"#The following line is important, identifying the binding relationship between rule and target pg_name = " 172.31.10.100_testdb_t_order"

Start:

sh start.sh

The journal looks something like this:

[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]

Testing:

5k records, via dedicated line from transmission to writing to pg took 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 records, via dedicated line from transmission to writing to pg took 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

Finally add:

When we do heterogeneous data synchronization, before using go-mysql-postgresql, we usually need to synchronize all the old mysql data, and then we can use go-mysql-postgresql to consume binlog to achieve the purpose of synchronizing data. For full synchronization of data, please refer to the previous blog at https://blog.51cto.com/lee90/2436325

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.