A high-performance MySQL proxy

Overview

kingshard 中文主页

Build Status

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/write sqls. Now it supports basic SQL statements (select, insert, update, replace, delete). The most important feature is the sharding function. Kingshard aims to simplify the sharding solution of MySQL. The Performance of kingshard is about 80% compared to connecting to MySQL directly.

Feature

1. Basic Function

  • Splits reads and writes
  • Client's ip ACL control.
  • Transaction in single node.
  • Support limiting the max count of connections to MySQL database.
  • Support setting the backend database online or offline dynamically.
  • Supports prepared statement: COM_STMT_PREPARE, COM_STMT_EXECUTE, etc.
  • Support multi slaves, and load balancing between slaves.
  • Support reading master database forcely.
  • Support last_insert_id().
  • Support MySQL backends HA.
  • Support set the charset of proxy.
  • Support SQL blacklist.
  • Support dynamically changing the config value of kingshard.

2. Sharding Function

  • Support hash,range and date sharding across multiple nodes.
  • Support sending sql to the specified node.
  • Support most commonly used functions, such as max, min, count, sum, and also support join, limit, order by,group by.

Install

  1. Install Go
  2. git clone https://github.com/flike/kingshard.git $GOPATH/src/github.com/flike/kingshard
  3. cd $GOPATH/src/github.com/flike/kingshard
  4. source ./dev.sh
  5. make
  6. set the config file (etc/ks.yaml)
  7. run kingshard (./bin/kingshard -config=etc/ks.yaml)

Details of kingshard

1.How to use kingshard building a MySQL cluster

License

kingshard is under the Apache 2.0 license. See the LICENSE directory for details.

