MySQL · InnoDB

This page is about one file per table. I show you how InnoDB stores a table as its own primary-key index, and how two logs split the jobs: redo for crashes, binlog for replicas. After it you will be able to find any table's .ibd file, read checkpoint age, check binlog retention, and tell whether undo is piling up.

MySQL · InnoDB: in one line

A relational database. Each table file is its primary-key index.

Good at: High-volume websites. Lookups by ID. Simple queries at huge scale. Operationally simple read replicas. Two logs: redo for crashes, binlog for replicas.

Runs as: one mysqld process, a thread per connection

The architecture: processes, memory, disk

Same three layers as every engine: process, memory, disk. The layer rule is written once on the PostgreSQL page: name the layer first, then diagnose inside it.

MySQL · InnoDB: architecture

Request path

Processesone mysqld · a thread per connection · page cleaner threads · purge thread · log writer threads
→
MemoryInnoDB buffer pool (most of the RAM budget) + redo log buffer + change buffer inside it
→
Disk: the datadir*.ibd per table · #innodb_redo/#ib_redo0-31 · mysql-bin.* · undo_001/002 · ibdata1
Who does what: Connection threads run queries. Page cleaners flush dirty pages to the .ibd files. The purge thread deletes undo records no reader still needs. Threads share one process. A crash takes all of them. Redo rebuilds the state.

See the real thing: the official InnoDB Architecture diagram, Figure 17.1. One picture, in-memory structures on one side, on-disk structures on the other.

Every log file a DBA needs to know

Same idea as PostgreSQL: exact names, one job per log, one check per incident. The rule is written once on the PostgreSQL page. This table adds the operational logs a DBA reads first: server, error, slow.

LogExact file nameWhere it sits (on-prem)What it doesWhat to check in it
MySQL
Redo log
#ib_redo0 · #ib_redo31#innodb_redo/ in the datadir (before 8.0.30: ib_logfile0/1 in the datadir root)Physical record of page changes. Replayed at startup to rebuild dirty pages after a crash.Checkpoint age in SHOW ENGINE INNODB STATUS (LOG section). Age near capacity means write stalls are coming.
MySQL
Binary log
mysql-bin.000001, .000002, …Datadir (path set by log_bin)Logical record of every committed change. Replicas replay it. It is the second half of point-in-time recovery.SHOW BINARY LOGS. Check total size and the oldest file. Retention must exceed the longest replica outage.
MySQL
Error + slow logs
<hostname>.err · *-slow.logDatadir / /var/log/mysql/Error log: startup, crashes, warnings. Slow log: statements over long_query_time, with rows examined.Error log at the incident start. Slow log digested (pt-query-digest), ranked by total time, not by count.

The parameters DBAs actually touch during outages

Same three outage rules as PostgreSQL: Safe live lever, Planned change, Do not touch. What each one means is written once on the PostgreSQL page.

MySQL · InnoDB Outage parameters

ParameterWhat it doesDuring an outage
innodb_buffer_pool_sizeSize of the page cache. The single most important MySQL setting. Typically 70 to 80% of RAM on a dedicated server.Planned change. It is resizable online in 8.0 (SET GLOBAL), but resizing under load is slow and competes with the workload. Verify headroom first. Watch Innodb_buffer_pool_resize_status.
innodb_redo_log_capacityTotal redo log size (8.0.30 and later). It bounds checkpoint age headroom.Safe live lever (dynamic). Use it when checkpoint age is pinned near capacity and writes are stalling. Raise it, then confirm age falls.
max_connectionsCap on concurrent connections. Each thread costs memory.Short bridge only. It is dynamic, so a modest raise can stop "too many connections" errors while you fix the cause. The cause is almost always missing pooling (ProxySQL) or leaked connections. A big raise converts a connection incident into a memory incident.
innodb_flush_log_at_trx_commitThe durability dial. 1 means redo fsynced at every commit (safe). 2 means written to the OS, fsynced about once a second. 0 means about once a second entirely.Do not relax it mid-outage to buy speed, unless the business explicitly accepts losing up to about 1 s of commits per crash. You would be trading tonight's latency incident for a future data-loss incident.
-- check SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'innodb_redo_log_capacity'; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'; SHOW GLOBAL STATUS LIKE 'Threads_connected'; -- the safe lever (8.0.30+): raise redo capacity live SET GLOBAL innodb_redo_log_capacity = 8589934592; -- 8 GB
Files on disk · what ls actually shows

