ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库培训-备份恢复-性能优化-集群容灾
Oracle数据库DBA高级工程师培训视频

Mycat实现数据库读写分离

内容发布:luashin| 发布时间:2017-6-8 20:04:28
Mycat实现数据库读写分离

什么是读写分离
    在数据库集群架构中,让主库负责处理事务性查询,而从库只负责处理select查询,让两者分工明确达到提高数据库整体读写性能。当然,主数据库另外一个功能就是负责将事务性查询导致的数据变更同步到从库中,也就是写操作。

读写分离的好处
1)分摊服务器压力,提高机器的系统处理效率
    读写分离适用于读远比写的场景,如果有一台服务器,当select很多时,update和delete会被这些select访问中的数据堵塞,等待select结束,并发性能并不高,而主从只负责各自的写和读,极大程度的缓解X锁和S锁争用;
    假如我们有1主3从,不考虑上述1中提到的从库单方面设置,假设现在1分钟内有10条写入,150条读取。那么,1主3从相当于共计40条写入,而读取总数没变,因此平均下来每台服务器承担了10条写入和50条读取(主库不承担读取操作)。因此,虽然写入没变,但是读取大大分摊了,提高了系统性能。另外,当读取被分摊后,又间接提高了写入的性能。所以,总体性能提高了,说白了就是拿机器和带宽换性能;
2)增加冗余,提高服务可用性,当一台数据库服务器宕机后可以调整另外一台从库以最快速度恢复服务

Mycat原理
    Mycat是一个开源的分布式数据库系统,但是因为数据库一般都有自己的数据库引擎,而Mycat并没有属于自己的独有数据库引擎,所有严格意义上说并不能算是一个完整的数据库系统,只能说是一个在应用和数据库之间起桥梁作用的中间件。

    在Mycat中间件出现之前,MySQL主从复制集群,如果要实现读写分离,一般是在程序段实现,这样就带来了一个问题,即数据段和程序的耦合度太高,如果数据库的地址发生了改变,那么我的程序也要进行相应的修改,如果数据库不小心挂掉了,则同时也意味着程序的不可用,而对于很多应用来说,并不能接受;
引入Mycat中间件能很好地对程序和数据库进行解耦,这样,程序只需关注数据库中间件的地址,而无需知晓底层数据库是如何提供服务的,大量的通用数据聚合、事务、数据源切换等工作都由中间件来处理;
Mycat中间件的原理是对数据进行分片处理,从原有的一个库,被切分为多个分片数据库,所有的分片数据库集群构成完成的数据库存储,有点类似磁盘阵列中的RAID0.

Mycat配置安装
环境准备:保证多实例/data/3306/和/data/3307已经实现简单异步主从复制
1)安装JDK
http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html
#注意版本必须JDK7或者更高版本
[root@db02 tools]# rpm -ivh jdk-8u91-linux-x64.rpm
Preparing...                ########################################### [100%]
  1:jdk1.8.0_91             ########################################### [100%]

2)下载Mycat
https://github.com/MyCATApache/Mycat-download#
# 这里测试用的是Mycat-server-1.4-release版本
解压拷贝到/application/mycat目录

3)创建用户
主库,web用户有增删改查权限
mysql> grant select,update,delete,insert on lilongzi.* to web@'172.16.2.%' identified by '123456';

从库由于只负责读数据,所有web只有select权限
mysql> grant select on lilongzi.* to web@'172.16.2.%' identified by '123456';

4)修改配置文件
[root@db02 conf]# vim /application/mycat/conf/server.xml #MyCAT对外的“虚拟数据库”配置文件
s">32</property> -->
        </system>
        <user name="web"> #web为主库和分库刚建立的用户
                <property name="password">123456</property> #用户密码
                <property name="schemas">lilongzi</property> #数据库名称
        </user>
        <user name="web_r"> #web_r表示只给读权限
                <property name="password">123456</property>
                <property name="schemas">lilongzi</property>
                <property name="readOnly">true</property>
        </user>

