# mysql-sharded-cluster **Repository Path**: jonluo/mysql-sharded-cluster ## Basic Information - **Project Name**: mysql-sharded-cluster - **Description**: go 语言利用 kingshard 实现 mysql 分片集群，主从复制，读写分离 - **Primary Language**: Go - **License**: Apache-2.0 - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 3 - **Forks**: 2 - **Created**: 2019-03-29 - **Last Updated**: 2024-07-30 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # mysql-sharded-cluster #### 介绍 go 语言利用 kingshard 实现 mysql 分片集群，主从复制，读写分离 * 获取 kingshard * go get github.com/flike/kingshard * cd $GOPATH/src/github.com/flike/kingshard * make * 获取文件夹 bin * 搭建 docker mysql 2个主从复制集群 * 创建集群配置文件 master0/mysql.cnf slave0/mysql.cnf master1/mysql.cnf slave1/mysql.cnf * docker mysql 主从配置 * master0 ``` docker run -v "$PWD/master0":"/etc/mysql/conf.d" \ -p 3306:3306 \ --name master0 \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql:5.7 docker exec -it master0 bash mysql -uroot -p123456 //执行下面命令进行复制授权 CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; //检查命令 File和Position用于slave show master status; ``` * slave0 ``` docker run -v "$PWD/slave0":"/etc/mysql/conf.d" \ -p 3307:3306 \ --name slave0 \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql:5.7 docker exec -it slave0 bash mysql -uroot -p123456 change master to master_host='192.168.0.203', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=617, master_connect_retry=30; start slave; show slave status\G ``` Slave_IO_Running: Yes 和 Slave_SQL_Running: Yes 表明配置成功。 配置 master_log_file='mysql-bin.000003', master_log_pos=617, 这两个参数要注意 和 show master status 输出的值保持一致，不然会出现同步错误 * master1 ``` docker run -v "$PWD/master1":"/etc/mysql/conf.d" \ -p 3316:3306 \ --name master1 \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql:5.7 docker exec -it master1 bash mysql -uroot -p123456 //执行下面命令进行复制授权 CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; //检查命令 File和Position用于slave show master status; ``` * slave1 ``` docker run -v "$PWD/slave1":"/etc/mysql/conf.d" \ -p 3317:3306 \ --name slave1 \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql:5.7 docker exec -it slave1 bash mysql -uroot -p123456 change master to master_host='192.168.0.203', master_user='slave', master_password='123456', master_port=3316, master_log_file='mysql-bin.000003', master_log_pos=617, master_connect_retry=30; start slave; show slave status\G ``` 2个mysql主从复制完成 * 编写分片配置文件 ks.yaml ``` # server listen addr addr : 0.0.0.0:9696 # prometheus server listen addr prometheus_addr : 0.0.0.0:7080 # server user and password user_list: - user : kingshard password : kingshard # the web api server web_addr : 0.0.0.0:9797 #HTTP Basic Auth web_user : admin web_password : admin # if set log_path, the sql log will write into log_path/sql.log,the system log # will write into log_path/sys.log #log_path : /Users/flike/log # log level[debug|info|warn|error],default error log_level : debug # if set log_sql(on|off) off,the sql log will not output log_sql: on # only log the query that take more than slow_log_time ms #slow_log_time : 100 # the path of blacklist sql file # all these sqls in the file will been forbidden by kingshard #blacklist_sql_file: /Users/flike/blacklist # only allow this ip list ip to connect kingshard # support ip and ip segment #allow_ips : 127.0.0.1,192.168.15.0/24 # the charset of kingshard, if you don't set this item # the default charset of kingshard is utf8. #proxy_charset: gbk # node is an agenda for real remote mysql server. nodes : - name : node1 # default max conns for mysql server max_conns_limit : 32 # all mysql in a node must have the same user and password user : root password : 123456 # master0 represents a real mysql master0 server master : 127.0.0.1:3306 # slave0 represents a real mysql salve server,and the number after '@' is # read load weight of this slave0. slave : 127.0.0.1:3307 down_after_noalive : 32 - name : node2 # default max conns for mysql server max_conns_limit : 32 # all mysql in a node must have the same user and password user : root password : 123456 # master0 represents a real mysql master0 server master : 127.0.0.1:3316 # slave0 represents a real mysql salve server slave : 127.0.0.1:3317 # down mysql after N seconds noalive # 0 will no down down_after_noalive: 32 # schema defines sharding rules, the db is the sharding table database. schema_list : - user: kingshard nodes: [node1,node2] default: node1 shard: - # 数据库 db : test # 虚拟表 table: userinfo # 用来分片的字段 key: id # 两个主从复制集群 nodes: [node1, node2] # hash 分片 type: hash # 每个分片的表个数，通过 表名+ _ + 4位数字组成，userinfo_0000 locations: [4,4] ``` * 分配表到两个主从复制集群上 * master0 创建数据库：test ``` create database test; ``` 创建4张表：userinfo_0000,userinfo_0001,userinfo_0002,userinfo_0003 ``` use test; CREATE TABLE `userinfo_0000` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo_0001` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo_0002` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo_0003` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; show tables; ``` * master1 创建数据库：test ``` create database test; ``` 创建4张表：userinfo_0004,userinfo_0005,userinfo_0006,userinfo_0007 ``` use test; CREATE TABLE `userinfo_0004` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo_0005` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo_0006` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `userinfo_0007` ( `id` int NOT NULL, `name` varchar(256) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; show tables; ``` * 启动 kingshard 代理 ``` ./bin/kingshard -config=ks.yaml ``` * 编写demo程序测试 main.go