skip to content

MySQL

MySQL runs a large share of the web, and interviews probe it as an engine: InnoDB and its clustered index, gap locks, binlog replication and server tuning.

on this pageshow

explore

questions

page 1 of 2

In MySQL, what is the binary log, and how do the ROW, STATEMENT and MIXED binlog formats differ?

level: juniorimportance: must knowfreq 70%

answer

  1. logical record of committed changes
  2. read by replicas and recovery tools
  3. SQL text versus changed row images
  4. MIXED switches on unsafe statements
  5. 8.4 default, and a deprecated variable

basics

~20 s

MySQL's binary log records committed data and schema changes for replicas and point-in-time recovery. ROW, the 8.4 default, logs changed row images; STATEMENT logs the SQL text; MIXED logs statements but switches to rows for unsafe ones.

solid answer

~50 s

The binary log is a series of numbered files (`binlog.000001` and on) that the server writes as transactions commit. It is separate from InnoDB's redo log, which repairs pages after a crash: replicas pull the binary log to repeat the changes, and `mysqlbinlog` replays it for point-in-time recovery. In MySQL 8.4 it is on by default with `binlog_format=ROW`, so each event carries the images of every changed row and the replica reaches the same result even for nondeterministic statements, at the price of a much bigger log for mass updates. `STATEMENT` logs the SQL text and is compact, but an `UPDATE ... LIMIT` without `ORDER BY` or a call to `UUID()` can change different rows on the replica. `MIXED` logs statements and switches to row events for unsafe ones. DDL is logged as a statement in every format. `binlog_format` is deprecated, so new setups should stay on ROW.

code

sql · 3 lines
sql
SELECT @@log_bin, @@binlog_format, @@binlog_row_image;
SHOW BINARY LOGS;
SHOW BINARY LOG STATUS;

go deeper

for a junior

Recall that the binary log feeds replicas and point-in-time recovery, that ROW is the default, and one concrete reason STATEMENT can diverge, such as LIMIT without ORDER BY.

for a middle

Explain how MIXED decides between statement and row events, why READ COMMITTED forces row logging, and why DDL is logged as statements even under ROW.

for a senior

Show you can judge the operational cost of ROW: binlog growth from mass updates, replica apply time, and decoding row events with mysqlbinlog during an incident.

for a principal

Take a position on retiring STATEMENT and MIXED across a fleet now that binlog_format is deprecated, and what batch jobs must change before that happens.

## What the binary log is MySQL's **binary log** (binlog) is a server-level, logical record of the changes the server committed: inserts, updates and deletes, plus schema changes such as `CREATE TABLE` and `ALTER TABLE`. It is written as a sequence of numbered files, `binlog.000001`, `binlog.000002` and so on, with an index file listing them. Two consumers depend on it: - **Replication.** A replica's receiver thread connects to the source, copies binlog events into its own **relay log**, and an applier repeats them. - **Point-in-time recovery.** After restoring a backup, `mysqlbinlog` turns the binlog files written since that backup into statements you can replay up to a chosen moment. The binlog is not InnoDB's **redo log**. The redo log is a physical, engine-level record that repairs data pages after a crash; the binlog lives above the storage engines and describes changes logically, so a replica can apply them to its own data files. In MySQL 8.4 binary logging is **on by default** (`log_bin` is `ON`) and is turned off only by starting the server with `--skip-log-bin` or `--disable-log-bin`. Old files are removed automatically after `binlog_expire_logs_seconds`, which defaults to 2592000 seconds, 30 days. ## The three formats The `binlog_format` system variable decides how a data change is written: | Format | What an event holds | Strength | Weakness | |---|---|---|---| | `ROW` (default) | before and after images of each changed row | the replica reaches the same rows whatever the statement did | a mass update logs every row it touched | | `STATEMENT` | the SQL text that ran | compact, readable | nondeterministic statements can diverge | | `MIXED` | statements, switching to rows when a statement is unsafe | compact for safe statements | two behaviours to reason about | Whatever the format, **DDL is logged as a statement**: under `ROW`, `CREATE TABLE`, `ALTER TABLE` and `DROP TABLE` still appear as SQL text. ## Why statement-based logging can diverge With `STATEMENT`, the replica re-executes the same SQL against its own copy of the data, and some SQL does not give the same answer twice. MySQL marks such statements **unsafe** and warns *Statement may not be safe to log in statement format*. Examples the manual lists: 1. `UPDATE`, `DELETE` or `INSERT ... SELECT` with `LIMIT` and no `ORDER BY`: which rows qualify first is not defined. 2. Calls to `UUID()` and to loadable functions. 3. `INSERT ... ON DUPLICATE KEY UPDATE` on a table with more than one primary or unique key, because the order in which keys are checked decides the row that gets updated. With `MIXED`, the server logs statements but switches to row events for these unsafe cases, so the compact form is kept where it is safe. Some combinations force the choice: with InnoDB at `READ COMMITTED` only row-based logging works, and under `MIXED` the server switches to rows on its own. ## What ROW costs and how to read it Row-based logging trades size for determinism. One `UPDATE orders SET status = 'archived' WHERE created_at < '2025-01-01'` that touches two million rows writes two million row images, which grows the binlog, the network traffic to replicas and their apply work. The `binlog_row_image` variable (default `full`) controls whether every column is logged or only the ones needed. Row events are stored in binary form. To read them, decode them with `mysqlbinlog --base64-output=DECODE-ROWS --verbose`, which prints each row change as a commented pseudo-SQL statement instead of an opaque `BINLOG` block. Checking the current settings takes one query: ```sql SELECT @@log_bin, @@binlog_format, @@binlog_row_image; ``` ## Where MySQL 8.4 stands - **ROW is the default and the direction of travel.** `binlog_format` is deprecated: the manual says changing the logging format was deprecated in MySQL 8.0 and that only row-based logging should be used for new replication setups. - **Group Replication requires ROW.** It needs row images to detect conflicting writes, and adds `REQUIRE_ROW_FORMAT` to its own channels. - **The format is set per server.** Changing it on the source does not change a replica's own format, and a replica cannot turn received row events back into statements, so it must use ROW or MIXED whenever its source does. A junior engineer who remembers three things is in good shape: the binlog is the logical change stream replicas and recovery read, ROW is the safe default, and STATEMENT breaks on nondeterministic SQL.

  • Why can a single UPDATE make a MySQL replica fall behind under ROW format but not under STATEMENT?
    Under `ROW` the source logs one row image per changed row, so an `UPDATE` touching millions of rows becomes millions of row events the replica must receive and apply, each locating its row by key. Under `STATEMENT` the replica runs the same single statement, which costs about what it cost on the source. Batching big updates into smaller transactions keeps row-based replicas from stalling.
  • With binlog_format=MIXED, when does MySQL write a row event instead of the statement?
    When it cannot guarantee that the statement gives the same result on the replica: calls to `UUID()` or loadable functions, `UPDATE` or `DELETE` with `LIMIT` but no `ORDER BY`, and similar unsafe statements. It also logs rows whenever the InnoDB isolation level is `READ COMMITTED`, because only row-based logging is supported there. Everything else stays in statement form.

  • The binary log is just InnoDB's redo log under another name.
  • STATEMENT format is always safe because the replica runs identical SQL.
  • MIXED format writes every change twice, as a statement and as rows.
  • Under ROW format, ALTER TABLE cannot replicate because DDL changes no rows.
  • Binary logging is off by default in MySQL 8.4 and must be enabled first.
  • Changing binlog_format on the source makes every replica switch to match.

Assumes MySQL 8.4 LTS: binary logging is on by default in ROW format, and binlog_format is deprecated, so ROW is the only format to plan around.

open as a page

In MySQL, what is the practical difference between a VARCHAR(n) column and a TEXT column, and when do you choose each?

level: juniorimportance: must knowfreq 72%

basics

~20 s

In MySQL, VARCHAR(n) holds up to n characters, can be indexed whole and takes a literal DEFAULT; TEXT is a large-object type that needs a prefix length to be indexed and only an expression default. Choose VARCHAR for bounded, searchable strings.

