Mycat(基于HGDB的水平切分、主從復(fù)制、讀寫分離)

1.Mycat是什么？

Text

Mycat是一款基于阿里開源產(chǎn)品Cobar而研發(fā)的開源數(shù)據(jù)庫分庫分表中間件（基于Java語言開發(fā)）。官網(wǎng)所言：Mycat國內(nèi)最活躍的、性能最好的開源數(shù)據(jù)庫中間件！
一個徹底開源的，面向企業(yè)應(yīng)用開發(fā)的大數(shù)據(jù)庫集群。
支持事務(wù)、ACID、可以替代MySQL的加強版數(shù)據(jù)庫。
一個可以視為MySQL集群的企業(yè)級數(shù)據(jù)庫，用來替代昂貴的Oracle集群。
一個融合內(nèi)存緩存技術(shù)、NoSQL技術(shù)、HDFS大數(shù)據(jù)的新型SQL Server。
結(jié)合傳統(tǒng)數(shù)據(jù)庫和新型分布式數(shù)據(jù)倉庫的新一代企業(yè)級數(shù)據(jù)庫產(chǎn)品。
一個新穎的數(shù)據(jù)庫中間件產(chǎn)品。

2.Mycat關(guān)鍵特性

Text

支持SQL92標(biāo)準(zhǔn)
支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常見SQL語法
遵守Mysql原生協(xié)議，跨語言，跨平臺，跨數(shù)據(jù)庫的通用中間件代理
基于心跳的自動故障切換，支持讀寫分離，支持MySQL主從，以及galera cluster集群
支持Galera for MySQL集群，Percona Cluster或者M(jìn)ariaDB cluster
基于Nio實現(xiàn)，有效管理線程，解決高并發(fā)問題
支持?jǐn)?shù)據(jù)的多片自動路由與聚合，支持sum,count,max等常用的聚合函數(shù),支持跨庫分頁
支持單庫內(nèi)部任意join，支持跨庫2表join，甚至基于caltlet的多表join
支持通過全局表，ER關(guān)系的分片策略，實現(xiàn)了高效的多表join查詢
支持多租戶方案
支持分布式事務(wù)（弱xa）
支持XA分布式事務(wù)（1.6.5）
支持全局序列號，解決分布式下的主鍵生成問題
分片規(guī)則豐富，插件化開發(fā)，易于擴展
強大的web，命令行監(jiān)控
支持前端作為MySQL通用代理，后端JDBC方式支持MySQL、PostgreSQL、Oracle、DB2、SQLServer、MongoDB、巨杉
支持密碼加密
支持服務(wù)降級
支持IP白名單
支持SQL黑名單、sql注入攻擊攔截
支持prepare預(yù)編譯指令（1.6）
支持非堆內(nèi)存(Direct Memory)聚合計算（1.6）
支持PostgreSQL的native協(xié)議（1.6）
支持mysql和oracle存儲過程，out參數(shù)、多結(jié)果集返回（1.6）
支持zookeeper協(xié)調(diào)主從切換、zk序列、配置zk化（1.6）
支持庫內(nèi)分表（1.6）
集群基于ZooKeeper管理，在線升級，擴容，智能優(yōu)化，大數(shù)據(jù)處理（2.0開發(fā)版

3.安裝與使用

下載地址

https://github.com/MyCATApache/Mycat-download/tree/master

#Mycat是基于Java開發(fā)的，確保安裝好了Java環(huán)境，可命令行輸入：java -version 進(jìn)行測試。
#Linux下還需配置Mycat的解壓目錄：vim /etc/profile，配置完成后使用：source /etc/profile
export MYCAT_HOME=/usr/local/mycat
export JAVA_HOME=/usr/local/java/jdk1.8.0_291
export JRE_HOME=$JAVA_HOME/jre
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JRE_HOME/lib
export PATH=$JAVA_HOME/bin:$JRE_HOME/bin:$MYCAT_HOME/bin:$PATH

#安裝好了jdk,并且配置了環(huán)境變量后進(jìn)入??/usr/local/mycat/conf???目錄下設(shè)置jvm
vim wrapper.conf
wrapper.java.command=/usr/local/java/jdk1.8.0_291/bin/java

