How MySQL Works: Server and Client

MySQL has two sides that work together: a server and a client.

The server

The MySQL server is the program (called mysqld, the MySQL daemon) that runs in the background. It:

• Owns the data files.
• Listens for connections, by default on port 3306.
• Checks who is connecting and what they may do.
• Runs SQL statements and sends back results.

Only one program should touch the data files: the server. Everything else asks the server.

The client

A client is anything that connects to the server and sends SQL. Examples:

The client and the server may run on the same computer (typical on your own machine) or on different computers (typical in production).

The request cycle

Every query follows the same path:

1. The client connects to the server and signs in with a user name and password.
2. The client sends an SQL statement as text.
3. The server parses it (checks the grammar) and plans how to run it, choosing indexes and the join order.
4. The server runs the plan against the data.
5. The server sends back the result: rows, or a message such as “1 row affected”.

You can see a tiny part of this from SQL itself:

1

SELECT CURRENT_USER() AS signed_in_as, DATABASE() AS current_database;

1
2
3
4
5
6

+----------------+------------------+
| signed_in_as   | current_database |
+----------------+------------------+
| root@localhost | learn            |
+----------------+------------------+
1 row in set

CURRENT_USER() is who the server thinks you are. DATABASE() is the database you are working in, which is learn here.

Connections have a cost

Each connection uses memory on the server. A busy website does not open a new connection for every tiny task. It keeps a few and reuses them (connection pooling), and it limits how many are open at once.

Why this matters for you

• When something fails, ask: is it the server (not running, wrong port), the connection (wrong password, firewall) or the query (a mistake in the SQL)? Each has different symptoms.
• Your production database user needs only the permissions your application uses (see Application Database Users).