Basic tutorials for configuring the agent tool Kingshard for Mysql installation _mysql

Environment description

This article is used only as the minimum experimental environment, and therefore does not use master, slave mode. Use Mysql_mutil to run two MySQL real columns on a single computer
Initializing the Data directory

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

build configuration file

Using the Mysqld_multi tool to generate configuration files

# Mysqld_multi--example > mysqld_multi.conf

Modify the mysqld_multi.conf according to your own needs
Cases:

[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

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 (started separately)

Note that the 2, 3 corresponds to the Conf configuration file Mysqld2, mysqld3, in order to differentiate.
View instance Status

[Root@testnode kingshard]# Mysqld_multi--defaults-extra-file=./mysqld_multi.conf

Reporting mysql servers
mysql server from group:mysqld2 be running
MySQL server from group:mysqld3 is running

Description 2 instances have been started.

Install Kingshard

1. Install the Go language environment, the specific steps please google.

git clone https://github.com/flike/kingshard.git src/github.com/flike/kingshard
CD src/github.com/flike/ Kingshard
source./dev.sh
Make

Set configuration file
Run Kingshard.

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

2. Description of the configuration file

# Kingshard address and Port addr:127.0.0.1:9696 # Connect Kingshard username and password User:kingshard password:kingshard # log level, [Debug|inf O|warn|error], default is Error Log_level:debug # only allow the following IP list to connect to Kingshard allow_ips:127.0.0.1 # A node node represents a data fragment of a MySQL cluster, including a master-multiple from (Can not be configured from library) nodes: #node节点名字 Name:node1 # The default number of idle connections in the pool idle_conns:16 # Kingshard connection to MySQL username and password in this node, MAS The username and password for ter and slave must be consistent User:kingshard Password:kingshard # Master's address and Port master:127.0.0.1:3306 # slave  Address and port, do not configure slave: #kingshard在300秒内都连接不上mysql, the MySQL down_after_noalive:300-name:node2 Idle_conns will be offline: Rw_split:true User:kingshard Password:kingshard master:192.168.59.103:3307 slave:down_after_n  
      OALIVE:100 # Sub-table rules 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 uses the Yaml method to parse the configuration file, and note that the YAML configuration file does not allow the TAB key, and the colon needs to be followed by a space. After the configuration file is written, you can verify that there are formatting errors in the Yaml Lint Web site.

Configure Kingshard

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

127.0.0.1 Node1
127.0.0.1 node2

Configured as follows

# server Listen addr addr:127.0.0.1:9696 # Server user and password User:kingshard password:kingshard # log Lev El[debug|info|warn|error],default Error Log_level:debug # only allow the IP list IP to connect kingshard #allow_ips: 12
7.0.0.1 # node is a agenda for real remote MySQL server. Nodes:-Name:node1 # Default Max idle Conns for MySQL server idle_conns:16 # If Rw_split is true, Sele CT would use slave server Rw_split:true # all MySQL in a node must have the same user and password P  Assword:root # Master represents a real MySQL master server master:127.0.0.1:3307 # Slave represents a real
  MySQL Salve server,and the ' @ ' is #read load weight to this slave. #slave: 192.168.0.11:3307@2,192.168.0.12:3307@5 Slave: #down_after_noalive: 300-name:node2 # default M
Ax idle conns for MySQL server idle_conns:16 # If Rw_split is true, select would 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 R  EAL MySQL master server master:127.0.0.1:3308 # Slave represents a real MySQL salve server slave: # down MySQL after N seconds noalive # 0 would no down down_after_noalive:100 # Schema defines which DB can is used by CLI ENT and this DB ' s SQL would be executed in which nodes schemas:-Db:kingshard nodes: [Node1,node2] Rules:def
      Ault: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
Category Login Mysqld2, mysqld3, create root user (this user is for Kingshard management, test for convenience so 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 Root Pass Word ' root '

Build a Database
Category Login Mysqld2, mysqld2, create 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

Testing Shard Features

Use Test_shard_hash to test shard hash table functionality.
Create a sub-table
Create the Test_shard_hash table (_0000~_0007), _0001~_0003 on Node1 (mysqld2), _0004~_0007 on Node2 (MYSQLD3).

For i in ' seq 0 3 ';d o/usr/bin/mysql-h 127.0.0.1-p 3307-u root-proot kingshard-e ' CREATE TABLE IF not EXISTS Test_sha rd_hash_000 "${i}" (ID BIGINT () UNSIGNED not NULL, str VARCHAR (256), F DOUBLE, E enum (' Test1 ', ' test2 '), U tinyint unsig Ned, I tinyint, ni tinyint, PRIMARY KEY (ID)) Engine=innodb DEFAULT Charset=utf8; ";  Done for
i in ' SEQ 4 7 ';d o/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 () UNSIGNED not NULL, str VARCHAR (256), F DOUBLE, E enum (' Test1 ', ' test2 '), u Tiny int unsigned, I tinyint, ni tinyint, PRIMARY KEY (ID)) Engine=innodb DEFAULT Charset=utf8; "; Done

Inserting data
MySQL connects to kingshard insert data

For i in ' seq 1 ';d o 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) ';d one

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 1 2015/07/29 07:39:15-in Fo-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, ' Te St1 ', 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/ 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_h ash_0006 (ID, str, f, E, u, i) VALUES (6, ' Abc6 ', 3.14, ' test6 ', 255, -127) 2015/07/29 T;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 Nfo-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-&G T;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 143->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-&
 Gt;127.0.0.1:3307:insert into test_shard_hash_0002 (ID, str, f, E, u, i) VALUES (ten, ' abc10 ', 3.14, ' test10 ', 255,-127)

Through the Kingshard log you can see the data inserted in the different hash values, inserted into a different child table.

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 Kingshard does not support select * from Test_hard_hash queries, only conditional queries are supported.