Introduction to the
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
Open source
Making: go – mysql – transfer
If this tool helps you, please support it under Star on Github
Installation operation
You can download the compiled installation package directly by clicking Download
Latest version: V1.0.3 release
You can also compile it yourself:
1. Rely on Golang 1.14 and above
Set ‘GO111MODULE=on’
‘git clone github.com/wj596/go-my… ‘
4. Go to the directory and run ‘Go build’ to compile
run
1. Enable MySQL binlog
Log-bin =mysql-bin # enable binlog binlog-format=ROW # select ROW mode server_id=1 # configure mysql Replaction needs to be defined. Do not repeat slave_id for go-mysql-transferCopy the codeExe Linux run nohup go-mysql-transfer.exe Linux run nohup go-mysql-transfer &
Docker run
Build the image based on the source code
Git clone github.com/wj596/go-my… Docker image build -t go-mysql-transfer-f Dockerfile. ‘4, run’ docker run -d ‘ –name go-mysql-transfer -p 8060:8060 go-mysql-transfer:latest ‘
Build images based on binary executables
Download the compiled installation package: 2. Decompress, Docker image build -t go-mysql-transfer-f Dockerfile. ‘4, run’ docker run -d –name ‘ go-mysql-transfer -p 8060:8060 go-mysql-transfer:latest ‘
Redis configuration
Related configurations are as follows:
# app.yml redis_addrs: 127.0.0.1:6379 # app.yml redis_addrs: 127.0.0.1:6379 Cluster #redis_master_name: #redis_pass: 123456 #redis password #redis_database: 0 #redis database 0-16, default 0. This parameter is invalid if group_type is clusterCopy the codeSupport Redis deployment mode: single machine, master/slave (Sentinel), cluster
Rule-based synchronization
The synchronization rules are as follows:
Table: t_user # table name #order_by_column: Select * from 'id'; Stock data cannot be null during synchronization # column_LOWER_CASE :false# Column names should be converted to lowercase, which is false by default #column_upper_case:false# Column names should be converted to uppercase, which is false by default Column_underscore_to_camel: true # Column name underline to camel, default to false # included columns, multi-value comma separated, such as id,name,age,area_id if empty, contains all columns # include_COLUMNS: ID,USER_NAME,PASSWORD #exclude_columns: BIRTHDAY,MOBIE # Exclude_columns: BIRTHDAY,MOBIE # Exclude_columns: BIRTHDAY,MOBIE # CARD_NO= SFZ # Column name mapping, multiple mapping relationships are separated by commas, for example, USER_NAME=account indicates that USER_NAME is mapped to account # default_column_VALUES: Source =binlog,area_name= hefei #date_formatter: Yyyy-mm-dd #datetime_formatter yyyY-MM-DD #datetime_formatter: Yyyy-mm-dd HH: MM :ss # Datetime, timestamp format, no default yyyY-MM-DD HH: MM :ss #lua_file_path: Lua /t_user.lua #lua script file, see the user manual, when this value is not empty, the following configuration is invalid except redis_structure #lua_script: Value_encoder: json # value encoding, support JSON, KV-commas, V-commas; Default is json #value_formatter: |. '{{ID}} {{. USER_NAME}} | | {{. REAL_NAME}} {{if eq. STATUS 0}} stop using {{else}} {{end}}' opening # redis related redis_structure: # string data type. Supports string, hash, list, set, and sortedSet (consistent with redis data types) redis_KEY_prefix: prefix of USER_ #key redis_key_column: USER_NAME which column # use value as the key, not fill in the default to the primary key # redis_key_formatter: '| {{. ID}} {{. USER_NAME}}' # redis_key_value: user # key value (fixed value); This value cannot be empty when redis_structure is hash, list, set, sortedset #redis_hash_field_prefix: _CARD_ #hash field prefix, only valid if redis_structure is hash #redis_hash_field_column: Cert_No # which column value to use as the hash field, only applies if redis_structure is hash, default primary key is used if not specifiedCopy the codeValue_encoder indicates the value encoding format, supporting JSON, KV-commas, and V-CommAs. If you do not fill in the value, the default value is JSON, as shown in the following table:
- Json: Format: {“id”: “1001”,”userName”: “admin”,”password”: “123456”, “createTime”: “2020-07-20 14:29:19”}
- Kv-commas: indicates key-value separated by commas (,). For example, id=1001,userName=admin,password=123456,createTime=2020-07-20 14:29:19
- V-commas: value The values are separated by commas, for example, 1001,admin,123456,2020-07-20 14:29:19
Value_formatter represents a formatted expression for a value. Value_encoder is invalid if value_formatter is not empty. Redis_key_formatter represents a formatted expression for a KEY.
The synchronized data type is string
Table t_user;
The configuration is as follows:
Schema: eseAP # database name TABLE: t_user # table name Column_underscore_to_camel: true # Column name underscore camel, default is false value_encoder: Json # value encoding reDIS_structure: string # data type. Supports string, hash, list, set, and sortedSet (consistent with redis data types) redis_KEY_prefix: prefix of 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 codeThe data synchronized to Redis is as follows:
Synchronize data to a list
Table t_user;
The configuration is as follows:
value_formatter: |. '{{ID}} {{the USER_NAME}}' # value formatting expression, such as: |. {{ID}} {{the USER_NAME}}, {{. ID}} said ID field value, {{. USER_NAME}} said that the value of the USER_NAME redis_structure: list redis_key_value: User_list #key value (fixed value); This value cannot be empty when redis_structure is hash, list, set, or sortedsetCopy the codeThe data synchronized to Redis is as follows:
The synchronization type is set
Table t_user;
The configuration is as follows:
value_formatter: |. '{{ID}} {{the USER_NAME}}' # value formatting expression, such as: |. {{ID}} {{the USER_NAME}}, {{. ID}} said ID field value, {{. USER_NAME}} said that the value of the USER_NAME redis_structure: set redis_key_value: The value of user_set #key (fixed); This value cannot be empty when redis_structure is hash, list, set, or sortedsetCopy the codeThe data synchronized to Redis is as follows:
Synchronize data to order Set type
Table t_user;
The configuration is as follows:
value_formatter: |. '{{ID}} {{the USER_NAME}}' # value formatting expression, such as: |. {{ID}} {{the USER_NAME}}, {{. ID}} said ID field value, {{. USER_NAME}} said that the value of the USER_NAME redis_structure: sortedset redis_key_value: The value of users #key (fixed value); When redis_structure is hash, list, set, sortedSET this value cannot be empty. CREATE_TIME # sortedSet score, when the data type is sortedSET, this item cannot be empty, the value of this item should be numericCopy the codeThe data synchronized to Redis is as follows:
The synchronized data type is hash
Table t_user;
The configuration is as follows:
Column_underscore_to_camel: true # Column name underline to camel, default to false value_encoder: Json # value encoding, supporting JSON, KV-commas, V-commas redis_structure: hash redis_key_value: user_cache #key value (fixed value); This value cannot be empty when redis_structure is hash, list, set, sortedSET: User_name_ # Hash field prefix, only available when redis_structure is hash redis_hash_field_column: User_name # which column value to use as the hash field, only applies if redis_structure is hash, default primary key is used if not specifiedCopy the codeThe data synchronized to Redis is as follows:
You can map a table to a HASH using rules, but if you need to map each row in talbe to a HASH, you can use Lua scripts to do so, as shown in the following examples.
Synchronization based on Lua scripts
Lua scripts can be used to achieve more complex data processing logic, and Go-mysql-Transfer supports Lua5.1 syntax
The sample a
Table t_user;
The Lua script:
-- t_user_redis. Lua local json = require("json") -- load json module local ops = require("redisOps") -- load redis operation module local row = Ops.rawrow () -- A row of data that has been changed in the database. Table type: key: column name local Action = ops.rawAction() -- a row of data that has been changed in the database. Insert, updare, delete local id = row[" id "] local userName = row["USER_NAME"] local key = "user_".. Id -- define key if action == "delete" -- delete event then ops.del (key) ops.srem ("user_set") else local password = Local createTime = row["CREATE_TIME"] -- CREATE_TIME local result = {} -- define a table result["id"] = id result["userName"] = userName result["password"] = password result["createTime"] = createTime Result ["source"] = "binlog" -- data source local val = json.encode(result) -- convert newTable to json ops.set (key,val) -- Corresponding to Redis SET command, the first parameter is key(support string type), The second parameter is value if action == "update" -- modify the event then local oldRow = ops.rawoldrow () -- change the data before the database changed (modify the data before) local oldUserName = SREM("user_set",oldUserName) -- delete the old value end ops.SADD("user_set",userName) -- Corresponding to the SADD command of Redis, the first parameter is key(supporting string type), and the second parameter is value endCopy the codeIntroducing scripts:
Rule: - schema: eseAP table: t_user LUA_file_path: lua/ T_user_redis. Lua # Lua script fileCopy the codeThe data synchronized to Redis is as follows:
Example 2 maps a row in talbe to a HASH
The Lua script is as follows:
-- t_user_redis2.lua local ops = require("redisOps") -- local row = ops.rawRow() -- Local action = op.rawAction () -- current database events, including: Insert, updare, delete if action == "insert" -- only listen for insert events then local key = row["USER_NAME"] -- obtain USER_NAME local id = Local userName = row["USER_NAME"] -- obtain the value of USER_NAME local password = row[" password "] -- obtain the value of password Local createTime = row["CREATE_TIME"] -- Get ops.HSET(key,"id",id) -- Redis HSET Ops. HSET(key,"userName",userName) -- HSET command of Redis -- HSET command of Redis Ops. HSET(key,"createTime",createTime) -- corresponding to Redis HSET command endCopy the codeIntroducing scripts:
Rule: - schema: eseAP table: t_user LUA_file_path: lua/ T_user_redis2. Lua # Lua script fileCopy the codeThe data synchronized to Redis is as follows:
RedisOps module
The redisOps module provides the following methods for manipulating Redis:
- SET: Redis string command to SET the value of the specified key. Such as: ops. SET (key, val)
- DEL: Redis string command to delete the specified key. Such as: ops. DEL (key)
- HSET: Redishash command to set the value of field in the key table. Such as: ops. HSET (key, field, val)
- HDEL: Redishash command to set the key field in the hash table. Such as: ops. HDEL (key, field)
- RPUSH: Redis list command to insert the value into the head of the list key. Such as: ops. RPUSH (key, val)
- LREM: Redis list command to remove the key from the list. Such as: ops. LREM (key, val)
- SADD: Redis collection command to add a value to the collection key. Such as: ops. SADD (key, val)
- SREM: Redis set command to remove the set key. Such as: ops. SREM (key, val)
- ZADD: Redis ordered collection command to add a value to the ordered collection key. Such as: ops. ZADD (key, score, val)
- ZREM: Redis ordered set command to remove the value of ordered set key. Such as: ops. ZREM (key, val)