[root@db02 conf]# vim /application/mycat/conf/server.xml #详细主库及读写分离模式配置文件
        <schema name="lilongzi" checkSQLschema="false" sqlMaxLimit="100"
        dataNode="dn1">
        #name=你的数据库名 ,添加dataNode="dn1" 表示数据库只在dn1上,没有分库
        <table name="test" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
        #table修改为你的表名,如果有多张表,可以按照这个格式添加
      </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="dn1" dataHost="localhost1" database="lilongzi" />
        <dataNode name="dn2" dataHost="localhost1" database="lilongzi" />
        <dataNode name="dn3" dataHost="localhost1" database="lilongzi" />
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="1"
                slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="172.16.2.10:3306" user="web"
                        password="123456">
                        <!-- can have multi read hosts -->
                <readHost host="hostS1" url="172.16.2.10:3307" user="web"
                        password="123456" />
                </writeHost>
                <!-- <writeHost host="hostM2" url="localhost:3316" user="root"
                password="123456"/> -->
        </dataHost>

这里面,有两个参数需要注意,balance和 switchType。
其中,balance指的负载均衡类型,目前的取值有4种:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
2. balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
3. balance="2",所有读操作都随机的在writeHost、readhost上分发。
4. balance="3",所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力
writeType表示写模式
writeType="0",所有的操作发送到配置的第一个writehost
writeType="1",随机发送到配置的所有writehost
writeType="2",不执行写操作
switchType指的是切换的模式,目前的取值也有4种:
1. switchType='-1' 表示不自动切换
2. switchType='1' 默认值,表示自动切换
3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
4. switchType='3'基于MySQL galary cluster的切换机制(适合集群)(1.4.1),心跳语句为 show status like 'wsrep%'。

5)启动Mycat
[root@db02 bin]# ./mycat console &
Running Mycat-server...
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |  Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    |
jvm 1    | log4j 2016-07-09 06:04:28 [./conf/log4j.xml] load completed.
jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@db02 bin]# lsof -i:{8066,9066}
COMMAND  PID USER  FD  TYPE DEVICE SIZE/OFF NODE NAME
java    87340 root  46u  IPv6 212352      0t0  TCP *:9066 (LISTEN) #虚拟schema管理端口
java    87340 root  50u  IPv6 212354      0t0  TCP *:8066 (LISTEN) #虚拟schema登陆端口
#能看到这个说明咱们的Mycat已经启动成功了

6)验证一下是否真的已经实现读写分离...
方法:
停掉从库的SQL线程,让从库虽然读取到了主库的binlog,但是不能发起SQL线程写入到自己的数据库中,这样我们模拟访问,插入一条数据,在主库中应该能看到新插入的数据,但是模拟访问的客户端却看不到,就说明读写分离成功。
我们在其他主机A上模拟web用户访问登陆查看数据
[root@m01 ~]# mysql -uweb -p123456 -h172.16.2.10 -P8066 #注意这里使用虚拟schema的登陆端口8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| lilongzi |
+----------+
1 row in set (0.00 sec)

mysql> use lilongzi;
Database changed

mysql> show tables;
+--------------------+
| Tables in lilongzi |
+--------------------+
| customer           |
| customer_addr      |
| employee           |
| goods              |
| hotnews            |
| orders             |
| order_items        |
| test               | #test为我们改过的表
| travelrecord       |
+--------------------+
9 rows in set (0.00 sec)
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
+----+--------+
1 row in set (0.00 sec)

我们停掉从库3307的SQL线程
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

在主机A远程插入一条数据test
mysql> insert into test values(2,'test2');
ERROR 1105 (HY000): Duplicate entry '2' for key 'PRIMARY'
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   | #立马查一下发现是看不到的
+----+--------+
1 row in set (0.01 sec)

但是在主库上面
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   |
|  2 | test2  | #显示已经写入成功
+----+--------+
2 rows in set (0.00 sec)

从库这边
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小明   | #同样看不到
+----+--------+
1 row in set (0.00 sec)