Same rule as PostgreSQL: there is no hidden state. Every file here is inspectable with ls and du while the server runs. The full version is on the PostgreSQL page.

MySQL: the datadir
$ ls /var/lib/mysql
ibdata1           # InnoDB system tablespace:
                  # internal bookkeeping
#innodb_redo/    # THE REDO LOG folder (8.0.30+;
                  # before that: ib_logfile0/1 here)
  #ib_redo0       # ┐ 32 files, #ib_redo0 to #ib_redo31,
  #ib_redo31      # ┘ each 1/32 of innodb_redo_log_capacity
mysql-bin.000001  # ┐ THE BINARY LOG: every change,
mysql-bin.000002  # │ numbered, for replicas + PITR
mysql-bin.index   # ┘ list of binlog files that exist
undo_001  undo_002 # undo tablespaces: old row versions
#ib_16384_0.dblwr  # doublewrite buffer: anti-torn-page
mysql/            # system schema (users, privileges)
shop/             # one folder per database you create
  users.ibd       # ← table "users": data + primary-key
                  #   tree in ONE file
  orders.ibd
<hostname>.err    # the error log
Ask the server where it lives: SHOW VARIABLES LIKE 'datadir'; One table is one .ibd file when innodb_file_per_table is on. That is the default.
The log files · one by one

Same pattern as PostgreSQL: five logs, one job each, and every operational budget is a time window. The full version is on the PostgreSQL page.

MySQL · InnoDB

Redo log

#ib_redo0 · #ib_redo31
Where it sits
In #innodb_redo/ in the datadir: 32 files, #ib_redo0 to #ib_redo31, each 1/32 of innodb_redo_log_capacity, written as a ring. Before 8.0.30 they were a pair of files, ib_logfile0/1, in the datadir root. Same log, new home.
What it does
InnoDB's write-ahead log. A physical record of every page change, written before the page itself. On startup after a crash, InnoDB replays it to rebuild every page that was dirty in the buffer pool.
What breaks without it
InnoDB refuses to start without a valid redo log. With good reason. Without it, a committed transaction could vanish in a crash and dirty pages would be unrecoverable.
Look at it
$ ls -lh /var/lib/mysql/#innodb_redo/ -- in mysql: SHOW ENGINE INNODB STATUS\G -- read the "LOG" section: Log sequence number
MySQL

Binary log (binlog)

mysql-bin.000001 · mysql-bin.index
Where it sits
In the datadir (path set by log_bin). Numbered files that roll over as they fill. mysql-bin.index lists the ones that exist. Each event can carry a GTID. That is a permanent, unique ID for the transaction.
What it does
A logical record of every committed change. "Insert this row", not "change these bytes". Replicas replay it to stay identical. It is also the second half of point-in-time recovery. Restore a backup, then replay the binlog up to the second before the mistake.
How old files go away
By time, not by replica. binlog_expire_logs_seconds sets the age, 2592000 seconds (30 days) by default, with auto-purge on by default. Expired files go at startup and when the binlog is flushed. A connected replica that has not yet read a file keeps that one file. A disconnected replica holds nothing back.
What breaks without it
No replication, and no point-in-time recovery. You can only restore whole backups, losing everything since the last one.
Look at it
-- in mysql: SHOW BINARY LOGS; SHOW BINLOG EVENTS IN 'mysql-bin.000001'; $ mysqlbinlog mysql-bin.000001 | less

The other logs: PostgreSQL: WAL · MongoDB · WiredTiger: journal and oplog · the pattern behind all five, and where they break

