KingShard使用

对于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上的子表。

具体的源码地址为

https://github.com/flike/kingshard/blob/master/README_ZH.md

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>

 

转载于:https://my.oschina.net/mickelfeng/blog/1860062

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值