Understanding MySQL Architecture: Files, Logs, and Configuration Explained

MySQL Architecture

MySQL consists of databases —collections of physical OS files such as .frm, .myd, .myi, .ibd (or in‑memory files for the NDB engine)—and database instances , which are the backend processes/threads plus a shared memory area that all threads can access.

Key MySQL File Types

Parameter file (my.cnf) : Stores startup locations, memory‑structure sizes, and other initialization settings.

Log files : Record MySQL’s responses to events, replication, and diagnostics.

Socket file : Enables local client connections via Unix domain sockets.

PID file : Contains the process ID of the MySQL instance.

Table definition files : .frm (schema) and .ibd (per‑table InnoDB data).

Storage‑engine files : Engine‑specific metadata (e.g., InnoDB tablespace files).

Parameter File (my.cnf)

MySQL reads my.cnf at startup. Common ways to locate it:

(1) Default location: mysql --help | grep my.cnf (2) From the running process: ps -eaf | grep mysql (3) Global search: find / -name my.cnf

Parameters are either dynamic (modifiable at runtime with SET GLOBAL …) or static (require editing my.cnf and restarting). Their scope can be GLOBAL or SESSION.

Log Files

Error log : Records startup, shutdown, runtime errors, warnings, and informational messages. Default filename ends with .err. Query location with SHOW VARIABLES LIKE 'log_error'; or set via log-error=/path/to/error.log in my.cnf.

Slow query log : Captures statements whose execution time exceeds long_query_time. Setting long_query_time=0 logs all queries. Output can be a file or the slow_log table, controlled by log_output.

General (full) query log : Logs every client request, useful for auditing but adds ~3‑5% overhead.

Binary log : Stores all data‑changing statements (excluding SELECT and SHOW) for recovery and replication. Enable with log-bin=/path/to/mysql-bin.log. Important variables: max_binlog_size (default 1 GB; many DBAs use 64 M) binlog_cache_size (in‑memory cache for uncommitted events) sync_binlog (disk‑flush frequency; 1 is safest) binlog-do-db / binlog-ignore-db (database filtering) log-slave-updates (records updates performed on a replica) binlog-format (options: STATEMENT, ROW, MIXED)

Redo log : InnoDB transaction log files ib_logfile0 and ib_logfile1. Inspect with SHOW VARIABLES LIKE 'innodb%log%';. Used for crash recovery.

Undo log : Holds before‑image records for rollback and MVCC. Stored in the shared tablespace ( ibdata1) or per‑table files when innodb_file_per_table is enabled.

Socket and PID Files

Socket file : Typically /tmp/mysql.sock or a custom path defined by socket=/path/to/mysql.sock in my.cnf. Locate with ps -eaf | grep mysql | grep socket.

PID file : Path controlled by pid_file. Query with SHOW VARIABLES LIKE 'pid_file'; or inspect the file directly.

Table Definition and InnoDB Data Files

.frm files store schema definitions; .ibd files store per‑table InnoDB data when innodb_file_per_table=1.

InnoDB tablespace files are defined by innodb_data_file_path, e.g., ibdata1:128M;ibdata2:128M:autoextend. Multiple files can compose a tablespace, and autoextend allows growth.

Practical Configuration Tips

Set max_binlog_size to a modest value (e.g., 64 M) to avoid very large binary logs.

Use sync_binlog=1 for maximum durability, understanding the potential performance impact.

Choose binlog-format based on workload: STATEMENT reduces size, ROW provides precise replication, MIXED balances both.

Understand the interaction between redo and undo logs to ensure point‑in‑time recovery and transaction rollback.

Illustration