open as a page

In MySQL, what are the practical differences between the InnoDB and MyISAM storage engines, and why is InnoDB the default?

level: juniorimportance: must knowfreq 75%

basics

~20 s

MySQL hands storage to an engine chosen per table. InnoDB, the default, gives transactions, row-level locking, foreign keys and automatic crash recovery; MyISAM has none of these, locks whole tables and can be left marked as crashed.

open as a page

In MySQL's EXPLAIN output, what do the type, key, key_len, rows and filtered columns tell you about how a table is read?

level: middleimportance: must knowfreq 78%

basics

~20 s

type is the access method, from const and eq_ref down through ref, range and index to ALL, a full scan. key is the chosen index, key_len how much of it is used, and rows times filtered estimates rows passed on.

open as a page

In MySQL with InnoDB, what does a secondary index entry contain, and how does the optimizer use the primary key columns stored there?

level: middleimportance: must knowfreq 65%

basics

~20 s

Each InnoDB secondary index entry holds the indexed columns followed by the row's primary key, which is used to find the row. The optimizer treats those appended columns as extra key parts for lookups, sorting and covering.

open as a page

In MySQL, how do you set up the slow query log to catch the statements behind a latency spike, and what does each entry record?

level: middleimportance: must knowfreq 70%

basics

~10 s

Enable slow_query_log (OFF by default), lower long_query_time from its 10-second default, and read each entry's Query_time, Lock_time, Rows_sent and Rows_examined. mysqldumpslow then groups similar statements so the worst offenders surface.

open as a page

In MySQL replication, what does a GTID identify, and what does SOURCE_AUTO_POSITION=1 change compared with binlog file and position?

level: middleimportance: must knowfreq 65%

basics

~20 s

A GTID names each committed transaction as source_uuid:sequence on every server it reaches. With SOURCE_AUTO_POSITION=1 a replica sends the GTID set it already holds and the source sends the rest, so no binlog file and offset is needed.

open as a page

In MySQL, how do DATETIME and TIMESTAMP differ in range, storage and time-zone handling, and which suits an order's placed-at column?

level: middleimportance: must knowfreq 66%

basics

~20 s

MySQL's TIMESTAMP converts values from the session time zone to UTC and back and only covers 1970 to 2038; DATETIME stores exactly what you give it from year 1000 to 9999. For order times, DATETIME written in UTC is usually the safer choice.

open as a page

In MySQL 8.4, what do ALTER TABLE's ALGORITHM=INSTANT, INPLACE and COPY mean, and why pin ALGORITHM and LOCK explicitly?

level: middleimportance: must knowfreq 60%

basics

~20 s

INSTANT changes only data-dictionary metadata, INPLACE works inside InnoDB and usually allows concurrent writes, and COPY rebuilds the table row by row while blocking writes. Pinning ALGORITHM and LOCK makes an ALTER fail immediately rather than silently fall back to a blocking copy.

open as a page

In MySQL's InnoDB, which index becomes a table's clustered index, and what happens when the table has no primary key?

level: middleimportance: must knowfreq 65%

basics

~20 s

InnoDB clusters rows on the PRIMARY KEY; without one, on the first UNIQUE index whose key columns are all NOT NULL; failing that, on a hidden GEN_CLUST_INDEX over a 6-byte row ID that no query can use.

open as a page

In MySQL InnoDB at REPEATABLE READ, why can a plain SELECT and SELECT ... FOR UPDATE in one transaction return different row values?

level: middleimportance: must knowfreq 65%

basics

~20 s

InnoDB serves a plain SELECT as a consistent read from a snapshot fixed at the transaction's first such read, while FOR UPDATE, FOR SHARE, UPDATE and DELETE are locking reads that see and lock the newest committed row version.

open as a page

In MySQL InnoDB at REPEATABLE READ, what do record, gap and next-key locks cover, and what does a range SELECT ... FOR UPDATE lock?

level: middleimportance: must knowfreq 75%

basics

~20 s

