You run mysql -u root -p, you type SQL queries, and it works. But what is running underneath? Which process handles your connections, where is the data stored, why does memory climb after a few minutes, and what is this buffer pool that everyone tells you to increase?
MySQL is not just a database, it is a complete system with its own vocabulary: instance, storage engine, InnoDB, buffer pool, redo log, binary log, data dictionary. InnoDB manages your data in memory and on disk with a journaling mechanism comparable to PostgreSQL's WAL. The binary log records every change for replication and restoration. This guide takes the internals apart so that you understand what you administer before touching the configuration.
What you will learn
- Describe MySQL's client/server architecture and understand the role of each layer (connector, parser, optimizer, storage engine)
- Explain why InnoDB is the default engine and what makes it transactional (ACID)
- Tell apart the three critical structures: the buffer pool (memory cache), the redo log (crash-safe journal) and the binary log (replication and point-in-time recovery, PITR)
- Identify MySQL threads and understand their role in handling connections and maintenance
- Differentiate the LTS track (8.4 and 9.7) from the Innovation track to choose the right version
- Navigate the files of a MySQL installation and know where to find the data, the configuration and the logs
In what context?
This guide is the starting point before any MySQL administration. You need it when:
- You are installing MySQL for the first time and want to understand what was created automatically (directories, files, system databases)
- You must diagnose a performance problem and you are told "increase the buffer pool", but you do not know what it is or how to size it
- You are preparing a backup strategy and must understand the difference between the redo log and the binary log before configuring anything
- You are reading the official documentation and the terms tablespace, doublewrite buffer and change buffer block you
- You must choose between an LTS version (8.4, 9.7) and an Innovation version for a new project
What this guide does not cover
This guide explains the architecture, it configures nothing and installs nothing. The hands-on steps live in the neighbouring guides of the module: Installation of MySQL 8.4 from the Oracle repository on Debian, Ubuntu or RHEL, Getting started with the mysql client to explore and query your databases, Configuration to tune InnoDB, memory, connections and logging, and Security for accounts, TLS, roles and least privilege. Each of them reuses the vocabulary set here.
The MySQL instance: one server, several databases
A single process: mysqld
Unlike PostgreSQL, which runs multi-process (one process per connection), MySQL uses a multi-threaded model: a single process, mysqld, handles everything. This process listens for connections, runs queries and coordinates I/O operations. Each connected client gets a dedicated thread inside this process.
The /proc/<PID>/status file of the Linux system shows the process name and the number of threads it carries:
grep -E '^(Name|Threads)' /proc/$(pidof mysqld)/statusName: mysqldThreads: 39One process, one PID, and 39 threads in this example (the number varies with the machine and the load). All connection handling, caching, I/O and maintenance happens inside this process through threads. In the test container, mysqld is process number 1: the command was run with /proc/1/status, since pidof is not installed there.
Several databases in one instance
A MySQL instance contains one or more databases (also called schemas, the two terms are synonyms in MySQL). Each database is a namespace containing tables, views, stored procedures and triggers.
On a fresh installation, four system databases exist:
SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+| Database | Role |
|---|---|
mysql | System database that holds user accounts, privileges, scheduled events and internal metadata. It is the equivalent of PostgreSQL's system catalog. |
information_schema | Read-only view of the metadata of all databases: tables, columns, indexes, constraints. Useful for introspection. |
performance_schema | Collects real-time metrics: queries, locks, I/O, threads, memory. It is MySQL's internal instrumentation system. |
sys | Simplified views that combine performance_schema and information_schema to ease diagnosis. |
Database and schema: the same thing in MySQL
In MySQL, CREATE DATABASE and CREATE SCHEMA are strictly synonymous. A schema = a database = a directory in the datadir. This is a fundamental difference from PostgreSQL, where one database contains several schemas.
-- These two commands are identicalCREATE DATABASE labdb;CREATE SCHEMA labdb;In the file system, each database corresponds to a directory. The following example assumes that you created the labdb database and its clients table by following the "Preparing a sample database" section of the Getting started with the mysql client guide (five rows, nothing else):
LC_ALL=C ls -x -w 110 /var/lib/mysql/#ib_16384_0.dblwr #ib_16384_1.dblwr #innodb_redo #innodb_temp auto.cnfbinlog.000001 binlog.000002 binlog.index ca-key.pem ca.pemclient-cert.pem client-key.pem ib_buffer_pool ibdata1 ibtmp1labdb mysql mysql.ibd mysql.sock mysql_upgrade_historyperformance_schema private_key.pem public_key.pem server-cert.pem server-key.pemsys undo_001 undo_002The mysql/, performance_schema/ and sys/ directories correspond to the system databases. When you create a labdb database, a labdb/ directory appears with the .ibd files of each table. The two #ib_16384_*.dblwr files belong to the doublewrite buffer, discussed below, and #innodb_redo/ holds the redo log.
Two clarifications on what you see. mysql.sock is not the socket itself: in the official image it is a symbolic link to /var/run/mysqld/mysqld.sock, where the real socket lives. And LC_ALL=C freezes the sort order: without it, a terminal using a French locale ranks the names that start with # elsewhere.
The client/server model
MySQL works as client/server. The server (mysqld) listens on a Unix socket (local connection) or a TCP port (network connection, 3306 by default). The client (mysql, mysqldump, mysqlsh, or any application) connects to the server, sends SQL queries and receives the results.
The internal layers of mysqld
When an SQL query arrives, it goes through several layers inside mysqld:
-
Connector, handles authentication (the
caching_sha2_passwordplugin by default since MySQL 8.0), and maintains the session and the thread dedicated to the client -
SQL parser, analyzes the syntax of the query and builds a syntax tree (AST). If the syntax is invalid, the error is returned here
-
Optimizer, chooses the most efficient execution plan: which index to use, in which order to join the tables, whether a sort or a full scan is needed. This is the part that determines performance
-
Executor, runs the chosen plan by calling the storage engine API. This is where MySQL delegates reading and writing data to InnoDB (or another engine)
-
Storage engine (InnoDB), handles physical storage: memory cache (buffer pool), journaling (redo log), writing to disk, row locking, ACID transactions
This layered architecture explains why MySQL supports several storage engines: the SQL layer is independent of the engine. In practice, though, InnoDB is the only engine to use for production data.
Local connection vs network connection
With the Oracle packages (.deb or .rpm), MySQL listens on two channels: the Unix socket and the TCP port. The shipped configuration file contains no bind-address line, so the variable keeps its default value * (MySQL 8.4 documentation, "Server System Variables"): the TCP port is open on all interfaces, not only locally. The distribution's mysql-server package may behave differently: read the real value with SELECT @@bind_address; rather than assuming it.
# Unix socket (local connection)ls -la /var/run/mysqld/mysqld.socksrwxrwxrwx 1 mysql mysql 0 Oct 20 19:18 /var/run/mysqld/mysqld.sock# TCP port (network connection)ss -tlnp | grep 3306With the Oracle packages, you get two LISTEN lines for mysqld, on ports 3306 (classic protocol) and 33060 (X protocol), with a local address of *, 0.0.0.0 or [::] and not 127.0.0.1. The details of the check and of the restriction are in the Installation guide.
The Unix socket is faster than TCP for local connections (no network layer). It is what mysql -u root -p uses by default when you do not specify -h. The client options are detailed in the Getting started with the mysql client guide.
| Type | Command | Use |
|---|---|---|
| Unix socket | mysql -u root -p | Local connection, administration |
| TCP localhost | mysql -h 127.0.0.1 -u root -p | Local connection over TCP |
| TCP network | mysql -h 192.168.122.70 -u labadmin -p | Connection from another server |
Storage engines: InnoDB at the center
InnoDB: the default engine
InnoDB has been MySQL's default storage engine since version 5.5 (2010). All tables should use InnoDB, except in very specific cases. It is the only engine that offers all the guarantees expected of a modern database:
| Feature | InnoDB | MyISAM |
|---|---|---|
| ACID transactions | Yes: COMMIT, ROLLBACK | No |
| Foreign keys | Yes: FOREIGN KEY | No |
| Locking | Yes: row-level | No: table-level |
| Crash recovery | Yes: via the redo log | No: manual repair |
| MVCC | Yes: non-blocking reads | No |
| Fulltext | Yes (since 5.6) | Yes |
| Compression | Yes: PAGE_COMPRESSED | Yes (different format) |
ACID stands for: Atomicity (a whole transaction succeeds or fails), Consistency (constraints are always respected), Isolation (concurrent transactions do not see each other), Durability (a committed transaction survives a crash).
MVCC (Multi-Version Concurrency Control) means that reads do not block writes and vice versa. Each transaction sees a consistent snapshot of the data, even if other transactions modify the same rows at the same time. This is what allows MySQL to handle thousands of simultaneous connections without SELECT statements being blocked by UPDATE statements.
To check a table's engine:
SHOW TABLE STATUS FROM labdb WHERE Name = 'clients'\G*************************** 1. row *************************** Name: clients Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 5 Avg_row_length: 3276 Data_length: 16384Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 6 Create_time: 2026-10-20 19:20:49 Update_time: 2026-10-20 19:20:49 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment:The Engine: InnoDB field confirms the engine. Row_format: Dynamic is the default format since MySQL 5.7, and it handles variable-size columns (VARCHAR, TEXT, BLOB) efficiently. Index_length: 16384 comes from the unique index on email: the primary key, for its part, lives in the 16,384 bytes of Data_length. Rows is an estimate for an InnoDB table.
Other engines (Memory, MyISAM, NDB)
MySQL supports other engines, but they serve very specific use cases:
| Engine | Use | Limits |
|---|---|---|
| Memory (HEAP) | Temporary tables in RAM, session caches | Data lost at restart, no transactions |
| MyISAM | Legacy, do not use for new tables | No transactions, table locking, crash = manual repair |
| NDB (Cluster) | MySQL Cluster (NDB Cluster) for distributed high availability | Complex architecture, very specific use cases (telecom, real time) |
| ARCHIVE | Compressed read-only tables, historical logs | No indexes, no UPDATE, no DELETE |
| CSV | Import/export of CSV files | No indexes, no transactions |
SHOW ENGINES;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine | Support | Comment | Transactions | XA | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL || MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO || InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO || MyISAM | YES | MyISAM storage engine | NO | NO | NO || FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL || ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL || MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO || BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO || CSV | YES | CSV storage engine | NO | NO | NO || ARCHIVE | YES | Archive storage engine | NO | NO | NO |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+The Support column distinguishes three states: DEFAULT for the engine used when CREATE TABLE names none, YES for an available engine, NO for an engine that is present but inactive (this is the case of ndbcluster, which requires an NDB cluster, and of FEDERATED). The order of the rows has no meaning.
InnoDB internal architecture
InnoDB is the heart of MySQL. Understanding its internal structures lets you diagnose performance problems and configure memory correctly. Here are the components to know, from the most useful to the most historical:
The buffer pool
The buffer pool is InnoDB's main memory area. It is a page cache (16 KB per page) that stores both the data and the indexes of InnoDB tables. When MySQL reads a row, it loads the page containing that row into the buffer pool. Later reads of the same page are served from memory, without disk access.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';+-------------------------+-----------+| Variable_name | Value |+-------------------------+-----------+| innodb_buffer_pool_size | 134217728 |+-------------------------+-----------+134,217,728 bytes = 128 MB, which is the default value. It suits a small lab, but not production. On a server dedicated to MySQL, this site keeps 50 to 70% of physical RAM for the buffer pool, the rule developed in the Configuration guide. The rest is needed by the other MySQL structures (sort buffers, temporary tables, threads) and by the operating system's file cache. On a server with 4 GB of RAM where MySQL is the only service, innodb_buffer_pool_size = 2G to 2800M is therefore reasonable.
To see the current buffer pool usage:
SHOW ENGINE INNODB STATUS\GThe BUFFER POOL AND MEMORY section shows:
----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 0Dictionary memory allocated 618912Buffer pool size 8192Free buffers 6920Database pages 1272Old database pages 486Modified db pages 0The values move from one second to the next: read the proportions, not the exact figures. Here, 6,920 free pages out of 8,192 mean that the buffer pool is used at about 15%.
| Metric | Meaning |
|---|---|
| Buffer pool size | Total number of pages (8,192 × 16 KB = 128 MB) |
| Free buffers | Free, unused pages |
| Database pages | Pages holding cached data |
| Modified db pages | Pages modified in memory but not yet written to disk (dirty pages) |
When Free buffers reaches 0, InnoDB has to evict old pages to load new ones, and that is where performance degrades. The Configuration guide details how to size the buffer pool.
The redo log (InnoDB log)
The redo log is InnoDB's crash recovery journal. Every data modification is first written to the redo log before being applied to the data files. This is the Write-Ahead Logging mechanism, the same principle as PostgreSQL's WAL.
Since MySQL 8.0.30, the redo log files are located in #innodb_redo/:
ls /var/lib/mysql/#innodb_redo/#ib_redo10_tmp #ib_redo11_tmp #ib_redo12_tmp #ib_redo13_tmp #ib_redo14_tmp#ib_redo15_tmp #ib_redo16_tmp #ib_redo17_tmp #ib_redo18_tmp #ib_redo19_tmp#ib_redo20_tmp #ib_redo21_tmp #ib_redo22_tmp #ib_redo23_tmp #ib_redo24_tmp#ib_redo25_tmp #ib_redo26_tmp #ib_redo27_tmp #ib_redo28_tmp #ib_redo29_tmp#ib_redo30_tmp #ib_redo31_tmp #ib_redo32_tmp #ib_redo33_tmp #ib_redo34_tmp#ib_redo35_tmp #ib_redo36_tmp #ib_redo37_tmp #ib_redo38_tmp #ib_redo39_tmp#ib_redo8 #ib_redo9The directory holds 32 files. The files without _tmp (here #ib_redo8 and #ib_redo9) are active (being written). The other thirty, suffixed _tmp, are spares (recycled when the active ones are full). The numbers depend on the age of the instance: they advance as files are recycled. InnoDB manages their overall capacity through innodb_redo_log_capacity. The old innodb_log_file_size and innodb_log_files_in_group variables are now deprecated.
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';+--------------------------+-----------+| Variable_name | Value |+--------------------------+-----------+| innodb_redo_log_capacity | 104857600 |+--------------------------+-----------+104,857,600 bytes = 100 MB by default, the overall capacity allocated to the redo logs. InnoDB automatically manages the size and number of files within this envelope.
The doublewrite buffer
The doublewrite buffer is a protection against partial writes to disk. The problem: an InnoDB page is 16 KB, but the file system may write in 4 KB blocks. If the server crashes in the middle of writing a page, only part of it is written and the page is corrupted.
The mechanism:
- Before writing a modified page to disk, InnoDB first writes it to the doublewrite buffer (dedicated files on disk)
- Then it writes the page to its final location in the tablespace
- If a crash interrupts step 2, InnoDB finds the intact copy in the doublewrite buffer at restart and rewrites it
Since MySQL 8.0.20, the doublewrite buffer is no longer stored in the system tablespace (ibdata1). InnoDB uses dedicated doublewrite files, controlled by the innodb_doublewrite_dir and innodb_doublewrite_files variables:
ls /var/lib/mysql/#ib_*dblwr*/var/lib/mysql/#ib_16384_0.dblwr /var/lib/mysql/#ib_16384_1.dblwrSHOW VARIABLES LIKE 'innodb_doublewrite%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| innodb_doublewrite | ON || innodb_doublewrite_batch_size | 0 || innodb_doublewrite_dir | || innodb_doublewrite_files | 2 || innodb_doublewrite_pages | 128 |+-------------------------------+-------+The 128 value of innodb_doublewrite_pages is the MySQL 8.4 default (128, was 64 in 8.0), the one the Configuration guide gives in its table of changed defaults. It sets the number of pages that each thread writes to the doublewrite buffer in one go.
Never disable the doublewrite buffer unless your file system guarantees atomic 16 KB writes (such as ZFS or some SANs with atomic writes). On ext4 or XFS, the risk of corruption is real without this protection.
The system tablespace and file-per-table tablespaces
A tablespace is a logical container that groups InnoDB data files. MySQL uses two types of tablespaces:
The system tablespace (ibdata1) hosts InnoDB's internal structures, including the area reserved for the change buffer (see below: this mechanism is inactive by default in 8.4). Since MySQL 8.0.20, the doublewrite buffer is stored in dedicated files (.dblwr), and the undo logs live in separate undo tablespaces (undo_001, undo_002):
ls -lh /var/lib/mysql/ibdata1-rw-r----- 1 mysql mysql 12M Oct 20 19:20 /var/lib/mysql/ibdata1File-per-table tablespaces: each InnoDB table is stored in its own .ibd file. This has been the default behavior since MySQL 5.6:
ls /var/lib/mysql/labdb/clients.ibdOne file per table: labdb holds only one table, hence a single .ibd file (131,072 bytes according to ls -l, that is 128 KB, while Data_length and Index_length report 32 KB: InnoDB reserves space in advance).
Each .ibd file contains the data and the indexes of the corresponding table. This model has several advantages:
| Advantage | Explanation |
|---|---|
| Space management | A DROP TABLE immediately frees disk space (deletes the .ibd) |
| Targeted backup | A single table can be copied/restored |
| Compression | Each table can be compressed independently |
| Monitoring | The size of each table is visible in the filesystem |
SHOW VARIABLES LIKE 'innodb_file_per_table';+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| innodb_file_per_table | ON |+-----------------------+-------+The change buffer
The change buffer is a historical InnoDB mechanism for writes on secondary indexes, and MySQL 8.4 disables it by default. To understand what it did, start from an insertion: MySQL must update not only the table but also all its indexes. If the index page is not already in memory (in the buffer pool), InnoDB has to load it from disk, an expensive operation.
With the change buffer active, InnoDB caches the change instead of loading the page. When the index page is eventually read (by a SELECT or during the next merge), the change is applied at that point. The point was to reduce random I/O on mechanical disks under heavy write load. With SSDs and more RAM, the gain has become negligible, hence the change of default.
Check the state on your server:
SHOW VARIABLES LIKE 'innodb_change_buffer%';+-------------------------------+-------+| Variable_name | Value |+-------------------------------+-------+| innodb_change_buffer_max_size | 25 || innodb_change_buffering | none |+-------------------------------+-------+innodb_change_buffering = none means that no operation is buffered: the mechanism is switched off. innodb_change_buffer_max_size (25% of the buffer pool at most) then plays no role. To re-enable it on a fleet of mechanical disks, the value all restores the old behavior; the Configuration guide gives the reasoning and the table of defaults changed in 8.4.
The binary log: replication and PITR
Binlog vs redo log
MySQL uses two distinct journaling systems with completely different roles:
| Redo log | Binary log (binlog) | |
|---|---|---|
| Managed by | InnoDB (storage engine) | MySQL Server (SQL layer) |
| Content | Physical changes to InnoDB pages | Logical events (INSERT, UPDATE, DELETE, DDL) |
| Use | Crash recovery (restart after a crash) | Replication + PITR (restoring to a precise point in time) |
| Files | #innodb_redo/#ib_redo* | binlog.000001, binlog.000002, … |
| Rotation | Automatic, circular recycling | Rotation by size (max_binlog_size) or flush |
| Retention | Short term (between checkpoints) | Configurable (binlog_expire_logs_seconds, default: 30 days) |
In short: the redo log is a journal internal to InnoDB so that it can recover after a crash. The binary log is a global journal of all modifications, used to send changes to replicas and to restore data to a precise point in time.
Binary log formats (ROW, STATEMENT, MIXED)
The binary log can record data changes in three formats:
| Format | What is recorded | Advantage | Drawback |
|---|---|---|---|
| ROW | The before/after image of each modified row | Deterministic, no ambiguity | Larger binlog for massive UPDATE statements |
| STATEMENT | The SQL query itself (UPDATE ... WHERE ...) | Compact binlog | Non-deterministic (NOW(), UUID(), random functions) |
| MIXED | STATEMENT when possible, ROW when needed | Compromise | Behavior sometimes unpredictable |
SHOW VARIABLES LIKE 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW |+---------------+-------+ROW has been the default format since MySQL 5.7.7 and it is the recommended format: the MySQL 8.4 documentation declares the binlog_format variable deprecated and states that only row-based logging should be used for any new replication, the other formats possibly being removed in a future version. It guarantees deterministic replication, the replica applies exactly the same changes as the source, whatever functions are used in the query.
To see the content of the binary log:
mysqlbinlog --verbose /var/lib/mysql/binlog.000002 | head -40The binary log contains structured events: Query_event for DDL, Write_rows_event for INSERT, Update_rows_event for UPDATE, Delete_rows_event for DELETE.
The data dictionary (since MySQL 8.0)
Before MySQL 8.0, table metadata (columns, indexes, types) was stored in .frm files, one file per table, in a non-transactional proprietary format. A crash during a DDL statement (Data Definition Language, the commands that change the structure: ALTER TABLE, DROP TABLE) could create inconsistencies between the .frm files and the InnoDB data.
Since MySQL 8.0, the data dictionary is stored in transactional InnoDB tables, in the mysql.ibd tablespace. No more .frm files. DDL operations are now atomic: a DROP TABLE on several tables succeeds or fails entirely, with no intermediate state.
ls /var/lib/mysql/mysql.ibd-rw-r----- 1 mysql mysql 32505856 Oct 20 19:20 /var/lib/mysql/mysql.ibdThis single file contains all the metadata: tables, columns, indexes, constraints, charset, collation, routines, views, for all the databases of the instance.
Practical impact: you will no longer see .frm files in the database directories. The INFORMATION_SCHEMA views are fed by the data dictionary (faster than before). And DDL operations are safer.
MySQL threads
Connection threads
When a client connects to MySQL, the server creates a connection thread (foreground thread) dedicated to that session. This thread runs the client's queries, manages transactions and returns the results. On disconnection, the thread can be recycled through the thread cache:
SHOW VARIABLES LIKE 'thread_cache_size';+-------------------+-------+| Variable_name | Value |+-------------------+-------+| thread_cache_size | 9 |+-------------------+-------+Instead of destroying the thread at each disconnection and creating a new one at each connection, MySQL keeps 9 threads in reserve (by default). This reduces the cost of creating threads for applications with many short-lived connections.
To see the currently active threads:
SHOW PROCESSLIST;+----+-----------------+-----------+-------+---------+------+------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------+-------+---------+------+------------------------+------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 162 | Waiting on empty queue | NULL || 47 | root | localhost | labdb | Query | 0 | init | SHOW PROCESSLIST |+----+-----------------+-----------+-------+---------+------+------------------------+------------------+Each row represents a thread visible in the list: your own session (root, which runs SHOW PROCESSLIST) and the event_scheduler thread, the server's event scheduler, waiting for work (Command is Daemon). The identifiers and the elapsed time change from one instance to another. The maximum number is controlled by max_connections (default: 151). Beyond that, MySQL refuses connections with the Too many connections error.
Background threads (purge, page cleaner, log writer)
In addition to connection threads, InnoDB starts background threads that handle continuous maintenance:
| Thread | Role | Control parameter |
|---|---|---|
| Master thread | Coordinates background operations, triggers flushes | - |
| Page cleaner | Writes the buffer pool's dirty pages to the .ibd files | innodb_page_cleaners (1 on the test server) |
| Purge thread | Removes old row versions (MVCC) that have become useless | innodb_purge_threads (1 on the test server) |
| Log writer | Writes the redo log buffer entries to the redo files | - |
| Log flusher | Synchronizes (fsync) the redo log files to disk | - |
| Checkpoint | Marks the positions in the redo log up to which data has been written to disk | - |
The two settings in the right-hand column adapt to the machine: the test server (16 cores, 128 MB buffer pool, hence a single buffer pool instance) set them to 1 each, which SHOW VARIABLES LIKE 'innodb_page_cleaners' and 'innodb_purge_threads' confirm. Do not memorize a "default of 4": read the value on your own server. The following query lists InnoDB's background threads.
SELECT DISTINCT name, typeFROM performance_schema.threadsWHERE type = 'BACKGROUND' AND name LIKE 'thread/innodb/%'ORDER BY name;+---------------------------------------------+------------+| name | type |+---------------------------------------------+------------+| thread/innodb/buf_dump_thread | BACKGROUND || thread/innodb/buf_resize_thread | BACKGROUND || thread/innodb/clone_gtid_thread | BACKGROUND || thread/innodb/dict_stats_thread | BACKGROUND || thread/innodb/fts_optimize_thread | BACKGROUND || thread/innodb/io_ibuf_thread | BACKGROUND || thread/innodb/io_read_thread | BACKGROUND || thread/innodb/io_write_thread | BACKGROUND || thread/innodb/log_checkpointer_thread | BACKGROUND || thread/innodb/log_files_governor_thread | BACKGROUND || thread/innodb/log_flush_notifier_thread | BACKGROUND || thread/innodb/log_flusher_thread | BACKGROUND || thread/innodb/log_write_notifier_thread | BACKGROUND || thread/innodb/log_writer_thread | BACKGROUND || thread/innodb/page_flush_coordinator_thread | BACKGROUND || thread/innodb/srv_error_monitor_thread | BACKGROUND || thread/innodb/srv_lock_timeout_thread | BACKGROUND || thread/innodb/srv_master_thread | BACKGROUND || thread/innodb/srv_monitor_thread | BACKGROUND || thread/innodb/srv_purge_thread | BACKGROUND |+---------------------------------------------+------------+DISTINCT groups threads of the same name: a name such as io_read_thread stands for several threads (eight on this server, the value of innodb_read_io_threads). The real names carry the thread/innodb/ prefix, which the previous table abbreviates. The srv_purge_thread thread is the purge thread, page_flush_coordinator_thread drives the page cleaners, and log_writer_thread and log_flusher_thread are the log writer and the log flusher.
Unlike PostgreSQL, where workers are processes visible with ps aux, InnoDB threads are internal to the mysqld process. You will not see them in ps, use performance_schema.threads to inspect them.
MySQL 8.4 LTS vs Innovation (9.x): which track to choose
Since 2023, Oracle has published MySQL on two parallel tracks, LTS and Innovation. The MySQL documentation describes both as production quality. As of 2026-10-20, Oracle's supported platforms page lists MySQL 8.4 LTS and MySQL 9.7 LTS. After 9.7, numbering switches to a calendar scheme (YY.M.P, for example 26.7.1): the number does not tell you whether a release is LTS or Innovation, it is a property announced for each release.
| LTS (Long Term Support) | Innovation | |
|---|---|---|
| Current versions | 8.4 (8.4.0: 2024-04-30) and 9.7 (9.7.0: 2026-04-21) | Dated releases, for example 9.x then 26.x (26.7.0: 2026-07-28) |
| Positioning | Functional stability | Faster access to new features |
| Quality | Production-grade | Production-grade |
| Support | Oracle Lifetime Support: 5 years of Premier Support + 3 years of Extended Support | Until the next Innovation release |
| API stability | No feature removal within an LTS series (except the first release of the series) | Behavior changes and removals possible |
| Use case | Stable production, standardization, certifications | Quick adoption of new features, teams ready to keep up with the pace |
| Updates | Series fixes (8.4.x, 9.7.x) | New Innovation release each cycle |
SELECT VERSION();+-----------+| VERSION() |+-----------+| 8.4.11 |+-----------+MySQL 8.0 is end of life. The concepts in this guide remain valid for understanding MySQL 8.0, but for a new deployment, choose a supported LTS version. According to the official release notes, MySQL 8.0 reached end of life (EoL) in April 2026, with version 8.0.46 of 2026-04-21.
Notable changes in MySQL 8.4 LTS compared with 8.0:
- The binary log remains enabled by default, as since MySQL 8.0 (no explicit
--log-binneeded). The exception is a data directory initialized by hand with--initialize - The
mysql_native_passwordplugin has been deprecated since 8.0.34, is disabled by default in 8.4 (re-enabled with--mysql-native-password=ON) and has been removed since MySQL 9.0.0: for new deployments, usecaching_sha2_password - The
innodb_log_file_sizeandinnodb_log_files_in_groupvariables are deprecated in favor ofinnodb_redo_log_capacity CHANGE MASTER TOis removed in 8.4: useCHANGE REPLICATION SOURCE TO, whoseMASTER_*options becomeSOURCE_*SHOW SLAVE STATUSis removed in 8.4, as areSTART SLAVEandSTOP SLAVE: useSHOW REPLICA STATUS,START REPLICAandSTOP REPLICA- The term slave is replaced by replica everywhere in the documentation and the commands
The key files of a MySQL installation
Here is the layout of a MySQL 8.4 installation with the role of each file. The content of /var/lib/mysql/ was captured on 8.4.11 (official image); /etc/mysql/ and /var/log/mysql/ are the locations of a Debian package, not replayed here:
/etc/mysql/├── my.cnf → Main file (includes conf.d/ and mysql.conf.d/)├── conf.d/│ └── mysql.cnf → mysql client options└── mysql.conf.d/ └── mysqld.cnf → mysqld server configuration
/var/lib/mysql/ → datadir (all the data)├── #ib_16384_0.dblwr, …_1.dblwr → Doublewrite buffer├── #innodb_redo/ → Redo log files├── #innodb_temp/ → Temporary tablespaces├── auto.cnf → Server UUID (unique per instance)├── binlog.000001, binlog.000002 → Binary log (replication + PITR)├── binlog.index → Index of the binlog files├── ca.pem, server-cert.pem, … → Auto-generated TLS certificates├── ib_buffer_pool → Buffer pool dump at shutdown (warm restart)├── ibdata1 → System tablespace (internal structures)├── ibtmp1 → Temporary tablespace├── mysql/ → mysql system database├── mysql.ibd → Data dictionary (transactional metadata)├── mysql.sock → Link to the socket in /var/run/mysqld/ (official image)├── performance_schema/ → performance_schema database├── sys/ → sys database├── undo_001, undo_002 → Undo tablespaces (MVCC, rollback)└── labdb/ → Your data (one directory per database) └── clients.ibd
/var/log/mysql/├── error.log → Error and startup log└── mysql-slow.log → Slow queries (if enabled)
/var/run/mysqld/└── mysqld.sock → Unix socket for local connections| File/Directory | Role | Criticality |
|---|---|---|
/etc/mysql/mysql.conf.d/mysqld.cnf | Server configuration (buffer pool, connections, log) | High, your custom settings |
/var/lib/mysql/ | All the data, the integrity of the database depends on this directory | Critical, back it up |
ibdata1 | Shared system tablespace | Critical, never delete |
mysql.ibd | Data dictionary (all the metadata) | Critical, required to start |
binlog.* | Binary log for replication and PITR | High, needed for restoration |
#innodb_redo/ | Redo log for crash recovery | Critical, managed automatically |
undo_001/002 | Undo tablespaces (rollback, MVCC) | High, managed automatically |
auto.cnf | Unique server UUID | Medium, never copy as is to a replica |
ib_buffer_pool | Buffer pool contents at the last shutdown | Low, restart optimization |
Essential vocabulary
| Term | Meaning in MySQL |
|---|---|
| Instance | A running mysqld process, with its datadir and its configuration |
| Database / Schema | A namespace containing tables (the two terms are synonyms) |
| InnoDB | Default storage engine, transactional, crash-safe, MVCC |
| Buffer pool | InnoDB's memory cache for data and index pages |
| Redo log | Crash recovery journal, written before any modification (Write-Ahead Logging) |
| Binary log (binlog) | Replication and PITR journal, records DML/DDL events |
| GTID | Global Transaction Identifier, a unique transaction identifier for replication |
| Data dictionary | Metadata stored in InnoDB (since MySQL 8.0), replaces the .frm files |
| Tablespace | Logical container of data files (ibdata1 = system, .ibd = per table) |
| Doublewrite buffer | Protection against partial writes when flushing pages |
| Change buffer | Cache of secondary index changes whose page is not in memory; disabled by default in 8.4 (innodb_change_buffering = none) |
| LTS | Long Term Support, a series supported for 8 years (5 years of Premier Support, 3 years of Extended Support); currently 8.4 and 9.7 |
| Connection thread | Thread dedicated to a connected client (one thread per session) |
| Performance Schema | Built-in instrumentation system exposing internal metrics |
Key takeaways
- MySQL is single-process, multi-threaded, a single
mysqldhandles everything, with one thread per client connection and background threads for maintenance. - InnoDB is the only engine to use in production. It offers ACID transactions, crash recovery through the redo log, MVCC and row-level locking.
- The buffer pool is the first memory setting to examine. The default value (128 MB) is too low for production: on a server dedicated to MySQL, start from 50 to 70% of RAM.
- The redo log (InnoDB) guarantees crash recovery. The binary log (MySQL Server) serves replication and PITR. They are two distinct journals, coordinated by a two-phase commit.
- Each InnoDB table is stored in its own
.ibdfile (file-per-table tablespace), which simplifies space management and targeted backups. - InnoDB's data dictionary (since MySQL 8.0) replaces the old
.frmfiles with transactional metadata, DDL is now atomic. - MySQL 8.4 LTS (the reference version of this course) and 9.7 LTS are suitable for production, with 8 years of support (5 + 3). An Innovation release is only supported until the next Innovation release. MySQL 8.0 has been end of life since April 2026.
- Local connections go through a Unix socket (
/var/run/mysqld/mysqld.sock), network connections through TCP on port 3306.