MySQL Overview

MySQL is a relational database built around pluggable storage engines, of which InnoDB is the one that matters: it provides ACID transactions, row-level locking, MVCC and crash recovery.

Its defining operational characteristics are a mature, well-understood replication system based on the binary log, and a very large ecosystem of tooling built on that log — online schema change tools, proxies, and change data capture connectors.

Architecture

MySQL separates the SQL layer (parsing, optimisation, privileges, the binary log) from the storage engine (data files, indexes, transactions, buffering). InnoDB implements the second half:

• Clustered primary key. Table rows are physically stored in primary key order inside the B-tree. Secondary indexes store the primary key as the row pointer, so every secondary lookup is two traversals and a wide primary key inflates every index.
• Buffer pool. InnoDB's cache for data and index pages. See Buffer Pool.
• Redo log. Guarantees durability and drives crash recovery.
• Undo log. Holds previous row versions for MVCC and rollback. A long-running transaction keeps undo alive and inflates its size.
• Binary log. A logical, server-level log of changes, used for replication and PITR. It is separate from the redo log, which is why durability settings must be coordinated across both.

Best use cases

• Transactional application workloads with mostly primary-key and well-indexed access.
• Read-heavy systems that scale out with replicas — replication tooling here is exceptionally mature.
• Environments with existing MySQL operational expertise; that expertise is worth more than most feature differences.
• Deployments that need proven online schema change at scale, via gh-ost or pt-online-schema-change.

When not to use it

• Analytical scans over large fact tables. Row storage and a simpler optimiser make this expensive; use a column store.
• Workloads that need rich types, expression indexes, partial indexes or advanced extensions — PostgreSQL is stronger here. See PostgreSQL vs MySQL.
• Multi-node write scaling without application sharding. Group Replication provides availability, not write throughput.

Data model

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id  BIGINT UNSIGNED NOT NULL,
  status       ENUM('pending','paid','shipped','cancelled') NOT NULL,
  total_cents  BIGINT NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY orders_customer_created_idx (customer_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Consistency and transactions

InnoDB defaults to REPEATABLE READ, which differs from PostgreSQL's implementation in ways that matter for concurrent code — see Isolation Levels. Locking reads take next-key locks, which prevent phantoms but also cause gap-lock contention that surprises people migrating from other engines.

Scaling and replication

One writable primary, N replicas, replicating through the binary log with GTIDs. Replicas can apply in parallel, and semi-synchronous replication narrows the data loss window at the cost of commit latency. See Replication.

Common mistakes