This content originally appeared on DEV Community and was authored by Kathir
đź“š Table of Contents
- Introduction
- Why do we need Protocol?
- What Happens When Java Connects?
- Complete MySQL Request Flow
-
MySQL Server Components
- Connection Manager
- Authentication & Authorization
- SQL Parser
- Preprocessor
- Optimizer
- Executor
- Storage Engine
- Pages in MySQL
- Inside a Page
- How Data is Organized
- Why Pages Instead of Rows?
- Pages Can Store More Than Rows
- Conclusion
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"
);
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
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
Number of rows per page:
16384 / 100 = 163 rows
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
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;
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.
This content originally appeared on DEV Community and was authored by Kathir
Kathir | Sciencx (2026-07-07T15:55:20+00:00) Day 2: Understanding MySQL Client-Server Communication, Protocols, and Pages. Retrieved from https://www.scien.cx/2026/07/07/day-2-understanding-mysql-client-server-communication-protocols-and-pages/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.