您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 分庫分表中間件 Kingshard 安裝配置實戰

編輯：MySQL 綜合教學

MySQL 分庫分表中間件 Kingshard 安裝配置實戰

Kingshard 是基於 Go 語言開發的 MySQL Proxy，支援讀寫分離、分庫分表、SQL 審計等功能。本文以單機多實例模擬分片環境，演示完整部署與測試流程。

一、環境準備

OS：CentOS 7，Go 1.13+
MySQL：使用 mysqld_multi 啟動兩個實例（3307、3308）模擬兩個分片
Kingshard：github.com/flike/kingshard

二、MySQL 多實例配置

1. 初始化資料目錄

mysql_install_db --datadir=/var/lib/mysql2 --user=mysql
mysql_install_db --datadir=/var/lib/mysql3 --user=mysql

2. 生成並修改 mysqld_multi 配置

mysqld_multi --example > mysqld_multi.conf
vi mysqld_multi.conf
[mysqld2]
socket = /var/lib/mysql2/mysql.sock2
port = 3307
datadir = /var/lib/mysql2

[mysqld3]
socket = /var/lib/mysql3/mysql.sock3
port = 3308
datadir = /var/lib/mysql3

3. 啟動並驗證

mysqld_multi --defaults-extra-file=./mysqld_multi.conf start
mysqld_multi --defaults-extra-file=./mysqld_multi.conf report

三、Kingshard 編譯安裝

git clone https://github.com/flike/kingshard.git src/github.com/flike/kingshard
cd src/github.com/flike/kingshard
source ./dev.sh
make
# 產出 bin/kingshard

四、核心配置文件 multi.yaml

addr: 127.0.0.1:9696
user: kingshard
password: kingshard
log_level: debug

nodes:
  - name: node1
    idle_conns: 16
    user: root
    password: root
    master: 127.0.0.1:3307
    slave: []
    down_after_noalive: 300
  - name: node2
    idle_conns: 16
    user: root
    password: root
    master: 127.0.0.1:3308
    slave: []
    down_after_noalive: 100

schemas:
  - db: kingshard
    nodes: [node1, node2]
    rules:
      default: node1
      shard:
        - table: test_shard_hash
          key: id
          nodes: [node1, node2]
          type: hash
          locations: [4,4]   # 每節點 4 張子表
        - table: test_shard_range
          key: id
          type: range
          nodes: [node1, node2]
          locations: [4,4]
          table_row_limit: 10000

關鍵點：YAML 禁用 Tab，冒號後需空格；locations: [4,4] 表示 node1 建 4 表、node2 建 4 表，共 8 張子表（_0000~_0007）。

五、MySQL 實例授權與建庫

# 設定 root 密碼
mysqladmin -h 127.0.0.1 -P 3307 -u root password 'root'
mysqladmin -h 127.0.0.1 -P 3308 -u root password 'root'

# 建立 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;"

六、啟動 Kingshard 並測試分表

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

1. 建立分表（在對應實例上建子表）

# node1 (3307) 建 _0000~_0003
for i in {0..3}; do
  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 UNSIGNED NOT NULL PRIMARY KEY,
      str VARCHAR(256), f DOUBLE, e ENUM('test1','test2'),
      u TINYINT UNSIGNED, i TINYINT, ni TINYINT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;"
done

# node2 (3308) 建 _0004~_0007
for i in {4..7}; do
  mysql -h 127.0.0.1 -P 3308 -u root -proot kingshard -e "
    CREATE TABLE IF NOT EXISTS test_shard_hash_000${i} (...);"
done

2. 透過 Kingshard 寫入資料

for i in {1..10}; do
  mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard -e "
    INSERT INTO test_shard_hash (id, str, f, e, u, i)
    VALUES (${i}, 'abc${i}', 3.14, 'test${i}', 255, -127);"
done

3. 查看 Kingshard 日誌驗證路由

2015/07/29 07:39:15 INFO 127.0.0.1:40135->127.0.0.1:3307: INSERT INTO test_shard_hash_0001 ...
2015/07/29 07:39:15 INFO 127.0.0.1:40138->127.0.0.1:3308: INSERT INTO test_shard_hash_0004 ...

可見 id=1~3 路由到 node1，id=4~7 路由到 node2，符合 Hash 分片規則。

4. 查詢驗證

mysql -h 127.0.0.1 -P 9696 -u kingshard -pkingshard -e "
  SELECT * FROM test_shard_hash WHERE id IN (2,3,4,5);"
+----+------+------+-------+------+------+
| id | str  | f    | e     | u    | i    |
+----+------+------+-------+------+------+
| 2  | abc2 | 3.14 | test2 | 255  | -127 |
| 3  | abc3 | 3.14 | test3 | 255  | -127 |
| 4  | abc4 | 3.14 | test4 | 255  | -127 |
| 5  | abc5 | 3.14 | test5 | 255  | -127 |
+----+------+------+-------+------+------+

限制：Kingshard 不支援無 WHERE 條件的 SELECT * FROM test_shard_hash，必須帶分片鍵條件。