mycat实现mysql读写分离实践

来自： http://my.oschina.net/rock912/blog/610878

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的记录