程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 為MySQL裝置設置裝備擺設署理對象Kingshard的根本教程

為MySQL裝置設置裝備擺設署理對象Kingshard的根本教程

編輯:MySQL綜合教程

為MySQL裝置設置裝備擺設署理對象Kingshard的根本教程。本站提示廣大學習愛好者:(為MySQL裝置設置裝備擺設署理對象Kingshard的根本教程)文章只能為提供參考,不一定能成為您想要的結果。以下是為MySQL裝置設置裝備擺設署理對象Kingshard的根本教程正文


情況解釋

本文僅作為最小試驗情況,是以不應用master, slave形式. 單機上應用mysql_mutil運轉二個mysql實列
初始化數據目次

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

 
生成設置裝備擺設文件

應用mysqld_multi對象生成設置裝備擺設文件

# mysqld_multi --example > mysqld_multi.conf

 
修正依據本身的需求修正mysqld_multi.conf
例:

[mysqld_multi]
mysqld   = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user    = multi_admin
password  = my_password
 
[mysqld2]
socket   = /var/lib/mysql2/mysql.sock2
port    = 3307
pid-file  = /var/lib/mysql2/hostname.pid2
datadir  = /var/lib/mysql2
#language  = /usr/share/mysql/english
user    = unix_user1
 
[mysqld3]
socket   = /var/lib/mysql3/mysql.sock3
port    = 3308
pid-file  = /var/lib/mysql3/hostname.pid3
datadir  = /var/lib/mysql3
#language  = /usr/share/mysql/swedish
user    = unix_user2

 
啟動多個實例

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

或許 mysqld_multi --defaults-extra-file=./mysqld_multi.conf start 2; mysqld_multi --defaults-extra-file=./mysqld_multi.conf start 3(分離啟動)
 
留意這裡的2、3對應conf設置裝備擺設文件 mysqld2、mysqld3,以此來辨別。
檢查實例狀況

[root@testnode kingshard]# mysqld_multi --defaults-extra-file=./mysqld_multi.conf report
Reporting MySQL servers
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
 
解釋2個實例都曾經啟動了。

裝置Kingshard

1.裝置Go說話情況,詳細步調請Google。

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

設置設置裝備擺設文件
運轉kingshard。 

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

 
2.設置裝備擺設文件解釋

# kingshard的地址和端口
addr : 127.0.0.1:9696
 
# 銜接kingshard的用戶名和暗碼
user : kingshard
password : kingshard
 
# log級別,[debug|info|warn|error],默許是error
log_level : debug
# 只許可上面的IP列表銜接kingshard
allow_ips: 127.0.0.1
 
# 一個node節點表現mysql集群的一個數據分片,包含一主多從(可以不設置裝備擺設從庫)
nodes :
  #node節點名字
  name : node1 
 
  # 銜接池中默許的余暇銜接數
  idle_conns : 16
 
  # kingshard銜接該node中mysql的用戶名和暗碼,master和slave的用戶名和暗碼必需分歧
  user : kingshard 
  password : kingshard
 
  # master的地址和端口 
  master : 127.0.0.1:3306
 
  # slave的地址和端口,可不設置裝備擺設
  slave : 
  #kingshard在300秒內都銜接不上mysql,則會下線該mysql
  down_after_noalive : 300
- 
  name : node2 
  idle_conns : 16
  rw_split: true
  user : kingshard 
  password : kingshard
 
  master : 192.168.59.103:3307
  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]
 
    -  
      table: test_shard_range
      key: id
      type: range
      nodes: [node1, node2]
      locations: [4,4]
      table_row_limit: 10000

 
3.Tips
kingshard采取的是yaml方法解析設置裝備擺設文件,須要留意的是yaml設置裝備擺設文件不許可湧現tab鍵,且冒號前面須要跟一個空格。設置裝備擺設文件編寫完成後,可以在yaml lint網站驗證能否有格局毛病。

設置裝備擺設Kingshard

修正/etc/hosts文件, 添加以下二行

127.0.0.1 node1
127.0.0.1 node2

 
設置裝備擺設以下

# server listen addr
addr : 127.0.0.1:9696
 
# server user and password
user : kingshard
password : kingshard
 
# log level[debug|info|warn|error],default error
log_level : debug
# only allow this ip list ip to connect kingshard
#allow_ips: 127.0.0.1
 
# node is an agenda for real remote mysql server.
nodes :
- 
  name : node1 
 
  # default max idle conns for mysql server
  idle_conns : 16
 
  # if rw_split is true, select will use slave server
  rw_split: true
 
  # all mysql in a node must have the same user and password
  user : root
  password : root
 
  # master represents a real mysql master server 
  master : 127.0.0.1:3307
 
  # slave represents a real mysql salve server,and the number after '@' is 
  #read load weight of this slave.
  #slave : 192.168.0.11:3307@2,192.168.0.12:3307@5
  slave : 
  #down_after_noalive : 300