#運行（Linux）
mycat start #啟動
mycat stop #停止
mycat console #前臺運行
mycat restart #重啟服務(wù)
mycat pause #暫停
mycat status #查看啟動狀態(tài)

4.Mycat配置

4.1.目錄說明

bin：啟動目錄
conf：配置文件目錄
    server.xml：是Mycat服務(wù)器參數(shù)調(diào)整和用戶授權(quán)的配置文件
    schema.xml：是邏輯庫定義和表以及分片定義的配置文件
    rule.xml：是分片規(guī)則的配置文件，分片規(guī)則的具體一些參數(shù)信息單獨存放為文件，也在這個目錄下，配置文件修改                    需要重啟MyCAT
    log4j.xml：日志存放在logs/log中，每天一個文件，日志的配置是在conf/log4j.xml中，根據(jù)自己的需要可以調(diào)整                   輸出級別為debug，debug級別下，會輸出更多的信息，方便排查問題
    autopartition-long.txt,partition-hash-int.txt,sequence_conf.properties,                                sequence_db_conf.properties  分片相關(guān)的id分片規(guī)則配置文件
lib：jar包目錄
logs ：日志目錄
tmlogs：臨時日志目錄

4.2.配置文件server.xml

Text

包含了Mycat需要的系統(tǒng)配置信息，用戶配置信息以及邏輯庫配置信息，源代碼中的映射類為：SystemConfig.class
添加如下配置：相當(dāng)于建立了一個叫做mycat用戶，對應(yīng)密碼為123456，該用戶管理了mycats這個邏輯庫。當(dāng)然了，也可以為用戶添加管理多個邏輯庫，以,（英文逗號）分隔開即可

<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
                <property name="defaultSqlParser">druidparser</property>
                <property name="mutiNodeLimitType">1</property>
                <property name="serverPort">8066</property> <!--連接端口，替換連接mysql時的3306端口-->
                <property name="managerPort">9066</property>
        </system>
        <!--用戶配置節(jié)點-->
        <user name="mycat"> <!-- 連接mycat的用戶名-->
                <property name="password">123456</property> <!-- 連接mycat的密碼-->
                <property name="schemas">mycats</property> <!-- 邏輯數(shù)據(jù)庫名，這里會和schema.xml中的配置關(guān)聯(lián)，多個用逗號分開 -->
                <property name="readOnly">false</property>
        </user>
</mycat:server>

4.3.配置文件schema.xml

Text

可以說是最重要的配置文件，管理著 MyCat 的邏輯庫、表、分片規(guī)則、DataNode 以及 DataSource
schema是實際邏輯庫的配置，多個schema代表多個邏輯庫
dataNode是邏輯庫對應(yīng)的分片，如果配置多個分片則需要添加多個dataNode即可
dataHost是實際的物理庫配置，可以根據(jù)業(yè)務(wù)需要配置多主、主從等其他配置，多個dataHost代表分片對應(yīng)的物理庫地址，下面的writeHost、readHost代表該分片是否配置多寫，主從，讀寫分離等高級特性

#添加如下配置：水平切分，數(shù)據(jù)按Id取模均勻劃分到兩個數(shù)據(jù)庫中
<mycat:schema xmlns:mycat="http://io.mycat/">
        <!-- 邏輯數(shù)據(jù)庫名：mycatDatbase，與server.xml中對應(yīng)；student 是物理數(shù)據(jù)庫中的表，這里在兩個庫都有這個表 ； dataNode 是自己定義的，區(qū)別兩個數(shù)據(jù)庫-->
        <schema name="mycats" checkSQLschema="false" sqlMaxLimit="100">
                <table name="student" primaryKey="id" dataNode="masterNode1,masterNode2" rule="ruleById"/>
        </schema>
        <!-- 設(shè)置dataNode 對應(yīng)的數(shù)據(jù)庫，name 和schema中對應(yīng) ,dataHost：自己定義的host配置 ，database是物理數(shù)據(jù)庫，master1和master2是兩個物理數(shù)據(jù)庫 -->
        <dataNode name="masterNode1" dataHost="masterHost1" database="master1" />
        <dataNode name="masterNode2" dataHost="masterHost2" database="master2" />
        <!-- mycat 邏輯主機dataHost對應(yīng)的物理主機.其中也設(shè)置對應(yīng)的mysql登陸信息 balance 為1 表示讀寫分離-->
        <dataHost name="masterHost1" maxCon="100" minCon="10" balance="3" writeType="0" dbType="postgresql" dbDriver="jdbc">
            <heartbeat>select 1</heartbeat>
            <writeHost host="hostM1" url="jdbc:highgo://192.168.100.10:5866/master1" user="sysdba" password="Qwer@1234">
                <readHost host="hostS1" url="jdbc:highgo://192.168.100.11:5866/master1" user="sysdba" password="Qwer@1234"/>
            </writeHost>
        </dataHost>

        <dataHost name="masterHost2" maxCon="100" minCon="10" balance="3" writeType="0" dbType="postgresql" dbDriver="jdbc">
            <heartbeat>select 1</heartbeat>
            <writeHost host="hostM2" url="jdbc:highgo://192.168.100.10:5866/master2" user="sysdba" password="Qwer@1234">
                <readHost host="hostS2" url="jdbc:highgo://192.168.100.11:5866/master2" user="sysdba" password="Qwer@1234"/>
            </writeHost>
        </dataHost>
