0.双主环境搭建

参见:https://pincheng.org/forward/d3c702fe.html

1.垂直拆分:

垂直拆分

2.mycat垂直分表

2.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
[root@db02 ~]# cd /application/mycat/conf/
[root@db02 /application/mycat/conf]# mv schema.xml schema.xml.ha
[root@db02 /application/mycat/conf]# cat >schema.xml <<'EOF'
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
<table name="user" dataNode="sh1"/>
<table name="order_t" dataNode="sh2"/>
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" /> #节点中真实存在的库
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" /> #节点中真实存在的库
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.0.0.51:3308" user="root" password="123">
<readHost host="db2" url="10.0.0.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.52:3308" user="root" password="123">
<readHost host="db4" url="10.0.0.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
EOF

2.2配置文件解释:

  • 两组datanode
    • sh1下面四组节点【承载taobao库user表】
    • 10.0.0.51:3307[db1]主库 //写操作
    • 10.0.0.51:3309[db2]从库 //读操作
    • 10.0.0.52:3307[db3]主库 //读操作,同时是sh1中db1的替补。实现高可用
    • 10.0.0.52:3309[db4]从库 //读操作
    • sh2下面四组节点【承载taobao库order_t表】
    • 10.0.0.51:3308[db1]主库 //写操作
    • 10.0.0.51:3310[db2]从库 //读操作
    • 10.0.0.52:3318[db3]主库 //读操作,同时是sh2中db1的替补。实现高可用
    • 10.0.0.52:3320[db4]从库 //读操作

物理架构图:
基础架构
垂直拆分逻辑架构图:
垂直拆分逻辑架构图

3.创建测试库和表:(db01或db02)

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
mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))"
mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"

[root@db02 ~]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
//此时,sh1四个节点中只存在user表,sh2四个节点中只存在order_t表【以表为粒度实现了垂直拆分】。并且两个数据节点都实现了读写分离和高可用

[root@db02 ~]# mysql -uroot -p123456 -h10.0.0.52 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB //进入mycat逻辑库
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
| user |
+------------------+
2 rows in set (0.00 sec)

//分别插入数据
mysql> insert into user(id,name) values(1,'zs');
Query OK, 1 row affected (0.04 sec)

mysql> insert into user(id,name) values(2,'ls');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user(id,name) values(3,'w5');
Query OK, 1 row affected (0.01 sec)

mysql> insert into order_t(id,name) values(1,'zs2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into order_t(id,name) values(2,'ls2');
Query OK, 1 row affected (0.01 sec)

mysql> insert into order_t(id,name) values(3,'w52');
Query OK, 1 row affected (0.00 sec)

4.验证

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
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "use taobao;show tables;select * from order_t;"  //sh2主节点中只存在order_t表
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
+------------------+
+------+------+
| id | name |
+------+------+
| 1 | zs2 |
| 2 | ls2 |
| 3 | w52 |
+------+------+
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "use taobao;show tables;select * from user;" //sh1主节点中只存在user表
+------------------+
| Tables_in_taobao |
+------------------+
| user |
+------------------+
+------+------+
| id | name |
+------+------+
| 1 | zs |
| 2 | ls |
| 3 | w5 |
+------+------+