DEV Community

Kathir
Kathir

Posted on

Day 2: Understanding MySQL Client-Server Communication, Protocols, and Pages

#architecture #database #sql #tutorial

📚 Table of Contents

Introduction

Welcome to Day 2 of my MySQL learning journey.

In the previous blog, I learned about MySQL Storage Engines and MVCC.

Today, I learned how a Java application communicates with a MySQL server, why protocols are necessary, what happens internally when a connection is established, and how InnoDB stores data using pages.

In this blog, I'll be covering:

  • Why do we need Protocols?
  • What happens when Java connects to MySQL?
  • MySQL Request Flow
  • MySQL Server Components
  • What are Pages in MySQL?
  • Why does InnoDB use Pages?

Why do we need Protocol?

Protocols are simply a set of rules.

Imagine two people who speak two different languages. In this way, they can't understand each other. To be able to understand each other, they need a common language.

This problem takes place in computers also.

Java applications don't understand how MySQL stores data, and MySQL doesn't understand Java objects.

So both agree on one common language:

The MySQL Client/Server Protocol

This protocol defines:

  • How to connect
  • How to authenticate
  • How to send SQL queries
  • How to receive results
  • How to report errors
  • How to close the connection

Without this protocol, Java and MySQL could never communicate.

What Happens When Java Connects?

Consider the following Java code:

Connection conn = DriverManager.getConnection(
    "jdbc:mysql://192.168.1.10:3306/company",
    "root",
    "password"
);
Enter fullscreen mode Exit fullscreen mode

Step 1

The JDBC Driver extracts:

  • IP Address: 192.168.1.10
  • Port: 3306
  • Database: company
  • Username: root

Step 2

The operating system creates a TCP Socket.

A socket is one endpoint of a network communication channel.

Step 3

TCP performs the Three-Way Handshake to establish a reliable connection.

Complete MySQL Request Flow 

Java Application
        │
        â–¼
JDBC Driver
        │
        â–¼
MySQL Client/Server Protocol (Application Layer)
        │
        â–¼
TCP (Reliable Transport)
        │
        â–¼
IP (Routing)
        │
        â–¼
Ethernet / Wi-Fi
        │
        â–¼
Internet / LAN
        │
        â–¼
MySQL Server Socket
        │
        â–¼
Connection Manager
        │
        â–¼
Authentication & Authorization
        │
        â–¼
SQL Parser
        │
        â–¼
Preprocessor
        │
        â–¼
Optimizer
        │
        â–¼
Executor
        │
        â–¼
Storage Engine
        │
        â–¼
Data Files on Disk
Enter fullscreen mode Exit fullscreen mode

MySQL Server Components

Connection Manager

Responsibilities:

  • Manages client sessions
  • Manages client threads

Authentication & Authorization

Responsibilities:

  • Confirms user identity
  • Checks user permissions

SQL Parser

Responsibilities:

  • Validates SQL syntax
  • Creates a Parse Tree

Preprocessor

Responsibilities:

  • Resolves table names
  • Resolves column names
  • Checks object existence

Optimizer

Responsibilities:

  • Chooses an efficient execution plan
  • Decides whether to use indexes
  • Determines join order
  • Optimizes query execution

Executor

Responsibilities:

  • Executes the chosen execution plan

Storage Engine

Responsibilities:

  • Reads the actual data
  • Writes the actual data

Pages in MySQL

InnoDB does not store tables as one big continuous block.

Instead, it divides the table into Pages.

The table is simply a collection of many pages.

A Page is a fixed-size block of storage (typically 16 KB in InnoDB) that contains parts of a table, parts of an index, or other internal data.

Inside a Page

Suppose a row is about 100 bytes.

A 16 KB Page contains:

16 * 1024 = 16384 bytes
Enter fullscreen mode Exit fullscreen mode

Number of rows per page:

16384 / 100 = 163 rows
Enter fullscreen mode Exit fullscreen mode

So, one page can contain approximately 163 rows.

The next page contains the next set of rows.

A row is smaller than a page, and a page contains many rows.

How Data is Organized 

Database
    │
    â–¼
Tables
    │
    â–¼
Pages
    │
    â–¼
Rows
Enter fullscreen mode Exit fullscreen mode

A database contains tables.

A table is stored as many pages.

Each page contains many rows.

Why Pages Instead of Rows?

Suppose you want to find a particular row with:

SELECT * FROM Employee WHERE id = 150;
Enter fullscreen mode Exit fullscreen mode

Would the SSD read only Row 150?

No.

SSDs and HDDs read data in blocks, not individual rows.

So, MySQL reads the entire page, even if you only wanted one row.

Then it finds Row 150 inside that page.

Pages Can Store More Than Rows

A page doesn't always store table rows.

It can also store:

  • Indexes
  • Internal metadata
  • Other internal data structures

Conclusion

Today, I learned about pages in MySQL and how Java connectivity works with MySQL.

Thank you for reading!

This is Day 2 of my MySQL learning journey.

In the next blog, I'll continue exploring more internal concepts of MySQL as I progress through my learning journey.

Top comments (0)