Golang MySql binary log replication listener

Overview

Go MySql binary log replication listener

Pure Go Implementation of MySQL replication protocol. This allow you to receive event like insert, update, delete with their datas and raw SQL queries. This code has been developed and maintained by Ven at January 2015.

Installation

go get github.com/2tvenom/myreplication

Test

The project is test with:

  • Go 1.3.3
  • MySQL 5.5, 5.6 and 5.7 (beta)
  • Docker 1.4.1 build 5bc2ff8 (functional tests)

It's not tested in real production situation.

Unit tests

go test

Docker tests

Functonal tests with Docker. Test statement based and row based replication. MySql versions 5.5, 5.6, 5.7.

cd tests
sudo ./test.sh

MySQL server settings

In your MySQL server configuration file you need to enable replication:

[mysqld]
server-id		 = 1
log_bin			 = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size  = 100M
binlog-format    = row #Row based replication

Example

package main

import (
	"fmt"
	"myreplication"
)

var (
	host     = "localhost"
	port     = 3307
	username = "admin"
	password = "admin"
)

func main() {
	newConnection := myreplication.NewConnection()
	serverId := uint32(2)
	err := newConnection.ConnectAndAuth(host, port, username, password)

	if err != nil {
		panic("Client not connected and not autentificate to master server with error:" + err.Error())
	}
	//Get position and file name
	pos, filename, err := newConnection.GetMasterStatus()

	if err != nil {
		panic("Master status fail: " + err.Error())
	}

	el, err := newConnection.StartBinlogDump(pos, filename, serverId)

	if err != nil {
		panic("Cant start bin log: " + err.Error())
	}
	events := el.GetEventChan()
	go func() {
		for {
			event := <-events

			switch e := event.(type) {
			case *myreplication.QueryEvent:
				//Output query event
				println(e.GetQuery())
			case *myreplication.IntVarEvent:
				//Output last insert_id  if statement based replication
				println(e.GetValue())
			case *myreplication.WriteEvent:
				//Output Write (insert) event
				println("Write", e.GetTable())
				//Rows loop
				for i, row := range e.GetRows() {
					//Columns loop
					for j, col := range row {
						//Output row number, column number, column type and column value
						println(fmt.Sprintf("%d %d %d %v", i, j, col.GetType(), col.GetValue()))
					}
				}
			case *myreplication.DeleteEvent:
				//Output delete event
				println("Delete", e.GetTable())
				for i, row := range e.GetRows() {
					for j, col := range row {
						println(fmt.Sprintf("%d %d %d %v", i, j, col.GetType(), col.GetValue()))
					}
				}
			case *myreplication.UpdateEvent:
				//Output update event
				println("Update", e.GetTable())
				//Output old data before update
				for i, row := range e.GetRows() {
					for j, col := range row {
						println(fmt.Sprintf("%d %d %d %v", i, j, col.GetType(), col.GetValue()))
					}
				}
				//Output new
				for i, row := range e.GetNewRows() {
					for j, col := range row {
						println(fmt.Sprintf("%d %d %d %v", i, j, col.GetType(), col.GetValue()))
					}
				}
			default:
			}
		}
	}()
	err = el.Start()
	println(err.Error())
}

Links

Licence

WTFPL

Comments
  • Cant get it to work

    Cant get it to work

    Hey guys,

    I can get replication to work just fine with npm's mysql-events package but I can't for the life of me getting this package to work. Here are some screenshots to show what is going on.

    selection_074 selection_075 selection_076

    opened by happilymarrieddad 19
  • what if binlog file is full

    what if binlog file is full

    el, err := newConnection.StartBinlogDump(pos, filename, serverId) StartBinlogDump need "pos" and "filename" what file named by filename is full(continuing writing) How should I know.

    opened by 756445638 0
  • Error panic: runtime error: slice bounds out of range [:-4]

    Error panic: runtime error: slice bounds out of range [:-4]

    Hello, after starting, I get the following error: goroutine 1 [running]: github.com/2tvenom/myreplication.(*packReader).readNextPackWithAdditionalLength(0xc00010e000, 0x4, 0xc00018b560, 0x0, 0x0) /2tvenom/myreplication/pack.go:86 +0x348 github.com/2tvenom/myreplication.(*eventLog).readEvent(0xc000120000, 0xc000105ee8, 0xc0001897a0, 0x0, 0x0) /2tvenom/myreplication/event_log.go:724 +0x45 github.com/2tvenom/myreplication.(*eventLog).Start(0xc000120000, 0x5540d8, 0xc0001120c0) /2tvenom/myreplication/event_log.go:640 +0x37 main.main() test.go:86 +0x133 Process finished with exit code 2

    What can I do?

    Thank you

    opened by bylli79 0
  • Get the column name

    Get the column name

    Is there a way to get the name of the column when you are reading from an event. I've looked through the code and I can't seem to find a good way to do this. Getting the values and the tableId are useful but not if I don't know what the column name for that table is. Here is an example

    case *myreplication.WriteEvent:
    				Output Write (insert) event
    				println("Write", e.GetTable())
    				println("NewID", e.GetTableId())
    				spew.Dump(e)
    				//Rows loop
    				for _, row := range e.GetRows() {
    					//Columns loop
    					fmt.Println("")
    					fmt.Println("Row")
    					spew.Dump(row)
    					fmt.Println("End Row")
    					for _, col := range row {
    						fmt.Println("Col")
    						spew.Dump(col)
    						
                                                   // HERE IT WOULD BE NICE IF I COULD KNOW THE COLUMN NAME 
                                                   
                                                   // fmt.Println(col.GetColumnName())
    						fmt.Println("EndCol")
    						fmt.Println("")
    						//Output row number, column number, column type and column value
    						//println(fmt.Sprintf("%d %d %d %v", i, j, col.GetType(), col.GetValue()))
    					}
    				}
    

    Or if there is a way to read the event into a table struct? Thanks!

    opened by happilymarrieddad 1
  • Missing characters

    Missing characters

    In trying to use this library, we found it was truncating the values from the binlog and we didn't take the time to figure out why. Switched to using: https://github.com/siddontang/go-mysql instead.

    opened by drasch 4
Owner
Pavel Gulbin
Pavel <Ven> Gulbin
A river for elasticsearch to automatically index mysql content using the replication feed.

Mysql River Plugin for ElasticSearch The Mysql River plugin allows to hook into Mysql replication feed using the excellent python-mysql-replication an

null 160 Jun 1, 2022
MySQL replication topology manager - agent (daemon)

orchestrator-agent MySQL topology agent (daemon) orchestrator-agent is a sub-project of orchestrator. It is a service that runs on MySQL hosts and com

GitHub 51 Mar 8, 2022
parse mysql slow log file

MSLP Tool For Parse MySQL Slow Log File And Send Via DingTalk Usage ./mslp --help Usage of ./mslp: -exclude-db string db1,db2,... exclude th

riet 0 Nov 29, 2021
mysql to mysql 轻量级多线程的库表数据同步

goMysqlSync golang mysql to mysql 轻量级多线程库表级数据同步 测试运行 设置当前binlog位置并且开始运行 go run main.go -position mysql-bin.000001 1 1619431429 查询当前binlog位置,参数n为秒数,查询结

null 14 Nov 15, 2022
Enhanced PostgreSQL logical replication

pgcat - Enhanced postgresql logical replication Why pgcat? Architecture Build from source Install Run Conflict handling Table mapping Replication iden

jinhua luo 365 Dec 21, 2022
Streaming replication for SQLite.

Litestream Litestream is a standalone streaming replication tool for SQLite. It runs as a background process and safely replicates changes incremental

Ben Johnson 7.9k Jan 9, 2023
Golang restAPI crud project with mySql database.

Golang RestAPI using gorilla/mux Golang restAPI crud project with mySql database. Test Api with Thunder Client vs code beautiful Extension. and use Be

Md Abu. Raihan 6 Mar 26, 2022
Go mysql tbox for golang

tbox Model generation tool, which mainly generates mysql table as golang type,

heige 2 Dec 30, 2021
Приложение представляет собой API на языке Golang с функциями CRUD для MySQL.

golang-crud-mysql Приложение представляет собой API на языке Golang с функциями CRUD для MySQL. Также реализован UI при помощи HTML5, CSS3, немного JQ

Emil 0 Jan 18, 2022
Single binary CLI for generating structured JSON, CSV, Excel, etc.

fakegen: Single binary CLI for generating a random schema of M columns to populate N rows of JSON, CSV, Excel, etc. This program generates a random sc

Multiprocess Labs LLC 55 Dec 26, 2022
a powerful mysql toolset with Go

go-mysql A pure go library to handle MySQL network protocol and replication. Call for Committer/Maintainer Sorry that I have no enough time to maintai

siddontang 3.9k Dec 28, 2022
Sync MySQL data into elasticsearch

go-mysql-elasticsearch is a service syncing your MySQL data into Elasticsearch automatically. It uses mysqldump to fetch the origin data at first, the

siddontang 3.9k Dec 30, 2022
A high-performance MySQL proxy

kingshard 中文主页 Overview kingshard is a high-performance proxy for MySQL powered by Go. Just like other mysql proxies, you can use it to split the read

Fei Chen 6.1k Dec 30, 2022
Vitess is a database clustering system for horizontal scaling of MySQL.

Vitess Vitess is a database clustering system for horizontal scaling of MySQL through generalized sharding. By encapsulating shard-routing logic, Vite

Vitess 15.3k Jan 3, 2023
db-recovery is a tool for recovering MySQL data.

db-recovery is a tool for recovering MySQL data. It is used in scenarios where the database has no backup or binlog. It can parse data files and redo/undo logs to recover data.

null 25 Nov 17, 2022
一个使 mysql,pgsql 数据库表自动生成 go struct 的工具

db2go 一个使 mysql、pgsql 数据库表自动生成 go struct 的工具 快速使用 将项目放入到GOPATH/src目录下

易水韩 19 Nov 25, 2022
🐳 A most popular sql audit platform for mysql

?? A most popular sql audit platform for mysql

Henry Yee 7.3k Jan 6, 2023
Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

?? Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

PingCAP 267 Nov 9, 2022