Mycat 实战

最新推荐文章于 2024-04-17 11:59:06 发布

杜莱

最新推荐文章于 2024-04-17 11:59:06 发布

阅读量424

点赞数 1

分类专栏：数据库文章标签： mycat mysql

本文链接：https://blog.csdn.net/LaneDu/article/details/119156248

版权

数据库专栏收录该内容

17 篇文章 1 订阅

订阅专栏

文章目录

数据库信息

创建 db lan1 和 lane2 分别创建表 position


CREATE TABLE `position` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `salary` varchar(50) DEFAULT NULL,
  `city` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Mycat 下载

提示：需要先安装 jdk，同 sharding proxy

吐槽一下：真是一个小天才啊，macos 一个版本、linux 一个版本、unix 一个版本

一开始我下载的 linux 不行，后来才发现竟然区分 mac 和 linux

下载 Mycat-server 工具包

http://www.mycat.org.cn/

进入 mycat/bin，启动 Mycat 命令如下

启动命令：./mycat start 
停止命令：./mycat stop 
重启命令：./mycat restart 
查看状态：./mycat status

Mycat 配置

server.xml

<!--只是修改了下主键生成规则从 1 改成了 0-->
<property name="sequenceHandlerType">0</property>
<!--用户改成了root和test-->
<user name="root" defaultAccount="true">
		<property name="password">root</property>
		<property name="schemas">lane_db</property>
		<property name="defaultSchema">lane_db</property>
		 
	</user>

	<user name="test">
		<property name="password">test</property>
		<property name="schemas">lane_db</property>
		<property name="readOnly">true</property>
		<property name="defaultSchema">lane_db</property>
	</user>

完整版 server.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
	<property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
	<property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
	在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
	<property name="useHandshakeV10">1</property>
    <property name="removeGraveAccent">1</property>
	<property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
	<property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
	<property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
		<property name="sequenceHandlerType">0</property> <!--0配置文件指定生成，1数据库生成-->
	<!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
	INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
	-->
	<!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
	<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
	<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
	<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
	<!-- <property name="processorBufferChunk">40960</property> -->
	<!-- 
	<property name="processors">1</property> 
	<property name="processorExecutor">32</property> 
	 -->
        <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
		<property name="processorBufferPoolType">0</property>

		<!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
		<property name="handleDistributedTransactions">0</property>
		<property name="useOffHeapForMerge">0</property>

		<!--
			单位为m
		-->
        <property name="memoryPageSize">64k</property>

		<!--
			单位为k
		-->
		<property name="spillsFileBufferSize">1k</property>

		<property name="useStreamOutput">0</property>

		<!--
			单位为m
		-->
		<property name="systemReserveMemorySize">384m</property>


		<!--是否采用zookeeper协调切换  -->
		<property name="useZKSwitch">false</property>

		<!-- XA Recovery Log日志路径 -->
		<!--<property name="XARecoveryLogBaseDir">./</property>-->

		<!-- XA Recovery Log日志名称 -->
		<!--<property name="XARecoveryLogBaseName">tmlog</property>-->
		<!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
		<property name="strictTxIsolation">false</property>
		<!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
		<property name="parallExecute">0</property>
	</system>
 
	<user name="root" defaultAccount="true">
		<property name="password">root</property>
		<property name="schemas">lane_db</property>
		<property name="defaultSchema">lane_db</property>
		 
	</user>

	<user name="test">
		<property name="password">test</property>
		<property name="schemas">lane_db</property>
		<property name="readOnly">true</property>
		<property name="defaultSchema">lane_db</property>
	</user>

</mycat:server>

schema.xml 修改后如下

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="lane_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<!-- auto sharding by id (long) -->
		<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
		<table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true" fetchStoreNodeByJdbc="true">
			<!-- <childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable> -->
		</table>
		 
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="lane1" />
	<dataNode name="dn2" dataHost="localhost1" database="lane2" />
  
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
		</writeHost>
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>