Issues
  • 反馈kingshard的limit问题

    反馈kingshard的limit问题

    我最近在mysql的slow log 发现很多这样的查询

    Query_time: 1.199735 Lock_time: 0.000064 Rows_sent: 7777 Rows_examined: 15588 SET timestamp=1459002386; select item_id from t_user_like_0018 where u_id = 448237 and effective = 1 order by id desc;

    而我们在代码里的查询语句是这样的: select item_id from t_user_like_0018 where u_id = 448237 and effective = 1 order by id desc limit 0,20;

    很显然是kingshard的limit是自己处理的,这样的确是分表的情况下一种无奈的做法,但是就导致load了7777条结果

    因为我们现在是通过u_id分表的,我不知道这种做法是否可行,在不垮节点的情况下(也就是查询条件里面带有分表字段的情况下),limit避免在kingshard这边完成

    非常感谢!

    question todo fixed 
    opened by phantasy1234 21
  • 需要ignore掉broken pipe signal,不然会导致kingshard exit

    需要ignore掉broken pipe signal,不然会导致kingshard exit

    可以将main.go对signal的处理改成如下:

    sc := make(chan os.Signal, 1) signal.Notify(sc, syscall.SIGINT, syscall.SIGTERM, syscall.SIGQUIT, syscall.SIGPIPE)

    go func() {
        for {
            select {
            case sig := <-sc:
                if sig == syscall.SIGINT || sig == syscall.SIGTERM || sig == syscall.SIGQUIT {
                    golog.Info("main", "main", "Got signal", 0, "signal", sig)
                    golog.GlobalSysLogger.Close()
                    golog.GlobalSqlLogger.Close()
                    svr.Close()
                    monitor_svr.Close()
                    return
                }
    
                if sig == syscall.SIGPIPE {
                    golog.Info("main", "main", "Ignore broken pipe signal", 0)
                }
            }
        }
    }()
    
    fixed 
    opened by jefjin 16
  • shared rule by date configure add time_format param

    shared rule by date configure add time_format param

    一般来说,时间切分的自动可能不是时间类型,只是其中某部分包含时间,所以应该有参数可以指定字段时间格式化类型,所以在date_year、date_month、date_day三种类型中添加了一个配置参数,time_format,可以指定如何格式话时间,默认YYYY-mm-dd HH:MM:SS,Y代表年,m代表月,d代表天,H代表小时,M代表分钟,S代表秒,如果格式化参数中不是这几个字符,则跳过一个字符,同时年必须是4位,其他必须是两位。

    opened by snower 16
  • 兼容 ORM sql 语句格式

    兼容 ORM sql 语句格式

    发现有问题额 clone的最新的代码 kingshard的log日志 select search_hash.id, search_hash.info_hash, search_hash.category, search_hash.data_hash, search_hash.name, search_hash.extension, search_hash.classified, search_hash.source_ip, search_hash.tagged, search_hash.length, search_hash.create_time, search_hash.last_seen, search_hash.requests, search_hash.comment, search_hash.creator from search_hash_0003 where search_hash.id in (95982) 2016/05/13 00:45:58 - ERROR - conn_select.go:[125] - [ClientConn] "handleSelect" "ERROR 1054 (42S22): Unknown column 'search_hash.id' in 'field list'" "" conn_id=10144

    原始sql应该是这样的 select search_hash.id, search_hash.info_hash, search_hash.category, search_hash.data_hash, search_hash.name, search_hash.extension, search_hash.classified, search_hash.source_ip, search_hash.tagged, search_hash.length, search_hash.create_time, search_hash.last_seen, search_hash.requests, search_hash.comment, search_hash.creator from search_hash where search_hash.id in (95982)

    bug todo fixed 
    opened by nqzhang 15
  • SHOW FULL FIELDS / COLUMNS doesn't work

    SHOW FULL FIELDS / COLUMNS doesn't work

    Hi,

    SHOW FULL FIELDS and SHOW FULL COLUMNS doesn't work for me, i tried with and without USE DATABASE statement before. Tried different MySQL clients and still the same problem. I am using Yii Framework which needs those commands to cache table informations.

    There was already another Issue about this problem but it didn't solve it and it is already closed. #180

    regards

    supported todo 
    opened by sysmate 13
  • JDBC executeUpdate返回的值疑问

    JDBC executeUpdate返回的值疑问

    JDBC: 5.1.35 mysql: 5.6.22

    sql: update TABLE set a=xx where b=xx;

    直连mysql无论是否有字段更新都是返回1,不存在返回0 通过ks有字段更新返回1,没有更新和不存在的话返回0

    请教下这个差异是JDBC的使用姿势不正确吗?还是正常现象?

    opened by unext-ops 12
  • 获取数据错误,对mysqlnd有要求吗

    获取数据错误,对mysqlnd有要求吗

    环境如下: 系统版本 CentOS release 6.9 (Final) 2.6.32-696.3.1.el6.x86_64 php版本 PHP 5.6.9 php-mysql驱动版本 mysqlnd 5.0.11-dev - 20120503 kingshard版本 最新 go版本 go1.11.1

    kingshard版本截图如下: image

    由于原来kingshard9月26日提交的版本出现连接无反应的问题问题,对kingshard升级至最新版。 升级后,连接问题得以解决,但出现了程序获取数据乱码现象

    程序通过kingshard访问,获取数据乱码,截图如下: image

    程序直接连接mysql访问,获取数据正常,截图如下: image

    请问,这是怎么回事呢?

    opened by amilytan 10
  • 按时间分表报错

    按时间分表报错

    中间件运行多日后,会突然开始报错(如下),开始报错后分表查询功能失效,insert 功能正常。 重启中间件后报错消失,功能恢复正常

    报错信息:

    2017/10/12 10:17:55 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777262
    2017/10/12 10:17:56 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:17:56 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777263
    2017/10/12 10:17:57 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:17:57 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777264
    2017/10/12 10:17:57 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:17:57 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777265
    2017/10/12 10:17:57 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:17:57 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777266
    2017/10/12 10:17:59 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:17:59 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777267
    2017/10/12 10:18:00 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:18:00 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777268
    2017/10/12 10:18:01 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:18:01 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777269
    2017/10/12 10:18:02 - ERROR - router.go:[414] - [Route] "BuildUpdatePlan" "plan have no criteria" "" conn_id=0
    2017/10/12 10:18:02 - ERROR - conn.go:[280] - [server] "Run" "plan have no criteria" "" conn_id=11777271
    

    配置信息

    addr: 127.0.0.1
    user: user
    password: passwd
    
    log_level : error
    log_sql : off
    slow_log_time : 100
    log_path: /data/log/ks/
    
    nodes:
    -
        name: node1
        user: nodeuser
        password: nodepasswd
        master : 127.0.0.1:3306
    
    schema:
        nodes: [node1]
        default: node1
        shard:
        -
            db: db1
            table: p_records
            key: created_at
            nodes: [node1]
            type: date_month
            date_range: [201705-202706]
        -
            db: db1
            table: r_records
            key: created_at
            nodes: [node1]
            type: date_month
            date_range: [201705-202706]
    
    bug fixed 
    opened by xiaocang 10
  • 需要增加检测功能

    需要增加检测功能

    搭建kingshard集群,在3台kingshard机器前面挡一层LVS,但是LVS本身有机制会去定时探测比如9696端口是否正常,这是的协议肯定不是sql的这种协议,会导致一堆的"readHandshakeResponse" "connection was bad",群主是否考虑在kingshard上面再开一个监听端口,用来给外围设备检测用的?

    supported todo 
    opened by jefjin 9
  • [特性讨论] - 多用户系统 (Multi-User Support)

    [特性讨论] - 多用户系统 (Multi-User Support)

    特性讨论


    背景描述

    为了实现多租户系统,并且为了在Mysql的物理连接中进行资源区分,现在我正在实现多用户特性。

    配置文件参考

    addr : 0.0.0.0:9696
    users:
    - 
     user: root
     password: root
    
    -
     user: user_1
     password: pass_1
    
    -
     user: user_2
     password: pass_2
    
    -
     user: user_3
     password: pass_3
    
    -
     user: user_4
     password: pass_4
    
    log_level : debug
    #log_sql: off
    proxy_charset: utf8
    
    nodes :
    -
        name : node1
        max_conns_limit : 32
        users:
        -
          user: root
          password: root
        master : 10.211.55.3:3306
    
    schema :
        db : 
        nodes: [node1]
        default: node1
        shard:
        -
    
    
    实现方案
    //新增一个UserPool来包裹backend.DB来实现用户级连接池
    type UserPool struct {
        sync.RWMutex
        pools map[string]*DB
    }
    
    

    ps1: 以上方案已经初步实现。

    ps2: 现在向大家征集需求,以及更好的实现方案。

    opened by imiskolee 9
  • 发生过多次,ks进程正常,mysql正常,但ks已经无法正常工作

    发生过多次,ks进程正常,mysql正常,但ks已经无法正常工作

    怀疑是跟连接池处理有关。

    2015/12/08 06:27:17 - ERROR - conn.go:[286] - [server] "Run" "connection was bad" "" conn_id=10004 2015/12/08 06:27:17 - ERROR - conn_query.go:[55] - [server] "preHandleShard" "connection was bad" "hasHandled=false" conn_id=0 2015/12/08 06:27:17 - ERROR - conn.go:[286] - [server] "Run" "connection was bad" "" conn_id=10004 2015/12/08 06:27:18 - ERROR - conn_query.go:[110] - [server] "getBackendConn" "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" "" conn_id=0 2015/12/08 06:27:18 - ERROR - conn_query.go:[55] - [server] "preHandleShard" "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" "hasHandled=false" conn_id=0 2015/12/08 06:27:18 - ERROR - conn.go:[286] - [server] "Run" "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" "" conn_id=10004

    bug fixed 
    opened by mia0x75 9
  • timeout设置问题.

    timeout设置问题.

    当interactive_timeout和wait_timeout有任意一个值小于20时,kingshard的sys.log里会每隔几秒产生一条报错记录: ERROR - node.go:[104] - [Node] "checkMaster" "Ping" "db.Addr=127.0.0.1:3306|error=connection was bad" conn_id=0 但不影响连接mysql,也不影响执行sql语句.

    只有两个值同时大于20时,才不会报这个错. 有哪个有经验的大哥能回答一下,在使用mysql proxy时,这两个值设多少合适? 谢谢

    opened by smallevil 0
  • 请问节点和子表的对应关系可以配置吗

    请问节点和子表的对应关系可以配置吗

    每个节点配置子表数量和与之对应和子表的映射关系,而非这种固定node1 :table_0000,table_0001,table_0002,table_0003 ,而是再保证子表后缀顺序连续(0-9999中任何一个数字都不缺少)的前提下,通过约束子表和节点关系(node1 :table_9999,table_8888,table_0001,table_****),通过配置来确定表和库的关系,这样后期调整节点和子表的关系,做完数据迁移,然后调整配置就可以了,并且也可以动态扩展新增子表

    opened by liuxzhi 0
