go-mysql

A pure Go library to handle MySQL network protocol and replication as used by MySQL and MariaDB.

Platform Support

As a pure Go library, this project follows Go's minimum requirements.

This library has been tested or deployed on the following operating systems and architectures:

Other platforms supported by Go may also work, but they have not been verified. Feel free to report your test results.

This library is not compatible with TinyGo.

Changelog

This library uses Changelog.

Content

• Replication - Process events from a binlog stream.
• Incremental dumping - Sync from MySQL to Redis, Elasticsearch, etc.
• Client - Simple MySQL client.
• Fake server - server side of the MySQL protocol, as library.
• database/sql like driver - An alternative database/sql driver for MySQL.
• Logging - Custom logging options.
• Migration - Information for how to migrate if you used the old location of this project.

Examples

The cmd directory contains example applications that can be build by running make build in the root of the project. The resulting binaries will be places in bin/.

• go-binlogparser: parses a binlog file at a given offset
• go-canal: streams binlog events from a server to canal
• go-mysqlbinlog: streams binlog events
• go-mysqldump: like mysqldump, but in Go
• go-mysqlserver: fake MySQL server

Replication

Replication package handles MySQL replication protocol like python-mysql-replication.

You can use it as a MySQL replica to sync binlog from master then do something, like updating cache, etc...

Example

import (
    "github.com/go-mysql-org/go-mysql/replication"
    "os"
)
// Create a binlog syncer with a unique server id, the server id must be different from other MySQL's.
// flavor is mysql or mariadb
cfg := replication.BinlogSyncerConfig {
    ServerID: 100,
    Flavor:   "mysql",
    Host:     "127.0.0.1",
    Port:     3306,
    User:     "root",
    Password: "",
}
syncer := replication.NewBinlogSyncer(cfg)

// Start sync with specified binlog file and position
streamer, _ := syncer.StartSync(mysql.Position{binlogFile, binlogPos})

// or you can start a gtid replication like
// gtidSet, _ := mysql.ParseGTIDSet(mysql.MySQLFlavor, "de278ad0-2106-11e4-9f8e-6edd0ca20947:1-2")
// streamer, _ := syncer.StartSyncGTID(gtidSet)
// the mysql GTID set is like this "de278ad0-2106-11e4-9f8e-6edd0ca20947:1-2" and uses mysql.MySQLFlavor
// the mariadb GTID set is like this "0-1-100" and uses mysql.MariaDBFlavor

for {
    ev, _ := streamer.GetEvent(context.Background())
    // Dump event
    ev.Dump(os.Stdout)
}

// or we can use a timeout context
for {
    ctx, cancel := context.WithTimeout(context.Background(), 2*time.Second)
    ev, err := streamer.GetEvent(ctx)
    cancel()

    if err == context.DeadlineExceeded {
        // meet timeout
        continue
    }

    ev.Dump(os.Stdout)
}

The output looks:

=== RotateEvent ===
Date: 1970-01-01 08:00:00
Log position: 0
Event size: 43
Position: 4
Next log name: mysql.000002

=== FormatDescriptionEvent ===
Date: 2014-12-18 16:36:09
Log position: 120
Event size: 116
Version: 4
Server version: 5.6.19-log
Create date: 2014-12-18 16:36:09

=== QueryEvent ===
Date: 2014-12-18 16:38:24
Log position: 259
Event size: 139
Salve proxy ID: 1
Execution time: 0
Error code: 0
Schema: test
Query: DROP TABLE IF EXISTS `test_replication` /* generated by server */

MariaDB 11.4+ compatibility

MariaDB 11.4+ introduced an optimization where events written through transaction or statement cache have LogPos=0 so they can be copied directly to the binlog without computing the real end position. This optimization improves performance but makes position tracking unreliable for replication clients that need to track LogPos of events inside transactions.

To address this, a FillZeroLogPos configuration option is available:

cfg := replication.BinlogSyncerConfig {
    ServerID: 100,
    Flavor:   "mariadb",
    Host:     "127.0.0.1",
    Port:     3306,
    User:     "root",
    Password: "",
    // Enable dynamic LogPos calculation for MariaDB 11.4+
    FillZeroLogPos: true,
}

Behavior: - When FillZeroLogPos is true and flavor is mariadb, the library automatically: - Adds BINLOG_SEND_ANNOTATE_ROWS_EVENT flag to binlog dump commands. This ensures correct position tracking by making the server send ANNOTATE_ROWS_EVENT events which are needed for accurate position calculation. - Calculates LogPos dynamically for events with LogPos=0 that are not artificial. - Only works with MariaDB flavor; has no effect with MySQL. - Should be set to true if tracking of LogPos inside transactions is required.

Canal

Canal is a package that can sync your MySQL into everywhere, like Redis, Elasticsearch.

First, canal will dump your MySQL data then sync changed data using binlog incrementally.

You must use ROW format for binlog, full binlog row image is preferred, because we may meet some errors when primary key changed in update for minimal or noblob row image.

A simple example:

package main

import (
    "github.com/go-mysql-org/go-mysql/canal"
)

type MyEventHandler struct {
    canal.DummyEventHandler
}

func (h *MyEventHandler) OnRow(e *canal.RowsEvent) error {
    log.Infof("%s %v\n", e.Action, e.Rows)
    return nil
}

