六、mycat全局自增
系列导航
一、实验环境
全局自增
实验环境 服务内容 dataNode
192.168.0.1 mycat dn162
192.168.0.2 mysql dn163
192.168.0.3 mysql dn164
二、实验内容
1、192.168.0.2 、192.168.0.3上分别在 ship_db_01和ship_db_02数据库上创建goobal_master表
注：建表脚本见：数据库环境及用户名密码见mycat实验数据.sql
2、162上进入本地的mysql（而不是mycat）创建名为mycat的database
>mysql -h 192.168.0.1 -P 3306 -uroot -p123456
mysql> create database mycat;
3、执行脚本文件dbseq.sql
cd /usr/local/mycat/conf
vi dbseq.sql
开头部位增加： use mycat;
[mycat@zhu conf]$ mysql -h 192.168.0.1 -P 3306 -uroot -p <dbseq.sql
Enter password:
4、查看权限
mysql -h 192.168.0.1 -P 3306 -uroot -p
mysql> use mysql;
Database changed
mysql> select user,host from user;
+---------------+-------------+
| user | host |
+---------------+-------------+
| root | % |
mysql> show grants for root@'%';
+-------------------------------------------------------------+
| Grants for root@% |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
注：这里如果不是root用户需要增加执行存储过程的权限
例如：grant execute on *.* to 'root'@'%';
5、配置 service.xml.xml
将之前<property name="sequnceHandlerType">2</property>
改为：
<property name="sequnceHandlerType">1</property>
6、配置 schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="demo_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="order_master" primaryKey="order_id" dataNode="dn163" />
<table name="customer_inf" primaryKey="customer_inf_id" dataNode="dn164" />
<table name="shipping_info" primaryKey="ship_id" dataNode="dn163_sp,dn164_sp" rule="mod-long" />
<!--这是水全局自增表-->
<table name="goobal_master" primaryKey="goobal_id" dataNode="dn163_sp,dn164_sp" rule="rule1" autoIncrement="true"/>
</schema>
<dataNode name="dn163" dataHost="mysql163" database="order_db" />
<dataNode name="dn164" dataHost="mysql164" database="customer_db" />
<dataNode name="dn163_sp" dataHost="mysql163" database="ship_db_01" />
<dataNode name="dn164_sp" dataHost="mysql164" database="ship_db_02" />
<dataNode name="dn162" dataHost="mysql162" database="mycat" />
<!--这是水全局自增数据库begin-->
<dataHost name="mysql162" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.0.1" url="192.168.0.1:3306" user="root" password="123456">
</writeHost>
</dataHost>
<!--这是水全局自增数据库end-->
<dataHost name="mysql163" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<writeHost host="192.168.0.2" url="192.168.0.2:3306" user="im_mycat" password="123456">
</writeHost>
</dataHost>
<dataHost name="mysql164" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" >
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="192.168.0.3" url="192.168.0.3:3306" user="im_mycat" password="123456">
</writeHost>
</dataHost>
</mycat:schema>
7、配置rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="mod-long">
<rule>
<columns>ship_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="rule1">
<rule>
<columns>goobal_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:rule>
7、修改配置文件
vi /usr/local/mycat/conf/sequence_db_conf.properties
#sequence stored in datanode
GLOBAL=dn162
GOOBAL_MASTER=dn162
注：mycat就是在192.168.0.1上创建的数据库，GOOBAL_MASTER是需要用全局自增逐渐的表（表名一定要大写）
8、192.168.0.1的mycat库里添加 GOOBAL_MASTER表自增的信息
mysql> use mycat
Database changed
mysql> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| MYCAT_SEQUENCE |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from MYCAT_SEQUENCE;
+--------+---------------+-----------+
| name | current_value | increment |
+--------+---------------+-----------+
| GLOBAL | 1 | 1 |
+--------+---------------+-----------+
1 row in set (0.00 sec)
mysql> insert into MYCAT_SEQUENCE values('GOOBAL_MASTER',1,1);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
9、重启mycat 进入mycat插入数据测试
mycat@zhu ~]$ mycat restart
>mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456
mysql> show databases;
+----------+
| DATABASE |
+----------+
| demo_db |
+----------+
1 row in set (0.01 sec)
mysql> use demo_db
Database changed
mysql> show tables;
+-------------------+
| Tables in demo_db |
+-------------------+
| customer_inf |
| goobal_master |
| order_master |
| region_info |
| shipping_info |
+-------------------+
5 rows in set (0.00 sec)
insert into goobal_master(order_status )values(0);
insert into goobal_master(order_status )values(1);
insert into goobal_master(order_status )values(2);
insert into goobal_master(order_status )values(3);
insert into goobal_master(order_status )values(4);
insert into goobal_master(order_status )values(5);
insert into goobal_master(order_status )values(6);
insert into goobal_master(order_status )values(7);
insert into goobal_master(order_status )values(8);
insert into goobal_master(order_status )values(9);
10、 登录192.168.0.1上的mycat 以及192.168.0.2，192.168.0.3上的mysql查看自增id是否符合预期
192.168.0.1：
[root@zhu logs]# mysql -h 192.168.0.1 -P 8066 -umycat_user -p123456
mysql> use demo_db
mysql> select * from goobal_master order by goobal_id asc;
+-----------+--------------+
| goobal_id | order_status |
+-----------+--------------+
| 5 | 0 |
| 6 | 1 |
| 7 | 2 |
| 8 | 3 |
| 9 | 4 |
| 10 | 5 |
| 11 | 6 |
| 12 | 7 |
| 13 | 8 |
| 14 | 9 |
+-----------+--------------+
10 rows in set (0.03 sec)
192.168.0.2：
mysql> select * from goobal_master order by goobal_id asc;
+-----------+--------------+
| goobal_id | order_status |
+-----------+--------------+
| 6 | 1 |
| 8 | 3 |
| 10 | 5 |
| 12 | 7 |
| 14 | 9 |
+-----------+--------------+
5 rows in set (0.00 sec)
192.168.0.3：
mysql> select * from goobal_master order by goobal_id asc;
+-----------+--------------+
| goobal_id | order_status |
+-----------+--------------+
| 5 | 0 |
| 7 | 2 |
| 9 | 4 |
| 11 | 6 |
| 13 | 8 |
+-----------+--------------+
5 rows in set (0.00 sec)
