mycat2 安装、分库分表、以及集群的搭建
mycat下载地址：http://dl.mycat.org.cn/2.0/
搭建双主双从
一个主机 m1 用于处理所有写请求，它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请求。当 m1 主机宕机后，m2 主机负责写请求，m1、m2 互为备机。
| 编号 | 角色 | ip地址 |
| 1 | Master1 | 192.168.31.23 |
| 2 | Slave1 | 192.168.31.22 |
| 3 | Master2 | 192.168.31.24 |
| 4 | Slave2 | 192.168.31.25 |
1、搭建 MySQL 数据库主从复制（双主双从）
双主机配置 Master1配置
修改配置文件：vim /etc/my.cnf
#主服务器唯一ID server-id=1 #启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置logbin格式 statement row binlog_format=fixed
Master2配置
修改配置文件：vim /etc/my.cnf
#主服务器唯一ID server-id=3 #启用二进制日志 log-bin=mysql-bin # 设置不要复制的数据库(可设置多个) binlog-ignore-db=mysql binlog-ignore-db=information_schema #设置logbin格式 binlog_format=fixed
双从机配置
Slave1配置
修改配置文件：vim /etc/my.cnf
server-id=2 #启用中继日志 relay-log=mysql-relay
Slave2配置
#从服务器唯一ID server-id=4 #启用中继日志 relay-log=mysql-relay
双主机、双从机重启 mysql 服务
在两台主机上建立帐户并授权 slave
#查询Master1的状态
show master status;
#分别记录下File和Position的值 #执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
在从机上配置需要复制的主机
Slava1 复制 Master1，Slava2 复制 Master2
#复制主机的命令 Slave1 CHANGE MASTER TO MASTER_HOST='192.168.31.23', MASTER_USER='slave', MASTER_PASSWORD='niening2014', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=11113;
执行 start slave;
查看从服务器状态
show slave status\G;
#复制主机的命令 Slave2 CHANGE MASTER TO MASTER_HOST='192.168.31.24', MASTER_USER='slave', MASTER_PASSWORD='niening2014', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=13730;
执行 start slave;
查看从服务器状态
show slave status\G;
两个主机互相复制
# Master2的复制命令 CHANGE MASTER TO MASTER_HOST='192.168.31.23', MASTER_USER='slave', MASTER_PASSWORD='niening2014', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=11113; # Master1的复制命令 CHANGE MASTER TO MASTER_HOST='192.168.31.24', MASTER_USER='slave', MASTER_PASSWORD='niening2014', MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=13730;
#启动两台主服务器复制功能
start slave;
查看从服务器状态
show slave status\G;
此时双主双从已经搭建完成。
安装mycat
下载地址：http://dl.mycat.org.cn/2.0/1.22-release/mycat2-1.22-release-jar-with-dependencies-2022-10-13.jar 下载最新的jar包
http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip 下载最新安装包
下载所需的 mycat2 的 fat jar 一般大小为 100mb 的一个 jar 文件 把这个 jar 放进解压的 tar 中的 mycat\lib
同时修改启动目录下的文件的权限 ，将权限改为 744
修改mycat prototype 的配置
vim conf/datasources/prototypeDs.datasource.json
{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ_WRITE", "logAbandoned":true, "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"prototypeDs", "password":"niening2014", "queryTimeout":0, "removeAbandoned":false, "removeAbandonedTimeoutSecond":180, "type":"JDBC", "url":"jdbc:mysql://192.168.31.23:3306/mousecat?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true", "user":"root", "weight":0 }
创建存储数据源
/*+ mycat:createDataSource{ "name":"dw0", "url":"jdbc:mysql://192.168.31.23:3306/mousecat", "user":"root", "password":"niening2014" } */; /*+ mycat:createDataSource{ "name":"dr0", "url":"jdbc:mysql://192.168.31.22:3306/mousecat", "user":"root", "password":"niening2014" } */; /*+ mycat:createDataSource{ "name":"dw1", "url":"jdbc:mysql://192.168.31.24:3306/mousecat", "user":"root", "password":"niening2014" } */; /*+ mycat:createDataSource{ "name":"dr1", "url":"jdbc:mysql://192.168.31.25:3306/mousecat", "user":"root", "password":"niening2014" } */;
#通过注释命名添加数据源后，在对应目录会生成相关配置文件
#//在 mycat 终端输入 /*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */; /*! mycat:createCluster{"name":"c1","masters":["dw1"],"replicas":["dr1"]} */; #可以查看集群配置信息 cd /www/server/mycat/clusters
#添加数据库mousecat
CREATE DATABASE mousecat;
#在建表语句中加上关键字 BROADCAST（广播，即为全局表）
CREATE TABLE mousecat.`travelrecord` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` varchar(100) DEFAULT NULL, `traveldate` date DEFAULT NULL, `fee` decimal(10,0) DEFAULT NULL, `days` int DEFAULT NULL, `blob` longblob, PRIMARY KEY (`id`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 BROADCAST;
#在 Mycat 终端直接运行建表语句进行数据分片 CREATE TABLE mousecat.orders( id BIGINT NOT NULL AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id), KEY `id` (`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8 dbpartition BY mod_hash(customer_id) tbpartition BY mod_hash(customer_id) tbpartitions 2 dbpartitions 1; #数据库分片规则，表分片规则，以及各分多少片
INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(1,101,100,100100); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(7,101,102,100100); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(8,101,103,100300); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(9,101,104,120000); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(10,101,105,103000); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(11,102,107,100400); INSERT INTO mousecat.orders(id,order_type,customer_id,amount) VALUES(12,102,108,100020);
SELECT * FROM orders;
创建 ER 表
#在 Mycat 终端直接运行建表语句进行数据分片 CREATE TABLE orders_detail( `id` BIGINT NOT NULL AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) )ENGINE=INNODB DEFAULT CHARSET=utf8 dbpartition BY mod_hash(order_id) tbpartition BY mod_hash(order_id) tbpartitions 2 dbpartitions 1; INSERT INTO orders_detail(id,detail,order_id) VALUES(1,'detail1',1); INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2); INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3); INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4); INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5); INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
#上述两表具有相同的分片算法,但是分片字段不相同 #Mycat2 在涉及这两个表的 join 分片字段等价关系的时候可以完成 join 的下
#Mycat2 无需指定 ER 表,是自动识别的,具体看分片算法的接口 #查看配置的表是否具有 ER 关系,使用 /*+ mycat:showErGroup{}*/
#group_id 表示相同的组,该组中的表具有相同的存储分布
运行关联查询语句 SELECT * FROM orders o INNER JOIN orders_detail od ON od.order_id=o.id;
