MySQL Architecture

Connection Layer

The connection layer handles interactions between applications and MySQL through SQL commands.

• Connection Pool: Manages and buffers user connections, thread handling, and other caching needs.
• Management Srevices and Utilities: System management and control tools including backup/restore, replication, and clustering.
• SQL Interface: Processes incoming SQL commands and returns query results.
• Query Parser: Validates and parses SQL commands (permissions, syntax).
• Query Optimizer: Enhances query performance before execution.

Example:

SELECT id, name FROM users WHERE age = 40;

Optimization aspects:

1. Filters rows based on WHERE clause before retrieving all data.
2. Projects specific columns (id, name) rather than fetching entire rows.
3. Combines conditions efficiently to produce final result set.

• Query Cache: Returns cached results directly if available.
• Storage Engine Interface: Defines how data is stored, updated, and retrieved. Different engines offer various capabilities.

Storage Engines

MyISAM

Does not support transactions or foreign keys, offering faster access speeds. Suitable for read-heavy applications without strict transactional requirements.

InnoDB

Default engine since MySQL 5.5. Supports ACID properties including commit, rollback, and crash recovery. Requires more disk space but ideal for write-heavy workloads requiring consistency.

MEMORY

Stores data in memory for maximum speed, but lacks persistence. Ideal for temporary tables or fast access scenarios.

BLACKHOLE

Data written to this engine disappears immediately. Used in master-slave replication setups for distributing writes.

File Types

Physical Files

Includes log files, data files, configuration files, PID files, and socket files.

Log Files

• Error Log: Records issues for troubleshooting; default location /var/log/mysqld.log
• Binary Log: Used for backups and replication; records DDL, DML, and DCL operations
• Relay Log: Used in replication to receive data from master
• Slow Query Log: Monitors queries exceeding a specified time threshold

Configuration examples:

[mysqld]
log-error=/var/log/mysqld.log
log-bin=/var/log/mysql-bin/bin.log
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3

Configuration File

Search order:

/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

Parameters are grouped under [mysqld] (server options) and [client] (client options).

Data Files

• .frm: Contains table structure metadata for all storage engines.
• .MYD: MyISAM-specific file storing table data.
• .MYI: MyISAM-specific file containing index information.
• .ibd & ibdata: InnoDB files storing data and indexes. Two modes:
  • Shared tablespace: Uses ibdata files
  • Individual tablespace: Uses .ibd files per table

PID File

Contains process ID of the mysqld daemon, essential for Unix/Linux systems.

Socket File

Used for Unix/Linux clients to connect via Unix domain sockets instead of TCP/IP.

Database Terminology

Database

A collection of data files on the operating system or storage device. File types vary by storage engine:

• .frm: Table definition
• .MYD/.MYI: MyISAM data/index files
• .ibd/ibdata: InnoDB data files

Database Instance

Composed of background processes, threads, and shared memory segments. This is the actual entity that operates on databases.

Typically one instance corresponds to one database, although in clustered environments multiple instances may share a single database.

Database Server

Physical machine hosting the database instance. Logical components include:

mysqld_process + memory + datafiles | logfiles | pidfile ...
-> databases -> tables -> records (rows and columns)

MySQL Help Resources

• Official documentation
• Manual pages (man mysql)
• Built-in command-line help:

mysql> HELP;
mysql> HELP CREATE TABLE;
mysql> ? CONTENTS

Categories include:

• Account Management
• Administration
• Data Definition
• Data Manipulation
• Data Types
• Functions
• Geographic Features
• Language Structure
• Storage Engines
• Stored Routines
• Table Maintenance
• Transactions
• Triggers