Mycat

参考文章：
https://zhuanlan.zhihu.com/p/72476293
基本操作

bin/mycat start
bin/mycat stop
bin/mycat restart
bin/mycat status

记得开启8066(数据端口，建议使用)，9066（管理端口）的端口防火墙
登陆mycat

mysql -h10.106.11.151 -uroot -p123456 -P8066

读写分离配置(通过mysql的日志进行了验证，注意balance参数使用读写策略)

<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="10.106.11.153:3306" user="root" password="123456">
        <readHost host="hostS1" url="10.106.11.152:3306" user="root" password="123456"></readHost>
    </writeHost>
    <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>

同一个数据库分表配置(注意要创建 hello1,hello2,hello3数据库表)

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
    <!-- auto sharding by id (long) -->
    <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
    <!-- <table name="hello" primaryKey="id" dataNode="dn1,dn2" rule="crc32slot" splitTableNames ="true"/> -->
    <table name="hello" primaryKey="id" dataNode="dn1" subTables="hello$1-3" rule="mod-long"/>
</schema>

不同库分表配置(要先创建BIG,SMALL数据库表)

<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100">
    <!-- auto sharding by id (long) -->
    <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
    <table name="test" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" splitTableNames ="true"/>
    <!-- <table name="hello" primaryKey="id" dataNode="dn1" subTables="hello$1-3" rule="mod-long"/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
    /> -->
<dataNode name="dn1" dataHost="localhost1" database="BIG" />
<dataNode name="dn2" dataHost="localhost1" database="SMALL" />

Mysql自带分区表的创建

-- 创建分区表
CREATE TABLE `sales` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR (40) NOT NULL,
    `created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`,`created_time`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 PARTITION BY RANGE (YEAR(created_time))(
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
  PARTITION p2 VALUES LESS THAN MAXVALUE);

-- 查询指定某个分区的数据
SELECT * FROM sales PARTITION (p0);

-- 观察优化器是否执行了分区过滤
EXPLAIN PARTITIONS SELECT * FROM sales;

-- 观察优化器是否执行了分区过滤
EXPLAIN PARTITIONS SELECT * FROM sales WHERE created_time>'2020-04-24 07:30:17';

Mycat运维

Mycat运维

Mycat

Mysql自带分区表的创建

推荐阅读更多精彩内容