mysql proxy 连接池, 连接分发测试

mysql proxy 0.8.2

mysql :5.5.17

测试proxy最大连接设置为2时，3个连接执行SQL，连接共用，连接切换

 

 

 

服务器情况：
mysql server : host vms-005
mysql proxy: host vms-004

配置：
mysql proxy 连接池： min=1 max=2，只设置读写库proxy-backend-addresses，未设置只读slave

database两个：db1 & db2

 

测试1连接切换：

连接1 & 连接2

1、连接1登录
mysql -h192.168.110.90 -uops -p -Ddb1

MYSQL观察:
mysql> select * from information_schema.processlist where user='ops';
+--------+------+----------------------+------+---------+------+-------+------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO |
+--------+------+----------------------+------+---------+------+-------+------+
| 503433 | ops  | 192.168.110.90:54247 | db1   | Sleep   |    3 |       | NULL |
+--------+------+----------------------+------+---------+------+-------+------+
1 row in set (0.00 sec)

2、连接2登录
mysql -h192.168.110.90 -P3306 -uops -p -Ddb2
MYSQL观察: 看到连接2把连接1的db修改
mysql> select * from information_schema.processlist where user='ops';
+--------+------+----------------------+------+---------+------+-------+------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO |
+--------+------+----------------------+------+---------+------+-------+------+
| 503441 | ops  | 192.168.110.90:54255 | db2  | Sleep   |    3 |       | NULL |
| 503433 | ops  | 192.168.110.90:54247 | db2   | Sleep   |    3 |       | NULL |
+--------+------+----------------------+------+---------+------+-------+------+
2 rows in set (0.00 sec)


3、连接1查询
mysql> select * from tb1;
+----+-------+
| id | name  |
+----+-------+
|  1 | conn1 |
+----+-------+
1 row in set (0.00 sec)
MYSQL观察:这个查询说明proxy会记住db 并改回了db1
mysql> select * from information_schema.processlist where user='ops';
+--------+------+----------------------+------+---------+------+-------+------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO |
+--------+------+----------------------+------+---------+------+-------+------+
| 503441 | ops  | 192.168.110.90:54255 | db2  | Sleep   |   30 |       | NULL |
| 503433 | ops  | 192.168.110.90:54247 | db1   | Sleep   |    2 |       | NULL |
+--------+------+----------------------+------+---------+------+-------+------+
2 rows in set (0.00 sec)


4、连接1设置为db2
mysql> use db2;
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观察: 两边同为db2 了
mysql> select * from information_schema.processlist where user='ops';
+--------+------+----------------------+------+---------+------+-------+------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO |
+--------+------+----------------------+------+---------+------+-------+------+
| 503441 | ops  | 192.168.110.90:54255 | db2  | Sleep   |    3 |       | NULL |
| 503433 | ops  | 192.168.110.90:54247 | db2  | Sleep   |    3 |       | NULL |
+--------+------+----------------------+------+---------+------+-------+------+
2 rows in set (0.00 sec)


5\连接2设置为db1
mysql> use db1;
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观察: use db1 和select 不同，将2个process 都改为了db1
mysql> select * from information_schema.processlist where user='ops';
+--------+------+----------------------+------+---------+------+-------+------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO |
+--------+------+----------------------+------+---------+------+-------+------+
| 503441 | ops  | 192.168.110.90:54255 | db1   | Sleep   |    2 |       | NULL |
| 503433 | ops  | 192.168.110.90:54247 | db1   | Sleep   |    2 |       | NULL |
+--------+------+----------------------+------+---------+------+-------+------+
2 rows in set (0.00 sec)

 

 

--分隔--------------------------------------------------------------------------------------------------------------------------------------------

 

测试2事务：
从proxy 开始三个连接: 1连接db1, 2连接db2, 3连接db1
从mysql server 登录观察连接情况

注意：我们proxy最大连接设置为2
mysql server : set global general_log=on;

步骤
1、连接1登录db1
mysql -h192.168.110.90 -P3306 -uops -p123 -Ddb1
2、连接2登录db2
mysql -h192.168.110.90 -P3306 -uops -p123 -Ddb2
3、连接3登录db1
mysql -h192.168.110.90 -P3306 -uops -p123 -Ddb1

4、从mysql端看连接
mysql> select * from information_schema.PROCESSLIST where USER='ops';
+--------+------+----------------------+------+---------+------+-------+------+
| ID     | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO |
+--------+------+----------------------+------+---------+------+-------+------+
| 465633 | ops  | 192.168.110.90:60239 | db1  | Sleep   |    7 |       | NULL |
| 465622 | ops  | 192.168.110.90:60228 | db1  | Sleep   |    7 |       | NULL |
+--------+------+----------------------+------+---------+------+-------+------+
2 rows in set (0.00 sec)
可以看到三个连接登录成功，后端产生两个连接。
注意：连接的DB均为db1，也就是proxy将所有的连接改为了，最后一次登录的DB1

5、连接1查看自己的进程ID
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.41, for pc-linux-gnu (i686) using readline 5.1

Connection id:        465622
...略...................

6、连接2查看自己的进程ID
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.41, for pc-linux-gnu (i686) using readline 5.1

Connection id:        465633
...略...................

7、连接3查看自己的进程ID
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.1.41, for pc-linux-gnu (i686) using readline 5.1

Connection id:        465633

 

8、连接1在DB1建表,并插入1条记录
mysql> CREATE TABLE `tb1` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(32) DEFAULT NULL,
    -> primary key (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb1 values(1,'con1');
Query OK, 1 row affected (0.00 sec)

9、连接2在DB2建表,并插入1条记录
mysql> CREATE TABLE `tb2` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(32) DEFAULT NULL,
    -> primary key (id)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb2 values(1,'a');
Query OK, 1 row affected (0.00 sec)

10、再次观察三个连接的进程ID，没有变化！

11、从这里开始很关键，我要进行的操作是在连接3上进行两条SQL
use db2 和 select * from tb2

在执行第一句use db2 时，连接3会用连接2的进程，但是执行完这一句后不立即执行第二句。 而是在连接2中用for update 锁住记录，目的是让执行的第二句SQL进行连接进程切换，

 

我们期望切换到连接1的进程中执行.我们知道连接1是连接着db1的，如果连接3的进程切换没有将DB信息带给连接1。则会报连接1的db1中没有tb2表.(但实时并非预期，后面会有说明)


连接3、
mysql> use db2;
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

连接2、
mysql> select * from tb2 where id=1 for update;
+----+------+
| id | name |
+----+------+
|  1 | a    |
+----+------+
1 row in set (0.00 sec)

连接3、
mysql> select * from tb2 where id=1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select * from tb2 where id=1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    477951
Current database: db2

+----+-------+
| id | name  |
+----+-------+
|  1 | a     |
+----+-------+
1 row in set (0.00 sec)


我们看到连接3并没有通过proxy连接切换到连接1。
而是直接报错返回。
等重新执行相同SQL时重新建立了连接 477951
并且记住了上次连接的DB1
也突破了proxy的设置max=2

这里连接正确的将DB信息带到了其他连接中。重复执行了多次，均正常.