Install and configure the proxy tool Kingshard for MySQL,

Install and configure the proxy tool Kingshard for MySQL,

Environment Description

This document serves only as the minimum lab environment, so master is not used. slave Mode. Use mysql_mutil on a single machine to run two mysql real columns
Initialize data directory

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

 
Generate configuration file

Use mysqld_multi to generate a configuration file

# mysqld_multi --example > mysqld_multi.conf

 
Modify mysqld_multi.conf as needed
Example:

[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

 
Start Multiple instances

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

Or mysqld_multi -- defaults-extra-file =./mysqld_multi.conf start 2; mysqld_multi -- defaults-extra-file =./mysqld_multi.conf start 3 (start separately)
 
Note that 2 and 3 correspond to the conf configuration files mysqld2 and mysqld3.
View instance status

[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

 
Both instances have been started.

Install Kingshard

1. Install the Go language environment. For detailed steps, Google.

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

Set the configuration file
Run kingshard.

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

 
2. configuration file description

# Kingshard address and port addr: 127.0.0.1: 9696 # username and password used to connect to kingshard user: kingshardpassword: kingshard # log Level, [debug | info | warn | error], the default value is errorlog_level: debug # only allow the following IP list to connect to kingshardallow_ips: 127.0.0.1 # A node indicates a data shard of the mysql cluster, including one master node and multiple slaves (slave database can be not configured) nodes: # node name: node1 # default number of idle connections in the connection pool idle_conns: 16 # username and password of kingshard connecting to mysql in the node, the username and password of master and slave must be consistent. user: kingshard password: kingshard # master Address and port master: 127.0.0.1: 3306 # slave address and port. do not configure slave: # If kingshard cannot connect to mysql within 300 seconds, the 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 # sharding rule schemas:-db: kingshard nodes: [node1, node2] rules: default: node1 shard:-table: test_shard_hash key: id nodes: [node1, node2] type: hash locations: [10000]-table: test_shard_range key: id type: range nodes: [node1, node2] locations: [] table_row_limit:

 
3. Tips
Kingshard uses the yaml method to parse the configuration file. Note that the yaml configuration file does not allow the tab key, and a space is required after the colon. After the configuration file is compiled, you can verify the format error on the yaml lint website.

Configure Kingshard

Modify the/etc/hosts file and add the following two lines:

127.0.0.1 node1127.0.0.1 node2

 
The configuration is as follows:

# 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,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

 
Set mysql instance information

Set User
Log on to mysqld2, mysqld3 by category, and create a root user (this user is managed by kingshard. For convenience, use root directly). If the user exists, skip this step.

/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'

 
Database creation
Log on to mysqld2, mysqld2, and create a kingshard database.

/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;"

 
Start Kingshard

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

 
Test the shard function.

Use test_shard_hash to test the shard hash table sharding function.
Create a table shard
Create a test_shard_hash table shard (_ 0000 ~ _ 0007), _ 0001 ~ _ 0003 created on node1 (mysqld2), _ 0004 ~ _ 0007 is created on 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;";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, 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

 
Insert data
Connect mysql to kingshard to insert data

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

 
The kingshard log is as follows:

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, 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 12015/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 12015/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 12015/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 12015/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 12015/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 12015/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 12015/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 12015/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 12015/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)

 
The kingshard log shows that data is inserted into different sub-tables based on different hash values during data insertion.

View data

[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 |+----+------+------+-------+------+------+------+

 
Note that kingshard does not support select * from test_hard_hash queries, but only supports queries with conditions.