Welcome 微信登录
编程资源 图片资源库 蚂蚁家优选

首页 / 数据库 / MySQL / MyCAT实现MySQL读写分离实践

MyCAT是一个的数据库中间件，基于阿里开源的cobar产品而研发，由几个有志之士的牛人共同完成并开源。提供高可用性数据分片集群，自动故障切换，高可用性 ，支持读写分离，支持MySQL双主多从，以及一主多从的模式 ，支持全局表，数据自动分片到多个节点，用于高效表关联查询 ，支持独有的基于E-R 关系的分片策略，实现了高效的表关联查询多平台支持，部署和实施简单。今天来实践下用MyCAT实现MySQL的读写分离，1.配置MySQL端主从数据同步，2.用MyCAT实现读写分离,配置MySQL端主从数据同步不作讲解，这里详细介绍下用MyCAT实现数据读写分离新建数据库读库和写库分别在两个不同主机下新建两个大库，读库r和写库w,在读库r下新建3个数据库分片（db1,db2,db3），在写库w下也新建3个分片（db1,db2,db3）。1.db1分片脚本/*
Navicat MySQL Data Transfer
Source Server       : mysql
Source Server Version : 50527
Source Host         : localhost:3306
Source Database     : db1
Target Server Type    : MYSQL
Target Server Version : 50527
File Encoding       : 65001
Date: 2016-01-27 15:48:40
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `company`
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of company
-- ----------------------------
-- ----------------------------
-- Table structure for `customer`
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  `sharding_id` bigint（20） DEFAULT NULL,
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES （"1", null）;
INSERT INTO `customer` VALUES （"4", "10000"）;
-- ----------------------------
-- Table structure for `employee`
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employee
-- ----------------------------
-- ----------------------------
-- Table structure for `goods`
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES （"11"）;
-- ----------------------------
-- Table structure for `hotnews`
-- ----------------------------
DROP TABLE IF EXISTS `hotnews`;
CREATE TABLE `hotnews` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hotnews
-- ----------------------------
-- ----------------------------
-- Table structure for `mycat_sequence`
-- ----------------------------
DROP TABLE IF EXISTS `mycat_sequence`;
CREATE TABLE `mycat_sequence` （
  `name` varchar（50） NOT NULL,
  `current_value` int（11） NOT NULL,
  `increment` int（11） NOT NULL DEFAULT "100",
  PRIMARY KEY （`name`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of mycat_sequence
-- ----------------------------
INSERT INTO `mycat_sequence` VALUES （"GLOBAL", "100400", "100"）;
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  `customer_id` bigint（20） DEFAULT NULL,
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES （"1", "1"）;
INSERT INTO `orders` VALUES （"4", "4"）;
-- ----------------------------
-- Table structure for `travelrecord`
-- ----------------------------
DROP TABLE IF EXISTS `travelrecord`;
CREATE TABLE `travelrecord` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of travelrecord
-- ----------------------------
INSERT INTO `travelrecord` VALUES （"1"）;
INSERT INTO `travelrecord` VALUES （"4"）;
INSERT INTO `travelrecord` VALUES （"10001"）;
INSERT INTO `travelrecord` VALUES （"100400"）;
-- ----------------------------
-- Function structure for `mycat_seq_currval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_currval`（seq_name VARCHAR（50）） RETURNS varchar（64） CHARSET utf8
    DETERMINISTIC
BEGIN DECLARE retval VARCHAR（64）;
SET retval="-999999999,null";
SELECT concat（CAST（current_value AS CHAR）,",",CAST（increment AS CHAR）） INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval;
END
;;
DELIMITER ;
-- ----------------------------
-- Function structure for `mycat_seq_nextval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`（seq_name VARCHAR（50）） RETURNS varchar（64） CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval（seq_name）;
END
;;
DELIMITER ;
-- ----------------------------
-- Function structure for `mycat_seq_setval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_setval`（seq_name VARCHAR（50）,value INTEGER） RETURNS varchar（64） CHARSET utf8
    DETERMINISTIC
BEGIN UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name;
RETURN mycat_seq_currval（seq_name）;
END
;;
DELIMITER ;2.db2分片脚本/*
Navicat MySQL Data Transfer
Source Server       : mysql
Source Server Version : 50527
Source Host         : localhost:3306
Source Database     : db2
Target Server Type    : MYSQL
Target Server Version : 50527
File Encoding       : 65001
Date: 2016-01-27 15:48:50
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `company`
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of company
-- ----------------------------
-- ----------------------------
-- Table structure for `customer`
-- ----------------------------
DROP TABLE IF EXISTS `customer`;
CREATE TABLE `customer` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  `sharding_id` bigint（20） DEFAULT NULL,
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of customer
-- ----------------------------
INSERT INTO `customer` VALUES （"2", null）;
INSERT INTO `customer` VALUES （"5", "10010"）;
-- ----------------------------
-- Table structure for `employee`
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employee
-- ----------------------------
-- ----------------------------
-- Table structure for `goods`
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES （"11"）;
-- ----------------------------
-- Table structure for `hotnews`
-- ----------------------------
DROP TABLE IF EXISTS `hotnews`;
CREATE TABLE `hotnews` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hotnews
-- ----------------------------
INSERT INTO `hotnews` VALUES （"1"）;
-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  `customer_id` bigint（20） DEFAULT NULL,
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES （"2", "2"）;
INSERT INTO `orders` VALUES （"5", "5"）;
-- ----------------------------
-- Table structure for `travelrecord`
-- ----------------------------
DROP TABLE IF EXISTS `travelrecord`;
CREATE TABLE `travelrecord` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of travelrecord
-- ----------------------------
INSERT INTO `travelrecord` VALUES （"2"）;3.db3分片脚本/*
Navicat MySQL Data Transfer
Source Server       : mysql
Source Server Version : 50527
Source Host         : localhost:3306
Source Database     : db3
Target Server Type    : MYSQL
Target Server Version : 50527
File Encoding       : 65001
Date: 2016-01-27 15:48:58
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `company`
-- ----------------------------
DROP TABLE IF EXISTS `company`;
CREATE TABLE `company` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of company
-- ----------------------------
-- ----------------------------
-- Table structure for `hotnews`
-- ----------------------------
DROP TABLE IF EXISTS `hotnews`;
CREATE TABLE `hotnews` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of hotnews
-- ----------------------------
INSERT INTO `hotnews` VALUES （"2"）;
-- ----------------------------
-- Table structure for `travelrecord`
-- ----------------------------
DROP TABLE IF EXISTS `travelrecord`;
CREATE TABLE `travelrecord` （
  `ID` bigint（20） NOT NULL DEFAULT "0",
  PRIMARY KEY （`ID`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of travelrecord
-- ----------------------------
INSERT INTO `travelrecord` VALUES （"3"）;
  分别在两个读写库中建立三个分片
 
schema.xml中配置读写分离<？xml version="1.0"？>
<！DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<！-- auto sharding by id （long） -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
<！-- global table is auto cloned to all defined data nodes ,so can join
with any table whose sharding node is in the same data node -->
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" />
<！-- random sharding using mod sharind rule -->
<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="mod-long" />
<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
rule="sharding-by-intfile">
<childTable name="orders" primaryKey="ID" joinKey="customer_id"
parentKey="id">
</childTable>
</table>
<table name="mycat_sequence" dataNode="dn1" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
<dataNode name="dn3" dataHost="localhost1" database="db3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
<heartbeat>select user（）</heartbeat>
<！-- can have multi write hosts -->
<writeHost host="hostM1" url="ip1:3306" user="root" password="123456">
    <！-- can have multi read hosts -->
    <readHost host="hostS1" url="ip2:3306" user="root" password="123456" weight="1" />
</writeHost>
</dataHost>
</mycat:schema>
 其中balance属性负载均衡类型，目前取值有3种： 1. balance="0", 开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 2. balance="1"，全部的readHost和stand by writeHost参与select语句的负载均衡 3. balance="2"，所有读操作都随机在writeHost、readhost上分发。 4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力以上ip1,ip2分别填写真实地址
 
测试读写分离启动mycat服务，测试读数据：select * from travelrecord可见数据是从读库中读取测试写数据：insert into travelrecord （ID） values（88）查看读库，没值，写库有一条88的记录MyCAT实现MySQL的读写分离  http://www.linuxidc.com/Linux/2016-01/127555.htm本文永久更新链接地址
版权所有©石家庄振强科技有限公司2024 冀ICP备08103738号-5 网站地图