安科网

MyCAT实现MySQL读写分离实践

CoderJiang

CoderJiang

2016-01-29

关注 关注

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

mysql mysql读写分离 读写分离 mysql集群 mycat mysql数据库

CoderJiang

CoderJiang

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

Mysql读写分离过期常用解决方案

读写分离的主要目标是分摊主库的压力，由客户端选择后端数据库进行查询。还有种架构就是在MYSQL和客户端之间有一个中间代理层proxy，客户端之连接proxy，由proxy根据请求类型和上下文决定请求的分发路由。客户端需要关注后端细节，连接维护、后端信息维护

要啥自行车一把梭 2020-09-10

MySQL 8.0.15配置MGR单主多从的方法

MySQL Group Replication字面意思是mysql组复制的意思,但其实他是一个高可用的集群架构,暂时只支持mysql5.7和mysql8.0版本.也是mysql官方基于组复制概念并充分参考MariaDB Galera Cluster和Per

暗夜之城 2020-11-11

MySQL主从复制原理以及需要注意的地方

最近在写Mycat专题，由于不少小伙伴最近要出去面试，问我能不能简单写下MySQL的主从复制原理和注意事项，因为在之前的面试中被问到了这些问题。 Master 将数据改变记录到二进制日志中，也就是配置文件 log-bin 指定的文件， 这些记录叫做二进制日

emmm00 2020-11-17

Mysql联表update数据的示例详解

在MySQL中，可以在 UPDATE语句 中使用JOIN子句执行跨表更新。employees表将存储在员工编号，姓名，工作表现和工资的数据。employees 和 merits 表之间以是 performance 字段相关联的。对于 employees 表

王艺强 2020-11-17

mysql 8.0.22 安装配置图文教程

解压后的目录并没有的my.ini文件，没关系可以自行创建在安装根目录下添加的my.ini ，写入基本配置：。# 允许连接失败的次数。初始化MySQL，在安装时，避免权限问题出错我们尽量使用管理员身份运行CMD，否则在安装时会报错，会导致安装失败的情况，如下

aydh 2020-11-12

MySQL用truncate命令快速清空一个数据库中的所有表

用文本编辑器把每条truncate语句前后的“|”替换为空字符，方便后面一次复制多条执行。truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；dro

世樹 2020-11-11

修改MySQL8.0 默认的数据目录(快捷操作无配置)

使用场景：我们使用的是阿里云，单独购买了数据盘，MySQL 8.0 数据库默认装在系统盘上，为了考虑安全性和空间问题，我们需要将默认的数据库目录更改到其它位置。操作系统：CentOS 7.6 数据库：MySQL 8.0 查看我本人更多原创文章，请点击

zry 2020-11-11

阿里云服务器安装Mysql数据库的详细教程

由于在学习过程中需要安装zookeeper，我的虚拟机一直有问题，就够买了阿里云服务器。安装完zookeeper后想着把数据库也安装在服务器上，释放一下电脑的压力，在安装数据库的时候遇到了很多问题，通过查看有些大佬的作品终于安装好了数据库。现在就我遇到的问

URML 2020-11-11

MySQL中的undo日志

我们知道，MySQL中的redo日志记录了事务的行为，在服务器宕机的时候，可以通过重做事务来达到恢复数据的目的，然而，有的时候，事务还有回滚的需求，也就是说，我们需要知道某条在变成当前情况之前的样子，这种情况下，undo日志就派上用场了。也就是说，undo

spurity 2020-11-10

php7连接MySQL实现简易查询程序的方法

假设我们制作的是分班情况查询程序，将使用PHP7的环境以PDO的方式连接MySQL。通过学号和姓名查询自己所在班级。准备就绪，开始吧，现在！echo "<tr><th>学号</th><th>姓名&l

yifangs 2020-10-13

JDBC连接MySQL

String sql = "select * from users where name=?String id = rs.getString;//1代表数据库中表的列数，id在第一列也可以！！！

Andrea0 2020-09-18

Uber为什么放弃Postgres选择迁移到MySQL？

Uber 的早期架构包含了一个用 Python 开发的单体后端应用程序，这个应用程序使用 Postgres 作为数据存储。从那个时候开始，Uber 的架构已经发生了巨大变化，变成了微服务，并采用新的数据平台模型。具体地说，之前使用 Postgres 的地方

Ida 2020-09-16

聊聊MySQL中的参数

在前面一些文章中，经常能看到介绍某某参数的作用，可能有些小伙伴仍搞不清楚 MySQL 参数是啥。我们所说的参数在官方文档中称为 系统变量，不同的变量有着不同的作用。MySQL 服务端维护了许多表示其配置的系统变量，所有变量均有默认值。若会话变量未单独设置，

ltd00 2020-09-12

shell脚本定时备份MySQL数据库数据并保留指定时间

公司用到的MySQL数据库，经常有同事通过一顿骚操作把一些关键的配置数据误删，每次恢复都要花上不少时间，于是写了个shell脚本，结合corntab每天凌晨备份数据库，并保留7天。同样的备份脚本也可用于生产环境MySQL数据库定时备份。上述脚本中指定了要备

tufeiax 2020-09-03

Mysql误删数据解决方案及kill语句原理

使用flashback工具闪回，把数据恢复回来。原理是修改binlog的内容，拿回原库重放，需要确保binlog_format=row和binlog_row_imsge=Full. 如果是insert，将binlog event类型是write_rows

xjd0 2020-09-10

MySQL 如何修改root用户的密码

mysql> set password for 用户名@localhost = password;关闭正在运行的MySQL服务。打开DOS窗口，转到mysql\bin目录。--skip-grant-tables的意思是启动MySQL服务的时候跳过权限

greatboylc 2020-09-10

通过实例解析MySql CURRENT_TIMESTAMP函数

`text` varchar DEFAULT '' COMMENT '内容',那么如何设置一个具体的默认时间呢？

adsadadaddadasda 2020-09-08

mysql主从复制配置过程

配置完毕后，重启数据库： service mysqld restart. 通过phpmyadmin，添加新用户，并授予REPLICATION SLAVE权限。或者通过root用户登录到mysql，赋予权限：。其中repl是用户名，password是该用户的

疯狂老司机 2020-09-08

Golang操作MySql数据库的完整步骤记录

MySQL是业界常用的关系型数据库，在平时开发中会经常与MySql数据库打交道，所以在接下来将介绍怎么使用Go语言操作MySql数据库。Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。我们常用的数

CoderToy 2020-11-16

MySQL数据类型优化原则

MySQL支持的数据类型很多，选择正确的数据类型对于高性能至关重要。下面几个简单的原则都有助于做出更好的选择。应该尽量使用可以正确储存数据的最小数据类型。如果无法确定哪个数据类型时最好的，就选择你认为不会超过范围的最小类型。比如用MySQ内建的类型而不是使

ribavnu 2020-11-16
CoderJiang

CoderJiang

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号