MyCAT实现MySQL的读写分离
    在MySQL中间件出现之前,对于MySQL主从集群,如果要实现其读写分离,一般是在程序端实现,这样就带来一个问题,即数据库和程序的耦合度太高,如果我数据库的地址发生改变了,那么我程序端也要进行相应的修改,如果数据库不小心挂掉了,则同时也意味着程序的不可用,而这对很多应用来说,并不能接受。引入MySQL中间件能很好的对程序端和数据库进行解耦,这样,程序端只需关注数据库中间件的地址,而无需知晓底层数据库是如何提供服务。作为当前炙手可热的MySQL中间件,MyCAT实现MySQL主从集群的读写分离自是应有之义,其配置也相当简单。
在这里,我用三个实例组成MySQL主从集群,来验证MyCAT的读写分离功能,其实,一主一从就可以满足,之所以用三个,是为了验证MyCAT的分片功能。
集群组成如下:
角色             主机名                      主机IP
master          mysql-server1          192.168.244.145
slave           mysql-server2          192.168.244.146
slave           mysql-server3          192.168.244.144

在这里,还是使用Travelrecord表进行测试。
首先编辑MyCAT的配置文件schema.xml,关于dataHost的配置信息如下:
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="localhost:3306" user="root"
                        password="123456">
                </writeHost>
                <writeHost host="hostS1" url="192.168.244.146:3306" user="root"
                        password="123456" />
                <writeHost host="hostS2" url="192.168.244.144:3306" user="root"                     
                        password="123456" />
</dataHost>
这里面,有两个参数需要注意,balance和 switchType。

其中,balance指的负载均衡类型,目前的取值有4种:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。
2. balance="1",全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。
3. balance="2",所有读操作都随机的在writeHost、readhost上分发。
4. balance="3",所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力

switchType指的是切换的模式,目前的取值也有4种:
1. switchType='-1' 表示不自动切换
2. switchType='1' 默认值,表示自动切换
3. switchType='2' 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status
4. switchType='3'基于MySQL galary cluster的切换机制(适合集群)(1.4.1),心跳语句为 show status like 'wsrep%'。
因此,该配置文件中的balance="1"意味着作为stand by writeHost的hostS1和hostS2将参与select语句的负载均衡,这就实现了主从的读写分离,switchType='-1'意味着当主挂掉的时候,不进行自动切换,即hostS1和hostS2并不会被提升为主,仍只提供读的功能。这就避免了将数据读进slave的可能性,毕竟,单纯的MySQL主从集群并不允许将数据读进slave中,除非配置的是双master。

验证读写分离
下面来验证一下,
创建Travelrecord表
create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
插入数据

mysql> insert into travelrecord(id,user_id,traveldate,fee,days)  values(1,@@hostname,20160101,100,10);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> insert into travelrecord(id,user_id,traveldate,fee,days)  values(5000001,@@hostname,20160102,100,10);
Query OK, 1 row affected, 1 warning (0.01 sec)
在这里,用了一个取巧的方法,即对user_id插入了当前实例的主机名,这样可直观的观察读写是否分离以及MyCAT的分片功能。能这样做的原因在于我当前的MySQL版本-5.6.26默认是基于statement的复制,如果是基于row的复制,则这个方法将不可取。

查询数据
mysql> select * from travelrecord;
+---------+---------------+------------+------+------+
| id      | user_id       | traveldate | fee  | days |
+---------+---------------+------------+------+------+
|       1 | mysql-server2 | 2016-01-01 |  100 |   10 |
| 5000001 | mysql-server3 | 2016-01-02 |  100 |   10 |
+---------+---------------+------------+------+------+
2 rows in set (0.01 sec)

mysql> select * from travelrecord;
+---------+---------------+------------+------+------+
| id      | user_id       | traveldate | fee  | days |
+---------+---------------+------------+------+------+
| 5000001 | mysql-server3 | 2016-01-02 |  100 |   10 |
|       1 | mysql-server2 | 2016-01-01 |  100 |   10 |
+---------+---------------+------------+------+------+
2 rows in set (0.02 sec)