InnoDB locks index records: a record lock covers one entry, a gap lock the space between entries, and a next-key lock both an entry and the gap before it, so a range FOR UPDATE blocks inserts anywhere in the scanned range.

open as a page

A MySQL product-search query's EXPLAIN shows type ALL, key NULL and Extra 'Using where; Using filesort'. What does each part tell you, and how would you fix the query's access path?

level: seniorimportance: must knowfreq 62%

basics

~20 s

type ALL with key NULL means a full scan with no usable index; Using where means the server discards most rows read; Using filesort means an extra sort pass. Index the equality columns, then the ORDER BY column.

open as a page

How do you size innodb_buffer_pool_size and innodb_redo_log_capacity on a dedicated MySQL 8.4 server, and which status counters show either is too small?

level: seniorimportance: must knowfreq 60%

basics

~20 s

Give innodb_buffer_pool_size most of RAM (default 128 MB; often 75 to 80 percent on a dedicated host) and raise innodb_redo_log_capacity from 100 MB to absorb peak write bursts. Buffer pool disk reads, Innodb_buffer_pool_wait_free and in-use redo versus capacity show shortfalls.

open as a page

Why do two MySQL InnoDB transactions that each run SELECT ... FOR UPDATE for a not-yet-present sku_id 42 on stock_reservation's non-unique index, then INSERT that row, deadlock with error 1213, and what fixes it?

level: seniorimportance: must knowfreq 60%

basics

~20 s

Both FOR UPDATE searches find no sku 42 entry and take compatible gap locks on the same index gap; each INSERT then needs an insert intention lock the other's gap lock blocks, so InnoDB detects the cycle and rolls one back.

open as a page

In MySQL with InnoDB, which kinds of index can you create, and which data structure backs each kind?

level: juniorimportance: should knowfreq 50%

basics

~20 s

InnoDB builds PRIMARY KEY, UNIQUE and plain indexes as B-trees, the primary key being the clustered index that holds the rows. FULLTEXT indexes are inverted word lists, SPATIAL indexes are R-trees, and a requested HASH index becomes a B-tree.

open as a page

In MySQL, what does a 'Too many connections' error mean, and which setting and status counters do you check first?

level: juniorimportance: should knowfreq 50%

basics

~10 s

MySQL refused a new session because max_connections (default 151) client connections were already open. Check Threads_connected, Max_used_connections and Connection_errors_max_connections, logging in through the extra slot kept for CONNECTION_ADMIN.

open as a page

In MySQL 8.4, how do prefix, descending and functional index key parts differ, and what does each restrict about the queries using it?

level: middleimportance: should knowfreq 38%

basics

~20 s

A prefix part col(N) indexes the first N characters, so it cannot cover the column or fully order it. A DESC part stores keys in descending order. A functional part ((expr)) indexes an expression, used only when the query repeats it exactly.

open as a page

In MySQL 8.4, how does EXPLAIN ANALYZE differ from EXPLAIN FORMAT=TREE, and how do you read its actual time, rows and loops figures?

level: middleimportance: should knowfreq 55%

basics

~20 s

EXPLAIN FORMAT=TREE prints the optimizer's iterator tree with estimated cost and rows without running anything. EXPLAIN ANALYZE executes the statement and adds actual time, rows and loops for every iterator, with times in milliseconds averaged per loop.

open as a page

In MySQL, why should new tables use utf8mb4 rather than utf8mb3, and what does the default collation utf8mb4_0900_ai_ci do to comparisons?

level: middleimportance: should knowfreq 55%

basics

~20 s

MySQL's utf8mb3 stores at most three bytes per character, so emoji and other supplementary characters fail; utf8mb4 stores all of Unicode and is the default. Its default collation, utf8mb4_0900_ai_ci, compares case- and accent-insensitively, so 'Jose' equals 'josé'.

open as a page

In MySQL's InnoDB, how do innodb_old_blocks_pct and innodb_old_blocks_time stop a large table scan from evicting hot pages from the buffer pool?

level: middleimportance: should knowfreq 35%

basics

~20 s

