kingshard 是一个由Go开发高性能MySQL Proxy项目.

功能简介：

• 读写分离
• 分库分表
• 数据库扩容

具体功能查看官方文档，这次主要是自己动手搭建基于Kingshard的MySQL集群。

环境

• Docker
• Go
• CentOS 7

过程

1. 安装Go语言

yum install golang -y

1. 安装Kingshard

git clone https://github.com/flike/kingshard.git src/github.com/flike/kingshardcd src/github.com/flike/kingshardsource ./dev.shmake

至此已经算安装了Kingshard，其配置文件等会再做修改

1. 拉取MysqL镜像，并启动两台MySQL实例

docker pull mysql:5.6docker run --name mysql1 -d -p3307:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.6docker run --name mysql2 -d -p3308:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.6

1. 初始化两台MySQL数据库，创建测试所需的表

这个语句比较枯燥，粘贴就好了

create database kingshard;use kingshard;CREATE TABLE `test_shard_hash_0000` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0001` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0002` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0003` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0004` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0005` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0006` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `test_shard_hash_0007` (  `id` bigint(64) unsigned NOT NULL,  `str` varchar(256) DEFAULT NULL,  `f` double DEFAULT NULL,  `e` enum('test1','test2') DEFAULT NULL,  `u` tinyint(3) unsigned DEFAULT NULL,  `i` tinyint(4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. 配置Kingshard的配置文件，这次只配置Kingshard的hash shard功能

# server listen addraddr : 0.0.0.0:9696user_list:-    user :  root    password : 123456web_addr : 0.0.0.0:9797web_user : adminweb_password : admin# log level[debug|info|warn|error],default errorlog_level : debug# if set log_sql(on|off) off,the sql log will not outputlog_sql: onnodes :-    name : node1    max_conns_limit : 32    user :  root    password : 123456    master : 127.0.0.1:3307    down_after_noalive : 32-    name : node2    max_conns_limit : 32    user :  root    password : 123456    master : 127.0.0.1:3308    down_after_noalive: 32# schema defines sharding rules, the db is the sharding table database.schema_list :-    user: root    nodes: [node1,node2]    default: node1    shard:    -        db : kingshard        table: test_shard_hash        key: id        nodes: [node1, node2]        type: hash        locations: [4,4]

1. 启动Kingshard

./bin/kingshard -config=etc/ks.yaml

image.png

1. 连接Kingshard，测试操作

mysql -h127.0.0.1 -P9696 -p -c执行相关的SQL语句insert into test_shard_hash(id,str,f,e,u,i) values(15,"flike",3.14,'test2',2,3);insert into test_shard_hash(id,str,f,e,u,i) values(7,"chen",2.1,'test1',32,3); insert into test_shard_hash(id,str,f,e,u,i) values(17,"github",2.5,'test1',32,3);insert into test_shard_hash(id,str,f,e,u,i) values(18,"kingshard",7.3,'test1',32,3);select * from test_shard_hash where id < 18;select * from test_shard_hash where id = 18;/*node2*/show tables;

image.png

在控制台我们都可以看到对应的输出信息

问题

1. ks.yml文件中配置的userList都要被使用才行
2. 如果想让非本机访问，ks.yml需要开启一些白名单
3. mysql命令行中的-c功能启用注释

最后

绝知此事要躬行，学东西不能光看，亲自动手才知道问题所在。有助于自己更好的理解

参考