- 
  name : node2 
 
  # default max idle conns for mysql server
  idle_conns : 16
 
  # if rw_split is true, select will use slave server
  rw_split: true
 
  # all mysql in a node must have the same user and password
  user : root
  password : root
 
  # master represents a real mysql master server 
  master : 127.0.0.1:3308
 
  # slave represents a real mysql salve server 
  slave : 
 
  # down mysql after N seconds noalive
  # 0 will no down
  down_after_noalive: 100
 
# schema defines which db can be used by client and this db's sql will be executed in which nodes
schemas :
-
  db : kingshard
  nodes: [node1,node2]
  rules:
    default: node1
    shard:
    -  
      table: test_shard_hash
      key: id
      nodes: [node1, node2]
      type: hash
      locations: [4,4]
 
    -  
      table: test_shard_range
      key: id
      type: range
      nodes: [node1, node2]
      locations: [4,4]
      table_row_limit: 10000

 
設置mysql實例信息

設置用戶
分類上岸mysqld2, mysqld3, 創立root用戶(該用戶是給kingshard治理的,測試為了便利所以直接應用root) 若用戶存在,跳過此步

/usr/bin/mysqladmin -h 127.0.0.1 -P 3307 -u root password 'root'
/usr/bin/mysqladmin -h 127.0.0.1 -P 3308 -u root password 'root'

 
建數據庫
分類上岸mysqld2, mysqld2,創立kingshard數據庫

/usr/bin/mysql -h 127.0.0.1 -P 3307 -u root -proot -e "create database kingshard;"
/usr/bin/mysql -h 127.0.0.1 -P 3308 -u root -proot -e "create database kingshard;"

 
啟動Kingshard

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

 
測試shard功效

應用test_shard_hash測試 shard hash分表功效.
創立分表
創立test_shard_hash分表(_0000~_0007), _0001~_0003在node1(mysqld2)上創立, _0004~_0007在node2(mysqld3)上創立。

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銜接到kingshard拔出數據

for i in `seq 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

 
kingshard日記以下:

2015/07/29 07:39:15 - INFO - 127.0.0.1:40135->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40135->127.0.0.1:3307:insert into test_shard_hash_0001(id, str, f, e, u, i) values (1, 'abc1', 3.14, 'test1', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40136->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40136->127.0.0.1:3307:insert into test_shard_hash_0002(id, str, f, e, u, i) values (2, 'abc2', 3.14, 'test2', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40137->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40137->127.0.0.1:3307:insert into test_shard_hash_0003(id, str, f, e, u, i) values (3, 'abc3', 3.14, 'test3', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40138->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40138->127.0.0.1:3308:insert into test_shard_hash_0004(id, str, f, e, u, i) values (4, 'abc4', 3.14, 'test4', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40139->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40139->127.0.0.1:3308:insert into test_shard_hash_0005(id, str, f, e, u, i) values (5, 'abc5', 3.14, 'test5', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40140->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40140->127.0.0.1:3308:insert into test_shard_hash_0006(id, str, f, e, u, i) values (6, 'abc6', 3.14, 'test6', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40141->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40141->127.0.0.1:3308:insert into test_shard_hash_0007(id, str, f, e, u, i) values (7, 'abc7', 3.14, 'test7', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40142->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40142->127.0.0.1:3307:insert into test_shard_hash_0000(id, str, f, e, u, i) values (8, 'abc8', 3.14, 'test8', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40143->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40143->127.0.0.1:3307:insert into test_shard_hash_0001(id, str, f, e, u, i) values (9, 'abc9', 3.14, 'test9', 255, -127)
2015/07/29 07:39:15 - INFO - 127.0.0.1:40144->127.0.0.1:3307:select @@version_comment limit 1
2015/07/29 07:39:15 - INFO - 127.0.0.1:40144->127.0.0.1:3307:insert into test_shard_hash_0002(id, str, f, e, u, i) values (10, 'abc10', 3.14, 'test10', 255, -127)

 
經由過程kingshard的日記可以看到數據拔出時依據分歧的hash值,拔出到分歧的子內外面去了。


檢查數據

[root@testnode kingshard]# 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  | ni  |
+----+------+------+-------+------+------+------+
| 2 | abc2 | 3.14 | test2 | 255 | -127 | NULL |
| 3 | abc3 | 3.14 |    | 255 | -127 | NULL |
| 4 | abc4 | 3.14 |    | 255 | -127 | NULL |
| 5 | abc5 | 3.14 |    | 255 | -127 | NULL |
+----+------+------+-------+------+------+------+

 
留意kingshard不支撐 select * from test_hard_hash查詢, 只支撐帶前提的查詢。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved