数据库信息

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

  2. CREATE TABLE `position` (
  3.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(256) DEFAULT NULL,
  5.   `salary` varchar(50) DEFAULT NULL,
  6.   `city` varchar(256) DEFAULT NULL,
  7.   PRIMARY KEY (`Id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Mycat 下载

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

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

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

下载 Mycat-server 工具包

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

Mycat 实战 - 图1

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

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

Mycat 配置

server.xml

  1. <!--只是修改了下主键生成规则从 1 改成了 0-->
  2. <property name="sequenceHandlerType">0</property>
  3. <!--用户改成了root和test-->
  4. <user name="root" defaultAccount="true">
  5.         <property name="password">root</property>
  6.         <property name="schemas">lane_db</property>
  7.         <property name="defaultSchema">lane_db</property>
  9.     </user>
  11.     <user name="test">
  12.         <property name="password">test</property>
  13.         <property name="schemas">lane_db</property>
  14.         <property name="readOnly">true</property>
  15.         <property name="defaultSchema">lane_db</property>
  16.     </user>

完整版 server.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  3. <!DOCTYPE mycat:server SYSTEM "server.dtd">
  4. <mycat:server xmlns:mycat="http://io.mycat/">
  5.     <system>
  6.     <property name="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->
  7.     <property name="ignoreUnknownCommand">0</property><!-- 0遇上没有实现的报文(Unknown command:),就会报错、1为忽略该报文，返回ok报文。
  8.     在某些mysql客户端存在客户端已经登录的时候还会继续发送登录报文,mycat会报错,该设置可以绕过这个错误-->
  9.     <property name="useHandshakeV10">1</property>
  10.     <property name="removeGraveAccent">1</property>
  11.     <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
  12.     <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
  13.     <property name="sqlExecuteTimeout">300</property>  <!-- SQL 执行超时 单位:秒-->
  14.         <property name="sequenceHandlerType">0</property> <!--0配置文件指定生成，1数据库生成-->
  15.     <!--<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
  16.     INSERT INTO `travelrecord` (`id`,user_id) VALUES ('next value for MYCATSEQ_GLOBAL',"xxx");
  17.     -->
  18.     <!--必须带有MYCATSEQ_或者 mycatseq_进入序列匹配流程 注意MYCATSEQ_有空格的情况-->
  19.     <property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
  20.     <property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->
  21.     <property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
  22.       <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
  23.         <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
  24.     <!-- <property name="processorBufferChunk">40960</property> -->
  25.     <!-- 
  26.     <property name="processors">1</property> 
  27.     <property name="processorExecutor">32</property> 
  28.      -->
  29.         <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena | type 2 NettyBufferPool -->
  30.         <property name="processorBufferPoolType">0</property>
  32.         <!--分布式事务开关，0为不过滤分布式事务，1为过滤分布式事务（如果分布式事务内只涉及全局表，则不过滤），2为不过滤分布式事务,但是记录分布式事务日志-->
  33.         <property name="handleDistributedTransactions">0</property>
  34.         <property name="useOffHeapForMerge">0</property>
  36.         <!--
  37.             单位为m
  38.         -->
  39.         <property name="memoryPageSize">64k</property>
  41.         <!--
  42.             单位为k
  43.         -->
  44.         <property name="spillsFileBufferSize">1k</property>
  46.         <property name="useStreamOutput">0</property>
  48.         <!--
  49.             单位为m
  50.         -->
  51.         <property name="systemReserveMemorySize">384m</property>
  54.         <!--是否采用zookeeper协调切换  -->
  55.         <property name="useZKSwitch">false</property>
  57.         <!-- XA Recovery Log日志路径 -->
  58.         <!--<property name="XARecoveryLogBaseDir">./</property>-->
  60.         <!-- XA Recovery Log日志名称 -->
  61.         <!--<property name="XARecoveryLogBaseName">tmlog</property>-->
  62.         <!--如果为 true的话 严格遵守隔离级别,不会在仅仅只有select语句的时候在事务中切换连接-->
  63.         <property name="strictTxIsolation">false</property>
  64.         <!--如果为0的话,涉及多个DataNode的catlet任务不会跨线程执行-->
  65.         <property name="parallExecute">0</property>
  66.     </system>
  68.     <user name="root" defaultAccount="true">
  69.         <property name="password">root</property>
  70.         <property name="schemas">lane_db</property>
  71.         <property name="defaultSchema">lane_db</property>
  73.     </user>
  75.     <user name="test">
  76.         <property name="password">test</property>
  77.         <property name="schemas">lane_db</property>
  78.         <property name="readOnly">true</property>
  79.         <property name="defaultSchema">lane_db</property>
  80.     </user>
  82. </mycat:server>

schema.xml 修改后如下

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  5.     <schema name="lane_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
  6.         <!-- auto sharding by id (long) -->
  7.         <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
  8. <!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
  9.         <table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true" fetchStoreNodeByJdbc="true">
  10.             <!-- <childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable> -->
  11.         </table>
  13.     </schema>
  14.     <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  15.         /> -->
  16.     <dataNode name="dn1" dataHost="localhost1" database="lane1" />
  17.     <dataNode name="dn2" dataHost="localhost1" database="lane2" />
  19.     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  20.               writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
  21.         <heartbeat>select user()</heartbeat>
  22.         <!-- can have multi write hosts -->
  23.         <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
  24.                    password="root">
  25.         </writeHost>
  26.         <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
  27.     </dataHost>
  29. </mycat:schema>

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

  2.     <tableRule name="mod-long">
  3.         <rule>
  4.             <columns>id</columns>
  5.             <algorithm>mod-long</algorithm>
  6.         </rule>
  7.     </tableRule>
  9.     <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
  10.         <!-- how many data nodes -->
  11.         <property name="count">2</property>
  12.     </function>

Mycat 启动

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

Mycat 实战 - 图2

Mycat 测试

测试下 test 用户

访问 mysql

  1. mysql -utest -ptest -h127.0.0.1 -P8066

具体操作

  1.  ~ mysql -utest -h 127.0.0.1   -P 8066 -p
  2. Enter password:
  3. mysql> show databases;
  4. +----------+
  5. | DATABASE |
  6. +----------+
  7. | lane_db  |
  8. +----------+
  9. 1 row in set (0.01 sec)
  11. mysql> use lane_db
  12. Database changed
  13. mysql> show tables;
  14. +-------------------+
  15. | Tables in lane_db |
  16. +-------------------+
  17. | position          |
  18. +-------------------+
  19. 1 row in set (0.00 sec)
  21. mysql> select * from position;
  22. +--------------------+--------+---------+-----------+
  23. | Id                 | name   | salary  | city      |
  24. +--------------------+--------+---------+-----------+
  25. | 624565542031720448 | lisi1  | 1000000 | shanghai  |
  26. | 624565542786695168 | lisi3  | 1000000 | shanghai  |
  27. | 624565543113850880 | lisi5  | 1000000 | shanghai  |
  28. | 624565543436812288 | lisi7  | 1000000 | shanghai  |
  29. | 624565543730413568 | lisi9  | 1000000 | shanghai  |
  30. | 624565543956905984 | lisi11 | 1000000 | shanghai  |
  31. | 624565544183398400 | lisi13 | 1000000 | shanghai  |
  32. | 624565544422473728 | lisi15 | 1000000 | shanghai  |
  33. | 624565544728657920 | lisi17 | 1000000 | shanghai  |
  34. | 624565544971927552 | lisi19 | 1000000 | shanghai  |
  35. | 625037191625572352 | root1  | 1000000 | beijing   |
  36. | 625390806554902528 | lucy   | 21000   | hangzhou  |
  37. | 625399491796664320 | sky9   | 100000  | guangzhou |
  38. | 624565542639894529 | lisi2  | 1000000 | shanghai  |
  39. | 624565542954467329 | lisi4  | 1000000 | shanghai  |
  40. | 624565543281623041 | lisi6  | 1000000 | shanghai  |
  41. | 624565543592001537 | lisi8  | 1000000 | shanghai  |
  42. | 624565543852048385 | lisi10 | 1000000 | shanghai  |
  43. | 624565544057569281 | lisi12 | 1000000 | shanghai  |
  44. | 624565544309227521 | lisi14 | 1000000 | shanghai  |
  45. | 624565544581857281 | lisi16 | 1000000 | shanghai  |
  46. | 624565544833515521 | lisi18 | 1000000 | shanghai  |
  47. | 624565545101950977 | lisi20 | 1000000 | shanghai  |
  48. | 625390705467981825 | tom    | 20000   | shanghai  |
  49. | 625399491637280769 | sky4   | 100000  | guangzhou |
  50. +--------------------+--------+---------+-----------+
  51. 25 rows in set (0.08 sec)
  53. mysql> delete from  position where name ='tom';
  54. ERROR 1495 (HY000): User readonly
  55. mysql>

测试下 root 用户

具体操作

  1. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  2. mysql> use lane_db;
  3. Database changed
  4. mysql> insert into position values(1,'yangqian1','1000000','olmpic');
  5. ERROR 1064 (HY000): insert must provide ColumnList
  6. mysql> insert into position(id,name,salary,city) values(1,'yangqian1','1000000','olmpic');
  7. Query OK, 1 row affected (0.02 sec)
  8.  OK!
  9. mysql> insert into position(id,name,salary,city) values(2,'yangqian2','1000000','olmpic');
  10. Query OK, 1 row affected (0.02 sec)
  11.  OK!
  12. mysql> select * from position;
  13. +--------------------+-----------+---------+-----------+
  14. | Id                 | name      | salary  | city      |
  15. +--------------------+-----------+---------+-----------+
  16. |                  2 | yangqian2 | 1000000 | olmpic    |
  17. | 624565542031720448 | lisi1     | 1000000 | shanghai  |
  18. | 624565542786695168 | lisi3     | 1000000 | shanghai  |
  19. | 624565543113850880 | lisi5     | 1000000 | shanghai  |
  20. | 624565543436812288 | lisi7     | 1000000 | shanghai  |
  21. | 624565543730413568 | lisi9     | 1000000 | shanghai  |
  22. | 624565543956905984 | lisi11    | 1000000 | shanghai  |
  23. | 624565544183398400 | lisi13    | 1000000 | shanghai  |
  24. | 624565544422473728 | lisi15    | 1000000 | shanghai  |
  25. | 624565544728657920 | lisi17    | 1000000 | shanghai  |
  26. | 624565544971927552 | lisi19    | 1000000 | shanghai  |
  27. | 625037191625572352 | root1     | 1000000 | beijing   |
  28. | 625390806554902528 | lucy      | 21000   | hangzhou  |
  29. | 625399491796664320 | sky9      | 100000  | guangzhou |
  30. |                  1 | yangqian1 | 1000000 | olmpic    |
  31. | 624565542639894529 | lisi2     | 1000000 | shanghai  |
  32. | 624565542954467329 | lisi4     | 1000000 | shanghai  |
  33. | 624565543281623041 | lisi6     | 1000000 | shanghai  |
  34. | 624565543592001537 | lisi8     | 1000000 | shanghai  |
  35. | 624565543852048385 | lisi10    | 1000000 | shanghai  |
  36. | 624565544057569281 | lisi12    | 1000000 | shanghai  |
  37. | 624565544309227521 | lisi14    | 1000000 | shanghai  |
  38. | 624565544581857281 | lisi16    | 1000000 | shanghai  |
  39. | 624565544833515521 | lisi18    | 1000000 | shanghai  |
  40. | 624565545101950977 | lisi20    | 1000000 | shanghai  |
  41. | 625390705467981825 | tom       | 20000   | shanghai  |
  42. | 625399491637280769 | sky4      | 100000  | guangzhou |
  43. +--------------------+-----------+---------+-----------+
  44. 27 rows in set (0.01 sec)

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

Mycat 实战 - 图3

分片规则

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

  1. <tableRule name="auto-sharding-long">
  2.         <rule>
  3.             <columns>id</columns>
  4.             <algorithm>rang-long</algorithm>
  5.         </rule>
  6.     </tableRule>
  7. <function name="rang-long"
  8.               class="io.mycat.route.function.AutoPartitionByLong">
  9.         <property name="mapFile">autopartition-long.txt</property>
  10.     </function>

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

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

Mycat 实战 - 图4

再修改下 autopartition-long.txt 文件

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

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

再次重启 mycat

  1. ➜  bin ./mycat restart

再次测试添加

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

具体操作如下

  1. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  2. mysql> show databases;
  3. +----------+
  4. | DATABASE |
  5. +----------+
  6. | lane_db  |
  7. +----------+
  8. 1 row in set (0.01 sec)
  9. mysql> use lane_db
  10. Database changed
  11. mysql> insert into position(id,name,salary,city) values(1000,'yangqian1000','1000000'
  12. Query OK, 1 row affected (0.07 sec)
  13.  OK!
  15. mysql> insert into position(id,name,salary,city) values(5000010,'yangqian500','1000000'
  16. Query OK, 1 row affected (0.01 sec)
  17.  OK!
  19. mysql>

Mycat 实战 - 图5

主键生成策略

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

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

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

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

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

0. 基于本地文件

修改 conf 下 sequence_conf.properties

  1. #default global sequence
  2. GLOBAL.HISIDS=
  3. GLOBAL.MINID=10001
  4. GLOBAL.MAXID=20000
  5. GLOBAL.CURID=10000
  7. # self define sequence
  8. COMPANY.HISIDS=
  9. COMPANY.MINID=1001
  10. COMPANY.MAXID=2000
  11. COMPANY.CURID=1000
  13. POSITION.HISIDS=
  14. POSITION.MINID=1001
  15. POSITION.MAXID=2000
  16. POSITION.CURID=1000

修改下

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

测试下

不写 id

  1. mysql> insert into position(name,salary,city) values('yangqian724','1000000','olmpic');
  2. mysql> select * from position;
  3. +--------------------+--------------+---------+-----------+
  4. | Id                 | name         | salary  | city      |
  5. +--------------------+--------------+---------+-----------+
  6. |                  2 | yangqian2    | 1000000 | olmpic    |
  7. |               1000 | yangqian1000 | 1000000 | olmpic    |
  8. |               1001 | yangqian724  | 1000000 | olmpic    |

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

指定 id 为 global 配置的生成

  2. mysql> insert into position(id,name,salary,city) values('next value for MYCATSEQ_GLOBAL','yangqian724champion','1000000','olmpic');
  4. mysql> select * from position;                                              
  5. +--------------------+---------------------+---------+-----------+
  6. | Id                 | name                | salary  | city      |
  7. +--------------------+---------------------+---------+-----------+
  8. |                  2 | yangqian2           | 1000000 | olmpic    |
  9. |               1000 | yangqian1000        | 1000000 | olmpic    |
  10. |               1001 | yangqian724         | 1000000 | olmpic    |
  11. |              10001 | yangqian724champion | 1000000 | olmpic    |

可以看到 id 变成了 10001

1. 基于数据库生成

首先指定 database

打开 sequence_db_conf.properties

添加修改内容

  1. #sequence stored in datanode
  2. GLOBAL=dn1
  3. COMPANY=dn1
  4. CUSTOMER=dn1
  5. ORDERS=dn1
  6. POSITION=dn1

打开初始化 dbseq.sql

在指定的 lane1 库中执行

  1. DROP TABLE IF EXISTS MYCAT_SEQUENCE;
  2. 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;
  4. -- ----------------------------
  5. -- Function structure for `mycat_seq_currval`
  6. -- ----------------------------
  7. DROP FUNCTION IF EXISTS `mycat_seq_currval`;
  8. DELIMITER ;;
  9. CREATE FUNCTION `mycat_seq_currval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1
  10.     DETERMINISTIC
  11. BEGIN
  12.     DECLARE retval VARCHAR(64);
  13.     SET retval="-1,0";
  14.     SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval FROM MYCAT_SEQUENCE  WHERE name = seq_name;
  15.     RETURN retval ;
  16. END
  17. ;;
  18. DELIMITER ;
  20. -- ----------------------------
  21. -- Function structure for `mycat_seq_nextval`
  22. -- ----------------------------
  23. DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
  24. DELIMITER ;;
  25. CREATE FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(64)) RETURNS varchar(64) CHARSET latin1
  26.     DETERMINISTIC
  27. BEGIN
  28.     DECLARE retval VARCHAR(64);
  29.     DECLARE val BIGINT;
  30.     DECLARE inc INT;
  31.     DECLARE seq_lock INT;
  32.     set val = -1;
  33.     set inc = 0;
  34.     SET seq_lock = -1;
  35.     SELECT GET_LOCK(seq_name, 15) into seq_lock;
  36.     if seq_lock = 1 then
  37.       SELECT current_value + increment, increment INTO val, inc FROM MYCAT_SEQUENCE WHERE name = seq_name for update;
  38.       if val != -1 then
  39.           UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name;
  40.       end if;
  41.       SELECT RELEASE_LOCK(seq_name) into seq_lock;
  42.     end if;
  43.     SELECT concat(CAST((val - inc + 1) as CHAR),",",CAST(inc as CHAR)) INTO retval;
  44.     RETURN retval;
  45. END
  46. ;;
  47. DELIMITER ;
  49. -- ----------------------------
  50. -- Function structure for `mycat_seq_setvals`
  51. -- ----------------------------
  52. DROP FUNCTION IF EXISTS `mycat_seq_nextvals`;
  53. DELIMITER ;;
  54. CREATE FUNCTION `mycat_seq_nextvals`(seq_name VARCHAR(64), count INT) RETURNS VARCHAR(64) CHARSET latin1
  55.     DETERMINISTIC
  56. BEGIN
  57.     DECLARE retval VARCHAR(64);
  58.     DECLARE val BIGINT;
  59.     DECLARE seq_lock INT;
  60.     SET val = -1;
  61.     SET seq_lock = -1;
  62.     SELECT GET_LOCK(seq_name, 15) into seq_lock;
  63.     if seq_lock = 1 then
  64.         SELECT current_value + count INTO val FROM MYCAT_SEQUENCE WHERE name = seq_name for update;
  65.         IF val != -1 THEN
  66.             UPDATE MYCAT_SEQUENCE SET current_value = val WHERE name = seq_name;
  67.         END IF;
  68.         SELECT RELEASE_LOCK(seq_name) into seq_lock;
  69.     end if;
  70.     SELECT CONCAT(CAST((val - count + 1) as CHAR), ",", CAST(val as CHAR)) INTO retval;
  71.     RETURN retval;
  72. END
  73. ;;
  74. DELIMITER ;
  76. -- ----------------------------
  77. -- Function structure for `mycat_seq_setval`
  78. -- ----------------------------
  79. DROP FUNCTION IF EXISTS `mycat_seq_setval`;
  80. DELIMITER ;;
  81. CREATE FUNCTION `mycat_seq_setval`(seq_name VARCHAR(64), value BIGINT) RETURNS varchar(64) CHARSET latin1
  82.     DETERMINISTIC
  83. BEGIN
  84.     DECLARE retval VARCHAR(64);
  85.     DECLARE inc INT;
  86.     SET inc = 0;
  87.     SELECT increment INTO inc FROM MYCAT_SEQUENCE WHERE name = seq_name;
  88.     UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name;
  89.     SELECT concat(CAST(value as CHAR),",",CAST(inc as CHAR)) INTO retval;
  90.     RETURN retval;
  91. END
  92. ;;
  93. DELIMITER ;
  95. INSERT INTO MYCAT_SEQUENCE VALUES ('GLOBAL', 1, 1);
  96. -- 自己添加的内容
  97. INSERT INTO MYCAT_SEQUENCE VALUES ('POSITION', 21, 10);

Mycat 实战 - 图6

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

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

重启 mycat

  1. ➜  bin ./mycat restart

执行操作

  1.  insert into position(name,salary,city) values('gold','1000000','olmpic');
  2. mysql> select * from position; 
  3. +--------------------+---------------------+---------+-----------+
  4. | Id                 | name                | salary  | city      |
  5. +--------------------+---------------------+---------+-----------+
  6. |                  2 | yangqian2           | 1000000 | olmpic    |
  7. |                 22 | gold                | 1000000 | olmpic    |
  10. mysql> insert into position(name,salary,city) values('gold2','1000000','olmpic');
  11. mysql> select * from position; 
  12. +--------------------+---------------------+---------+-----------+
  13. | Id                 | name                | salary  | city      |
  14. +--------------------+---------------------+---------+-----------+
  15. |                  2 | yangqian2           | 1000000 | olmpic    |
  16. |                 22 | gold                | 1000000 | olmpic    |
  17. |                 23 | gold2               | 1000000 | olmpic    |

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

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

2. 基于时间戳的方式

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

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

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

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

重启下 mycat

  1. ➜  bin ./mycat restart

具体测试

  2. mysql> insert into position(name,salary,city) values('medal2','1000000','olmpic');
  3. Query OK, 1 row affected (0.02 sec)
  4.  OK!
  6. mysql> select * from position;
  7. +---------------------+---------------------+---------+-----------+
  8. | Id                  | name                | salary  | city      |
  9. +---------------------+---------------------+---------+-----------+
  10. |                   2 | yangqian2           | 1000000 | olmpic    |
  11. |  625399491637280769 | sky4                | 100000  | guangzhou |
  12. | 1418830095157694465 | medal2              | 1000000 | olmpic    |
  13. +---------------------+---------------------+---------+-----------+
  14. 38 rows in set (0.01 sec)

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

全局表的创建

分别在 lane1 和 lane2 库 创建 city 表

  1. CREATE TABLE `city` (
  2.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(256) DEFAULT NULL,
  4.   `province` varchar(256) DEFAULT NULL,
  5.   PRIMARY KEY (`Id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修改下 schema.xml 添加表 city

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

再次重启

  1. ➜  bin ./mycat restart

具体测试操作

  1. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  2. mysql> show databases;
  3. +----------+
  4. | DATABASE |
  5. +----------+
  6. | lane_db  |
  7. +----------+
  8. 1 row in set (0.00 sec)
  9. mysql> use lane_db
  10. Database changed
  11. mysql> show tables;
  12. +-------------------+
  13. | Tables in lane_db |
  14. +-------------------+
  15. | city              |
  16. | position          |
  17. +-------------------+
  18. 2 rows in set (0.01 sec)
  20. mysql> select * from city;
  21. +--------------------+---------+----------+
  22. | Id                 | name    | province |
  23. +--------------------+---------+----------+
  24. | 624572431226372096 | beijing | beijing  |
  25. +--------------------+---------+----------+
  26. 1 row in set (0.08 sec)
  28. mysql> insert into city(id,name,province) values(2021,'tokyo','japan');
  29. Query OK, 1 row affected (0.05 sec)
  30.  OK!
  32. mysql> select * from city;
  33. +--------------------+---------+----------+
  34. | Id                 | name    | province |
  35. +--------------------+---------+----------+
  36. |               2021 | tokyo   | japan    |
  37. | 624572431226372096 | beijing | beijing  |
  38. +--------------------+---------+----------+
  39. 2 rows in set (0.01 sec)
  40. mysql>

Mycat 实战 - 图7

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

主从分离

准备工作

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

  1. mysql> CREATE TABLE `city` (
  2.     ->   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  3.     ->   `name` varchar(256) DEFAULT NULL,
  4.     ->   `province` varchar(256) DEFAULT NULL,
  5.     ->   PRIMARY KEY (`Id`)
  6.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  7. Query OK, 0 rows affected (0.01 sec)
  9. mysql> insert into city(id,name,province) values(2021,'tokyo-5','japan-5');
  10. 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 做备用

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

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  5.     <schema name="lane_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
  6.         <!-- auto sharding by id (long) -->
  7.         <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
  8. <!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
  9.         <table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true">
  10.         </table>
  11.         <table name="city" primaryKey="id" dataNode="dn3" ruleRequired="false"
  12.             autoIncrement="true">
  13.         </table>
  14.         <!-- <table name="city" primaryKey="id" dataNode="dn1,dn2" type="global"  
  15.             autoIncrement="true">
  16.         </table> -->
  18.     </schema>
  19.     <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  20.         /> -->
  21.     <dataNode name="dn1" dataHost="localhost1" database="lane1" />
  22.     <dataNode name="dn2" dataHost="localhost1" database="lane2" />
  23.     <dataNode name="dn3" dataHost="localhost2" database="lane1" />
  25.     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  26.               writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
  27.         <heartbeat>select user()</heartbeat>
  28.         <!-- can have multi write hosts -->
  29.         <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
  30.                    password="root">
  31.         </writeHost>
  33.     </dataHost>
  34.     <dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
  35.               writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
  36.         <heartbeat>select user()</heartbeat>
  37.         <!-- can have multi write hosts -->
  38.         <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
  39.                    password="root">
  40.         <readHost host="hostS1" url="jdbc:mysql://172.16.94.5:3306" user="root"
  41.                    password="root">
  42.         </readHost>
  43.         </writeHost>
  44.     </dataHost>
  46. </mycat:schema>

Mycat 实战 - 图8

Mycat 实战 - 图9

重启 mycat

  1. ➜  bin ./mycat restart

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

  1. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  2. mysql> select * from city;
  3. +------+---------+----------+
  4. | Id   | name    | province |
  5. +------+---------+----------+
  6. | 2021 | tokyo-5 | japan-5  |
  7. +------+---------+----------+
  8. 1 row in set (0.11 sec)
  10. mysql> insert into city(id,name,province) values(2022,'beijing','beijing');
  11. Query OK, 1 row affected (0.03 sec)
  12. mysql> select * from city;
  13. +------+---------+----------+
  14. | Id   | name    | province |
  15. +------+---------+----------+
  16. | 2021 | tokyo-5 | japan-5  |
  17. +------+---------+----------+
  18. 1 row in set (0.01 sec)

Mycat 实战 - 图10

主从分离高可用

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

修改下 schema.xml 文件

  1. <?xml version="1.0"?>
  2. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  3. <mycat:schema xmlns:mycat="http://io.mycat/">
  5.     <schema name="lane_db" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
  6.         <!-- auto sharding by id (long) -->
  7.         <!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
  8. <!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
  9.         <table name="position" primaryKey="id" dataNode="dn1,dn2" rule="mod-long" autoIncrement="true">
  10.         </table>
  11.         <table name="city" primaryKey="id" dataNode="dn3" ruleRequired="false"
  12.             autoIncrement="true">
  13.         </table>
  14.         <!-- <table name="city" primaryKey="id" dataNode="dn1,dn2" type="global"  
  15.             autoIncrement="true">
  16.         </table> -->
  18.     </schema>
  19.     <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
  20.         /> -->
  21.     <dataNode name="dn1" dataHost="localhost1" database="lane1" />
  22.     <dataNode name="dn2" dataHost="localhost1" database="lane2" />
  23.     <dataNode name="dn3" dataHost="localhost2" database="lane1" />
  25.     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  26.               writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
  27.         <heartbeat>select user()</heartbeat>
  28.         <!-- can have multi write hosts -->
  29.         <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
  30.                    password="root">
  31.         </writeHost>
  33.     </dataHost>
  34.     <dataHost name="localhost2" maxCon="1000" minCon="10" balance="1"
  35.               writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
  36.         <heartbeat>select user()</heartbeat>
  37.         <!-- can have multi write hosts -->
  38.         <writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
  39.                    password="root">
  41.         </writeHost>
  42.         <writeHost host="hostS1" url="jdbc:mysql://172.16.94.5:3306" user="root"
  43.                    password="root">
  44.         </writeHost>
  45.     </dataHost>
  47. </mycat:schema>

关掉主库

  1. ➜  bin sudo mysql.server stop;
  2. Password:
  3. Shutting down MySQL
  4. .... SUCCESS!
  5. ➜  bin

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

  2. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  3. mysql> use lane_db;
  4. Database changed
  5. mysql> show tables;
  6. +-------------------+
  7. | Tables in lane_db |
  8. +-------------------+
  9. | city              |
  10. | position          |
  11. +-------------------+
  12. 2 rows in set (0.00 sec)
  14. mysql> select * from city;
  15. +------+---------+----------+
  16. | Id   | name    | province |
  17. +------+---------+----------+
  18. | 2021 | tokyo-5 | japan-5  |
  19. +------+---------+----------+
  20. 1 row in set (0.11 sec)
  21. mysql> insert into city(id,name,province) values(2024,'chongqing','chongqing');
  22. Query OK, 1 row affected (0.01 sec)
  23. mysql> select * from city;
  24. +------+-----------+-----------+
  25. | Id   | name      | province  |
  26. +------+-----------+-----------+
  27. | 2021 | tokyo-5   | japan-5   |
  28. | 2024 | chongqing | chongqing |
  29. +------+-----------+-----------+
  30. 2 rows in set (0.00 sec)

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

  1. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  2. Enter password:
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 3
  5. Server version: 5.6.29-mycat-1.6.7.5-release-20210616151418 MyCat Server (OpenCloudDB)
  7. Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> select * from city;
  16. +--------------------+---------+----------+
  17. | Id                 | name    | province |
  18. +--------------------+---------+----------+
  19. |               2021 | tokyo   | japan    |
  20. |               2022 | beijing | beijing  |
  21. |               2023 | tianjin | tianjing |
  22. | 624572431226372096 | beijing | beijing  |
  23. +--------------------+---------+----------+
  24. 4 rows in set (0.00 sec)
  26. mysql> insert into city(id,name,province) values(2025,'shenzhen','shenzhen');
  27. Query OK, 1 row affected (0.01 sec)
  28.  OK!
  30. mysql> select * from city;
  31. +--------------------+---------+----------+
  32. | Id                 | name    | province |
  33. +--------------------+---------+----------+
  34. |               2021 | tokyo   | japan    |
  35. |               2022 | beijing | beijing  |
  36. |               2023 | tianjin | tianjing |
  37. | 624572431226372096 | beijing | beijing  |
  38. +--------------------+---------+----------+
  39. 4 rows in set (0.01 sec)

强制路由

修改下 schema.xml 下的 localhost2

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

重启 mycat

  1. bin ./mycat restart

强制路由到主库

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

强制路由到从库

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

具体操作如下

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

  1. ➜  ~ mysql -uroot -h 127.0.0.1   -P 8066 -p
  2. mysql> /*!mycat:db_type=master*/ select * from city;
  3. +--------------------+---------+----------+
  4. | Id                 | name    | province |
  5. +--------------------+---------+----------+
  6. |               2021 | tokyo   | japan    |
  7. |               2022 | beijing | beijing  |
  8. |               2023 | tianjin | tianjing |
  9. | 624572431226372096 | beijing | beijing  |
  10. +--------------------+---------+----------+
  11. 4 rows in set (0.09 sec)
  12. mysql> /*!mycat:db_type=slave*/ select * from city;
  13. +------+-----------+-----------+
  14. | Id   | name      | province  |
  15. +------+-----------+-----------+
  16. | 2021 | tokyo-5   | japan-5   |
  17. | 2024 | chongqing | chongqing |
  18. | 2025 | shenzhen  | shenzhen  |
  19. +------+-----------+-----------+
  20. 3 rows in set (0.00 sec)
  22. mysql>

主从延时切换

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

switchType 参数：

-1： 表示不自动切换

1 ：表示自动切换

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

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

对于非集群

修改 schema.xml

  1. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"
  3. dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
  5. <heartbeat>show slave status </heartbeat> <!-- can have multi write hosts -->
  7. <writeHost host="M1" url="localhost:3306" user="root" password="root">
  9. </writeHost>
  11. <writeHost host="S1" url="localhost:3316" user="root"
  13. </dataHost>

对于集群

修改 schema.xml 文件

  1. <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0"
  3. dbType="mysql" dbDriver="native" switchType="3" >
  5. <heartbeat> show status like ‘wsrep%’</heartbeat>
  7. <writeHost host="M1" url="localhost:3306" user="root"password="root">
  9. </writeHost>
  11. <writeHost host="S1"url="localhost:3316"user="root"password="root" >
  13. </writeHost> </dataHost>

Mycat 事务使用

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

具体操作

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

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

  2. mysql> set autocommit=0;
  3. Query OK, 0 rows affected (0.01 sec)
  5. mysql> set xa=on;
  6. Query OK, 0 rows affected (0.00 sec)
  8. mysql> insert into city(id,name,province) values(200,'chengdu','sichuan');
  9. Query OK, 1 row affected (0.01 sec)
  10.  OK!
  12. mysql> update position set salary='300000' where id<5;
  13. Query OK, 2 rows affected (0.04 sec)
  14.  OK!
  16. mysql> insert into city(id,name,province) values(200,'chengdu3','sichuan3');
  17. ERROR 1062 (HY000): Duplicate entry '200' for key 'PRIMARY'
  18. mysql> commit；
  19.     -> commit;
  20. Query OK, 0 rows affected (0.00 sec)
  21. mysql> /*!mycat:db_type=master*/ select * from city;
  22. +--------------------+---------+----------+
  23. | Id                 | name    | province |
  24. +--------------------+---------+----------+
  25. |               2021 | tokyo   | japan    |
  26. |               2022 | beijing | beijing  |
  27. |               2023 | tianjin | tianjing |
  28. | 624572431226372096 | beijing | beijing  |
  29. +--------------------+---------+----------+
  30. 4 rows in set (0.01 sec)
  32. mysql> /*!mycat:db_type=slave*/ select * from city;
  33. +------+-----------+-----------+
  34. | Id   | name      | province  |
  35. +------+-----------+-----------+
  36. | 2021 | tokyo-5   | japan-5   |
  37. | 2024 | chongqing | chongqing |
  38. | 2025 | shenzhen  | shenzhen  |
  39. +------+-----------+-----------+
  40. 3 rows in set (0.01 sec)
  42. mysql>