KingShard MySQL中介軟體快速入門

kingshard 是一個由Go開發高效能MySQL Proxy專案.

功能簡介：

• 讀寫分離
• 分庫分表
• 資料庫擴容

具體功能檢視官方文件，這次主要是自己動手搭建基於Kingshard的MySQL叢集。

環境

• Docker
• Go
• CentOS 7

過程

1. 安裝Go語言

   yum install golang -y

2. 安裝Kingshard

   git clone  src/github.com/flike/kingshard
   cd src/github.com/flike/kingshard
   source ./dev.sh
   make

至此已經算安裝了Kingshard，其配置檔案等會再做修改

1. 拉取MysqL映象，並啟動兩臺MySQL例項

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

2. 初始化兩臺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 addr
   addr : 0.0.0.0:9696

user_list:

user :  root
password : 123456

web_addr : 0.0.0.0:9797

web_user : admin
web_password : admin

log level[debug|info|warn|error],default error

log_level : debug

if set log_sql(on|off) off,the sql log will not output

log_sql: on

nodes :

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]

6. 啟動Kingshard

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

![image.png](https://upload-images.jianshu.io/upload_images/426671-f130292240e82acc.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

7. 連線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 select from test_shard_hash where id = 18;
/node2/show tables;

![image.png](https://upload-images.jianshu.io/upload_images/426671-62e5b20c433444a0.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

在控制檯我們都可以看到對應的輸出資訊

### 問題
1. ks.yml檔案中配置的userList都要被使用才行
2. 如果想讓非本機訪問，ks.yml需要開啟一些白名單
3. mysql命令列中的-c功能啟用註釋

### 最後
絕知此事要躬行，學東西不能光看，親自動手才知道問題所在。有助於自己更好的理解

### 參考
- [Kingshard文件]()