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信息带到了其他连接中。重复执行了多次,均正常.
分享到:
相关推荐
MySQL Proxy 实现负载均衡测试 MySQL Proxy 实现负载均衡测试
mysql proxy搭建 mysql proxy 搭建实验文档,测试可使用
支持数据库连接池,能够有效解决PHP带来的数据库连接瓶颈 支持SQL92标准 遵守Mysql原生协议,跨语言,跨平台的通用中间件代理。 支持多个数据库连接,多个数据库,多个用户,灵活搭配。 支持mysql事物 采用协程...
mysql-proxy-0.8.5-windows-x86-32bit. 官网下载挺麻烦的,直接下载下来备份。 mysql proxy for windows
为您提供SMProxy MySQL数据库连接池下载,SMProxy(Swoole MySQL Proxy)一个基于 MySQL 协议,Swoole 开发的MySQL数据库连接池。原理:将数据库连接作为对象存储在内存中,当用户需要访问数据库时,首次会建立连接...
实现mysql读写分离。 forking socket模型。
mysql-proxy(mysql代理).txt 主要是配置 linux 环境下mysql的代理设置功能
NULL 博文链接:https://zhaoshijie.iteye.com/blog/1969432
该压缩包中包含了测试proxy和mycat对mysql主从的方法,测试环境的搭建步骤和测试对比结果
mysqlslap MySQL压力测试工具 mysql proxy安装配置 mysql 触发器 mysql与MongoDB语法对比 mysql分表的3种方法 MySQL数据库扩展小记 mysql架构方案 MySQL水平分区表实际操作总结 Mysql水平分表 mysql水平分表和垂直...
windows++mysql-proxy全部数据包 mysql代理
根据网上的资源,自己也跟着测试了一番,还有些问题,请见谅!
mysql-proxy 架构 读写分离的基本文档 目前可以部署使用
使用mysql-proxy实现mysql读写分离
安装mysql-proxy所需工具
bl-mid-proxy,中间件代理端目前本中间件,主要实现mysql连接池、redis连接池、crontab任务定时器、logging多色日志输出,四大功能。本中间件实质为一个服务端,通过http请求进行通信,任何编程语言都可以对接。提供...
Mysql Proxy 实现mysql读写分离.pdf
此文档中详细记载了,mysql proxy问题的解决方法,希望可以帮助到你!
mysqlproxy笔记mysqlproxy笔记
mysql proxy 教程