</mycat:schema>

4.4.配置文件rule.xml

Text

定義了表拆分所涉及到的規(guī)則定義。根據(jù)業(yè)務(wù)可以靈活的對表使用不同的分片算法（目前已實現(xiàn)十余種不同的分片規(guī)則，對應(yīng)所在源碼包為：io.mycat.route.function），或者對表使用相同的算法但具體的參數(shù)不同。

#添加如下配置：水平切分，數(shù)據(jù)按Id取模均勻劃分到兩個數(shù)據(jù)庫中
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="ruleById">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- 說明我們前邊用多少個mode,數(shù)量要一致，不然會報錯-->
                <property name="count">2</property>
        </function>
</mycat:rule>

5.Springboot項目通過Mycat連接HGDB

5.1.準(zhǔn)備環(huán)境

1.瀚高數(shù)據(jù)庫4.5.8搭建主從流復(fù)制集群，參考流復(fù)制搭建手冊。
2.主節(jié)點創(chuàng)建數(shù)據(jù)庫master1和master2,分別創(chuàng)建表student
CREATE TABLE public.student(
    id integer NOT NULL,
    name character varying(200),
    age integer,
    address character varying(200),
    PRIMARY KEY (id)
);
3.放入驅(qū)動HgdbJdbc-6.2.3.jar，路徑為/usr/local/mycat/lib

5.2.配置application.properties

#注意了，這里都是用連mysql的方式去配置，Mycat會在后端做好對其它數(shù)據(jù)庫的連接
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=mycat
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://192.168.100.5:8066/mycats

5.3.添加實體Student.java

public class Student implements Serializable{
    private int id;
    private String name;
    private int age;
    private String address;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Student(int id, String name, int age, String address) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.address = address;
    }

    public Student() {
    }
    public String toString(){
        return "[id:"+this.id+",age:"+this.age+",name:"+this.name+",address:"+this.address+"]";
    }

}

5.4.添加StundentMapper.java

public interface StudentMapper {

    int  getCount();

    List<Student> getStuList();

    int insert(Student student);

    List<Student> getStuByAge(int age);

}

5.5.添加StudentService.java

@Service
public class StudentService implements StudentMapper {
    @Autowired
    private StudentMapper studentMapper;
    @Override
    public int getCount() {
        return studentMapper.getCount();
    }

    @Override
    public List<Student> getStuList() {
        return studentMapper.getStuList();
    }

    @Override
    public int insert(Student student) {
        return studentMapper.insert(student);
    }

    @Override
    public List<Student> getStuByAge(int age) {
        return studentMapper.getStuByAge(age);
    }

}

5.6.添加StudentController.java

@Api(value="StudentController",description = "學(xué)生信息相關(guān)接口，讀寫分離了")
@Controller
@RequestMapping("/stu")
public class StudentController {
    private static final Logger log = LoggerFactory.getLogger(StudentController.class);
    @Autowired
    private StudentService studentService;

