Mycat 水平分表,垂直分表实践(2)
来源:互联网 发布:三菱plc模拟量编程 编辑:程序博客网 时间:2024/05/01 22:48
本文原文连接: http://blog.csdn.net/freewebsys/article/details/44399901 转载请注明出处!
1,迁移数据
举例说明,比如一个博客数据库数据表如下:
这里水平拆分,垂直拆分,只是做个简单的实验,真正的线上业务要根据情况,数据进行拆分。
--分类CREATE TABLE category ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, name VARCHAR(200) NOT NULL, PRIMARY KEY (id));--标签CREATE TABLE tag ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, name VARCHAR(100) NOT NULL, PRIMARY KEY (id));--文章CREATE TABLE `article` ( `id` int(11) NOT NULL PRIMARY KEY , `user_id` int(11) NOT NULL, `title` varchar(200) NOT NULL, `abstract` text, `content` text, `publish_time` datetime NOT NULL, `create_time` datetime NOT NULL, `update_time` datetime NOT NULL, `category_id` int(11) NOT NULL, KEY `category_id` (`category_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;--标签CREATE TABLE article_tags ( user_id INT NOT NULL, article_id INT NOT NULL, tag_id INT NOT NULL, PRIMARY KEY (user_id,article_id,tag_id));--用户信息CREATE TABLE user_info ( id INT NOT NULL AUTO_INCREMENT, email VARCHAR(200) NOT NULL, name VARCHAR(200) NOT NULL, password VARCHAR(100) NOT NULL, create_time DATETIME NOT NULL, update_time DATETIME NOT NULL, last_login_time DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY (name));--链接CREATE TABLE link ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, name VARCHAR(100) NOT NULL, url VARCHAR(300) NOT NULL, PRIMARY KEY (id), KEY (user_id) );
将所有数据都迁移到mycat中,一共有4个数据库,blog01,blog02,blog_article01,blog_article02。
article,article_tags分别在blog_article01,blog_article02,按照uid进行水平拆分。
user_info表在blog01,link,category,tag在blog02数据库中。
CREATE DATABASE blog01 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE blog02 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE blog_article01 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;CREATE DATABASE blog_article02 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;#创建一共mycat数据库,存储mycat相关数据比如sequence。CREATE DATABASE mycat DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
2,schema.xml,配置
<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <schema name="mycat" checkSQLschema="false" sqlMaxLimit="100"> <table name="MYCAT_SEQUENCE" primaryKey="name" type="global" dataNode="nodeUser" /> </schema> <schema name="blog_user" checkSQLschema="false" sqlMaxLimit="100"> <table name="user_info" primaryKey="id" type="global" dataNode="nodeUser" /> </schema> <schema name="blog_category" checkSQLschema="false" sqlMaxLimit="100"> <table name="category" primaryKey="id" type="global" dataNode="nodeCategory" /> <table name="tag" primaryKey="id" type="global" dataNode="nodeCategory" /> <table name="link" primaryKey="id" type="global" dataNode="nodeCategory" /> </schema> <schema name="blog_article" checkSQLschema="false" sqlMaxLimit="100"> <table name="article" primaryKey="user_id" dataNode="nodeArticle01,nodeArticle02" rule="mod-long" /> <table name="article_tags" primaryKey="id" type="global" dataNode="nodeArticle01,nodeArticle02" /> </schema> <dataNode name="nodeMycat" dataHost="dataHost01" database="mycat" /> <dataNode name="nodeUser" dataHost="dataHost01" database="blog01" /> <dataNode name="nodeCategory" dataHost="dataHost01" database="blog02" /> <dataNode name="nodeArticle01" dataHost="dataHost01" database="blog_article01" /> <dataNode name="nodeArticle02" dataHost="dataHost01" database="blog_article02" /> <dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select 1</heartbeat> <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root"/> </dataHost></mycat:schema>
配置4个数据库连接,user_info做垂直拆分(配置type=global)。
blog_article,数据库进行水平拆分,拆分成两个数据库。
当设置成全局表的时候必须保证这个表在所有的数据库中,和配置的dataNode无关。
article规则配置:
<!DOCTYPE mycat:rule SYSTEM "rule.dtd"><mycat:rule xmlns:mycat="http://org.opencloudb/"> <!--article 分区配置,按照id进行模2.--> <tableRule name="mod-long"> <rule> <columns>user_id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="org.opencloudb.route.function.PartitionByMod"> <property name="count">2</property> </function></mycat:rule>
配置service.xml:
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mycat:server SYSTEM "server.dtd"><mycat:server xmlns:mycat="http://org.opencloudb/"> <system> <property name="defaultSqlParser">druidparser</property> </system> <user name="mycat"> <property name="password">mycat</property> <property name="schemas">mycat,blog_user,blog_category,blog_article</property> </user></mycat:server>
3,数据库sequence生成
牵扯到分库的时候就一定要由sequence生成,mysql的主键自增就不能使用了。
mycat的文档里面没有找到这部分的配置资料,但是找到了sequence的存储过程。
可以把这个存储过程存储到一个单独的数据库中,然后进行一次查询,再做入库。
存储过程地址:
https://github.com/MyCATApache/Mycat-doc/blob/master/%E5%85%A5%E9%97%A8%E6%8C%87%E5%8D%97/sequnce-sql.txt
DROP TABLE IF EXISTS MYCAT_SEQUENCE; CREATE TABLE MYCAT_SEQUENCE ( name VARCHAR(50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 100, PRIMARY KEY (name) ) ENGINE=InnoDB;DROP FUNCTION IF EXISTS `mycat_seq_currval`;DELIMITER ;;CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET latin1 DETERMINISTICBEGIN 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 ;DROP FUNCTION IF EXISTS `mycat_seq_nextval`;DELIMITER ;;CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64) CHARSET latin1 DETERMINISTICBEGIN UPDATE MYCAT_SEQUENCE SET current_value = current_value + increment WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END;;DELIMITER ;DROP FUNCTION IF EXISTS `mycat_seq_setval`;DELIMITER ;;CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER) RETURNS varchar(64) CHARSET latin1 DETERMINISTICBEGIN UPDATE MYCAT_SEQUENCE SET current_value = value WHERE name = seq_name; RETURN mycat_seq_currval(seq_name); END;;DELIMITER ;--同时增加权限,否则不能执行。mysql> grant all privileges on *.* to root@"%" identified by ".";Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)
4,测试mycat数据库
–mycat数据库的ip是192.168.1.16,端口是8066。
mysql -umycat -pmycat -P8066 -h 192.168.1.16
mysql> show databases;+---------------+| DATABASE |+---------------+| blog_article || blog_category || blog_user || mycat |+---------------+4 rows in set (0.00 sec)mysql> use blog_user;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------------+| Tables in blog_user |+---------------------+| user_info |+---------------------+1 row in set (0.00 sec)mysql> insert into user_info(email,`name`,password,create_time,update_time,last_login_time) values('zhangsan@126.com','zhangsan',password('123456'),now(),now(),now());Query OK, 1 row affected (0.01 sec)mysql> select * from user_info;+----+------------------+----------+-------------------------------------------+---------------------+---------------------+---------------------+| id | email | name | password | create_time | update_time | last_login_time |+----+------------------+----------+-------------------------------------------+---------------------+---------------------+---------------------+| 1 | zhangsan@126.com | zhangsan | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 2015-03-26 02:47:38 | 2015-03-26 02:47:38 | 2015-03-26 02:47:38 |+----+------------------+----------+-------------------------------------------+---------------------+---------------------+---------------------+1 row in set (0.11 sec)mysql> update user_info set name = 'lisi' where id = 1;Query OK, 1 row affected (0.08 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> delete from user_info where name = 'lisi';Query OK, 1 row affected (0.00 sec)mysql> use blog_category;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+-------------------------+| Tables in blog_category |+-------------------------+| category || link || tag |+-------------------------+3 rows in set (0.00 sec)mysql> insert into tag(id,user_id,name) values(1,1,'java');Query OK, 1 row affected (0.11 sec)mysql> select * from tag;+----+---------+------+| id | user_id | name |+----+---------+------+| 1 | 1 | java |+----+---------+------+1 row in set (0.11 sec)mysql> update tag set name = 'j2ee' where id = 1;Query OK, 1 row affected (0.08 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> insert into tag(user_id,name) values(2,'mysql');Query OK, 1 row affected (0.00 sec)mysql> select * from tag;+----+---------+-------+| id | user_id | name |+----+---------+-------+| 1 | 1 | j2ee || 2 | 2 | mysql |+----+---------+-------+2 rows in set (0.00 sec)mysql> delete from tag where id = 1;Query OK, 1 row affected (0.00 sec)mysql> use blog_article;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -Amysql> insert into article(id,user_id,title,publish_time,create_time,update_time,category_id) \ -> values (1,1,'test1',now(),now(),now(),1),(2,1,'test2',now(),now(),now(),2),\ -> (3,1,'test3',now(),now(),now(),3),(4,2,'test4',now(),now(),now(),3);Query OK, 4 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> select * from article;+----+---------+-------+----------+---------+---------------------+---------------------+---------------------+-------------+| id | user_id | title | abstract | content | publish_time | create_time | update_time | category_id |+----+---------+-------+----------+---------+---------------------+---------------------+---------------------+-------------+| 4 | 2 | test4 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 3 || 1 | 1 | test1 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 1 || 2 | 1 | test2 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2 || 3 | 1 | test3 | NULL | NULL | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 2015-03-26 03:30:55 | 3 |+----+---------+-------+----------+---------+---------------------+---------------------+---------------------+-------------+4 rows in set (0.00 sec)mysql> update article set title = 'new title' where user_id = 1 and id =2;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0
测试sequence生成:
mysql> INSERT INTO MYCAT_SEQUENCE VALUES ('article_seq', 1, 1);1 row in set (0.06 sec)mysql> SELECT MYCAT_SEQ_CURRVAL('article_seq');+----------------------------------+| MYCAT_SEQ_CURRVAL('article_seq') |+----------------------------------+| 1,1 |+----------------------------------+1 row in set (0.01 sec)mysql> SELECT MYCAT_SEQ_SETVAL('article_seq', 2);+------------------------------------+| MYCAT_SEQ_SETVAL('article_seq', 2) |+------------------------------------+| 2,1 |+------------------------------------+1 row in set (0.01 sec)mysql> SELECT MYCAT_SEQ_CURRVAL('article_seq');+----------------------------------+| MYCAT_SEQ_CURRVAL('article_seq') |+----------------------------------+| 2,1 |+----------------------------------+1 row in set (0.01 sec)mysql> SELECT MYCAT_SEQ_NEXTVAL('article_seq');+----------------------------------+| MYCAT_SEQ_NEXTVAL('article_seq') |+----------------------------------+| 3,1 |+----------------------------------+1 row in set (0.01 sec)mysql> SELECT MYCAT_SEQ_NEXTVAL('article_seq');+----------------------------------+| MYCAT_SEQ_NEXTVAL('article_seq') |+----------------------------------+| 4,1 |+----------------------------------+1 row in set (0.02 sec)
总结
mycat提供了数据库垂直拆分和水平拆分的方案,对于数据迁移可以按照id,user_id进行拆分。
也可以将数据分库存储,不拆分,但必须放到不同的库中(和水平拆分的分开)。
同时,如果进行拆分,则需要修改主键生成方式,目前看到mycat中提供一个sequence的存储过程,可以直接使用。
- Mycat 水平分表,垂直分表实践(2)
- mysql-水平分表-垂直分表
- 水平分表与垂直分表
- 水平分表 和 垂直分表
- 水平分表和垂直分表
- mycat学习05之水平分表
- mysql水平分表和垂直分表的优缺点
- mysql水平分表和垂直分表的优缺点
- mysql的水平分表和垂直分表
- Mycat(4):消息表mysql数据库分表实践
- 数据库读写分离和垂直分库、水平分表
- 数据库读写分离和垂直分库、水平分表
- 数据库读写分离和垂直分库、水平分表
- 数据库读写分离与垂直分库水平分表
- SqlServer 垂直分表
- Mysql水平分表
- mysql水平分表
- 数据库水平分表
- 第一周上机实践_长方柱
- 第二周项目2-长方柱
- .net学习第十四天
- 2015-第二周-旱冰场造价
- JAVA进阶学习-java String类
- Mycat 水平分表,垂直分表实践(2)
- Unity UGUI——Image(基本属性)
- 第二周 项目3-时间类(2)
- .net学习第十五天
- ReportNG 手把手教你弄测试报告
- 第一周项目二
- 121.Best Time to Buy and Sell Stock
- android-测试so动态库(九)
- Mac OS X 下开发 Android 程序时使用 USB 连真机调试