</mycat:schema>

rule.xml 修改下为对 2 取模，对应于 schema 配置的分片规则 mod-long


	<tableRule name="mod-long">
		<rule>
			<columns>id</columns>
			<algorithm>mod-long</algorithm>
		</rule>
	</tableRule>

	<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
		<!-- how many data nodes -->
		<property name="count">2</property>
	</function>

Mycat 启动

cd /Users/dulane/software/mycat/mycat-mac/bin
./mycat start
Starting Mycat-server...

Mycat 测试

测试下 test 用户

访问 mysql

mysql -utest -ptest -h127.0.0.1 -P8066

具体操作

 ~ mysql -utest -h 127.0.0.1   -P 8066 -p
Enter password:
mysql> show databases;
+----------+
| DATABASE |
+----------+
| lane_db  |
+----------+
1 row in set (0.01 sec)

mysql> use lane_db
Database changed
mysql> show tables;
+-------------------+
| Tables in lane_db |
+-------------------+
| position          |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from position;
+--------------------+--------+---------+-----------+
| Id                 | name   | salary  | city      |
+--------------------+--------+---------+-----------+
| 624565542031720448 | lisi1  | 1000000 | shanghai  |
| 624565542786695168 | lisi3  | 1000000 | shanghai  |
| 624565543113850880 | lisi5  | 1000000 | shanghai  |
| 624565543436812288 | lisi7  | 1000000 | shanghai  |
| 624565543730413568 | lisi9  | 1000000 | shanghai  |
| 624565543956905984 | lisi11 | 1000000 | shanghai  |
| 624565544183398400 | lisi13 | 1000000 | shanghai  |
| 624565544422473728 | lisi15 | 1000000 | shanghai  |
| 624565544728657920 | lisi17 | 1000000 | shanghai  |
| 624565544971927552 | lisi19 | 1000000 | shanghai  |
| 625037191625572352 | root1  | 1000000 | beijing   |
| 625390806554902528 | lucy   | 21000   | hangzhou  |
| 625399491796664320 | sky9   | 100000  | guangzhou |
| 624565542639894529 | lisi2  | 1000000 | shanghai  |
| 624565542954467329 | lisi4  | 1000000 | shanghai  |
| 624565543281623041 | lisi6  | 1000000 | shanghai  |
| 624565543592001537 | lisi8  | 1000000 | shanghai  |
| 624565543852048385 | lisi10 | 1000000 | shanghai  |
| 624565544057569281 | lisi12 | 1000000 | shanghai  |
| 624565544309227521 | lisi14 | 1000000 | shanghai  |
| 624565544581857281 | lisi16 | 1000000 | shanghai  |
| 624565544833515521 | lisi18 | 1000000 | shanghai  |
| 624565545101950977 | lisi20 | 1000000 | shanghai  |
| 625390705467981825 | tom    | 20000   | shanghai  |
| 625399491637280769 | sky4   | 100000  | guangzhou |
+--------------------+--------+---------+-----------+
25 rows in set (0.08 sec)

mysql> delete from  position where name ='tom';
ERROR 1495 (HY000): User readonly
mysql>

测试下 root 用户

具体操作

➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
mysql> use lane_db;
Database changed
mysql> insert into position values(1,'yangqian1','1000000','olmpic');
ERROR 1064 (HY000): insert must provide ColumnList
mysql> insert into position(id,name,salary,city) values(1,'yangqian1','1000000','olmpic');
Query OK, 1 row affected (0.02 sec)
 OK!
mysql> insert into position(id,name,salary,city) values(2,'yangqian2','1000000','olmpic');
Query OK, 1 row affected (0.02 sec)
 OK!