See it yourself · copy-paste commands

Open a real database and check every claim on this page

Same rule as PostgreSQL: every mechanism above is observable on a running server in under two minutes. The full version is on the PostgreSQL page.

Run it locally: docker-compose-mysql.yml starts MySQL 8.4 on port 3306 with a shop database. docker compose -f docker-compose-mysql.yml up -d, then run the commands below against it.

MySQL · mysql client + shell
-- where is everything?
SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
$ ls /var/lib/mysql/shop
-- → users.ibd, orders.ibd, …

-- the binary log
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'mysql-bin.000001';
$ mysqlbinlog mysql-bin.000001 | less

-- redo log position + undo backlog
SHOW ENGINE INNODB STATUS\G
-- replica state and lag
SHOW REPLICA STATUS\G

The optimizer's selectivity decision

Problem. I add an index. The query ignores it. I run EXPLAIN first. It says full scan. That feels wrong. It is often right.

Mechanism. An index lookup reads pages one at a time, in key order. Those pages sit scattered across the .ibd file. Each one is a random read. A full scan reads the same file front to back, in one pass. One sequential pass can beat thousands of random reads. The planner compares those two costs. It picks the cheaper one. It skips the index when the query must return a large share of the table.

The planner estimates row counts from statistics. A bulk load can leave those statistics stale. The planner then guesses wrong. ANALYZE TABLE refreshes the statistics. Run it after a bulk load. Then read EXPLAIN again.

A secondary index does not hold the row. It holds the primary key value. Each match needs a second read into the clustered index. That read fetches the row from users.ibd. This is the bookmark lookup. One match costs two reads. A thousand matches cost two thousand reads.

Every secondary entry carries a copy of the primary key. A long string key makes every secondary entry longer. Longer entries mean more pages per index. Keep the primary key short. Use an integer id when you can.

Exact check. Run EXPLAIN. Then refresh statistics. Then check what the index holds.

EXPLAIN SELECT id, email FROM users WHERE status = 'active'; ANALYZE TABLE users; SELECT INDEX_NAME, COLUMN_NAME, CARDINALITY FROM information_schema.statistics WHERE TABLE_SCHEMA = 'shop' AND TABLE_NAME = 'users';

Read EXPLAIN for the access type. A scan on a wide query is the planner doing its job. A scan on a narrow query means stale statistics or a missing index.

One incident I keep coming back to

Writes started failing. Then the database refused writes entirely.

I broke the disk down by owner before touching anything. The binlog was the filler. The purge had not kept up, and the reason was not a dead replica. MySQL purges binlogs by time: binlog_expire_logs_seconds sets the age, 2592000 seconds (30 days) by default, auto-purge is on by default, and expired files go at startup and when the binlog is flushed. A replica that is disconnected holds nothing back. Only a connected replica that has not yet read a file keeps that one file. A disk full of binlogs means retention times write rate outgrew the disk, or retention was set to 0, or auto-purge was off, or a write burst filled it faster than purge could keep up. This incident does not record which of those it was, so check the retention value, the write rate, and the oldest file before naming the cause.

The fix is the purge, not the replica. Check every replica position with SHOW REPLICA STATUS, then free space with PURGE BINARY LOGS. Never rm old binlog files by hand. Set binlog_expire_logs_seconds to a value your write rate and disk can actually hold, and alert at 70 percent, not 98 percent. A replica that still needs a file you purged fails with error 1236 and must be rebuilt from a backup, which is why positions come first.

Real versions of this story: GitHub automated the failover with Orchestrator, then upgraded 1,200 hosts the same way, and Slack and Shopify both moved to Vitess. All four are on the Stories page. How to host MySQL on-prem, on EC2, in Kubernetes, or managed is on the Cloud page.

Before you leave, check this

One command. It tells you how close the redo log is to stalling writes.

SHOW ENGINE INNODB STATUS\G -- read the LOG section: checkpoint age vs capacity