使用TcpDump 和 Wireshark 分析数据库异常

MySQL : 5.7.19
OS : rhel 6.3
jdbc : mysql-connector-java-5.1.45.jar
java : 17.0.7
问题 ：MySQL 数据库在修改 character_set_server 和 表的对应字段 为 utf8mb4 后，任然无法插入emoji 表情

官方文档是这么解释的，但是测试结果好像有偏差
https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

1. 抓包

1、登陆MySQL服务器，确定实际使用的网卡；
2、执行 tcpdump -s 65535 -x -nn -q -tttt  -i eth0 '((tcp) and (port 3306) and ((src host 10.57.xxx.xx) or (src host 10.xxx.xxx.xx)))' -w /opt/mysql_tcpdump233.pcap

其中，10.57.xxx.xx 为我本地ip，10.xxx.xxx.xx 为数据库IP

2. 用java 模拟客户端登录数据库

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
 
public class MySQLDemo {
 
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    // DB_URL    mysql://IP:PORT/schema_name
    // 指定字符集   characterEncoding=UTF8
    static final String DB_URL = "jdbc:mysql://10.xxx.xxx.xx:3306/wnmprd14?useSSL=false&characterEncoding=UTF8";

    // 数据库的用户名与密码
    static final String USER = "admin";
    static final String PASS = "wnmRds@233";
 
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
        
            // 打开链接
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
        
            // 执行
            stmt = conn.createStatement();
            String sql;
            sql = "show variables like '%char%'";
            ResultSet rs = stmt.executeQuery(sql);
        
            while(rs.next()){
                String Variable_name  = rs.getString("Variable_name");
                String Value = rs.getString("Value");
    
                System.out.print("Variable_name: " + Variable_name);
                System.out.print(", Value: " + Value);
                System.out.print("\n");
            }
            // 完成后关闭
             rs.close();
             stmt.close();
             conn.close();
        }catch(SQLException se){
            se.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            System.out.println("bye!");
        }
    }
}

执行该断代码后，模拟的是应用通过 jdbc 向数据库建立一个链接，并且返回当前连接的 字符集的相关信息

3. 使用 Wireshark 分析

用 Wireshark 打开mysql_tcpdump233.pcap 文件进行分析：

客户端，在与MySQL 数据库三次握手之后，向数据库请求了以下信息

/* mysql-connector-java-5.1.45 ( Revision: 9131eefa398531c7dc98776e8a3fe839e544c5b2 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS tx_isolation, @@wait_timeout AS wait_timeout

SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server
对应以上查询结果: 1,utf8,utf8,utf8,utf8mb4
在获取到character_set_server 的值为utf8mb4 后，任然进行了SET NAMES utf8操作