《Mysql應用為MySQL安裝配置代理工具Kingshard的基本教程》要點：
本文介紹了Mysql應用為MySQL安裝配置代理工具Kingshard的基本教程，希望對您有用。如果有疑問，可以聯(lián)系我們。
環(huán)境說明MYSQL入門
本文僅作為最小實驗環(huán)境,因此不使用master, slave模式. 單機上使用mysql_mutil運行二個mysql實列
初始化數(shù)據(jù)目錄MYSQL入門
# mysql_install_db --datadir=/var/lib/mysql2/ --user=mysql # mysql_install_db --datadir=/var/lib/mysql3/ --user=mysql
?
生成配置文件MYSQL入門
利用mysqld_multi工具生成配置文件MYSQL入門
# mysqld_multi --example > mysqld_multi.conf
?
修改根據(jù)自己的需求修改mysqld_multi.conf
例：MYSQL入門
[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
?
啟動多個實例MYSQL入門
# 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,以此來區(qū)分.
查看實例狀態(tài)MYSQL入門
[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?
安裝KingshardMYSQL入門
1.安裝Go語言環(huán)境,具體步驟請Google.
MYSQL入門
git clone https://github.com/flike/kingshard.git src/github.com/flike/kingshard cd src/github.com/flike/kingshard source ./dev.sh make
設置配置文件
運行kingshard.?MYSQL入門
./bin/kingshard -config=etc/multi.yaml
?
2.配置文件說明MYSQL入門
# 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節(jié)點表示mysql集群的一個數(shù)據(jù)分片,包括一主多從(可以不配置從庫)
nodes :
#node節(jié)點名字
name : node1
# 連接池中默認的空閑連接數(shù)
idle_conns : 16
# kingshard連接該node中mysql的用戶名和密碼,master和slave的用戶名和密碼必須一致
user : kingshard
password : kingshard
# master的地址和端口
master : 127.0.0.1:3306
# slave的地址和端口,可不配置
slave :
#kingshard在300秒內(nèi)都連接不上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
# 分表規(guī)則
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配置文件不允許出現(xiàn)tab鍵,且冒號后面需要跟一個空格.配置文件編寫完成后,可以在yaml lint網(wǎng)站驗證是否有格式錯誤.MYSQL入門
配置KingshardMYSQL入門
修改/etc/hosts文件, 添加如下二行
MYSQL入門
127.0.0.1 node1 127.0.0.1 node2
?
配置如下
MYSQL入門
# 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實例信息MYSQL入門
設置用戶
分類登陸mysqld2, mysqld3, 創(chuàng)建root用戶(該用戶是給kingshard管理的,測試為了方便所以直接使用root) 若用戶存在,跳過此步
MYSQL入門
/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'
?
建數(shù)據(jù)庫
分類登陸mysqld2, mysqld2,創(chuàng)建kingshard數(shù)據(jù)庫
MYSQL入門
/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
MYSQL入門
# ./bin/kingshard -config=etc/multi.yaml
?
測試shard功能MYSQL入門
使用test_shard_hash測試 shard hash分表功能.
創(chuàng)建分表
創(chuàng)建test_shard_hash分表(_0000~_0007), _0001~_0003在node1(mysqld2)上創(chuàng)建, _0004~_0007在node2(mysqld3)上創(chuàng)建.
MYSQL入門
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
?
插入數(shù)據(jù)
mysql連接到kingshard插入數(shù)據(jù)MYSQL入門
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日志如下：MYSQL入門
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的日志可以看到數(shù)據(jù)插入時根據(jù)不同的hash值,插入到不同的子表里面去了.MYSQL入門
查看數(shù)據(jù)MYSQL入門
[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查詢, 只支持帶條件的查詢.MYSQL入門
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/1620.html
