使用 Go 開發 MySQL binlog 同步工具
背景
這篇是一個使用 golang 開發的 binlog 解析工具，更偏向 demo 和研究性質。簡單來說，就是模擬 MySQL binlog 協議，開發一個服務，作爲 MySQL 的 “從庫”，獲取 binlog，有點像 java 開發的 canal。
實踐
過程和結構
執行過程主要是server模塊。首先連接 MySQL，這裏參考了我們使用的中間件部分 (kingshard)。然後先關閉 checkSum，然後作爲從庫註冊到主庫，發送 binlog_dump 命令。最後的操作就是監聽獲取 binlog，然後通過go-mysql提供的方法，將 binlog events 解析出來並打印。
代碼
- config
配置部分，描述 binlog 文件，位置，主庫 MySQL 賬號信息等。
package app
type Config struct {
Host string
Port int
User string
Pass string
ServerId int
LogFile string
Position int
}- server 模塊
整個的核心部分，包括連接，註冊，發送命令，獲取 binlog 都是在這裏。這裏的解析 binlog 使用了go-mysql。
package app
import (
"bufio"
"bytes"
"context"
"crypto/sha1"
"encoding/binary"
"errors"
"fmt"
"github.com/siddontang/go-mysql/replication"
"io"
"net"
"os"
"time"
)
const (
MinProtocolVersion byte = 10
OK_HEADER byte = 0x00
ERR_HEADER byte = 0xff
EOF_HEADER byte = 0xfe
LocalInFile_HEADER byte = 0xfb
)
const MaxPayloadLength = 1<<24 - 1
type Server struct {
Cfg *Config
Ctx context.Context
conn net.Conn
io *PacketIo
registerSucc bool
}
func (s *Server) Run() {
defer func() {
s.Quit()
}()
s.dump()
}
func (s *Server) dump() {
err := s.handshake()
if err != nil {
panic(err)
}
s.invalidChecksum()
fmt.Println("dump ...")
s.register()
s.writeDumpCommand()
parser := replication.NewBinlogParser()
for {
//time.Sleep(2 * time.Second)
//s.query("select 1")
data, err := s.io.readPacket()
if err != nil || len(data) == 0 {
continue
}
//s.Quit()
if data[0] == OK_HEADER {
//skip ok
data = data[1:]
if e, err := parser.Parse(data); err == nil {
e.Dump(os.Stdout)
} else {
fmt.Println(err)
}
} else {
s.io.HandleError(data)
}
}
}
func (s *Server) invalidChecksum() {
sql := `SET @master_binlog_checksum='NONE'`
if err := s.query(sql); err != nil{
fmt.Println(err)
}
//must read from tcp connection , either will be blocked
_, _ = s.io.readPacket()
}
func (s *Server) handshake() error {
conn, err := net.DialTimeout("tcp", fmt.Sprintf("%s:%d", s.Cfg.Host, s.Cfg.Port), 10*time.Second)
if err != nil {
return err
}
tc := conn.(*net.TCPConn)
tc.SetKeepAlive(true)
tc.SetNoDelay(true)
s.conn = tc
s.io = &PacketIo{}
s.io.r = bufio.NewReaderSize(s.conn, 16*1024)
s.io.w = tc
data, err := s.io.readPacket()
if err != nil {
return err
}
if data[0] == ERR_HEADER {
return errors.New("error packet")
}
if data[0] < MinProtocolVersion {
return fmt.Errorf("version is too lower, current:%d", data[0])
}
pos := 1 + bytes.IndexByte(data[1:], 0x00) + 1
connId := uint32(binary.LittleEndian.Uint32(data[pos : pos+4]))
pos += 4
salt := data[pos : pos+8]
pos += 8 + 1
capability := uint32(binary.LittleEndian.Uint16(data[pos : pos+2]))
pos += 2
var status uint16
var pluginName string
if len(data) > pos {
//skip charset
pos++
status = binary.LittleEndian.Uint16(data[pos : pos+2])
pos += 2
capability = uint32(binary.LittleEndian.Uint16(data[pos:pos+2]))<<16 | capability
pos += 2
pos += 10 + 1
salt = append(salt, data[pos:pos+12]...)
pos += 13
if end := bytes.IndexByte(data[pos:], 0x00); end != -1 {
pluginName = string(data[pos : pos+end])
} else {
pluginName = string(data[pos:])
}
}
fmt.Printf("conn_id:%v, status:%d, plugin:%v\n", connId, status, pluginName)
//write
capability = 500357
length := 4 + 4 + 1 + 23
length += len(s.Cfg.User) + 1
pass := []byte(s.Cfg.Pass)
auth := calPassword(salt[:20], pass)
length += 1 + len(auth)
data = make([]byte, length+4)
data[4] = byte(capability)
data[5] = byte(capability >> 8)
data[6] = byte(capability >> 16)
data[7] = byte(capability >> 24)
//utf8
data[12] = byte(33)
pos = 13 + 23
if len(s.Cfg.User) > 0 {
pos += copy(data[pos:], s.Cfg.User)
}
pos++
data[pos] = byte(len(auth))
pos += 1 + copy(data[pos+1:], auth)
err = s.io.writePacket(data)
if err != nil {
return fmt.Errorf("write auth packet error")
}
pk, err := s.io.readPacket()
if err != nil {
return err
}
if pk[0] == OK_HEADER {
fmt.Println("handshake ok ")
return nil
} else if pk[0] == ERR_HEADER {
s.io.HandleError(pk)
return errors.New("handshake error ")
}
return nil
}
func (s *Server) writeDumpCommand() {
s.io.seq = 0
data := make([]byte, 4+1+4+2+4+len(s.Cfg.LogFile))
pos := 4
data[pos] = 18 //dump binlog
pos++
binary.LittleEndian.PutUint32(data[pos:], uint32(s.Cfg.Position))
pos += 4
//dump command flag
binary.LittleEndian.PutUint16(data[pos:], 0)
pos += 2
binary.LittleEndian.PutUint32(data[pos:], uint32(s.Cfg.ServerId))
pos += 4
copy(data[pos:], s.Cfg.LogFile)
s.io.writePacket(data)
//ok
res, _ := s.io.readPacket()
if res[0] == OK_HEADER {
fmt.Println("send dump command return ok.")
} else {
s.io.HandleError(res)
}
}
func (s *Server) register() {
s.io.seq = 0
hostname, _ := os.Hostname()
data := make([]byte, 4+1+4+1+len(hostname)+1+len(s.Cfg.User)+1+len(s.Cfg.Pass)+2+4+4)
pos := 4
data[pos] = 21 //register slave command
pos++
binary.LittleEndian.PutUint32(data[pos:], uint32(s.Cfg.ServerId))
pos += 4
data[pos] = uint8(len(hostname))
pos++
n := copy(data[pos:], hostname)
pos += n
data[pos] = uint8(len(s.Cfg.User))
pos++
n = copy(data[pos:], s.Cfg.User)
pos += n
data[pos] = uint8(len(s.Cfg.Pass))
pos++
n = copy(data[pos:], s.Cfg.Pass)
pos += n
binary.LittleEndian.PutUint16(data[pos:], uint16(s.Cfg.Port))
pos += 2
binary.LittleEndian.PutUint32(data[pos:], 0)
pos += 4
//master id = 0
binary.LittleEndian.PutUint32(data[pos:], 0)
s.io.writePacket(data)
//ok
res, _ := s.io.readPacket()
if res[0] == OK_HEADER {
fmt.Println("register success.")
s.registerSucc = true
} else {
s.io.HandleError(data)
}
}
func (s *Server) writeCommand(command byte) {
s.io.seq = 0
_ = s.io.writePacket([]byte{
0x01, //1 byte long
0x00,
0x00,
0x00, //seq
command,
})
}
func (s *Server) query(q string) error {
s.io.seq = 0
length := len(q) + 1
data := make([]byte, length+4)
data[4] = 3
copy(data[5:], q)
return s.io.writePacket(data)
}
func (s *Server) Quit() {
//quit
s.writeCommand(byte(1))
//maybe only close
if err := s.conn.Close(); nil != err {
fmt.Printf("error in close :%v\n", err)
}
}
type PacketIo struct {
r *bufio.Reader
w io.Writer
seq uint8
}
func (p *PacketIo) readPacket() ([]byte, error) {
//to read header
header := []byte{0, 0, 0, 0}
if _, err := io.ReadFull(p.r, header); err != nil {
return nil, err
}
length := int(uint32(header[0]) | uint32(header[1])<<8 | uint32(header[2])<<16)
if length == 0 {
p.seq++
return []byte{}, nil
}
if length == 1 {
return nil, fmt.Errorf("invalid payload")
}
seq := uint8(header[3])
if p.seq != seq {
return nil, fmt.Errorf("invalid seq %d", seq)
}
p.seq++
data := make([]byte, length)
if _, err := io.ReadFull(p.r, data); err != nil {
return nil, err
} else {
if length < MaxPayloadLength {
return data, nil
}
var buf []byte
buf, err = p.readPacket()
if err != nil {
return nil, err
}
if len(buf) == 0 {
return data, nil
} else {
return append(data, buf...), nil
}
}
}
func (p *PacketIo) writePacket(data []byte) error {
length := len(data) - 4
if length >= MaxPayloadLength {
data[0] = 0xff
data[1] = 0xff
data[2] = 0xff
data[3] = p.seq
if n, err := p.w.Write(data[:4+MaxPayloadLength]); err != nil {
return fmt.Errorf("write find error")
} else if n != 4+MaxPayloadLength {
return fmt.Errorf("not equal max pay load length")
} else {
p.seq ++
length -= MaxPayloadLength
data = data[MaxPayloadLength:]
}
}
data[0] = byte(length)
data[1] = byte(length >> 8)
data[2] = byte(length >> 16)
data[3] = p.seq
if n, err := p.w.Write(data); err != nil {
return errors.New("write find error")
} else if n != len(data) {
return errors.New("not equal length")
} else {
p.seq ++
return nil
}
}
func calPassword(scramble, password []byte) []byte {
crypt := sha1.New()
crypt.Write(password)
stage1 := crypt.Sum(nil)
crypt.Reset()
crypt.Write(stage1)
hash := crypt.Sum(nil)
crypt.Reset()
crypt.Write(scramble)
crypt.Write(hash)
scramble = crypt.Sum(nil)
for i := range scramble {
scramble[i] ^= stage1[i]
}
return scramble
}
func (p *PacketIo) HandleError(data []byte) {
pos := 1
code := binary.LittleEndian.Uint16(data[pos:])
pos += 2
pos++
state := string(data[pos : pos+5])
pos += 5
msg := string(data[pos:])
fmt.Printf("code:%d, state:%s, msg:%s\n", code, state, msg)
}- main
package main
import (
"flag"
"fmt"
"github.com/igoso/gbinlog/app"
"os"
"os/signal"
"runtime"
"syscall"
)
var myHost = flag.String("host", "127.0.0.1", "MySQL replication host")
var myPort = flag.Int("port", 3306, "MySQL replication port")
var myUser = flag.String("user", "root", "MySQL replication user")
var myPass = flag.String("pass", "****", "MySQL replication pass")
var serverId = flag.Int("server_id", 1111, "MySQL replication server id")
func main() {
sc := make(chan os.Signal, 1)
signal.Notify(sc,
os.Kill,
os.Interrupt,
syscall.SIGHUP,
syscall.SIGQUIT,
syscall.SIGINT,
syscall.SIGTERM,
)
runtime.GOMAXPROCS(runtime.NumCPU()/4 + 1)
flag.Parse()
cfg := &app.Config{
*myHost,
*myPort,
*myUser,
*myPass,
*serverId,
"mysql-bin.000032",
3070,
}
srv := &app.Server{Cfg: cfg}
go srv.Run()
select {
case n := <-sc:
srv.Quit()
fmt.Printf("receive signal %v, closing", n)
}
}- go.mod
只有一個依賴
module github.com/igoso/gbinlog
go 1.15
require (
github.com/siddontang/go-mysql v1.1.0
)其他
注意如果使用 binlog dump 連接執行 quit 命令，在 MySQL 端查看，不會立刻消失，處在
close_wait狀態。當下次再次有新的連接過來後，纔會消失並建立新的。中間可能有1236:相同對的server_id存在的錯誤，但不影響使用
本來在嘗試自己解析 binlog，如果實際做的話工作量還是很大的，以爲有很多種類的 binlog event 需要處理。後來在 siddentang 的 go-mysql 包中發現已經有實現了一個很好用的binlogSyncer，其中就有完善的解析方法。包括他實現的binlogSyncer也非常方便，感興趣的可以參考如下。
package main
import (
"context"
"flag"
"fmt"
"os"
"github.com/pingcap/errors"
"github.com/siddontang/go-mysql/mysql"
"github.com/siddontang/go-mysql/replication"
)
var host = flag.String("host", "127.0.0.1", "MySQL host")
var port = flag.Int("port", 3306, "MySQL port")
var user = flag.String("user", "root", "MySQL user, must have replication privilege")
var password = flag.String("password", "****", "MySQL password")
var flavor = flag.String("flavor", "mysql", "Flavor: mysql or mariadb")
var file = flag.String("file", "mysql-bin.000032", "Binlog filename")
var pos = flag.Int("pos", 3070, "Binlog position")
var semiSync = flag.Bool("semisync", false, "Support semi sync")
var backupPath = flag.String("backup_path", "", "backup path to store binlog files")
var rawMode = flag.Bool("raw", false, "Use raw mode")
func main() {
flag.Parse()
cfg := replication.BinlogSyncerConfig{
ServerID: 101,
Flavor: *flavor,
Host: *host,
Port: uint16(*port),
User: *user,
Password: *password,
RawModeEnabled: *rawMode,
SemiSyncEnabled: *semiSync,
UseDecimal: true,
}
b := replication.NewBinlogSyncer(cfg)
pos := mysql.Position{Name: *file, Pos: uint32(*pos)}
if len(*backupPath) > 0 {
// Backup will always use RawMode.
err := b.StartBackup(*backupPath, pos, 0)
if err != nil {
fmt.Printf("Start backup error: %v\n", errors.ErrorStack(err))
return
}
} else {
s, err := b.StartSync(pos)
if err != nil {
fmt.Printf("Start sync error: %v\n", errors.ErrorStack(err))
return
}
for {
e, err := s.GetEvent(context.Background())
if err != nil {
// Try to output all left events
events := s.DumpEvents()
for _, e := range events {
e.Dump(os.Stdout)
}
fmt.Printf("Get event error: %v\n", errors.ErrorStack(err))
return
}
e.Dump(os.Stdout)
}
}
}以上就是本期的全部內容。
轉自：
jianshu.com/p/ab2450a19bdd
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/UHkEC72g60cUHYFiMQ2TBQ