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的记录
本文由用户 huyuanliang 自行上传分享，仅供网友学习交流。所有权归原作者，若您的权利被侵害，请联系管理员。
转载本站原创文章，请注明出处，并保留原始链接、图片水印。
本站是一个以用户分享为主的开源技术平台，欢迎各类分享！