mysql> select * from travelrecord;
+---------+---------------+------------+------+------+
| id      | user_id       | traveldate | fee  | days |
+---------+---------------+------------+------+------+
| 5000001 | mysql-server3 | 2016-01-02 |  100 |   10 |
|       1 | mysql-server3 | 2016-01-01 |  100 |   10 |
+---------+---------------+------------+------+------+
2 rows in set (0.01 sec)

mysql> select * from travelrecord;
+---------+---------------+------------+------+------+
| id      | user_id       | traveldate | fee  | days |
+---------+---------------+------------+------+------+
| 5000001 | mysql-server3 | 2016-01-02 |  100 |   10 |
|       1 | mysql-server3 | 2016-01-01 |  100 |   10 |
+---------+---------------+------------+------+------+
2 rows in set (0.01 sec)

mysql> select * from travelrecord;
+---------+---------------+------------+------+------+
| id      | user_id       | traveldate | fee  | days |
+---------+---------------+------------+------+------+
|       1 | mysql-server2 | 2016-01-01 |  100 |   10 |
| 5000001 | mysql-server2 | 2016-01-02 |  100 |   10 |
+---------+---------------+------------+------+------+
从上面的输出结果,可以得出以下两点:
一、该配置已实现读写分离,读出来的数据没有master节点的。
二、MyCAT的随机分发不是基于statement的,即一个select语句查询其中一个节点,另外一个select语句查询另外一个节点。它分发针对的是片的,同一个select语句的结果是有不同dataNode返回的。
不仅如此,从MyCAT日志中也可以获取读写分离的相关信息,当然,前提是MyCAT的日志级别是debug。日志相关信息如下:

验证mater挂了,slave还能提供读的功能
对于MySQL主从集群,我们的需求是master挂了,slave还能提供读的功能。

下面来测试一下
首先,人为的关闭主库
[root@mysql-server1 ~]# /etc/init.d/mysqld stop

登录MyCAT
[root@mysql-server1 ~]# mysql -utest -ptest -h127.0.0.1 -P8066 -DTESTDB
插入数据
mysql> insert into travelrecord(id,user_id,traveldate,fee,days)  values(10000001,@@hostname,20160103,100,10);
ERROR 1184 (HY000): Connection refused
mysql> select * from travelrecord;
+---------+---------------+------------+------+------+
| id      | user_id       | traveldate | fee  | days |
+---------+---------------+------------+------+------+
|       1 | mysql-server2 | 2016-01-01 |  100 |   10 |
| 5000001 | mysql-server3 | 2016-01-02 |  100 |   10 |
+---------+---------------+------------+------+------+
2 rows in set (0.02 sec)
可见无法插入数据,但不影响读取数据。
至此,MyCAT实现MySQL的读写分离部署测试完毕。

总结:
1. 其实,刚开始配置的是readHost节点,配置如下:
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                writeType="0" dbType="mysql" dbDriver="native" switchType="-1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="localhost:3306" user="root"
                        password="123456">
                        <!-- can have multi read hosts -->
                <readHost host="hostS1" url="192.168.244.146:3306" user="root" password="123456" />
                </writeHost>
</dataHost>
但这种方式有个问题,即master挂了以后,slave也不能提供服务,而这违反了MySQL主从集群的初衷。
2. 如果开启了事务模式,即set autocommit=0,则事务内的读走的是master节点,而不是从节点。

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读写分离
    MyCat就相当于一个调度器,具有数据库分片功能,本身不存储数据,其功能是对后端的真实数据库的数据,进行数据划分和数据整合,方便管理。
HOW-Mycat-Read && write separation
现在通过MyCat实现数据库的读写分离
定义读,写节点
vim  schema.xml

                 <table name="employee" primaryKey="ID" dataNode="dn1,dn2"
                            rule="sharding-by-intfile" />
         <dataHost name="server33.lalala.com" maxCon="1000" minCon="10" balance="1"
                           writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                 <heartbeat>select user()</heartbeat>
                 <!-- can have multi write hosts -->
                 <writeHost host="hostM1" url="server33.lalala.com:3306" user="mycat"
                                    password="Mycat+007">
                         <!-- can have multi read hosts -->

                 <readHost host="hostS1" url="server55.lalala.com:3306" user="mycat"
                                    password="Mycat+007">
                 </readHost>     <!-- can have multi read hosts -->
                 <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
                 </writeHost>
         </dataHost>
                        writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
