MySQL

What it is

A relational database with pluggable storage engines, of which InnoDB is the one that matters. Dual licensed under GPL-2.0 and a commercial licence, owned by Oracle.

Architecture

A SQL layer (parsing, optimisation, privileges, the binary log) over a storage engine. InnoDB provides clustered primary key storage, a buffer pool, redo and undo logs, row-level locking and MVCC. The binary log is a separate, server-level logical log used for replication and point-in-time recovery.

Best use cases

• Transactional workloads with mostly primary-key and well-indexed access.
• Read-heavy systems scaled with replicas — the replication tooling is exceptionally mature.
• Environments with existing MySQL expertise, which is worth more than most feature differences.
• Deployments needing proven online schema change at scale (gh-ost, pt-online-schema-change).

When not to use it

• Analytical scans over large fact tables.
• Workloads needing rich types, expression indexes, partial indexes or PostgreSQL's extension ecosystem.
• Multi-node write scaling without application sharding — Group Replication provides availability, not throughput.

Data model

Typed tables with utf8mb4 as the only sensible character set. Because InnoDB stores rows inside the primary key B-tree and secondary indexes store the primary key as their pointer, the primary key choice affects every index.

Consistency and transactions

ACID through InnoDB. Defaults to REPEATABLE READ, whose implementation differs from PostgreSQL's: locking reads see the latest committed data rather than the transaction's snapshot, and next-key locking prevents phantoms at the cost of gap-lock contention.

Scaling model

Vertical, then read replicas. Write scaling requires application-level sharding or a proxy layer such as Vitess.

Replication

Asynchronous or semi-synchronous, through the binary log with GTIDs. Parallel apply with writeset dependency tracking substantially improves replica throughput. Group Replication adds consensus-based automatic failover.

Backup and recovery

Percona XtraBackup for physical backups, mysqldump/mysqlpump for logical dumps, binary log replay for point-in-time recovery.

Monitoring

Performance Schema with the sys schema views, SHOW REPLICA STATUS, and InnoDB metrics. See MySQL Monitoring.

Common mistakes

Production checklist

See MySQL Production Checklist.

Related guides