proxysql proxy 集成golang-mysqlserver

类似pg 集成mysql_fdw 使用proxysql 集成基于dolthub/go-mysql-server 开发的golang server

环境准备

  • docker-compose 文件
version: "3"
services:
    proxysql: 
     image: proxysql/proxysql:2.0.15
     ports: 
     - "6033:6033"
     - "6032:6032"
     volumes:
     - "./proxysql.cnf:/etc/proxysql.cnf"
    mysqlserever:
      build: ./
      ports: 
      - "3307:3307"
    mysql2:
     image: mysql:5.6
     command: --character-set-server=utf8
     ports:
     - "3308:3306"
     environment:
     - MYSQL_ROOT_PASSWORD=dalong
     - MYSQL_USER=boss
     - MYSQL_DATABASE=boss
     - MYSQL_PASSWORD=dalong
  • golang mysql server 代码
    go.mod
 
module demoapp
​
go 1.15
​
require github.com/dolthub/go-mysql-server v0.6.1-0.20201123210644-8545fbee5245 // indirect

main.go

package main
​
import (
    sqle "github.com/dolthub/go-mysql-server"
    "github.com/dolthub/go-mysql-server/auth"
    "github.com/dolthub/go-mysql-server/memory"
    "github.com/dolthub/go-mysql-server/server"
    "github.com/dolthub/go-mysql-server/sql"
    "github.com/dolthub/go-mysql-server/sql/information_schema"
)
​
func main() {
    driver := sqle.NewDefault()
    driver.Analyzer.Debug = true
    driver.AddDatabase(createTestDatabase())
    config := server.Config{
        Protocol: "tcp",
        Address:  "0.0.0.0:3307",
        Auth:     auth.NewNativeSingle("root", "dalong", auth.AllPermissions),
    }
​
    s, err := server.NewDefaultServer(config, driver)
    if err != nil {
        panic(err)
    }
    // proxysql 需要information_schema
    driver.AddDatabase(information_schema.NewInformationSchemaDatabase(driver.Catalog))
    s.Start()
}
​
func createTestDatabase() *memory.Database {
    const (
        dbName    = "test"
        tableName = "mytable"
    )
​
    db := memory.NewDatabase(dbName)
    table := memory.NewTable(tableName, sql.Schema{
        {Name: "name", Type: sql.Text, Nullable: false, Source: tableName},
        {Name: "email", Type: sql.Text, Nullable: false, Source: tableName},
    })
​
    db.AddTable(tableName, table)
    ctx := sql.NewEmptyContext()
​
    rows := []sql.Row{
        sql.NewRow("John Doe", "john@doe.com"),
        sql.NewRow("John Doe", "johnalt@doe.com"),
        sql.NewRow("Jane Doe", "jane@doe.com"),
        sql.NewRow("Evil Bob", "evilbob@gmail.com"),
    }
​
    for _, row := range rows {
        table.Insert(ctx, row)
    }
​
    return db
}

Dockerfile

FROM golang:1.15-alpine AS build-env
WORKDIR /go/src/app
ENV  GO111MODULE=on
ENV  GOPROXY=https://goproxy.cn
COPY . .
RUN apk update && apk add git \
    && go build
​
FROM alpine:latest
RUN apk update && apk add ca-certificates  && rm -rf /var/cache/apk/*
COPY --from=build-env /go/src/app/demoapp /usr/bin/demoapp
ENTRYPOINT [ "/usr/bin/demoapp" ]
  • proxysql 配置
datadir="/var/lib/proxysql"
admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    stacksize=1048576
    server_version="5.7.9"
    connect_timeout_server=3000
    monitor_username="root"
    monitor_password="dalong"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}
 mysql_servers =
 (
  {
    address="mysqlserever"
    port=3307
    hostgroup=1
    max_connections=200
  },
  {
    address="mysql2"
    port=3306
    hostgroup=2
    max_connections=200
  }
 )
mysql_users:
 (
  {
    username = "root"
    password = "dalong"
    default_hostgroup = 1
    max_connections=1000
    active = 1
  },
  {
    username = "boss"
    password = "dalong"
    default_hostgroup = 2
    max_connections=1000
    active = 1
  }
 )

运行效果

  • 构建
docker-compose build
  • 运行
docker-compose up -d
  • 访问
    通过 127.0.0.1 6033 root dalong 就可以访问自己编写的mysql server 了
    效果

     

     

     

     

一些问题

  • information_schema
    proxysql 需要information_schema，所以自己编写的mysql server 需要注册information_schema db
  • lost connect
    信息
 
2013 - Lost connection to MySQL server during query

 

 


应该是proxysql 与Vitess 兼容的问题（Vitess 对于sql 实现的完整性）

参考资料

https://github.com/dolthub/go-mysql-server
https://github.com/sysown/proxysql/wiki
https://github.com/rongfengliang/go-mysql-server-proxysql

posted on 2020-11-29 11:11  荣锋亮  阅读(510)  评论(0)  编辑  收藏  举报

导航