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

数据运维 2023-04-25 Escape 手机阅读

环境说明 本文仅作为最小实验环境，因此不使用master, slave模式. 单机上使用mysql_mutil运行二个mysql实列 初始化数据目录 # mysql_install_db --datadir=/var/lib/mysql2/ --user=mysql# mysql_install_db --data

                        <p>环境说明</p>

本文仅作为最小实验环境，因此不使用master, slave模式. 单机上使用mysql_mutil运行二个mysql实列 初始化数据目录

  1. mysql_install_db --datadir=/var/lib/mysql2/ --user=mysql
  2. mysql_install_db --datadir=/var/lib/mysql3/ --user=mysql

 生成配置文件

利用mysqld_multi工具生成配置文件

  1. 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

 启动多个实例

  1. 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

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

安装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.配置文件说明

  1. kingshard的地址和端口 addr : 127.0.0.1:9696

  2. 连接kingshard的用户名和密码 user : kingshard password : kingshard

  3. log级别，[debug|info|warn|error],默认是error log_level : debug

  4. 只允许下面的IP列表连接kingshard allow_ips: 127.0.0.1

  5. 一个node节点表示mysql集群的一个数据分片，包括一主多从（可以不配置从库） nodes : #node节点名字 name : node1

    1. 连接池中默认的空闲连接数 idle_conns : 16

    2. kingshard连接该node中mysql的用户名和密码，master和slave的用户名和密码必须一致 user : kingshard password : kingshard

    3. master的地址和端口 master : 127.0.0.1:3306

    4. 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

  6. 分表规则 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

  配置如下

  1. server listen addr addr : 127.0.0.1:9696

  2. server user and password user : kingshard password : kingshard

  3. log level[debug|info|warn|error],default error log_level : debug

  4. only allow this ip list ip to connect kingshard #allow_ips: 127.0.0.1

  5. node is an agenda for real remote mysql server. nodes :

    • name : node1

    1. default max idle conns for mysql server idle_conns : 16

    2. if rw_split is true, select will use slave server rw_split: true

    3. all mysql in a node must have the same user and password user : root password : root

    4. master represents a real mysql master server master : 127.0.0.1:3307

    5. 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

    6. default max idle conns for mysql server idle_conns : 16

    7. if rw_split is true, select will use slave server rw_split: true

    8. all mysql in a node must have the same user and password user : root password : root

    9. master represents a real mysql master server master : 127.0.0.1:3308

    10. slave represents a real mysql salve server slave :

    11. down mysql after N seconds noalive

    12. 0 will no down down_after_noalive: 100

  6. 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

  1. ./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查询, 只支持带条件的查询。