Releases(1.6)
Owner
Fei Chen
Familiar with distributed system architecture,MySQL,Redis,Rocksdb,Kafka,Raft/Paxos etc
Fei Chen
A proxy is database proxy that de-identifies PII for PostgresDB and MySQL

Surf Surf is a database proxy that is capable of de-identifying PII and anonymizing sentive data fields. Supported databases include Postgres, MySQL,

null 1 Dec 14, 2021
mysql to mysql 轻量级多线程的库表数据同步

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

null 13 Jun 14, 2022
Gaea is a mysql proxy, it's developed by xiaomi b2c-dev team.

简介 Gaea是小米中国区电商研发部研发的基于mysql协议的数据库中间件,目前在小米商城大陆和海外得到广泛使用,包括订单、社区、活动等多个业务。Gaea支持分库分表、sql路由、读写分离等基本特性,更多详细功能可以参照下面的功能列表。其中分库分表方案兼容了mycat和kingshard两个项目的路

Xiaomi 2.3k Jun 26, 2022
MySQL proxy backups check recovery

一 、前言 感谢kingshard明星开源项目,本服务正是基于kingshard 开发而来。 本服务适用于相对封闭且经常断电的环境 针对此场景建议使用 MyISAM引擎。 在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失,大概分为以下几种: 硬件故障 软件故障(目前生产环境经常发生的)

