Mysql应用为MySQL安装配置代理工具Kingshard的基本教程

概述介绍《Mysql应用为MySQL安装配置代理工具Kingshard的基本教程》开发教程，希望对您有用。

《MysqL应用为MysqL安装配置代理工具Kingshard的基本教程》要点：
本文介绍了MysqL应用为MysqL安装配置代理工具Kingshard的基本教程，希望对您有用。如果有疑问，可以联系我们。

环境说明MysqL入门

本文仅作为最小实验环境,因此不使用master,slave模式. 单机上使用MysqL_mutil运行二个MysqL实列
初始化数据目录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

 
修改根据自己的需求修改MysqLd_multi.conf
例：MysqL入门

[MysqLd_multi]MysqLd   = /usr/bin/MysqLd_safeMysqLadmin = /usr/bin/MysqLadminuser    = multi_adminpassword  = my_password [MysqLd2]socket   = /var/lib/MysqL2/MysqL.sock2port    = 3307pID-file  = /var/lib/MysqL2/hostname.pID2datadir  = /var/lib/MysqL2#language  = /usr/share/MysqL/englishuser    = unix_user1 [MysqLd3]socket   = /var/lib/MysqL3/MysqL.sock3port    = 3308pID-file  = /var/lib/MysqL3/hostname.pID3datadir  = /var/lib/MysqL3#language  = /usr/share/MysqL/swedishuser    = 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,以此来区分.
查看实例状态MysqL入门

[root@testnode kingshard]# MysqLd_multi --defaults-extra-file=./MysqLd_multi.conf report

Reporting MysqL serversMysqL server from group: MysqLd2 is runningMysqL server from group: MysqLd3 is running

 
说明２个实例都已经启动了.

安装KingshardMysqL入门

1.安装Go语言环境,具体步骤请Google.
MysqL入门

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

设置配置文件
运行kingshard. MysqL入门

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

 
2.配置文件说明MysqL入门

# kingshard的地址和端口addr : 127.0.0.1:9696 # 连接kingshard的用户名和密码user : kingshardpassword : kingshard # log级别,[deBUG|info|warn|error],默认是errorlog_level : deBUG# 只允许下面的IP列表连接kingshardallow_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网站验证是否有格式错误.MysqL入门

配置KingshardMysqL入门

修改/etc/hosts文件,添加如下二行
MysqL入门

127.0.0.1 node1127.0.0.1 node2

 
配置如下
MysqL入门

# server Listen addraddr : 127.0.0.1:9696 # server user and passworduser : kingshardpassword : kingshard # log level[deBUG|info|warn|error],default errorlog_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 nodesschemas :-  db : kingshard  nodes: [node1,4]      table_row_limit: 10000

 
设置MysqL实例信息MysqL入门

设置用户
分类登陆MysqLd2,MysqLd3,创建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'

 
建数据库
分类登陆MysqLd2,MysqLd2,创建kingshard数据库
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分表功能.
创建分表
创建test_shard_hash分表(_0000~_0007),_0001~_0003在node1(MysqLd2)上创建,_0004~_0007在node2(MysqLd3)上创建.
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;";donefor 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,PRIMARY KEY (ID)) ENGINE=InnoDB DEFAulT CHARSET=utf8;";done

 
插入数据
MysqL连接到kingshard插入数据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 12015/07/29 07:39:15 - INFO - 127.0.0.1:40135->127.0.0.1:3307:insert into test_shard_hash_0001(ID,i) values (1,'abc1','test1',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40136->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40136->127.0.0.1:3307:insert into test_shard_hash_0002(ID,i) values (2,'abc2','test2',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40137->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40137->127.0.0.1:3307:insert into test_shard_hash_0003(ID,i) values (3,'abc3','test3',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40138->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40138->127.0.0.1:3308:insert into test_shard_hash_0004(ID,i) values (4,'abc4','test4',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40139->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40139->127.0.0.1:3308:insert into test_shard_hash_0005(ID,i) values (5,'abc5','test5',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40140->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40140->127.0.0.1:3308:insert into test_shard_hash_0006(ID,i) values (6,'abc6','test6',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40141->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40141->127.0.0.1:3308:insert into test_shard_hash_0007(ID,i) values (7,'abc7','test7',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40142->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40142->127.0.0.1:3307:insert into test_shard_hash_0000(ID,i) values (8,'abc8','test8',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40143->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40143->127.0.0.1:3307:insert into test_shard_hash_0001(ID,i) values (9,'abc9','test9',-127)2015/07/29 07:39:15 - INFO - 127.0.0.1:40144->127.0.0.1:3307:select @@version_comment limit 12015/07/29 07:39:15 - INFO - 127.0.0.1:40144->127.0.0.1:3307:insert into test_shard_hash_0002(ID,i) values (10,'abc10','test10',-127)

 
通过kingshard的日志可以看到数据插入时根据不同的hash值,插入到不同的子表里面去了.MysqL入门

查看数据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入门

总结

以上是内存溢出为你收集整理的Mysql应用为MySQL安装配置代理工具Kingshard的基本教程全部内容，希望文章能够帮你解决Mysql应用为MySQL安装配置代理工具Kingshard的基本教程所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。