    @RequestMapping(value = "/getCount",method = RequestMethod.GET)
    @ResponseBody
    @ApiOperation("查詢所有學(xué)生數(shù)量")
    public String getStuCount(){
        System.out.print("==========");
        int count = studentService.getCount();
        String str = "總計數(shù)量："+count;
        return str;
    }
    @RequestMapping(value = "/getAllStudent",method = {RequestMethod.GET})
    @ResponseBody
    @ApiOperation("得到所有學(xué)生信息列表")
    public List<Student> getAllStudent(){
        log.info("======== getAllStudent start ==============");
        List<Student> studentList = studentService.getStuList();
        log.info("=============== studentList message ==============");
        log.info("studentList: "+studentList);
        log.info("======== getAllStudent end ==============");
        return studentList;
    }
    @RequestMapping(value = "/getStuByAge/{age}",method = {RequestMethod.GET})
    @ResponseBody
    @ApiOperation(value="查詢年齡小于age的學(xué)生信息",notes = "查詢年齡大于age的學(xué)生信息 ")
    public List<Student> getStuByAge(@PathVariable(value="age") Integer age){
        log.info("======== getStuByAge start ==============");
        log.info("=======================age:>"+age);
        List<Student> studentList = studentService.getStuByAge(age);
        return studentList;
    }
    @ApiOperation(value = "新增學(xué)生信息",notes = "")
    @RequestMapping(value = "/insertStu",method = RequestMethod.GET)
    @ResponseBody
    public String insertStu(){
        for (int i = 1; i <= 50; i++) {
            Student student = new Student();
            student.setId(i);
            student.setName("lisi"+i);
            student.setAge(18);
            student.setAddress("山東");
            studentService.insert(student);
        }
        System.out.println("=============insert success==================");
        return "success";
    }
}

5.7.啟動項目

Text

http://localhost:8080/stu/insertStu #測試插入
#查看master1和master2，數(shù)據(jù)按id取模的方式劃分到了兩個數(shù)據(jù)庫中，同時從庫同步了主庫的數(shù)據(jù)
master1=# select * from student;
 id |  name  | age | address 
----+--------+-----+---------
  2 | lisi2  |  18 | 山東
  4 | lisi4  |  18 | 山東
  6 | lisi6  |  18 | 山東
  8 | lisi8  |  18 | 山東
 10 | lisi10 |  18 | 山東
 12 | lisi12 |  18 | 山東
 14 | lisi14 |  18 | 山東
 16 | lisi16 |  18 | 山東
 18 | lisi18 |  18 | 山東
 20 | lisi20 |  18 | 山東
 22 | lisi22 |  18 | 山東
 24 | lisi24 |  18 | 山東
 26 | lisi26 |  18 | 山東
 28 | lisi28 |  18 | 山東
 30 | lisi30 |  18 | 山東
 32 | lisi32 |  18 | 山東
 34 | lisi34 |  18 | 山東
 36 | lisi36 |  18 | 山東
 38 | lisi38 |  18 | 山東
 40 | lisi40 |  18 | 山東
 42 | lisi42 |  18 | 山東
 44 | lisi44 |  18 | 山東
 46 | lisi46 |  18 | 山東
 48 | lisi48 |  18 | 山東
 50 | lisi50 |  18 | 山東
(25 rows)
master2=# select * from student;
 id |  name  | age | address 
----+--------+-----+---------
  1 | lisi1  |  18 | 山東
  3 | lisi3  |  18 | 山東
  5 | lisi5  |  18 | 山東
  7 | lisi7  |  18 | 山東
  9 | lisi9  |  18 | 山東
 11 | lisi11 |  18 | 山東
 13 | lisi13 |  18 | 山東
 15 | lisi15 |  18 | 山東
 17 | lisi17 |  18 | 山東
 19 | lisi19 |  18 | 山東
 21 | lisi21 |  18 | 山東
 23 | lisi23 |  18 | 山東
 25 | lisi25 |  18 | 山東
 27 | lisi27 |  18 | 山東
 29 | lisi29 |  18 | 山東
 31 | lisi31 |  18 | 山東
 33 | lisi33 |  18 | 山東
 35 | lisi35 |  18 | 山東
 37 | lisi37 |  18 | 山東
 39 | lisi39 |  18 | 山東
 41 | lisi41 |  18 | 山東
 43 | lisi43 |  18 | 山東
 45 | lisi45 |  18 | 山東
 47 | lisi47 |  18 | 山東
 49 | lisi49 |  18 | 山東