参数说明:
balance=”1″ writeType=”0″ switchType=”1″

balance
1、balance=0 不开启读写分离机制,所有读操作都发送到当前可用的writehostle .
2、balance=1 全部的readhost与stand by writeHost 参与select语句的负载均衡。简单的说,双主双从模式(M1àS1,M2àS2,并且M1和M2互为主备),正常情况下,M1,S1,S2都参与select语句的复杂均衡。
3、balance=2 所有读操作都随机的在readhost和writehost上分发

writeType
负载均衡类型,目前的取值有3种:
1、writeType=”0″, 所有写操作发送到配置的第一个writeHost。
2、writeType=”1″,所有写操作都随机的发送到配置的writeHost。
3、writeType=”2″,不执行写操作。

switchType
1、switchType=-1 表示不自动切换
2、switchType=1 默认值,自动切换
3、switchType=2 基于MySQL 主从同步的状态决定是否切换

创建只写用户和只读用户
vim server.xml

        <user name="mycat">
                <property name="password">Mycat+007</property>
                <property name="schemas">JamesMycatSchema</property>

                <!-- 表级 DML 权限设置 -->
                <!--           
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>         
                  -->
        </user>

        <user name="mycat_r">
                <property name="password">cat</property>
                <property name="schemas">JamesMycatSchema</property>
                <property name="readOnly">true</property>

        </user>

检测
关闭salve的sql_线程
(因为我们要做读写分离,所以数据库的同步,会对实验造成干扰)
mysql> stop slave sql_thread;

在客户端登陆mycat: 插入数据:5,mydog,10010
mysql -umycat -pMycat+007 -h172.25.88.33 -P8066

插入之后再读取,也没有看见刚刚插入的id=5

在master(server33)的mysql数据库登陆,db1数据库中查询到了刚刚插入的数据,所以写操作是在master(server33)执行的.

slave(server44)的SQL线程被关闭了,所以slave(server44)的db1也没有id=5的数据

至此读写分离圆满成功。

MyCAT ER分片的验证

在这里,构造了两张表,熟悉Oracle的童鞋都知道,dept(部门表)和emp(员工表),其中dept中的deptno是emp表中dept_no的外键。
两表的建表语句如下:
create table dept(deptno int,dname varchar(10),datanode varchar(10));
create table emp(empno int,dept_no int,datanode varchar(10));
注意:在这里,最后一列都是datanode,通过插入database()函数可以很直观的获取插入的节点名,从而验证分片的效果。

分别测试以下两种情况:
1. 父表按照主键ID分片,子表的分片字段与主表ID关联,配置为ER分片
2. 父表的分片字段为其他字段,子表的分片字段与主表ID关联,配置为ER分片

首先测试第一种情况,在这里,dept表作为父表,主键是deptno,empno为子表,关联字段为dept_no,分片字段为deptno。
schema.xml中的配置如下:
<table name="dept" primaryKey="deptno" dataNode="dn1,dn2,dn3"
                rule="sharding-by-intfile">
                <childTable name="emp" primaryKey="empno" joinKey="dept_no"
                                parentKey="deptno">
                        </childTable>
  </table>

rule.xml中的配置如下:
<tableRule name="sharding-by-intfile">
                <rule>
                        <columns>deptno</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>
修改很简单,就是将原来的id修改为deptno,代表分片的字段
partition-hash-int.txt的值修改为:
10=0
20=1

