os:centos 7.4
jdk: 1.8.0_131
mysql: 5.7.22
mycat: 1.6.5
node1 节点 mycat 1.6.5
node2、node3 节点 mysql 5.7.22
node1 节点插入数据
所有的操作都需要通过mycat进行,如果事先在node2、node3节点上创建表,在insert时就会报错。
# mysql -h127.0.0.1 -P3306 -uroot -prootroot
mysql> use peiybdb;
mysql> show tables;
+-------------------+
| Tables in peiybdb |
+-------------------+
| tmp_t0 |
+-------------------+
1 row in set (0.02 sec)
mysql> select * from tmp_t0;
Empty set (0.00 sec)
mysql> show create table tmp_t0G
*************************** 1. row ***************************
Table: tmp_t0
Create Table: CREATE TABLE `tmp_t0` (
`c1` varchar(100) DEFAULT NULL,
`c2` varchar(100) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> insert into tmp_t0(c1,c2,c3)values('1','11','111');
ERROR 1105 (HY000): Unknown column '_mycat_op_time' in 'field list'
原因是在schema.xml定义了 type=”global” ,
<table name="tmp_t0" dataNode="node2,node3" type="global" />
这个是由参数 useGlobleTableCheck 控制的全局表一致性检测,原理通过在全局表增加_MYCAT_OP_TIME字段来进行一致性检测,类型为bigint,create语句通过mycat执行会自动加上这个字段,其他情况请自己手工添加。
所以创建表不要在 datanode 各节点去创建,而是通过 mycat 去创建。
# mysql -h127.0.0.1 -P3306 -uroot -prootroot
mysql> drop table tmp_t0;
mysql> CREATE TABLE tmp_t0 (
c1 varchar(100) DEFAULT NULL,
c2 varchar(100) DEFAULT NULL,
c3 varchar(100) DEFAULT NULL,
KEY idx_tmp_t0_x0 (c1)
);
mysql> show create table tmp_t0G
*************************** 1. row ***************************
Table: tmp_t0
Create Table: CREATE TABLE `tmp_t0` (
`c1` varchar(100) DEFAULT NULL,
`c2` varchar(100) DEFAULT NULL,
`c3` varchar(100) DEFAULT NULL,
`_mycat_op_time` bigint(20) DEFAULT NULL COMMENT '全局表保存修改时间戳的字段名',
KEY `idx_tmp_t0_x0` (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
node1 节点的mycat再次插入数据
mysql> insert into tmp_t0(c1,c2,c3)values('1','11','111'),('2','12','112'),('3','13','113'),('4','14','114');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from tmp_t0;
+------+------+------+----------------+
| c1 | c2 | c3 | _mycat_op_time |
+------+------+------+----------------+
| 1 | 11 | 111 | 1529432866207 |
| 2 | 12 | 112 | 1529432866207 |
| 3 | 13 | 113 | 1529432866207 |
| 4 | 14 | 114 | 1529432866207 |
+------+------+------+----------------+
4 rows in set (0.01 sec)
node2、node3 节点查询数据一致,没有分散。
mysql> select * from tmp_t0;
+------+------+------+----------------+
| c1 | c2 | c3 | _mycat_op_time |
+------+------+------+----------------+
| 1 | 11 | 111 | 1529432866207 |
| 2 | 12 | 112 | 1529432866207 |
| 3 | 13 | 113 | 1529432866207 |
| 4 | 14 | 114 | 1529432866207 |
+------+------+------+----------------+
4 rows in set (0.00 sec)
分析后发现是 schema.xml 的
缺少 rule 属性去掉 type=”global” ,添加 rule=”rule1”,修改后如下:
同时修改rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">2</property>
<property name="partitionLength">512</property>
</function>
</mycat:rule>
要遵守 partitionCount*partitionLength=1024
tmp_t0需要添加一列id
mysql> CREATE TABLE tmp_t0 (
id bigint(20),
c1 varchar(100) DEFAULT NULL,
c2 varchar(100) DEFAULT NULL,
c3 varchar(100) DEFAULT NULL,
KEY idx_tmp_t0_x0 (id)
);
mysql> insert into tmp_t0(id,c1,c2,c3)values(1,'1','11','111'),(2,'2','12','112'),(3,'3','13','113'),(4,'4','14','114'),(5,'1','11','111'),(6,'2','12','112'),(7,'3','13','113'),(8,'4','14','114'),(9,'3','13','113'),(10,'4','14','114'),(11,'3','13','113'),(12,'4','14','114');
mysql> insert into tmp_t0(id,c1,c2,c3)values(512,'1','11','111'),(513,'1','11','111'),(514,'1','11','111');
node2、node3节点上查询的数据分布符合预期
看来是要了解下 mysql的分片规则了。