mysql> select * from position;
+--------------------+-----------+---------+-----------+
| Id                 | name      | salary  | city      |
+--------------------+-----------+---------+-----------+
|                  2 | yangqian2 | 1000000 | olmpic    |
| 624565542031720448 | lisi1     | 1000000 | shanghai  |
| 624565542786695168 | lisi3     | 1000000 | shanghai  |
| 624565543113850880 | lisi5     | 1000000 | shanghai  |
| 624565543436812288 | lisi7     | 1000000 | shanghai  |
| 624565543730413568 | lisi9     | 1000000 | shanghai  |
| 624565543956905984 | lisi11    | 1000000 | shanghai  |
| 624565544183398400 | lisi13    | 1000000 | shanghai  |
| 624565544422473728 | lisi15    | 1000000 | shanghai  |
| 624565544728657920 | lisi17    | 1000000 | shanghai  |
| 624565544971927552 | lisi19    | 1000000 | shanghai  |
| 625037191625572352 | root1     | 1000000 | beijing   |
| 625390806554902528 | lucy      | 21000   | hangzhou  |
| 625399491796664320 | sky9      | 100000  | guangzhou |
|                  1 | yangqian1 | 1000000 | olmpic    |
| 624565542639894529 | lisi2     | 1000000 | shanghai  |
| 624565542954467329 | lisi4     | 1000000 | shanghai  |
| 624565543281623041 | lisi6     | 1000000 | shanghai  |
| 624565543592001537 | lisi8     | 1000000 | shanghai  |
| 624565543852048385 | lisi10    | 1000000 | shanghai  |
| 624565544057569281 | lisi12    | 1000000 | shanghai  |
| 624565544309227521 | lisi14    | 1000000 | shanghai  |
| 624565544581857281 | lisi16    | 1000000 | shanghai  |
| 624565544833515521 | lisi18    | 1000000 | shanghai  |
| 624565545101950977 | lisi20    | 1000000 | shanghai  |
| 625390705467981825 | tom       | 20000   | shanghai  |
| 625399491637280769 | sky4      | 100000  | guangzhou |
+--------------------+-----------+---------+-----------+
27 rows in set (0.01 sec)

查看下 database ，可以看到杨倩选手成功获得了东京奥运会首金，跑题了，可以看到 id 是按照模 2 进行的分库操作,分别添加到了库 lane1 和 lane2 里面了

分片规则

分片规则还是蛮多的，这里 rule 修改为 auto-sharding-long

<tableRule name="auto-sharding-long">
		<rule>
			<columns>id</columns>
			<algorithm>rang-long</algorithm>
		</rule>
	</tableRule>
<function name="rang-long"
			  class="io.mycat.route.function.AutoPartitionByLong">
		<property name="mapFile">autopartition-long.txt</property>
	</function>

修改 schema.xml 下的分片规则如下

<table name="position" primaryKey="id" dataNode="dn1,dn2" rule="auto-sharding-long" 
autoIncrement="true" fetchStoreNodeByJdbc="true">
</table>

再修改下 autopartition-long.txt 文件

因为我们只有两个数据库，这里注释掉 1000M-1500M

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
#1000M-1500M=2

再次重启 mycat

➜  bin ./mycat restart

再次测试添加

两条数据 id 分别为 500 万以上和 500 万以下

具体操作如下

➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
mysql> show databases;
+----------+
| DATABASE |
+----------+
| lane_db  |
+----------+
1 row in set (0.01 sec)
mysql> use lane_db
Database changed
mysql> insert into position(id,name,salary,city) values(1000,'yangqian1000','1000000'
Query OK, 1 row affected (0.07 sec)
 OK!

mysql> insert into position(id,name,salary,city) values(5000010,'yangqian500','1000000'
Query OK, 1 row affected (0.01 sec)
 OK!

mysql>

主键生成策略

0 表示使用本地文件方式；

1 表示使用数据库方式生成；

2 表示使用本地时间戳方式；

3 表示基于 ZK 与本地配置的分布式 ID 生成器；

4 表示使用 zookeeper 递增方式生成

0. 基于本地文件

修改 conf 下 sequence_conf.properties

#default global sequence
GLOBAL.HISIDS=
GLOBAL.MINID=10001
GLOBAL.MAXID=20000
GLOBAL.CURID=10000

# self define sequence
COMPANY.HISIDS=
COMPANY.MINID=1001
COMPANY.MAXID=2000
COMPANY.CURID=1000

POSITION.HISIDS=
POSITION.MINID=1001
POSITION.MAXID=2000
POSITION.CURID=1000

修改下

<property name="sequenceHandlerType">0</property> <!--0配置文件指定生成，1数据库生成-->

测试下

不写 id

mysql> insert into position(name,salary,city) values('yangqian724','1000000','olmpic');
mysql> select * from position;
+--------------------+--------------+---------+-----------+
| Id                 | name         | salary  | city      |
+--------------------+--------------+---------+-----------+
|                  2 | yangqian2    | 1000000 | olmpic    |
|               1000 | yangqian1000 | 1000000 | olmpic    |
|               1001 | yangqian724  | 1000000 | olmpic    |

可以看到的确是按照我们配置的 position 最小值 1001

指定 id 为 global 配置的生成


mysql> insert into position(id,name,salary,city) values('next value for MYCATSEQ_GLOBAL','yangqian724champion','1000000','olmpic');

mysql> select * from position;                                              
+--------------------+---------------------+---------+-----------+
| Id                 | name                | salary  | city      |
+--------------------+---------------------+---------+-----------+
|                  2 | yangqian2           | 1000000 | olmpic    |
|               1000 | yangqian1000        | 1000000 | olmpic    |
|               1001 | yangqian724         | 1000000 | olmpic    |
|              10001 | yangqian724champion | 1000000 | olmpic    |

可以看到 id 变成了 10001

1. 基于数据库生成

首先指定 database

打开 sequence_db_conf.properties

添加修改内容

#sequence stored in datanode
GLOBAL=dn1
COMPANY=dn1
CUSTOMER=dn1
ORDERS=dn1
POSITION=dn1

打开初始化 dbseq.sql

在指定的 lane1 库中执行

DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (  name VARCHAR(64) NOT NULL,  current_value BIGINT(20) NOT NULL,  increment INT NOT NULL DEFAULT 1, PRIMARY KEY (name) ) ENGINE=InnoDB;

-- ----------------------------
-- Function structure for `mycat_seq_currval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE retval VARCHAR(64);
    SET retval="-1,0";
    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 FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE retval VARCHAR(64);
    DECLARE val BIGINT;
    DECLARE inc INT;
    DECLARE seq_lock INT;
    set val = -1;
    set inc = 0;
    SET seq_lock = -1;
    SELECT GET_LOCK(seq_name, 15) into seq_lock;
    if seq_lock = 1 then
      SELECT current_value + increment, increment INTO val, inc FROM MYCAT_SEQUENCE WHERE name = seq_name for update;
      if val != -1 then
          UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name;
      end if;
      SELECT RELEASE_LOCK(seq_name) into seq_lock;
    end if;
    SELECT concat(CAST((val - inc + 1) as CHAR),",",CAST(inc as CHAR)) INTO retval;
    RETURN retval;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for `mycat_seq_setvals`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_nextvals`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_nextvals`(seq_name VARCHAR(64), count INT) RETURNS VARCHAR(64) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE retval VARCHAR(64);
    DECLARE val BIGINT;
    DECLARE seq_lock INT;
    SET val = -1;
    SET seq_lock = -1;
    SELECT GET_LOCK(seq_name, 15) into seq_lock;
    if seq_lock = 1 then
        SELECT current_value + count INTO val FROM MYCAT_SEQUENCE WHERE name = seq_name for update;
        IF val != -1 THEN
            UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name;
        END IF;
        SELECT RELEASE_LOCK(seq_name) into seq_lock;
    end if;
    SELECT CONCAT(CAST((val - count + 1) as CHAR), ",", CAST(val as CHAR)) INTO retval;
    RETURN retval;
END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for `mycat_seq_setval`
-- ----------------------------
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(64), value BIGINT) RETURNS varchar(64) CHARSET latin1
    DETERMINISTIC
BEGIN
    DECLARE retval VARCHAR(64);
    DECLARE inc INT;
    SET inc = 0;
    SELECT increment INTO inc FROM MYCAT_SEQUENCE WHERE name = seq_name;
    UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name;
    SELECT concat(CAST(value as CHAR),",",CAST(inc as CHAR)) INTO retval;
    RETURN retval;
END
;;
DELIMITER ;

INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 1, 1);
-- 自己添加的内容
INSERT INTO MYCAT_SEQUENCE VALUES ('POSITION', 21, 10);

修改下 server.xml 数据库主键生成策略为 1

<property name="sequenceHandlerType">1</property> <!--0配置文件指定生成，1数据库生成-->

重启 mycat

➜  bin ./mycat restart

执行操作

 insert into position(name,salary,city) values('gold','1000000','olmpic');
mysql> select * from position; 
+--------------------+---------------------+---------+-----------+
| Id                 | name                | salary  | city      |
+--------------------+---------------------+---------+-----------+
|                  2 | yangqian2           | 1000000 | olmpic    |
|                 22 | gold                | 1000000 | olmpic    |


mysql> insert into position(name,salary,city) values('gold2','1000000','olmpic');
mysql> select * from position; 
+--------------------+---------------------+---------+-----------+
| Id                 | name                | salary  | city      |
+--------------------+---------------------+---------+-----------+
|                  2 | yangqian2           | 1000000 | olmpic    |
|                 22 | gold                | 1000000 | olmpic    |
|                 23 | gold2               | 1000000 | olmpic    |

因为步进是 10，下次连接再测试

mysql> use lane_db
mysql> insert into position(name,salary,city) values('medal1','1000000','olmpic');
Query OK, 1 row affected (0.14 sec)
mysql> select * from position;
+--------------------+---------------------+---------+-----------+
| Id                 | name                | salary  | city      |
+--------------------+---------------------+---------+-----------+
|                  2 | yangqian2           | 1000000 | olmpic    |
|                 22 | gold                | 1000000 | olmpic    |
|                 32 | medal1              | 1000000 | olmpic    |

2. 基于时间戳的方式

修改下 server.xml 数据库主键生成策略为 2

<property name="sequenceHandlerType">2</property> <!--0配置文件指定生成，1数据库生成，2时间戳-->

因为时间戳生成的 id 比较大，500 万都 hold 不住，需要再次改写 schema.xml 分片规则为 rang-long

<table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true" fetchStoreNodeByJdbc="true">
		
</table>

重启下 mycat

➜  bin ./mycat restart

具体测试


mysql> insert into position(name,salary,city) values('medal2','1000000','olmpic');
Query OK, 1 row affected (0.02 sec)
 OK!

mysql> select * from position;
+---------------------+---------------------+---------+-----------+
| Id                  | name                | salary  | city      |
+---------------------+---------------------+---------+-----------+
|                   2 | yangqian2           | 1000000 | olmpic    |
|  625399491637280769 | sky4                | 100000  | guangzhou |
| 1418830095157694465 | medal2              | 1000000 | olmpic    |
+---------------------+---------------------+---------+-----------+
38 rows in set (0.01 sec)

可以看到 id 是时间戳的方式，并远远大于 500M

全局表的创建

分别在 lane1 和 lane2 库创建 city 表

CREATE TABLE `city` (
  `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `province` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修改下 schema.xml 添加表 city

<table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true"  >
</table>
<table name="city" primaryKey="id" dataNode="dn1,dn2"   autoIncrement="true" type="global"   ></table>

再次重启

➜  bin ./mycat restart

具体测试操作

➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
mysql> show databases;
+----------+
| DATABASE |
+----------+
| lane_db  |
+----------+
1 row in set (0.00 sec)
mysql> use lane_db
Database changed
mysql> show tables;
+-------------------+
| Tables in lane_db |
+-------------------+
| city              |
| position          |
+-------------------+
2 rows in set (0.01 sec)

mysql> select * from city;
+--------------------+---------+----------+
| Id                 | name    | province |
+--------------------+---------+----------+
| 624572431226372096 | beijing | beijing  |
+--------------------+---------+----------+
1 row in set (0.08 sec)

mysql> insert into city(id,name,province) values(2021,'tokyo','japan');
Query OK, 1 row affected (0.05 sec)
 OK!

mysql> select * from city;
+--------------------+---------+----------+
| Id                 | name    | province |
+--------------------+---------+----------+
|               2021 | tokyo   | japan    |
| 624572431226372096 | beijing | beijing  |
+--------------------+---------+----------+
2 rows in set (0.01 sec)
mysql>

可以看到两个库 lane1 和 lane2 都有相同的数据

主从分离

准备工作

在远程创建库 lane1，表 city ,插入数据

mysql> CREATE TABLE `city` (
    ->   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(256) DEFAULT NULL,
    ->   `province` varchar(256) DEFAULT NULL,
    ->   PRIMARY KEY (`Id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into city(id,name,province) values(2021,'tokyo-5','japan-5');
Query OK, 1 row affected (0.01 sec)

主库本地 localhost

从库虚拟机 172.16.94.5

balance 参数：

use0 ：所有读操作都发送到当前可用的 writeHost

use1 ：所有读操作都随机发送到 readHost 和 stand by writeHost

use2 ：所有读操作都随机发送到 writeHost 和 readHost

use3 ：所有读操作都随机发送到 writeHost 对应的 readHost 上，但是 writeHost 不负担读压力

writeType 参数：

use0 ：所有写操作都发送到可用的 writeHost

use1 ：所有写操作都随机发送到 readHost

use2 ：所有写操作都随机发送到 writeHost，readHost2

修改下 schema.xml 文件

readHost 只能放在 writeHost 里面才行，可以 1 个 writeHost 里放多个 readHost，再加一个 writeHost 做备用

我们是对表来进行读写分离的，也可以不写表则是对库进行读写分离

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="lane_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<!-- auto sharding by id (long) -->
		<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
		<table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true">
		</table>
		<table name="city" primaryKey="id" dataNode="dn3" ruleRequired="false"
			autoIncrement="true">
		</table>
		<!-- <table name="city" primaryKey="id" dataNode="dn1,dn2" type="global"  
			autoIncrement="true">
		</table> -->
		 
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="lane1" />
	<dataNode name="dn2" dataHost="localhost1" database="lane2" />
	<dataNode name="dn3" dataHost="localhost2" database="lane1" />
  
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
		</writeHost>
		 
	</dataHost>
	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
		<readHost host="hostS1" url="jdbc:mysql://172.16.94.5:3306" user="root"
				   password="root">
		</readHost>
		</writeHost>
	</dataHost>

</mycat:schema>

重启 mycat

➜  bin ./mycat restart

再次测试下,可以看到写入的库和读取的库数据的不同 2

➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
mysql> select * from city;
+------+---------+----------+
| Id   | name    | province |
+------+---------+----------+
| 2021 | tokyo-5 | japan-5  |
+------+---------+----------+
1 row in set (0.11 sec)

mysql> insert into city(id,name,province) values(2022,'beijing','beijing');
Query OK, 1 row affected (0.03 sec)
mysql> select * from city;
+------+---------+----------+
| Id   | name    | province |
+------+---------+----------+
| 2021 | tokyo-5 | japan-5  |
+------+---------+----------+
1 row in set (0.01 sec)

主从分离高可用

如果 readHost 在 writeHost 内部，当出现外层 writeHost 异常的时候完全不可用，此时需要修改下

修改下 schema.xml 文件

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="lane_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
		<!-- auto sharding by id (long) -->
		<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
		<table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true">
		</table>
		<table name="city" primaryKey="id" dataNode="dn3" ruleRequired="false"
			autoIncrement="true">
		</table>
		<!-- <table name="city" primaryKey="id" dataNode="dn1,dn2" type="global"  
			autoIncrement="true">
		</table> -->
		 
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="lane1" />
	<dataNode name="dn2" dataHost="localhost1" database="lane2" />
	<dataNode name="dn3" dataHost="localhost2" database="lane1" />
  
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
		</writeHost>
		 
	</dataHost>
	<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
	
		</writeHost>
		<writeHost host="hostS1" url="jdbc:mysql://172.16.94.5:3306" user="root"
				   password="root">
		</writeHost>
	</dataHost>

</mycat:schema>

关掉主库

➜  bin sudo mysql.server stop;
Password:
Shutting down MySQL
.... SUCCESS!
➜  bin

再次测试，从库依然可以使用


➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
mysql> use lane_db;
Database changed
mysql> show tables;
+-------------------+
| Tables in lane_db |
+-------------------+
| city              |
| position          |
+-------------------+
2 rows in set (0.00 sec)

mysql> select * from city;
+------+---------+----------+
| Id   | name    | province |
+------+---------+----------+
| 2021 | tokyo-5 | japan-5  |
+------+---------+----------+
1 row in set (0.11 sec)
mysql> insert into city(id,name,province) values(2024,'chongqing','chongqing');
Query OK, 1 row affected (0.01 sec)
mysql> select * from city;
+------+-----------+-----------+
| Id   | name      | province  |
+------+-----------+-----------+
| 2021 | tokyo-5   | japan-5   |
| 2024 | chongqing | chongqing |
+------+-----------+-----------+
2 rows in set (0.00 sec)

如果重新开启主库，则原先的主库变成了从库了

➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6.7.5-release-20210616151418 MyCat Server (OpenCloudDB)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from city;
+--------------------+---------+----------+
| Id                 | name    | province |
+--------------------+---------+----------+
|               2021 | tokyo   | japan    |
|               2022 | beijing | beijing  |
|               2023 | tianjin | tianjing |
| 624572431226372096 | beijing | beijing  |
+--------------------+---------+----------+
4 rows in set (0.00 sec)

mysql> insert into city(id,name,province) values(2025,'shenzhen','shenzhen');
Query OK, 1 row affected (0.01 sec)
 OK!

mysql> select * from city;
+--------------------+---------+----------+
| Id                 | name    | province |
+--------------------+---------+----------+
|               2021 | tokyo   | japan    |
|               2022 | beijing | beijing  |
|               2023 | tianjin | tianjing |
| 624572431226372096 | beijing | beijing  |
+--------------------+---------+----------+
4 rows in set (0.01 sec)

强制路由

修改下 schema.xml 下的 localhost2

<dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
				   <readHost host="hostS1" url="jdbc:mysql://172.16.94.5:3306" user="root"
				   password="root"/>
		</writeHost>
		<writeHost host="hostS2" url="jdbc:mysql://172.16.94.5:3306" user="root"
				   password="root">
		</writeHost>
	</dataHost>

重启 mycat

bin ./mycat restart

强制路由到主库

/*!mycat:db_type=master*/ select * from city;

强制路由到从库

mysql> /*!mycat:db_type=slave*/ select * from city;

具体操作如下

可以看到分别查询出了主库和从库的信息

➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
mysql> /*!mycat:db_type=master*/ select * from city;
+--------------------+---------+----------+
| Id                 | name    | province |
+--------------------+---------+----------+
|               2021 | tokyo   | japan    |
|               2022 | beijing | beijing  |
|               2023 | tianjin | tianjing |
| 624572431226372096 | beijing | beijing  |
+--------------------+---------+----------+
4 rows in set (0.09 sec)
mysql> /*!mycat:db_type=slave*/ select * from city;
+------+-----------+-----------+
| Id   | name      | province  |
+------+-----------+-----------+
| 2021 | tokyo-5   | japan-5   |
| 2024 | chongqing | chongqing |
| 2025 | shenzhen  | shenzhen  |
+------+-----------+-----------+
3 rows in set (0.00 sec)

mysql>

主从延时切换

避免读取到未同步的从库数据，在未同步的时候读取主库数据，同步之后自动切换读取从库数据

switchType 参数：

-1：表示不自动切换

1 ：表示自动切换

2 ：基于 MySQL 主从同步状态决定是否切换

3 ：基于 MySQL cluster 集群切换机制

对于非集群

修改 schema.xml

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"

dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">

<heartbeat>show slave status </heartbeat> <!-- can have multi write hosts -->

<writeHost host="M1" url="localhost:3306" user="root" password="root">

</writeHost>

<writeHost host="S1" url="localhost:3316" user="root"

</dataHost>

对于集群

修改 schema.xml 文件

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"

dbType="mysql" dbDriver="native" switchType="3" >

<heartbeat> show status like ‘wsrep%’</heartbeat>

<writeHost host="M1" url="localhost:3306" user="root"password="root">

</writeHost>

<writeHost host="S1"url="localhost:3316"user="root"password="root" >

</writeHost> </dataHost>

Mycat 事务使用

支持 xa 弱事务单库内部可以保证事务的完整性，如果跨库事务，在执行的时候任何分片出错，可以保证所有分片回滚。

具体操作

#XA 事务需要设置手动提交
set autocommit=0;
#使用该命令开启 XA 事务
set xa=on;
#执行相应的 SQL 语句部分
insert into city(id,name,province) values(200,'chengdu','sichuan'); 
update position set salary='300000' where id<5;
#提交或回滚事务
commit； rollback;

实际执行，既是出现异常之后，commit 依然数据回滚


mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> set xa=on;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into city(id,name,province) values(200,'chengdu','sichuan');
Query OK, 1 row affected (0.01 sec)
 OK!

mysql> update position set salary='300000' where id<5;
Query OK, 2 rows affected (0.04 sec)
 OK!

mysql> insert into city(id,name,province) values(200,'chengdu3','sichuan3');
ERROR 1062 (HY000): Duplicate entry '200' for key 'PRIMARY'
mysql> commit；
    -> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> /*!mycat:db_type=master*/ select * from city;
+--------------------+---------+----------+
| Id                 | name    | province |
+--------------------+---------+----------+
|               2021 | tokyo   | japan    |
|               2022 | beijing | beijing  |
|               2023 | tianjin | tianjing |
| 624572431226372096 | beijing | beijing  |
+--------------------+---------+----------+
4 rows in set (0.01 sec)

mysql> /*!mycat:db_type=slave*/ select * from city;
+------+-----------+-----------+
| Id   | name      | province  |
+------+-----------+-----------+
| 2021 | tokyo-5   | japan-5   |
| 2024 | chongqing | chongqing |
| 2025 | shenzhen  | shenzhen  |
+------+-----------+-----------+
3 rows in set (0.01 sec)

mysql>

杜莱

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mycat 实战

文章目录数据库信息Mycat 下载Mycat 配置Mycat 启动Mycat 测试测试下 test 用户测试下 root 用户分片规则主键生成策略0. 基于本地文件1. 基于数据库生成2. 基于时间戳的方式全局表的创建主从分离主从分离高可用强制路由主从延时切换Mycat 事务使用数据库信息创建 db lan1 和 lane2 分别创建表 positionCREATE TABLE `position` ( `Id` bigint(11) NOT NULL AUTO_INCREMENT, `na
复制链接

扫一扫