验证如下:
插入父表的值,
datanode为db1,与partition-hash-int.txt中的配置相符
mysql> insert into dept(deptno,dname,datanode) values(10,'ACCOUNTING',database());
Query OK, 1 row affected (0.05 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | datanode |
+--------+------------+----------+
|     10 | ACCOUNTING | db1      |
+--------+------------+----------+
1 row in set (0.12 sec)

日志输出信息如下:


接下来插入子表的值
mysql> insert into emp(empno,dept_no,datanode) values(7788,10,database());
Query OK, 1 row affected (0.01 sec)

日志输出信息如下:

关键点在于“using parent partion rule directly”。意思是直接使用父的分片规则。如果表本身并没有指定外键约束。
则即便父并没有相应的主键值,子表依旧可以插入,只要分片规则中有所定义。
如下所示:
mysql> select * from emp;
+-------+---------+----------+
| empno | dept_no | datanode |
+-------+---------+----------+
|  7788 |      10 | db1      |
+-------+---------+----------+
1 row in set (0.52 sec)

mysql> select * from dept;
+--------+------------+----------+
| deptno | dname      | datanode |
+--------+------------+----------+
|     10 | ACCOUNTING | db1      |
+--------+------------+----------+
1 row in set (0.13 sec)

mysql> insert into emp(empno,dept_no,datanode) values(1234,20,database());
Query OK, 1 row affected (0.09 sec)
尽管dept中没有deptno为20的行,但emp还是能插入dept_no为20的值

日志的输出信息如下:

所以,对于第一种情况,即父表按照主键分片,字表的分片字段与主表关联,结论就是,子表进行insert操��时,并不会检查父表中是否有相关的主键(对于子表的外键),而是直接根据分片规则进行判断。

如果分片规则中没有定义,则会报如下错误:
mysql> insert into emp(empno,dept_no,datanode) values(1234,30,database());
ERROR 1064 (HY000): can't find datanode for sharding column:DEPTNO val:30

2. 父表的分片字段为其他字段,子表的分片字段与主表ID关联,配置为ER分片
在这里,打算用dname作为分片字段,这样的话,只需要修改route.xml和partition-hash-int.txt了。
首先,修改rule.xml
<tableRule name="sharding-by-intfile">
                <rule>
                        <columns>dname</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>
<function name="hash-int"
                class="org.opencloudb.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
                <property name="type">1</property>
        </function>
因dname是字符类型,故将type设置为1。

接着,修改算法配置文件的值
[root@mysql-server1 conf]# cat partition-hash-int.txt
accounting,research=0
sales=1
operations=2

登录mycat管理端口,重新加载配置文件
[root@mysql-server1 conf]# mysql -h192.168.244.145 -utest -ptest -P9066
mysql> reload @@config;
Query OK, 1 row affected (0.14 sec)
Reload config success

现在进行测试:
mysql> insert into dept(deptno,dname,datanode) values(10,'accounting',database());
Query OK, 1 row affected (0.14 sec)

mysql> insert into emp(empno,dept_no,datanode) values(1234,10,database());
Query OK, 1 row affected (0.29 sec)
日志的输出信息如下:


插入第二个分区中的值
mysql> insert into dept(deptno,dname,datanode) values(20,'sales',database());
Query OK, 1 row affected (0.01 sec)

mysql> insert into emp(empno,dept_no,datanode) values(1234,20,database());
Query OK, 1 row affected (0.41 sec)

日志的输出信息如下:

这次是在两个分区查找子表的分区节点

插入第三个分区中的值
mysql>  insert into dept(deptno,dname,datanode) values(30,'operations',database());
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp(empno,dept_no,datanode) values(4567,30,database());
Query OK, 1 row affected (0.62 sec)

日志的输出信息如下:


在三个分区中查找子表的分区节点
总结:在父表的分片字段为其他字段,子表的分片字段与主表ID关联这种情况下,子表的分区节点是顺序查找的,在该例中,依次从dn1,dn2和dn3中查找,如果dn1中存在,就直接插入到dn1中,反之,则继续查找dn2节点,如果仍不存在,则继续在dn3节点中查找。



上一篇:MyCAT读写分离与主从切换
下一篇:LVS + Keepalived + MyCAT搭建高可用负载均衡集群
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表