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

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

准备

环境

macos:12
mysql:8

软件

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

  brew install –cask wireshark

$ tshark -v                                       
TShark (Wireshark) 4.4.1 (v4.4.1-0-g575b2bf4746e)

说明安装成功

• time & java

  $ 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

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代码所在目录及其下文件

$ 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

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

对mysql 3306端口的网络监听

$ 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程序

$ 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代码