InnoDB inserts newly read pages at the head of an old sublist, 37% of the LRU list by default, and promotes one only if it is touched again after innodb_old_blocks_time (1000 ms). One-pass scan pages therefore age out first.

open as a page

In MySQL's InnoDB, what does a table's ROW_FORMAT control, and why does the DYNAMIC default matter for tables with long columns?

level: middleimportance: should knowfreq 30%

basics

~20 s

ROW_FORMAT sets how InnoDB lays rows out in pages. DYNAMIC, the default, moves long variable-length values wholly off-page behind a 20-byte pointer and allows 3072-byte index prefixes; COMPACT and REDUNDANT keep 768-byte prefixes in-page and cap prefixes at 767 bytes.

open as a page

In MySQL 8.4 InnoDB, what does innodb_autoinc_lock_mode control, and why is its default 2 (interleaved) rather than 1 (consecutive)?

level: middleimportance: should knowfreq 35%

basics

~20 s

innodb_autoinc_lock_mode chooses how InnoDB hands out AUTO_INCREMENT values: 0 and 1 use a statement-long table-level AUTO-INC lock for some or all inserts, while 2, the 8.4 default, uses none, trading consecutive per-statement values for concurrency.

open as a page

After traffic grew, a MySQL 8.4 server's p99 latency doubled; how do you use Performance Schema statement digests and the sys schema to find the statements responsible?

level: seniorimportance: should knowfreq 45%

basics

~10 s

Query performance_schema.events_statements_summary_by_digest, which Performance Schema fills by default, ranking digests by SUM_TIMER_WAIT for total load and QUANTILE_99 for tail latency, or read sys.statement_analysis. Compare two snapshots to see which statements changed.

open as a page

A MySQL 8.4 replica running the default four applier workers still falls behind at peak; how does it choose which transactions to apply in parallel, and what limits it?

level: seniorimportance: should knowfreq 50%

basics

~20 s

A MySQL 8.4 replica runs transactions in parallel when the source's binlog marks them independent, using writesets of the rows each changed. Shared rows, DDL, huge transactions, tables without primary keys and preserved commit order all serialize apply.

open as a page

After a MySQL primary using semisynchronous replication to two replicas crashed, why might the promoted replica lack orders that customers were told had committed?

level: seniorimportance: should knowfreq 45%

basics

~20 s

MySQL semisync only promises one replica received each commit before the client hears back, and only while it stays on. Loss usually means the source had timed out back to async, used AFTER_COMMIT, or the wrong replica was promoted.

open as a page

An ALGORITHM=INSTANT ALTER TABLE on a busy MySQL orders table hangs, and within seconds every query on that table stalls; what is happening, and how do you recover and prevent it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

The ALTER is waiting for an exclusive metadata lock that an open transaction on orders still holds, and MySQL makes every new query on the table wait behind that pending request. Kill the ALTER or the blocker, then retry with a short lock_wait_timeout.

open as a page

Why does MySQL's InnoDB write flushed pages to a doublewrite buffer when it already has a redo log, and when is turning it off acceptable?

level: seniorimportance: should knowfreq 40%

basics

~20 s

A crash can leave a 16 KB InnoDB page half-written, and redo records cannot be replayed onto a torn page. The doublewrite buffer keeps an intact copy to restore first; disable it only where storage guarantees whole-page writes.

open as a page

In MySQL's InnoDB, the History list length in SHOW ENGINE INNODB STATUS keeps climbing for hours; what does it measure, and how do you diagnose and fix it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

History list length counts committed undo logs that purge has not processed. It climbs when a long-open transaction pins old versions or purge falls behind; find the oldest transaction in INNODB_TRX, end it, and let purge catch up.

open as a page

A MySQL service logs InnoDB's ERROR 1205 Lock wait timeout exceeded; what state is its transaction left in, and how do you find the blocking session?

level: seniorimportance: should knowfreq 50%

basics

~10 s

By default InnoDB rolls back only the timed-out statement, leaving the transaction open with its earlier locks; sys.innodb_lock_waits, built on performance_schema.data_lock_waits, names the blocking session, often an idle one holding an uncommitted transaction.

open as a page

showing 1–30 of 36