An overview,

Go-mysql-transfer is a real-time incremental synchronization tool for mysql database.

It monitors changes in the MySQL binary log and sends messages in a specified format to the receiver in real time. Thus a high performance and low latency incremental data synchronization update pipeline is formed between the database and the receiver.

Features:

1, simple, independent of other components, one-click deployment

2, integrate multiple receivers, such as Redis, MongoDB, Elasticsearch, RocketMQ, Kafka, RabbitMQ, HTTP API, etc., no need to write a client, out of the box

3, built-in rich data parsing, message generation rules, template syntax

4, support Lua script extension, can deal with complex logic

5. Integrate Prometheus client to monitor alarms

6. Integrate Web Admin monitoring page

7. High availability cluster deployment is supported

8. Data synchronization fails and retry

9. Support full data initialization

Second, compared with similar tools

features Canal mysql_stream go-mysql-transfer
Development of language Java Python Golang
High availability support support support
The receiving end Code to customize Kafka, etc. (MQ) Redis, MongoDB, Elasticsearch, RabbitMQ, Kafka, RocketMQ, HTTP API

More support
Initialize full data Does not support support support
The data format Code to customize Json (fixed format) Json (Rule configuration)

Template syntax

The Lua script

Iii. Design and implementation

1. Implementation principle

1) Go-mysql-transfer disguizes itself as a Slave of mysql,

2) Send the dump protocol to Master to obtain the binlog, parse the binlog, and generate messages

3) Send the generated messages to the receiver in real time and in batches

2. Rule-based synchronization

Most of the synchronization can be done using configurations, such as synchronizing table T_user to REids, with the following rules:

Table: t_user # table name Column_underscore_to_camel: Datetime_formatter: datetime_formatter: Yyyy-mm-dd HH: MM :ss # Datetime, timestamp format, no default yyyY-MM-DD HH: MM: SS value_encoder: Json #value_formatter: supports json, KV-commas, V-commas #value_formatter: |. '{{ID}} {{. USER_NAME}} | | {{. REAL_NAME}} {{if eq. STATUS 0}} stop using {{else}} {{end}}' opening redis_structure: string # redis data types. Supports string, hash, list, and set (consistent with the data type of redis) redis_KEY_prefix: USER_ #key redis_KEY_column: USER_NAME # which column value will be used as key. Default primary key is used if this is not specifiedCopy the code

Table t_user;

After being synchronized to Redis, the data is as follows:

For more rules-based synchronization examples, see www.kancloud.cn/wj596/go-my…

3. Synchronization based on Lua scripts

As a professional built-in scripting language, Lua is designed to be embedded in applications to provide flexible extension and customization capabilities for applications. It only takes a little time for developers to get a rough grasp of Lua’s syntax and write usable scripts.

Lua based high scalability, can achieve more complex data conversion, and processing work

Sample script:

Local ops = require("redisOps") local row = ops.rawrow () Local Action = op.rawAction () -- Operation events of the current database, including: Insert, updare, delete local id = row[" id "] local userName = row["USER_NAME"] local key = "user_".. Id -- delete key if action == "delete" -- delete event then ops.del (key) -- delete key else local password = row[" password "] Localcreatetime = row["CREATE_TIME"] localresult = {} localresult ["id"] = id result["userName"] = userName result["password"] = password result["createTime"] = createTime result["source"] = SET(key,val) -- Redis SET command, the first parameter is key(string type), The second argument is value endCopy the code

Table t_user;

After being synchronized to Redis, the data is as follows:

For more rules-based synchronization examples, see www.kancloud.cn/wj596/go-my…

4. Monitoring alarms Supports two monitoring modes, Prometheus and built-in WebAdmin

Prometheus is a popular open source monitoring alarm system and TSDB, whose indicator collection component is known as Exporters. Go-mysql-transfer is itself an exporter. Provide Prometheus with application status, receiving status, insert number, Update number, Delete number, delay, and other indicators.

5. High availability

Supports high availability cluster construction based on ZooKeeper or ETCD

Only the leader node responds to the dump event of binglog, while the followers node is dormant and does not send dump commands. Therefore, multiple followers do not add to the burden of the MySQL database

When the leader node fails, the followers node quickly takes over to achieve second-level failover

The structure is as follows:

6. Fail and retry

Network jitter and receiver faults may cause data synchronization failures. A retry mechanism is required to ensure that data is not missed and all data can be delivered.

When a fault occurs, record the position(displacement) of binglog at the fault moment. After the fault is recovered, dump data from the position again and send it to the receiver. Ensure that no data is lost, and ensure that the data sequence, correct.

7. Initialize full data

Support to synchronize existing data in the database to the receiver at one time. You can either synchronize existing data in the database to the receiver and then increment it or use the full data synchronization function as an ETL tool

# 4, open source address

Making: go – mysql – transfer

If this tool helps you, please support it under Star on Github

# 5. Use documentation

  • The deployment of running
  • High availability cluster
  • Synchronize data to Redis
    • Redis configuration
    • Rule-based synchronization
    • Synchronization based on Lua scripts
  • Synchronize data to MongoDB
    • Mongo configuration
    • Rule-based synchronization
    • Synchronization based on Lua scripts
  • Synchronize data to RocketMQ
    • RocketMQ configuration
    • Rule-based synchronization
    • Synchronization based on Lua scripts
  • Synchronize data to Kafka
    • Kafka configuration
    • Rule-based synchronization
    • Synchronization based on Lua scripts
  • Synchronize data to RabbitMQ
    • The RabbitMQ configuration
    • Rule-based synchronization
    • Synchronization based on Lua scripts
  • Synchronize data to Elasticsearch
    • Elasticsearch configuration
    • Rule-based synchronization
    • Synchronization based on Lua scripts
  • Full data import
  • The Lua script
    • Basic module
    • Json module
    • HttpClient module
    • DBClient module
  • monitoring
  • The performance test
  • Q&A