null 4 Oct 29, 2021
pREST (PostgreSQL REST), simplify and accelerate development, ⚡ instant, realtime, high-performance on any Postgres application, existing or new

pREST pREST (PostgreSQL REST), simplify and accelerate development, instant, realtime, high-performance on any Postgres application, existing or new P

pREST 3.3k Jun 29, 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.7k Jun 24, 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.8k Jun 25, 2022
Golang MySql binary log replication listener

Go MySql binary log replication listener Pure Go Implementation of MySQL replication protocol. This allow you to receive event like insert, update, de

Pavel <Ven> Gulbin 185 Apr 13, 2022
MySQL replication topology management and HA

orchestrator [Documentation] orchestrator is a MySQL high availability and replication management tool, runs as a service and provides command line ac

null 4.6k Jun 28, 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 14.3k Jun 26, 2022
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 22 Jun 13, 2022
一个使 mysql,pgsql 数据库表自动生成 go struct 的工具

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

易水韩 18 Jun 1, 2022
🐳 A most popular sql audit platform for mysql

?? A most popular sql audit platform for mysql

Henry Yee 6.5k Jun 21, 2022
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 260 Jun 10, 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 14.3k Jun 29, 2022
GitHub's Online Schema Migrations for MySQL

gh-ost GitHub's online schema migration for MySQL gh-ost is a triggerless online schema migration solution for MySQL. It is testable and provides paus

GitHub 10.1k Jun 22, 2022
Bifrost ---- 面向生产环境的 MySQL 同步到Redis,MongoDB,ClickHouse,MySQL等服务的异构中间件

Bifrost ---- 面向生产环境的 MySQL 同步到Redis,ClickHouse等服务的异构中间件 English 漫威里的彩虹桥可以将 雷神 送到 阿斯加德 和 地球 而这个 Bifrost 可以将 你 MySQL 里的数据 全量 , 实时的同步到 : Redis MongoDB Cl

brokerCAP 1.2k Jun 30, 2022
go mysql driver, support distributed transaction

Go-MySQL-Driver A MySQL-Driver for Go's database/sql package Features Requirements Installation Usage DSN (Data Source Name) Password Protocol Address

Open Transaction 33 Apr 3, 2022