对于kingshard的功能,在git中可以看到明确的功能说明
主要功能:
1. 基础功能
- 支持SQL读写分离。
- 支持透明的MySQL连接池,不必每次新建连接。
- 支持平滑上线DB或下线DB,前端应用无感知。
- 支持多个slave,slave之间通过权值进行负载均衡。
- 支持强制读主库。
- 支持主流语言(java,php,python,C/C++,Go)SDK的mysql的prepare特性。
- 支持到后端DB的最大连接数限制。
- 支持SQL日志及慢日志输出。
- 支持SQL黑名单机制。
- 支持客户端IP访问白名单机制,只有白名单中的IP才能访问kingshard。
- 支持字符集设置。
- 支持last_insert_id功能。
- 支持热加载配置文件,动态修改kingshard配置项(具体参考管理端命令)。
- 支持以Web API调用的方式管理kingshard。
- 支持多用户模式,不同用户之间的表是权限隔离的,互补感知。
2. sharding功能
- 支持按整数的hash和range分表方式。
- 支持按年、月、日维度的时间分表方式。
- 支持跨节点分表,子表可以分布在不同的节点。
- 支持跨节点的count,sum,max和min等聚合函数。
- 支持单个分表的join操作,即支持分表和另一张不分表的join操作。
- 支持跨节点的order by,group by,limit等操作。
- 支持将sql发送到特定的节点执行。
- 支持在单个节点上执行事务,不支持跨多节点的分布式事务。
- 支持非事务方式更新(insert,delete,update,replace)多个node上的子表。
具体的源码地址为
Windows下: win下clone下代码,根据文档
go build -o bin/kingshard.exe ./cmd/kingshard
编译不通过,需要注释掉:
/*
sc := make(chan os.Signal, 1)
signal.Notify(sc,
syscall.SIGINT,
syscall.SIGTERM,
syscall.SIGQUIT,
syscall.SIGPIPE,
syscall.SIGUSR1,
)
go func() {
for {
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()
} else if sig == syscall.SIGPIPE {
golog.Info("main", "main", "Ignore broken pipe signal", 0)
} else if sig == syscall.SIGUSR1 {
golog.Info("main", "main", "Got update config signal", 0)
newCfg, err := config.ParseConfigFile(*configFile)
if err != nil {
golog.Error("main", "main", fmt.Sprintf("parse config file error:%s", err.Error()), 0)
} else {
svr.UpdateConfig(newCfg)
}
}
}
}()
*/
mysql 3307 端口配置:
[mysqld]
server-id = 2
port= 3307
tmpdir = "D:/MySql3307/tmp"
datadir = "D:/MySql3307/data"
log_error = "mysql_error.log"
bin\mysqld --defaults-file=D:\3307\my.ini
mysql 3308 端口配置:
[mysqld]
server-id = 2
port= 3307
tmpdir = "D:/MySql3308/tmp"
datadir = "D:/MySql3308/data"
log_error = "mysql_error.log"
bin\mysqld --defaults-file=D:\3307\my.ini
启动kingshard:(使用etc下的配置文件,稍作修改)
kingshard --config=ks.yaml
分类登陆mysqld2, mysqld2,创建kingshard数据库
mysql -h 127.0.0.1 -P 3307 -u root -proot -e "create database kingshard;"
mysql -h 127.0.0.1 -P 3308 -u root -proot -e "create database kingshard;"
for i in `seq 0 3`;do /usr/bin/mysql -h 127.0.0.1 -P 3307 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_000"${i}" ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";done
for i in `seq 4 7`;do /usr/bin/mysql -h 127.0.0.1 -P 3308 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_000"${i}" ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;";done
mysql -h 127.0.0.1 -P 3307 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0000 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3307 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0001 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3307 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0002 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3307 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0003 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3308 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0004 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3308 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0005 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3308 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0006 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 3308 -u root -proot kingshard -e "CREATE TABLE IF NOT EXISTS test_shard_hash_0007 ( id BIGINT(64) UNSIGNED NOT NULL, str VARCHAR(256), f DOUBLE, e enum('test1', 'test2'), u tinyint unsigned, i tinyint, ni tinyint, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(1, 'abc1', 3.14, 'test1', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(2, 'abc1', 3.14, 'test2', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(3, 'abc1', 3.14, 'test1', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(4, 'abc1', 3.14, 'test2', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(5, 'abc1', 3.14, 'test2', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(6, 'abc1', 3.14, 'test2', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(7, 'abc1', 3.14, 'test1', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(8, 'abc1', 3.14, 'test2', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(9, 'abc1', 3.14, 'test2', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "insert into test_shard_hash (id, str, f, e, u, i) values(10, 'abc1', 3.14, 'test1', 255, -127)"
mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard kingshard -e "select * from test_shard_hash where id in (2, 3, 4, 5)"
http://www.ttlsa.com/kingshard/kingshard_sharding_introduce/
http://www.ttlsa.com/kingshard/kingshard_quick_try/
数据分散在两个数据库中
# bin\mysql.exe -ukingshard -pkingshard -P 9696 -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10056
Server version: 5.6.20-kingshard MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use kingshard;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_kingshard |
+----------------------+
| test_shard_hash_0000 |
| test_shard_hash_0001 |
| test_shard_hash_0002 |
| test_shard_hash_0003 |
+----------------------+
4 rows in set (0.00 sec)
mysql> select * from test_shard_hash_0000;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 8 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from test_shard_hash_0001;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 1 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
| 9 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from test_shard_hash_0002;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 2 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 10 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
2 rows in set (0.00 sec)
mysql> select * from test_shard_hash_0003;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 3 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from test_shard_hash;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 4 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 5 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 6 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 7 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
| 8 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 1 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
| 9 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 2 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
| 10 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
| 3 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
10 rows in set (0.00 sec)
mysql> quit
Bye
tao.zeng@DESKTOP-S0J61R2 d:\3307
# bin\mysql.exe -uroot -proot -P3308 -h127.0.0.1
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 121
Server version: 5.5.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use kingshard;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_kingshard |
+----------------------+
| test_shard_hash_0004 |
| test_shard_hash_0005 |
| test_shard_hash_0006 |
| test_shard_hash_0007 |
+----------------------+
4 rows in set (0.00 sec)
mysql> select * from test_shard_hash_0004;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 4 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from test_shard_hash_0005;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 5 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from test_shard_hash_0006;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 6 | abc1 | 3.14 | test2 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from test_shard_hash_0007;
+----+------+------+-------+------+------+------+
| id | str | f | e | u | i | ni |
+----+------+------+-------+------+------+------+
| 7 | abc1 | 3.14 | test1 | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+
1 row in set (0.00 sec)
mysql>