MySQL Jdbc 驱动应用和源码解析了解MySQL jdbc connector java 和 MySQL 客户端建

背景

了解MySQL jdbc connector java 和 MySQL 客户端建立连接过程中的协议和网络交互步骤。

概要

本次主要会从MySQL JDBC驱动的常规使用出发，然后介绍MySQL jdbc如何作为业务系统和MySQL数据库服务器之间的沟通媒介，来实现诸如连接建立、登录鉴权、查询和结果处理等基本操作的。具体内容会分如下几个部分。

准备工作

首先是准备工作，包括数据库服务器搭建，抓包工具安装和演示样例代码编写等。其次是介绍具体的连接创建，网络交互等过程。

wireshark 用来进行抓包分析协议和网络过程；
Idea + Java 写一个简单的样例来配合进行jdbc的解析；
一个运行的MySQL服务，本地使用了mysql 5.7.35，通过docker安装；
Mysql jdbc 驱动源码，使用maven可以直接获取，这里版本使用了5.1.48，不同版本会有所差异；

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>5.1.48</version>

</dependency>

第一部分初试JDBC

package com.demo.all.db;

import org.junit.After;

import org.junit.Before;

import org.junit.Test;

import java.sql.*;

public class JdbcTest {

Connection connection;

@Before

public void init() throws Exception {

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

String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&profileSQL=true";

String user = "root";

String pass = "pass";

connection = DriverManager.*getConnection*(url, user, pass);

}

@Test

public void testConnection() throws SQLException {

String sql = "select 1";

Statement stmt = connection.createStatement();

ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {

System.*out*.println(rs.getString(1));

}

stmt.close();

}

@After

public void destroy() throws SQLException {

if (null != connection) {

connection.close();

}

}

}

第二部分运行机制

获取参数
建立连接
执行握手登录

TCP三次握手建立连接

Greeting

Jdbc 获取MySQL服务端参数：

参数详情：

/* mysql-connector-java-5.1.48 ( Revision: 29734982609c32d3ab7e5cac2e6acee69ff6b4aa ) */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, @@collation_connection AS collation_connection, @@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, @@performance_schema AS performance_schema, @@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, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout;