MySQL容量规划之tcpcopy应用之道
官方文档:https://github.com/session-replay-tools/mysql-replay-module
tcpcopy可以将正式环境上来自客户端的请求复制一份到测试端并复现,想要真实的对MySQL进行容量规划,可以借助tcpcopy来将线上的流量
呈倍数的增长,将其复制到测试环境,从而快速定位测试环境出现瓶颈时负载情况,进而做好容量的全局把控
部署
伪装客户端IP:1.1.1.4
online server:1.1.1.1
target server :1.1.1.2
assistant server:1.1.1.3
前提条件:
1、三个节点的网络互通无网卡的安全限制(大多数云环境设置了安全限制),tcpcopy通过 -c 选项可以将线上服务器抓取的包复制一份并将来源IP
伪装成指定的客户端IP发送给target,如果进行了安全限制,一个网卡无法绑定2个IP,所以online server则会拒绝发送复制包,在online server
上通过tcpdump抓取的包将会如下
tcpdump -i eth0 -nn port 3306 and host 1.1.1.4
每隔3秒online server会发送RST
关于tcp标志
正常情况下伪客户端会和target建立三次握手
2、操作系统关闭rp_filter,内核2.6版本默认是关闭
echo 0 > /proc/sys/net/ipv4/conf/all/rp_filter
3、按照官方文档将MySQL服务的用户名密码写入配置文件
4、assistant server关闭路由功能,默认是关闭的
echo 0 > /proc/sys/net/ipv4/ip_forward
具体操作
online server
/usr/local/src/tcpcopy/objs/tcpcopy -x 3306-1.1.1.2:3306 -s 1.1.1.3 -c 1.1.1.4 -n 3 -d
# 如果是多实例,sourcePort-targetIP:targetPort,以逗号分隔
/usr/local/src/tcpcopy/objs/tcpcopy -x 3306-1.1.1.2:3306,3307-1.1.1.2:3307 -s 1.1.1.3 -c 1.1.1.4 -n 3 -d
target server
/usr/local/src/intercept/objs/intercept -i eth0 -F tcp and src port 3306 -d
assistant server
route add -host 1.1.1.4 gw 1.1.1.3
# mysql -h1.1.1.1 -uadmin -p123123 -P3306
# 创建带有主键的表t2 CREATE TABLE `t2` ( `id` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 # 创建无约束字段的表t3 CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
# 然后在两个表中分别插入一条数据
insert into t2 values(1);
insert into t3 values(1);
target server
mysql> show processlist; +------+-------------+---------------------+------+---------+---------+--------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+-------------+---------------------+------+---------+---------+--------------------------------------------------------+------------------+ | 11 | system user | | NULL | Connect | 3044244 | Slave has read all relay log; waiting for more updates | NULL | | 12 | system user | | NULL | Connect | 3044248 | Waiting for an event from Coordinator | NULL | | 13 | system user | | NULL | Connect | 3044248 | Waiting for an event from Coordinator | NULL | | 14 | system user | | NULL | Connect | 3044248 | Waiting for an event from Coordinator | NULL | | 15 | system user | | NULL | Connect | 3044248 | Waiting for an event from Coordinator | NULL | | 3961 | root | localhost | NULL | Query | 0 | starting | show processlist | | 3962 | admin | 1.1.1.4:24695 | NULL | Sleep | 5 | | NULL | | 3963 | admin | 1.1.1.4:24286 | NULL | Sleep | 5 | | NULL | | 3964 | admin | 1.1.1.4:24759 | NULL | Sleep | 5 | | NULL | +------+-------------+---------------------+------+---------+---------+--------------------------------------------------------+------------------+ 9 rows in set (0.00 sec)
mysql> select * from t2;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> select * from t3;
+------+
| id |
+------+
| 1 |
| 1 |
| 1 |
+------+
3 rows in set (0.00 sec)
可以看到同时3倍的流量复制效应,对应的是3个回话;表t2有约束,tcpcopy会处理冲突的部分,
所以最后看到的是t1表中只有一条数据,而t2表中则是3倍流量复制的结果