mysql服务端和客户端的网络通信实践
准备
环境
macos:12
mysql:8
软件
- 监听mysql网络通信 - tshark
macos 安装brew install –cask wireshark
1 | $ tshark -v |
- 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.java1
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
62import 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 | $ ll |
实践
课件截图
客户端连接
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 |