(25 rows)

Text

http://localhost:8080/stu/getAllStudent #測試查詢
[{"id":1,"name":"lisi1","age":18,"address":"山東"},{"id":3,"name":"lisi3","age":18,"address":"山東"},{"id":5,"name":"lisi5","age":18,"address":"山東"},{"id":7,"name":"lisi7","age":18,"address":"山東"},{"id":9,"name":"lisi9","age":18,"address":"山東"},{"id":11,"name":"lisi11","age":18,"address":"山東"},{"id":13,"name":"lisi13","age":18,"address":"山東"},{"id":15,"name":"lisi15","age":18,"address":"山東"},{"id":17,"name":"lisi17","age":18,"address":"山東"},{"id":19,"name":"lisi19","age":18,"address":"山東"},{"id":21,"name":"lisi21","age":18,"address":"山東"},{"id":23,"name":"lisi23","age":18,"address":"山東"},{"id":2,"name":"lisi2","age":18,"address":"山東"},{"id":4,"name":"lisi4","age":18,"address":"山東"},{"id":6,"name":"lisi6","age":18,"address":"山東"},{"id":8,"name":"lisi8","age":18,"address":"山東"},{"id":10,"name":"lisi10","age":18,"address":"山東"},{"id":12,"name":"lisi12","age":18,"address":"山東"},{"id":14,"name":"lisi14","age":18,"address":"山東"},{"id":16,"name":"lisi16","age":18,"address":"山東"},{"id":18,"name":"lisi18","age":18,"address":"山東"},{"id":20,"name":"lisi20","age":18,"address":"山東"},{"id":22,"name":"lisi22","age":18,"address":"山東"},{"id":24,"name":"lisi24","age":18,"address":"山東"},{"id":26,"name":"lisi26","age":18,"address":"山東"},{"id":28,"name":"lisi28","age":18,"address":"山東"},{"id":30,"name":"lisi30","age":18,"address":"山東"},{"id":32,"name":"lisi32","age":18,"address":"山東"},{"id":34,"name":"lisi34","age":18,"address":"山東"},{"id":36,"name":"lisi36","age":18,"address":"山東"},{"id":38,"name":"lisi38","age":18,"address":"山東"},{"id":40,"name":"lisi40","age":18,"address":"山東"},{"id":42,"name":"lisi42","age":18,"address":"山東"},{"id":44,"name":"lisi44","age":18,"address":"山東"},{"id":46,"name":"lisi46","age":18,"address":"山東"},{"id":48,"name":"lisi48","age":18,"address":"山東"},{"id":50,"name":"lisi50","age":18,"address":"山東"},{"id":25,"name":"lisi25","age":18,"address":"山東"},{"id":27,"name":"lisi27","age":18,"address":"山東"},{"id":29,"name":"lisi29","age":18,"address":"山東"},{"id":31,"name":"lisi31","age":18,"address":"山東"},{"id":33,"name":"lisi33","age":18,"address":"山東"},{"id":35,"name":"lisi35","age":18,"address":"山東"},{"id":37,"name":"lisi37","age":18,"address":"山東"},{"id":39,"name":"lisi39","age":18,"address":"山東"},{"id":41,"name":"lisi41","age":18,"address":"山東"},{"id":43,"name":"lisi43","age":18,"address":"山東"},{"id":45,"name":"lisi45","age":18,"address":"山東"},{"id":47,"name":"lisi47","age":18,"address":"山東"},{"id":49,"name":"lisi49","age":18,"address":"山東"}]

Text

#讀操作都走了從庫
#從庫日志
2023-03-09 04:49:04.072 EST,"sysdba","master1",1504,"192.168.100.5:44528",6409ab54.5e0,8,"SELECT",2023-03-09 04:48:04 EST,2/10,0,LOG,00000,"execute <unnamed>: SELECT *
FROM student
LIMIT 100",,,,,,,,,"PostgreSQL JDBC Driver"
2023-03-09 04:49:04.072 EST,"sysdba","master2",1502,"192.168.100.5:44524",6409ab54.5de,10,"SELECT",2023-03-09 04:48:04 EST,6/12,0,LOG,00000,"execute <unnamed>: SELECT *
FROM student
LIMIT 100",,,,,,,,,"PostgreSQL JDBC Driver"