func (h *MyEventHandler) String() string {
    return "MyEventHandler"
}

func main() {
    cfg := canal.NewDefaultConfig()
    cfg.Addr = "127.0.0.1:3306"
    cfg.User = "root"
    // We only care table canal_test in test db
    cfg.Dump.TableDB = "test"
    cfg.Dump.Tables = []string{"canal_test"}

    c, err := canal.NewCanal(cfg)
    if err != nil {
        log.Fatal(err)
    }

    // Register a handler to handle RowsEvent
    c.SetEventHandler(&MyEventHandler{})

    // Start canal
    c.Run()
}

You can see go-mysql-elasticsearch for how to sync MySQL data into Elasticsearch.

Client

Client package supports a simple MySQL connection driver which you can use it to communicate with MySQL server.

For an example see example_client_test.go. You can run this testable example with go test -v ./client -run Example.

Tested MySQL versions for the client include: - 5.5.x - 5.6.x - 5.7.x - 8.0.x

Example for SELECT streaming (v1.1.1)

You can use also streaming for large SELECT responses. The callback function will be called for every result row without storing the whole resultset in memory. result.Fields will be filled before the first callback call.

// ...
var result mysql.Result
err := conn.ExecuteSelectStreaming(`select id, name from table LIMIT 100500`, &result, func(row []mysql.FieldValue) error {
    for idx, val := range row {
        field := result.Fields[idx]
        // You must not save FieldValue.AsString() value after this callback is done.
        // Copy it if you need.
        // ...
    }
    return nil
}, nil)

// ...

Example for connection pool (v1.3.0)

import (
    "github.com/go-mysql-org/go-mysql/client"
)

pool := client.NewPool(log.Debugf, 100, 400, 5, "127.0.0.1:3306", `root`, ``, `test`)
// ...
conn, _ := pool.GetConn(ctx)
defer pool.PutConn(conn)

conn.Execute() / conn.Begin() / etc...

Server

Server package supplies a framework to implement a simple MySQL server which can handle the packets from the MySQL client. You can use it to build your own MySQL proxy. The server connection is compatible with MySQL 5.5, 5.6, 5.7, and 8.0 versions, so that most MySQL clients should be able to connect to the Server without modifications.

Example

Minimalistic MySQL server implementation:

package main

import (
    "log"
    "net"

    "github.com/go-mysql-org/go-mysql/server"
)

func main() {
    // Listen for connections on localhost port 4000
    l, err := net.Listen("tcp", "127.0.0.1:4000")
    if err != nil {
        log.Fatal(err)
    }

    // Accept a new connection once
    c, err := l.Accept()
    if err != nil {
        log.Fatal(err)
    }

    // Create a connection with user root and an empty password.
    // You can use your own handler to handle command here.
    conn, err := server.NewConn(c, "root", "", server.EmptyHandler{})
    if err != nil {
        log.Fatal(err)
    }

    // as long as the client keeps sending commands, keep handling them
    for {
        if err := conn.HandleCommand(); err != nil {
            log.Fatal(err)
        }
    }
}

Another shell

$ mysql -h127.0.0.1 -P4000 -uroot
Your MySQL connection id is 10001
Server version: 5.7.0

MySQL [(none)]>
// Since EmptyHandler implements no commands, it will throw an error on any query that you will send

NewConn() will use default server configurations: 1. automatically generate default server certificates and enable TLS/SSL support. 2. support three mainstream authentication methods 'mysql_native_password', 'caching_sha2_password', and 'sha256_password' and use 'mysql_native_password' as default. 3. use an in-memory user credential provider to store user and password.

To customize server configurations, use NewServer() and create connection via NewCustomizedConn().

Driver

Driver is the package that you can use go-mysql with go database/sql like other drivers. A simple example:

package main

import (
    "database/sql"

    _ "github.com/go-mysql-org/go-mysql/driver"
)

func main() {
    // dsn format: "user:password@addr?dbname"
    dsn := "root@127.0.0.1:3306?test"
    db, _ := sql.Open("mysql", dsn)
    db.Close()
}

Structured Connector

If you prefer a structured configuration over a DSN string, you can use database/sql.OpenDB with driver.Connector:

package main

import (
    "database/sql"
    "net/url"

    "github.com/go-mysql-org/go-mysql/driver"
)

func main() {
    connector := driver.Connector{
        Addr: "127.0.0.1:3306",
        User: "root",
        DB:   "test",
        Params: url.Values{
            // same option keys as the standard DSN form
            "timeout": []string{"10s"},
        },
    }

    db := sql.OpenDB(connector)
    db.Close()
}

Driver Options

Configuration options can be provided by the standard DSN (Data Source Name).

[user[:password]@]addr[/db[?param=X]]

collation

Set a collation during the Auth handshake.

compress

Enable compression between the client and the server. Valid values are 'zstd','zlib','uncompressed'.

readTimeout

I/O read timeout. The time unit is specified in the argument value using golang's ParseDuration format.

0 means no timeout.

ssl or tls

Enable TLS between client and server. Valid values are true,false,skip-verify or custom. When using custom, the connection will use the TLS configuration set by SetCustomTLSConfig matching the host.

timeout

Timeout is the maximum amount of time a dial will wait for a connect to complete. The time unit is specified in the argument value using golang's ParseDuration format.

0 means no timeout.