1,迁移数据
举例说明,比如一个博客数据库数据表如下:
这里水平拆分,垂直拆分,只是做个简单的实验,真正的线上业务要根据情况,数据进行拆分。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
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数据库中。
1
2
3
4
5
6
7
|
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,配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
<!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规则配置:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
<!DOCTYPE mycat: rule SYSTEM "rule.dtd" > <! --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:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
<?xml version= "1.0" encoding= "UTF-8" ?> <!DOCTYPE mycat:server SYSTEM "server.dtd" > <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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
|
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 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 ; DROP FUNCTION IF EXISTS `mycat_seq_nextval`; DELIMITER ;; CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR (50)) RETURNS varchar (64) CHARSET latin1 DETERMINISTIC BEGIN 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 DETERMINISTIC BEGIN 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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: 0 mysql> 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 names You can turn off this feature to get a quicker startup with -A Database changed mysql> 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: 0 mysql> 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 names You can turn off this feature to get a quicker startup with -A mysql> 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: 0 mysql> 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生成:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
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的存储过程,可以直接使用。