环境：
centos7.4 + mysql5.7.20 + mycat1.6
单台主机上安装了5台mysql_5.7.20 实例（3306，3307，3308，3309，3310）
3306为独立实例 (db6,order_db,sequence_test)
3307为独立实例 (db7,order_db)
3308为主DB (db8,user_db) --GTID复制
3309为从DB(主3308)
3301为从DB(主3308)
一、环境创建
#创建mycat账号 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create user mycat6@'%' identified by '123456'; grant all privileges on *.* to mycat6@'%'; " mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e"create user mycat7@'%' identified by '123456'; grant all privileges on *.* to mycat7@'%'; " mysql -uroot -p123456 -h127.0.0.1 -P 3308 -e"create user mycat8@'%' identified by '123456'; grant all privileges on *.* to mycat8@'%'; " #创建数据库及表 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create database db6;" mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e"create database db7;" mysql -uroot -p123456 -h127.0.0.1 -P 3308 -e"create database db8;" mysql -uroot -p123456 -h127.0.0.1 -P 3308 -e"create database user_db;" ##配读写分离 mysql -uroot -p123456 -h127.0.0.1 -P 3308 -e"create table user_db.users(id int auto_increment primary key ,user_id int ,user_name varchar(12));" ##配读写分离 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create database order_db;" ##配分片 mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e"create database order_db;" ##配分片 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create table order_db.orders(id int auto_increment primary key ,user_id int ,order_id varchar(12));" ##配分片 mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e"create table order_db.orders(id int auto_increment primary key ,user_id int ,order_id varchar(12));" ##配分片 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create table db6.city(id int auto_increment primary key ,city_name varchar(16));" ##配全局表 mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e"create table db7.city(id int auto_increment primary key ,city_name varchar(16));" ##配全局表 mysql -uroot -p123456 -h127.0.0.1 -P 3308 -e"create table db8.city(id int auto_increment primary key ,city_name varchar(16));" ##配全局表 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create database sequence_test;" ##全局序列库 mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"create table sequence_test.MYCAT_SEQUENCE(name VARCHAR (50) NOT NULL PRIMARY KEY , current_value INT NOT NULL ,increment INT NOT NULL DEFAULT 100 ); " ##全局序列号表 mysql -uroot -p123456 -h127.0.0.1 -P 3306 use sequence_test ; #取当前squence的值 mysql> DROP FUNCTION IF EXISTS mycat_seq_currval; DELIMITER $ CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN DECLARE retval VARCHAR(64); SET retval="-999999999,null"; SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM MYCAT_SEQUENCE WHERE name = seq_name; RETURN retval; END $ DELIMITER ; #设置 sequence 值 mysql> DROP FUNCTION IF EXISTS mycat_seq_setval; DELIMITER $ CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),value INTEGER) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END $ DELIMITER ; #取下一个sequence的值 mysql> DROP FUNCTION IF EXISTS mycat_seq_nextval; DELIMITER $ CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET utf8 DETERMINISTIC BEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END $ DELIMITER ; mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e"use sequence_test;INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES ('seq_test', 1, 100); " #全局序列号名称引用关系 #INSERT INTO MYCAT_SEQUENCE(name,current_value,increment) VALUES (【'seq_test'】, 1, 100); " #【SEQ_TEST]】=dn_seq #next value for MYCATSEQ_【SEQ_TEST】
二、文件配置
sequence_db_conf.properties 全局序列号文件配置 SEQ_TEST=dn_seq server.xml 文件配置（修改内容） <system> <property name="sequnceHandlerType">1</property> ##全局序列号 </system> <user name="mycat"> <property name="password">123456</property> <property name="schemas">s_global_db,s_db6,s_db7,s_db8,s_user_db,s_order_db</property> ##mysql> show databases;看到的内容 </user> schema.xml 文件配置 <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="s_db6" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn6" > </schema> <schema name="s_db7" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn7" > </schema> <schema name="s_db8" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn8" > </schema> <schema name="s_user_db" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn82" > </schema> <schema name="s_order_db" checkSQLschema="false" sqlMaxLimit="100"> <table name="orders" primaryKey="id" autoIncrement="true" dataNode="dn62,dn72" rule="mod-long" /> ##分片表 </schema> <schema name="s_global_db" checkSQLschema="false" sqlMaxLimit="100"> <table name="city" primaryKey="id" autoIncrement="true" type="global" dataNode="dn6,dn7,dn8" /> ##全局表 </schema> <dataNode name="dn6" dataHost="dh6" database="db6" /> <dataNode name="dn7" dataHost="dh7" database="db7" /> <dataNode name="dn8" dataHost="dh8" database="db8" /> <dataNode name="dn82" dataHost="dh8" database="user_db" /> <dataNode name="dn62" dataHost="dh6" database="order_db" /> ##分片表 <dataNode name="dn72" dataHost="dh7" database="order_db" /> ##分片表 <dataNode name="dn_seq" dataHost="dh6" database="sequence_test" /> ##全局序列号节点
<dataHost name="dh6" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host6_M" url="127.0.0.1:3306" user="mycat6" password="123456"> </writeHost> </dataHost> <dataHost name="dh7" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host7_M" url="127.0.0.1:3307" user="mycat7" password="123456"> </writeHost> </dataHost> <dataHost name="dh8" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host8_M" url="127.0.0.1:3308" user="mycat8" password="123456"> ##读写分离 <readHost host="host8_S1" url="127.0.0.1:3309" user="mycat8" password="123456" /> <readHost host="host8_s2" url="127.0.0.1:3310" user="mycat8" password="123456" /> </writeHost> </dataHost> </mycat:schema> rule.xml文件配置（修改内容） <tableRule name="mod-long"> ##分片表(规则) <rule> <columns>user_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>
三、测试结果
测试分表及全局序列号结果： mysql -umycat -p123456 -h127.0.0.1 -P 8066 -e "show databases;" mysql -umycat -p123456 -h127.0.0.1 -P 8066 -e "use s_order_db ; " mysql -umycat -p123456 -h127.0.0.1 -P 8066 -e "use s_order_db ; select * from orders; insert into orders(id,user_id,order_id) values (next value for MYCATSEQ_SEQ_TEST,1,'A01'); insert into orders(id,user_id,order_id) values (next value for MYCATSEQ_SEQ_TEST,2,'A02'); insert into orders(id,user_id,order_id) values (next value for MYCATSEQ_SEQ_TEST,3,'A03'); insert into orders(id,user_id,order_id) values (next value for MYCATSEQ_SEQ_TEST,4,'A04'); insert into orders(id,user_id,order_id) values (next value for MYCATSEQ_SEQ_TEST,5,'A05'); insert into orders(id,user_id,order_id) values (next value for MYCATSEQ_SEQ_TEST,7,'A07');" mysql -uroot -p123456 -h127.0.0.1 -P 8066 -e "use s_order_db ; select * from orders; " mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e "use order_db ; select * from orders; " mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e "use order_db ; select * from orders; " 测试全局表结果： mysql -umycat -p123456 -h127.0.0.1 -P 8066 -e "use s_global_db ; insert into city(id,city_name) values (null,'武汉'); insert into city(id,city_name) values (null,'深圳');" mysql -umycat -p123456 -h127.0.0.1 -P 8066 -e "use s_global_db ; select * from city; " mysql -uroot -p123456 -h127.0.0.1 -P 3306 -e "use db6 ; select * from city; " mysql -uroot -p123456 -h127.0.0.1 -P 3307 -e "use db7 ; select * from city; " mysql -uroot -p123456 -h127.0.0.1 -P 3308 -e "use db8 ; select * from city; "