30 Practical MySQL Key Notes

When I first learned MySQL, I studied connections, character sets, pages, indexes, transactions, and locks as separate topics. When I actually encountered a slow SQL query, however, it was still easy to get confused. I later found that many issues became much easier to understand once I connected these topics through a single sequence: how SQL enters the system, how data is stored, how indexes locate data, and how transactions keep data safe.

I. First Understand How MySQL Connects and Is Configured

▸ 1. `mysqld` Is the Server, and `mysql` Is the Client

First, remember these two names: `mysqld` is the server process that actually stores data and executes SQL, while `mysql` is the command-line client. MySQL listens on port 3306 by default. To temporarily use a different port, start it like this:

mysqld --port=3307

When connecting from the client, it is generally best to specify the host, port, and user explicitly:

mysql -h 127.0.0.1 -P 3307 -u root -p

▸ 2. `localhost` and `127.0.0.1` May Use Different Connection Methods

This is a detail I often overlooked. On Linux and macOS, `localhost` may use a Unix socket, while `127.0.0.1` explicitly uses TCP. The two connection methods may…