转载

每日一博 | 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 ;

每日一博 | mycat 实现 mysql 读写分离实践

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');

每日一博 | mycat 实现 mysql 读写分离实践

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');

每日一博 | mycat 实现 mysql 读写分离实践

分别在两个读写库中建立三个分片

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分别填写真实地址