Architecture, Storage Engines, and Authentication

Two layers, not one#

MySQL is split into a server layer and a storage engine layer. The server parses SQL, checks permissions, and plans queries. The storage engine actually reads and writes data on disk.

 Client
   │
 ┌─▼──────────────────────────────┐
 │ Connection / auth / threads    │
 │ Parser ▶ Optimizer ▶ Executor  │   server layer
 └─▲──────────────────────────────┘
   │ handler API
 ┌─┴────────┐ ┌────────┐ ┌────────┐
 │ InnoDB   │ │ MyISAM │ │ Memory │   storage engines
 └──────────┘ └────────┘ └────────┘

That separation is why you can pick an engine per table.

InnoDB is the default for good reasons#

SHOW ENGINES;
SHOW TABLE STATUS WHERE Name = 'customers'\G

InnoDB gives you:

• Transactions with commit and rollback.
• Row-level locking, so two sessions can update different rows at once.
• Foreign keys that are actually enforced.
• Crash recovery through a redo log.

MyISAM has none of those. It locks whole tables and can corrupt on crash. You'll see it in old systems; you shouldn't create new tables with it.

What InnoDB does on a write#

When you UPDATE a row, InnoDB changes the page in memory (the buffer pool), writes a record to the redo log, and keeps the old version in an undo log so other transactions can still see it and so rollback works. The dirty page is flushed to disk later.

That's why a crash doesn't lose committed work: on restart, InnoDB replays the redo log. It's also why the redo log's flush setting (innodb_flush_log_at_trx_commit) is a durability versus speed trade-off.

The clustered index#

InnoDB stores table rows inside the primary key's B-tree. The table is the primary key index. This has real consequences: lookups by primary key are fast, and a huge or random primary key (like a UUID string) makes every secondary index bigger and inserts scatter across pages.

Authentication basics#

A MySQL account is 'user'@'host', not just a name. 'app'@'localhost' and 'app'@'%' are different accounts with different passwords and privileges.

SELECT user, host, plugin FROM mysql.user;

MySQL 8 defaults to caching_sha2_password. Older client libraries sometimes can't use it, and you'll see "authentication plugin cannot be loaded" errors. The fix is to upgrade the client, not to weaken the account.

When you connect, the server finds the best-matching user@host row, verifies the password with that account's plugin, then checks privileges on every statement.

Common mistakes#

• Assuming root@localhost and root@% are the same account.
• Leaving tables on MyISAM after importing an old dump. Check with SHOW CREATE TABLE.
• Choosing a long random string as the primary key without understanding the clustered index cost.

Practice#

Run SHOW ENGINES; and note which one is DEFAULT. Create a table with ENGINE=MyISAM, then try START TRANSACTION; INSERT ...; ROLLBACK; and check whether the row survives. Repeat with InnoDB. Write two sentences explaining the difference you observed.