mysql服务端和客户端的网络通信实践

mysql服务端和客户端的网络通信实践

准备

环境

macos:12
mysql:8

软件

  • 监听mysql网络通信 - tshark
    macos 安装

    brew install –cask wireshark

1
2
3
4
$ tshark -v                                       
TShark (Wireshark) 4.4.1 (v4.4.1-0-g575b2bf4746e)

说明安装成功
  • time & java
    1
    $ time java -cp .:./mysql-connector-java-5.1.45.jar Test "jdbc:mysql://127.0.0.1:3306/test123?useSSL=false&useServerPrepstmts=true&cachePrepstmts=true&connectTimeout=500&socketTimeout=170000" username password "select sleep (2.6), id from sbtest1 where id=?" 1

java代码

Test.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
import java.lang.management.ManagementFactory;
import java.lang.management.RuntimeMXBean;
import java.sql.*;

public class Test {
public static void main(String args[]) throws NumberFormatException, InterruptedException, ClassNotFoundException {
try {
outPutPid();

System.out.println("开始执行---");
int i = System.in.read();
System.out.println("read input result:" + i);
}catch (Exception exception){
System.err.println("执行异常---");
}

Class.forName("com.mysql.jdbc.Driver");

String url = args[0];
String user = args[1];
String pass = args[2];
String sql = args [3];
// sql 参数
String interval = args [4];

try {
Connection conn = DriverManager.getConnection(url, user, pass);

while (true) {
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setFetchSize(Integer.MIN_VALUE);
stmt.setString(1, interval);
ResultSet rs = stmt.executeQuery();
rs.close();
stmt.close();


PreparedStatement stmt2 = conn.prepareStatement(sql);
stmt2.setString(1, interval);
rs = stmt2.executeQuery();

while (rs. next()) {
System.out. println("fine");
}
rs.close();
stmt2.close();

}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

public static void outPutPid() {
RuntimeMXBean runtimeMXBean = ManagementFactory.getRuntimeMXBean();
System.out.println(runtimeMXBean.getName());
System.out.println(runtimeMXBean.getVmName());
System.out.println(runtimeMXBean.getVmVendor());
String pid = runtimeMXBean.getName().split("@")[0];
System.out.println("Pid is:" + pid);
}
}

java代码所在目录及其下文件

1
2
3
4
5
6
7
8
9
$ ll
-rw-r--r-- 1 test staff 3.8K 11 10 20:39 PidAndPort.class
-rw-r--r-- 1 test staff 2.8K 11 10 20:38 PidAndPort.java
-rw-r--r-- 1 test staff 2.5K 11 10 20:39 Test.class
-rw-r--r-- 1 test staff 1.9K 11 10 20:19 Test.java
-rw-r--r-- 1 test staff 2.6K 11 10 20:39 Test2.class
-rw-r--r-- 1 test staff 2.0K 11 10 20:38 Test2.java
-rw-r--r--@ 1 test staff 976K 11 9 23:02 mysql-connector-java-5.1.45.jar
-rw-r--r-- 1 test staff 2.2M 11 9 23:01 mysql-connector-java-8.0.16.jar

实践

课件截图
课件截图

客户端连接

NOTE: 连接时加上 --ssL-mode=DISABLED

1
$ mysql -h127.1 --ssL-mode=DISABLED -uroot -p123 test

对mysql 3306端口的网络监听

1
$ tshark -i lo -Y "tcp.port==3306" -T fields -e frame.number -e frame.time -e frame.time_delta -e tcp.srcport -e tcp.dstport -e tcp.len -e _ws.col.Info -e mysql.query

执行java程序

1
$ time java -cp .:./mysql-connector-java-5.1.45.jar Test "jdbc:mysql://127.0.0.1:3306/test123?useSSL=false&useServerPrepstmts=true&cachePrepstmts=true&connectTimeout=500&socketTimeout=170000" username password "select sleep (2.6), id from sbtest1 where id=?" 1

内容来源

抓包技巧 QPS/RT和并发的关系

附录

Test2.java

Test2.java代码

java获取进程pid和端口 PidAndPort.java

PidAndPort.java代码