Amoeba：垂直切分

最新推荐文章于 2024-10-06 21:49:48 发布

crj2951

最新推荐文章于 2024-10-06 21:49:48 发布

阅读量122

点赞数

文章标签：数据库

1、之前的测试环境依然保留，在my.cnf中加入，并创建mydb数据库

replicate-ignore-db = mydb
[root@localhost:(none)][07:47:29am] MySQL-> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.02 sec)

2、创建测试表
master1：

[root@localhost:mydb][08:25:58am] MySQL-> show create table users\G
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`user_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`address` varchar(50) DEFAULT NULL,
`email` varchar(50) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

master2：

[root@localhost:mydb][08:29:13am] MySQL-> show create table groups\G
*************************** 1. row ***************************
Table: groups
Create Table: CREATE TABLE `groups` (
`group_id` int(11) NOT NULL,
`group_name` varchar(20) DEFAULT NULL,
`privilege_id` int(11) NOT NULL,
PRIMARY KEY (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

往表中插入数据

insert into users values (1,1,'john','zhejiang','a@163.com');
insert into users values (2,1,'james','bejing','b@163.com');
insert into users values (3,2,'kitty','shanghai','c@163.com');
insert into users values (4,4,'dave','hubei','d@163.com');
insert into users values (5,4,'mica','jiangsu','e@163.com');
insert into users values (6,3,'chole','zhejiang','f@163.com');
insert into users values (7,4,'ice','shanghai','g@163.com');
insert into groups values (1,'admin',1);
insert into groups values (2,'leder',2);
insert into groups values (3,'engineer',4);
insert into groups values (4,'sale',3)

3、修改rule.xml，注释所有的样例规则，在最后加入2行

<tableRule name="users" schema="mydb" defaultPools="master1" />
<tableRule name="groups" schema="mydb" defaultPools="master2" />

4、测试

root@proxy:~# mysql -uroot -p -h10.10.10.39 -P8066
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 906872446
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
[root@10.10.10.39:(none)][08:49:50am] MySQL-> use mydb;
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
[root@10.10.10.39:mydb][08:49:56am] MySQL-> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| users |
+----------------+
1 row in set (0.02 sec)
[root@10.10.10.39:mydb][08:50:08am] MySQL-> select * from mydb.users;
+---------+----------+-------+----------+-----------+
| user_id | group_id | name | address | email |
+---------+----------+-------+----------+-----------+
| 1 | 1 | john | zhejiang | a@163.com |
| 2 | 1 | james | bejing | b@163.com |
| 3 | 2 | kitty | shanghai | c@163.com |
| 4 | 4 | dave | hubei | d@163.com |
| 5 | 4 | mica | jiangsu | e@163.com |
| 6 | 3 | chole | zhejiang | f@163.com |
| 7 | 4 | ice | shanghai | g@163.com |
+---------+----------+-------+----------+-----------+
7 rows in set (0.02 sec)
[root@10.10.10.39:mydb][08:50:17am] MySQL-> select * from mydb.groups;
+----------+------------+--------------+
| group_id | group_name | privilege_id |
+----------+------------+--------------+
| 1 | admin | 1 |
| 2 | leder | 2 |
| 3 | engineer | 4 |
| 4 | sale | 3 |
+----------+------------+--------------+
4 rows in set (0.01 sec)

从amoeba连接到数据库，默认是master1，而且看不到groups表。

4.1 查看表连接的join查询

[root@10.10.10.39:mydb][08:55:52am] MySQL-> select user_id,group_id,name,address,email,group_name,privilege_id
-> from mydb.users a,mydb.groups b
-> where a.group_id=b.group_id;
ERROR 1146 (42S02): Table 'mydb.users' doesn

显示表不存在。

4.2 amoeba插入数据

[root@10.10.10.39:mydb][09:01:44am] MySQL-> insert into users values (8,2,'aice','beijing','h@163.com');
Query OK, 1 row affected (0.04 sec)
[root@10.10.10.39:mydb][09:01:46am] MySQL-> insert into groups values (5,'pmo',2);
Query OK, 1 row affected (0.02 sec)
[root@10.10.10.39:mydb][09:02:37am] MySQL-> select * from mydb.users;
+---------+----------+-------+----------+-----------+
| user_id | group_id | name | address | email |
+---------+----------+-------+----------+-----------+
| 1 | 1 | john | zhejiang | a@163.com |
| 2 | 1 | james | bejing | b@163.com |
| 3 | 2 | kitty | shanghai | c@163.com |
| 4 | 4 | dave | hubei | d@163.com |
| 5 | 4 | mica | jiangsu | e@163.com |
| 6 | 3 | chole | zhejiang | f@163.com |
| 7 | 4 | ice | shanghai | g@163.com |
| 8 | 2 | aice | beijing | h@163.com |
+---------+----------+-------+----------+-----------+
8 rows in set (0.00 sec)
[root@10.10.10.39:mydb][09:02:50am] MySQL-> select * from mydb.groups;
+----------+------------+--------------+
| group_id | group_name | privilege_id |
+----------+------------+--------------+
| 1 | admin | 1 |
| 2 | leder | 2 |
| 3 | engineer | 4 |
| 4 | sale | 3 |
| 5 | pmo | 2 |
+----------+------------+--------------+
5 rows in set (0.00 sec)