The tcpcopy application of MySQL capacity planning

Official Document: Https://github.com/session-replay-tools/mysql-replay-module

Tcpcopy can replicate a formal environment request from the client to the Test side and reproduce it, in order to realistically plan for the capacity of MySQL, you can use Tcpcopy to connect the traffic on the line

Multiply the growth, copy it to the test environment, so as to quickly locate the test environment bottlenecks in the load situation, so as to do the capacity of the global control

Deployment

Masquerading client ip:1.1.1.4

Online server:1.1.1.1

Target server:1.1.1.2

Assistant server:1.1.1.3

Prerequisites:

1, three nodes of network interoperability without network card security restrictions (most cloud environment set security restrictions), tcpcopy with the-C option can be a server crawl of the package to copy one copy and the source IP

The client IP that is disguised as specified is sent to target, and if a security restriction is made, a network card cannot bind 2 IPs, so the online server rejects the sending of the replication package, in the online server

The package crawled through tcpdump will be as follows

Tcpdump-i Eth0-nn Port 3306 and host 1.1.1.4

An RST is sent every 3 seconds online server

About the TCP flag

Under normal circumstances, the pseudo-client will establish three handshake with target.

2, operating system shutdown Rp_filter, Kernel 2.6 version is off by default

echo 0 >/proc/sys/net/ipv4/conf/all/rp_filter

3. Write the user name password of the MySQL service to the configuration file according to the official document

4. Assistant server turns off the routing feature, which is off by default

Echo 0 >/proc/sys/net/ipv4/ip_forward

Specific operation

Online server

3306-1.1. 1.2:33061.1. 1.3 1.1. 1.4 3 -D
# If it is a multi-instance, Sourceport-targetip:targetport, separated by commas
/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

The tcpcopy will capture the current host's ' 3306 ' message and change the client's IP to 1.1.1.4, send these messages to target port ' 3306 ' of destination server 1.1.1.2, and connect 1.1.1.3 Ask Intercept Pass the response packet to yourself----3 is the part that replicates 3 times times the traffic to Target,tcpcopy will handle the conflict

Target server

3306 -D

Ntercept will capture TCP packets listening on port 3306 from the ETH0 network card

Assistant Server

1.1. 1.4 1.1. 1.3

Route all response packages for clients to Assistant server impersonate client traffic online server

# mysql-h1.1.1.1-uadmin-p123123-p3306
# Create a table with a primary key T2CREATE TABLE' T2 ' (' ID ')int( One) not NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDBDEFAULTCHARSET=UTF8 # Create a table with unconstrained fields T3CREATE TABLE' T3 ' (' ID ')int( One)DEFAULT NULL) ENGINE=InnoDBDEFAULTCHARSET=Utf8
# then insert a single piece of data in two tables
INSERT into T2 values (1);
INSERT into T3 values (1);

Target server

Mysql>show Processlist;+------+-------------+---------------------+------+---------+---------+--------------------------------------- -----------------+------------------+|Id| User        |Host|Db|Command|Time|State|Info|+------+-------------+---------------------+------+---------+---------+--------------------------------------- -----------------+------------------+|    One |SystemUser |                     | NULL |Connect| 3044244 |Slave hasRead  AllRelayLog; Waiting forMore Updates| NULL             ||    A |SystemUser |                     | NULL |Connect| 3044248 |Waiting forAn event fromCoordinator| NULL             ||    - |SystemUser |                     | NULL |Connect| 3044248 |Waiting forAn event fromCoordinator| NULL             ||    - |SystemUser |                     | NULL |Connect| 3044248 |Waiting forAn event fromCoordinator| NULL             ||    the |SystemUser |                     | NULL |Connect| 3044248 |Waiting forAn event fromCoordinator| 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             |+------+-------------+---------------------+------+---------+---------+-------------------------------------- ------------------+------------------+9Rowsinch Set(0.00Sec

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)

Can see 3 times times the simultaneous flow copy effect, corresponding to 3 reply; Table T2 is constrained, tcpcopy will deal with conflicting parts,

So the last thing you see is that there is only one piece of data in the T1 table, and the T2 table is 3 times times the result of a copy of the traffic

The tcpcopy application of MySQL capacity planning