Explanation¶

MySQL is a relational database server built around a pluggable storage engine architecture. The server layer handles connections, parsing, optimization and execution. The storage engine layer, reached through the Handler API, decides how rows are stored and retrieved. InnoDB has been the default engine since MySQL 5.5. Among the engines shipped with Community Server it is the one that provides ACID transactions, foreign keys and crash recovery (NDB, used by MySQL NDB Cluster, is transactional too but is a separate product line).

This page explains how the pieces work and why. Exact defaults, limits and version tables are in Reference. Procedures are in How-to Guides. See also the MySQL hub.

Server Component Architecture¶

The server layer is shared by all storage engines. The diagram shows the path of one SQL statement from the client to the storage engine.

graph TB
    CLIENT["Client<br/>(mysql CLI, Connector/J, mysqlclient, X DevAPI)"]
    subgraph SERVER["mysqld server layer"]
        CONN["Connection handler<br/>(thread per connection or Thread Pool plugin)"]
        AUTH["Authentication<br/>(caching_sha2_password, auth_socket, ...)"]
        PARSE["Parser<br/>(Bison grammar, builds parse tree)"]
        RESOLVE["Resolver / prepare<br/>(tables, columns, views, privileges)"]
        OPT["Optimizer<br/>(cost-based, classic or hypergraph)"]
        EXEC["Executor<br/>(iterator executor, hash join)"]
        BINLOG["Binary log<br/>(replication, PITR)"]
    end
    API["Handler API<br/>(storage engine interface)"]
    INNODB["InnoDB"]
    OTHER["Other engines<br/>(MyISAM, MEMORY, ARCHIVE, CSV, NDB)"]

    CLIENT --> CONN
    CONN --> AUTH
    AUTH --> PARSE
    PARSE --> RESOLVE
    RESOLVE --> OPT
    OPT --> EXEC
    EXEC --> API
    EXEC --> BINLOG
    API --> INNODB
    API --> OTHER

    style INNODB fill:#e8f5e9,stroke:#4caf50,color:#000
    style OPT fill:#fff3e0,stroke:#ff9800,color:#000

The query cache that sat in front of the parser in 5.7 was removed in 8.0. It serialized on a global mutex and was invalidated by every write to a table, so it hurt more than it helped on multi-core servers.

Connection Management¶

By default each client connection gets its own OS thread, which authenticates the client, reads statements and returns results. That is simple and fast at low concurrency, but thousands of active threads cause context switching and lock contention. The Thread Pool plugin groups connections and runs statements on a bounded set of worker threads. It was Enterprise-only until MySQL 26.7.0, which ships it in Community Server. MySQL accepts TCP/IP, Unix socket and (on Windows) named pipe connections. The X Protocol listens on port 33060 next to the classic protocol on 3306.

Query Optimizer¶

MySQL uses a cost-based optimizer that compares alternative plans using table statistics, index cardinality, histograms (8.0+) and a cost model. Main techniques:

Range optimization: scans only the relevant index ranges.
Index merge: combines several indexes for one table access.
Hash join: introduced in 8.0.18 for equi-joins. Since 8.0.20 it replaces block nested-loop for all joins without a usable index.
Subquery materialization and semijoin strategies: turn IN (subquery) into joins or temporary tables.
Window functions and CTEs (including recursive CTEs): native since 8.0.
Hypergraph optimizer: a newer join planner that enumerates bushy join orders and costs plans more completely than the classic left-deep planner. It was internal/HeatWave-only for years. MySQL 9.7 LTS ships it in Community Server, but it is off by default (SET optimizer_switch='hypergraph_optimizer=on'). Oracle's February 2026 plan had promised it default-enabled.

InnoDB Storage Engine¶

InnoDB provides ACID transactions, row-level locking, MVCC and crash recovery. The diagram shows its in-memory and on-disk structures and the file names used in 8.0.30 and later.

graph TB
    subgraph MEM["InnoDB memory structures"]
        BP["Buffer pool<br/>(16 KiB data, index and undo pages)"]
        CB["Change buffer<br/>(part of buffer pool, default off in 8.4)"]
        AHI["Adaptive hash index<br/>(default off in 8.4)"]
        LB["Log buffer<br/>(redo records, 64 MiB default in 8.4)"]
    end

    subgraph DISK["InnoDB on-disk structures"]
        SYS["System tablespace ibdata1<br/>(change buffer storage)"]
        DD["mysql.ibd<br/>(data dictionary)"]
        FPT["File-per-table tablespaces<br/>(db/table.ibd)"]
        REDO["Redo log<br/>(#innodb_redo/#ib_redoN)"]
        UNDO["Undo tablespaces<br/>(undo_001, undo_002)"]
        DW["Doublewrite files<br/>(#ib_16384_N.dblwr)"]
        TMP["Temporary tablespaces<br/>(ibtmp1, #innodb_temp)"]
    end

    PC["Page cleaner threads"]
    PURGE["Purge threads"]

    LB -->|"write + fsync at commit"| REDO
    BP --> PC
    PC -->|"1. write dirty page copy"| DW
    PC -->|"2. write page in place"| FPT
    CB -.-> SYS
    BP --- DD
    PURGE --> UNDO

    style BP fill:#e3f2fd,stroke:#2196f3,color:#000
    style LB fill:#fce4ec,stroke:#e91e63,color:#000
    style REDO fill:#fce4ec,stroke:#e91e63,color:#000

Buffer Pool¶

The buffer pool is InnoDB's main memory structure. It caches data pages (16 KiB by default), index pages and undo pages so that reads avoid disk I/O. It is sized with innodb_buffer_pool_size (default 128 MiB, typically 50 to 80% of RAM on a dedicated server) and split into innodb_buffer_pool_instances to reduce mutex contention.

Midpoint insertion LRU: the LRU list has a "new" (young) sublist and an "old" sublist. innodb_old_blocks_pct (default 37, about 3/8) sets the size of the old sublist. A newly read page is inserted at the midpoint, the head of the old sublist, not at the head of the whole list. It is promoted to the young sublist only if it is accessed again after innodb_old_blocks_time (default 1000 ms). One full table scan or read-ahead therefore cannot flush the hot working set.
Flushing: page cleaner threads write dirty pages in the background. Adaptive flushing uses innodb_io_capacity and innodb_io_capacity_max to balance flushing against redo generation. The defaults rose from 200/2000 in 8.0 to 10000/20000 in 8.4 because SSD and NVMe are now normal.
Warm restarts: the buffer pool page list is dumped at shutdown and reloaded at startup (innodb_buffer_pool_dump_at_shutdown, innodb_buffer_pool_load_at_startup, both ON).

Change Buffer¶

The change buffer caches changes to non-unique secondary index pages that are not in the buffer pool. The changes are merged later, when the page is read for another reason. This turned random secondary-index writes into fewer, larger I/Os, which mattered on spinning disks.

On SSD/NVMe the benefit is small and the merge work adds overhead and complexity. MySQL 8.4 therefore changed the default of innodb_change_buffering from all to none. The feature still exists and can be turned back on for write-heavy workloads on slow storage.

Adaptive Hash Index (AHI)¶

InnoDB watches index lookups. When it sees repeated equality lookups on the same B-tree pages, it builds an in-memory hash index on those pages, so point lookups skip the B-tree descent. The cost is extra work on every DML and latch contention under high concurrency, especially with many writes or DROP TABLE / TRUNCATE on large tables. MySQL 8.4 turned innodb_adaptive_hash_index off by default for predictability. It is still available and can help read-mostly workloads whose working set fits in memory.

Doublewrite Buffer¶

A crash in the middle of a 16 KiB page write can leave a torn page: half old, half new. Redo records cannot repair a torn page, because they describe changes to a consistent page image. Before writing dirty pages in place, InnoDB first writes them to the doublewrite area and fsyncs it. During recovery a torn page is restored from its doublewrite copy, and then redo is applied.

Since 8.0.20 the doublewrite area lives in separate #ib_*.dblwr files (location innodb_doublewrite_dir) rather than inside ibdata1.
innodb_doublewrite defaults to ON. Turn it off only on storage that guarantees atomic 16 KiB writes. 8.4 raised innodb_doublewrite_pages to 128 for better throughput on fast storage.

Redo Log and Undo Log¶

Redo Log¶

The redo log is InnoDB's write-ahead log (WAL). It records physical changes to pages so that committed transactions whose pages were not yet flushed can be replayed after a crash.

Log before page: a change is described in the log buffer before the modified page can be flushed.
Sequential writes: redo is appended sequentially, which avoids random I/O at commit time.
Group commit: many concurrent commits share one redo write and fsync.
Checkpoint: as page cleaners flush dirty pages, the checkpoint advances and old redo space is reused.

Since 8.0.30 the redo log is a set of 32 files in the #innodb_redo directory sized by innodb_redo_log_capacity, which can be changed online. This replaces innodb_log_file_size and innodb_log_files_in_group and the old ib_logfile0/1 pair.

Durability at commit is controlled by innodb_flush_log_at_trx_commit:

Value	Behavior	Data at risk on crash
`1` (default)	Write and fsync redo at every commit	None (ACID)
`2`	Write to the OS page cache at commit, fsync about once a second	Up to about 1 s on OS crash or power loss. Survives a mysqld crash.
`0`	Write and fsync about once a second	Up to about 1 s even on a mysqld crash

Undo Log and MVCC¶

Undo logs store the previous version of each modified row. They serve two purposes:

Rollback: restore the prior state on ROLLBACK or on crash recovery of uncommitted transactions.
MVCC: give each consistent read a snapshot. Under the default REPEATABLE READ isolation, a transaction's read view is fixed at its first consistent read. Readers follow the undo chain (through the row's roll pointer) to the version visible to their read view, so readers do not block writers.

Undo logs live in dedicated undo tablespaces (undo_001, undo_002 by default). Purge threads remove undo records when no read view needs them. A long-running transaction holds back purge, so the history list length and the undo tablespaces grow. Monitor History list length in SHOW ENGINE INNODB STATUS. Undo tablespaces are truncated automatically (innodb_undo_log_truncate=ON), and 26.7.0 improved undo truncation.

Binary Log (Binlog)¶

The binary log is a server-level logical log, separate from the InnoDB redo log. It records committed changes in an engine-independent format for replication, change data capture (for example Debezium) and point-in-time recovery.

Format	Description
ROW (default)	Logs before/after row images. Deterministic. Larger for bulk updates (`binlog_row_image=MINIMAL` reduces size).
STATEMENT	Logs SQL text. Compact, but non-deterministic statements can diverge on replicas.
MIXED	Statement format by default, row format for unsafe statements.

binlog_format has been deprecated since 8.0.34. Row-based logging is the long-term direction. Typical events in one transaction: Gtid, Query (BEGIN), Table_map, Write_rows / Update_rows / Delete_rows, Xid (commit marker).

Keeping Redo and Binlog Consistent¶

Because there are two logs, InnoDB and the binlog use an internal two-phase commit (XA). The transaction is first prepared in InnoDB (redo written). The binlog group commit then runs three stages: flush (write events), sync (fsync by sync_binlog) and commit (InnoDB commit in binlog order). On crash recovery, prepared InnoDB transactions are committed if their Xid is in the binlog and rolled back otherwise. With sync_binlog=1 and innodb_flush_log_at_trx_commit=1, the engine and the replication stream always agree.

Replication Architecture¶

Classic replication is asynchronous and pull-based. The diagram shows the threads involved on the source and on a replica.

graph LR
    subgraph SRC["Source (primary)"]
        SESS["Client sessions<br/>(commit via binlog group commit)"]
        BL["Binary log files<br/>(binlog.000042 ...)"]
        DUMP["Binlog dump thread<br/>(one per replica)"]
    end

    subgraph REP["Replica"]
        RCV["Receiver thread<br/>(replication I/O thread)"]
        RELAY["Relay log"]
        COORD["Applier coordinator<br/>(replication SQL thread)"]
        WORKERS["Applier workers<br/>(replica_parallel_workers = 4)"]
        DATA["InnoDB data +<br/>replica's own binlog"]
    end

    SESS --> BL
    BL --> DUMP
    DUMP -->|"events over TCP 3306"| RCV
    RCV --> RELAY
    RELAY --> COORD
    COORD --> WORKERS
    WORKERS --> DATA

    style BL fill:#fff3e0,stroke:#ff9800,color:#000
    style RELAY fill:#e8f5e9,stroke:#4caf50,color:#000

The multithreaded applier schedules transactions in parallel using WRITESET dependency tracking: two transactions that touched disjoint primary keys can apply concurrently, and replica_preserve_commit_order=ON keeps commit order identical to the source. In 8.4 WRITESET is the only mode. MySQL 26.7.0 adds the Change Stream Applier, an opt-in per-channel alternative applier that scales to 1,024 workers.

GTID (Global Transaction Identifiers)¶

A GTID uniquely identifies each committed transaction as source_uuid:sequence, and sets of them are written as ranges, for example 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5. Since 8.3 a GTID can also carry a tag (uuid:tag:sequence). GTIDs make replication position-independent:

Replica promotion: no need to compute binlog file and offset for each replica.
Failover: tools compare gtid_executed sets to choose the most up-to-date replica.
Idempotent replay: a transaction whose GTID is already in gtid_executed is skipped.

GTIDs are enabled with gtid_mode=ON and enforce_gtid_consistency=ON. They are required for Group Replication and InnoDB Cluster.

Replication Topologies¶

The options trade latency against the risk of losing acknowledged commits. A full comparison table is in Reference: Replication and HA Options.

Asynchronous (default): the source never waits. A source crash can lose transactions that clients saw as committed but no replica had received.
Semi-synchronous: the source waits (after its own commit preparation) until at least rpl_semi_sync_source_wait_for_replica_count replicas have written the transaction to their relay log. With AFTER_SYNC (default) no client sees a commit that no replica has. On timeout it silently falls back to async, so monitor it.
Group Replication: a group of up to 9 members agrees on a total order of transactions through a Paxos-based protocol and certifies conflicts deterministically on every member. It has built-in membership, failure detection and automatic primary election.

Group Replication Flow¶

The sequence shows one commit in single-primary mode. Consensus is reached on the delivery order of the write set, not on the apply, and each member certifies independently. Secondaries apply asynchronously unless the session asks for stronger consistency.

sequenceDiagram
    participant C as Client
    participant P as Primary mysqld
    participant X as GCS / XCom<br/>(Paxos, majority)
    participant S1 as Secondary 1
    participant S2 as Secondary 2

    C->>P: COMMIT
    P->>P: before_commit hook extracts write set (PK hashes) and snapshot version
    P->>X: Broadcast transaction (write set + binlog events)
    X->>X: Majority agrees on global delivery order
    X-->>P: Deliver in total order
    X-->>S1: Deliver in total order
    X-->>S2: Deliver in total order
    P->>P: Certify (no conflict), assign GTID, binlog + InnoDB commit
    P-->>C: COMMIT OK
    S1->>S1: Certify (same result), queue in group_replication_applier relay log, apply
    S2->>S2: Certify (same result), queue in group_replication_applier relay log, apply
    Note over P,S2: With group_replication_consistency = AFTER, the primary answers the client only after all members have applied

Key consequences:

A commit needs a majority of members reachable. A 3-member group tolerates 1 failure, a 5-member group tolerates 2. Without quorum the group blocks writes rather than splitting its brain.
In multi-primary mode two members can commit conflicting rows concurrently. Certification lets the first in the total order win and rolls back the other at commit time, so applications must retry.
The default consistency level changed in 8.4 from EVENTUAL to BEFORE_ON_PRIMARY_FAILOVER: after a failover, the new primary holds new transactions until it has applied its backlog, so clients never read stale data from a freshly promoted primary.
Flow control throttles members whose applier or certifier queue grows too long (statistics moved to Community in 9.7).
The communication stack decides how members talk. XCOM uses its own connection handling and TLS settings on a separate port (commonly 33061). MYSQL (default since 26.7.0) reuses the server's connection security, and the recovery user needs the GROUP_REPLICATION_STREAM privilege.

InnoDB Cluster, ClusterSet and Router¶

InnoDB Cluster is Oracle's packaged HA stack: Group Replication for data, MySQL Shell's AdminAPI (dba.*) for deployment and management, and MySQL Router for client routing. InnoDB ClusterSet links a primary cluster to replica clusters in other regions over a managed asynchronous channel.

graph TB
    APP["Application<br/>(any MySQL connector)"]
    SHELL["MySQL Shell AdminAPI<br/>(dba.createCluster, addInstance, createClusterSet)"]

    subgraph ROUTERS["MySQL Router"]
        RW["R/W port 6446"]
        RO["R/O port 6447"]
    end

    subgraph PC["Primary InnoDB Cluster (region A)"]
        M1["mysqld primary"]
        M2["mysqld secondary"]
        M3["mysqld secondary"]
        M1 <-->|"Group Replication"| M2
        M2 <-->|"Group Replication"| M3
        M1 <-->|"Group Replication"| M3
    end

    subgraph RC["Replica cluster (region B)"]
        N1["mysqld primary (read-only)"]
        N2["mysqld secondary"]
        N1 <-->|"Group Replication"| N2
    end

    APP --> RW
    APP --> RO
    RW --> M1
    RO --> M2
    RO --> M3
    M1 -->|"ClusterSet async channel"| N1
    SHELL -.->|"configure + metadata"| PC
    SHELL -.-> RC
    ROUTERS -.->|"reads mysql_innodb_cluster_metadata"| PC

    style M1 fill:#e65100,color:#fff
    style N1 fill:#1565c0,color:#fff

Router keeps a cached view of the topology from the mysql_innodb_cluster_metadata schema and the GR membership tables, so after a primary election it redirects R/W traffic without client changes. Since 8.1, clusters can also have read replicas: asynchronous members outside the GR group that Router can use for read scale-out. InnoDB ReplicaSet offers the same Shell + Router experience on top of plain asynchronous replication, without automatic failover.

InnoDB Data Storage¶

Tablespaces¶

Tablespace type	Description
System tablespace (`ibdata1`)	Change buffer storage, plus table data only for tables created with `innodb_file_per_table=OFF`. The data dictionary moved out in 8.0 and the doublewrite buffer in 8.0.20.
Data dictionary (`mysql.ibd`)	Transactional data dictionary introduced in 8.0 (replaced `.frm` files).
File-per-table (`table_name.ibd`)	Default since 5.6.6 (`innodb_file_per_table=ON`). One tablespace per table, so `DROP`/`TRUNCATE` return space to the OS.
General tablespace	Shared tablespace created with `CREATE TABLESPACE`. Can hold many tables.
Undo tablespaces	`undo_001`, `undo_002` by default. More can be added with `CREATE UNDO TABLESPACE`.
Temporary tablespaces	`ibtmp1` (global) and session temporary tablespaces in `#innodb_temp`.

Page and Extent Structure¶

Page: the unit of I/O and buffering, 16 KiB by default (4, 8, 32 and 64 KiB possible, fixed when the instance is initialized).
Extent: 1 MiB of contiguous pages (64 pages at 16 KiB).
Segment: a set of extents and pages. Each index has two segments: one for non-leaf pages and one for leaf pages.
Clustered index: table rows are stored in the leaf pages of the primary key B+tree. Secondary index entries store the primary key, not a row pointer, so a long primary key makes every secondary index larger, and secondary lookups do a second B-tree descent.

Release Model and Versioning¶

Oracle replaced the "8.0 gets features in every patch release" model in 2023. The reason: 8.0 patch releases regularly introduced behavior changes, which made even minor upgrades risky. The new model separates the two needs:

LTS releases (8.4, 9.7) change features only at x.y.0 and then receive quarterly fixes for 5 years of Premier plus 3 years of Extended support.
Innovation releases (8.1 to 8.3, 9.0 to 9.6, and from July 2026 26.7, 26.10, ...) carry new features, deprecations and removals every quarter. Each is supported only until the next ships.

From July 2026 Innovation and LTS releases use calendar versions YY.M.P. 9.7 was the last sequential version, so there is no 9.8.

The state diagram shows the lifecycle of a release series under this model.

stateDiagram-v2
    [*] --> Innovation: quarterly release (for example 26.7)
    Innovation --> Superseded: next Innovation or LTS ships
    Superseded --> [*]
    [*] --> LTS: last release of a cycle (8.4, 9.7)
    LTS --> Premier: quarterly patch releases, 5 years
    Premier --> Extended: security and critical fixes, 3 years
    Extended --> Sustaining: EOL (8.0 on 2026-04-30)
    Sustaining --> [*]

Editions and HeatWave¶

MySQL is open core. Community Server is GPLv2. Enterprise Edition adds commercial components: Enterprise Audit, Enterprise Firewall, data masking, extra keyrings, LDAP/Kerberos/WebAuthn/OpenID Connect authentication, Enterprise Backup, and MySQL Enterprise Monitor. HeatWave is Oracle's managed cloud service (OCI, with AWS and Azure offerings) that adds an in-memory columnar query accelerator, Lakehouse (query object-storage files), AutoML, GenAI with a vector store, and Autopilot tuning. "MySQL AI" packages some of these capabilities for on-premises Enterprise customers.

The line between the editions moves. The DISTANCE() vector function is still restricted to HeatWave and MySQL AI, while 9.7 and 26.7 moved several operational features (hypergraph optimizer, GR telemetry and primary election, Thread Pool) into Community. Critics such as Vonng argue these are long-available features finally released rather than new innovation.

Governance and Community (2025-2026)¶

2025-09: Oracle laid off a large part of the MySQL engineering team. The Register reported about 70 senior engineers; the later open letter cites about a 50% staff reduction in autumn 2025. Monty Widenius and Percona's Peter Zaitsev publicly criticized the cuts.
2026-01/02: MySQL Community Summits in San Francisco and Brussels. Nearly 200 developers, users and companies, led by Percona (Vadim Tkachenko), published an open letter asking Oracle to move MySQL toward foundation-style governance. It criticized closed development, private code drops and opaque security handling.
2026-02-11: Oracle's "new era of community engagement" post announced three pillars: innovation in Community Edition, a larger developer community, and more transparency (public proposal discussions, feedback on rejected patches, publication of security bugs after fixes). Oracle declined to hand MySQL to an independent foundation.
2026-04-21: 9.7 LTS moved several Enterprise features to Community.
2026-05-27: The OurSQL Foundation launched as an independent, vendor-neutral community body. Founding board: Percona, PlanetScale, PingCAP, Alibaba, VillageSQL and independent experts, with Vadim Tkachenko as president. Oracle is not a member.
2026-06-25: Oracle published a MySQL Community Governance Model with a technical steering committee (initially AWS, Google Cloud and Oracle, plus users). The committee is advisory and Oracle keeps control. OurSQL says the commitments are not binding.
2026-07-28: 26.7.0, the first calendar-versioned Innovation release, shipped the Thread Pool in Community.

MySQL, MariaDB and Percona Server¶

	MySQL (Oracle)	Percona Server for MySQL	MariaDB Server
Relationship	Upstream	Drop-in, tracks upstream releases (8.4, 9.7)	Fork since 2009, diverged
Governance	Oracle	Percona	MariaDB Foundation + MariaDB plc
HA	Group Replication / InnoDB Cluster	Same as upstream, plus Percona XtraDB Cluster (Galera)	Galera Cluster (MariaDB plc acquired Codership in 2025), MaxScale
Vector search	`VECTOR` type, `DISTANCE()` only in HeatWave / MySQL AI	`DISTANCE()` / `VECTOR_DISTANCE()` since 9.7.2-2	Native `VECTOR` + HNSW index, GA in 11.8 LTS (2025)
Current LTS (2026-09)	9.7 and 8.4	8.4, 9.7	12.3 LTS (supported to 2029-06)

MariaDB is no longer a drop-in replacement for current MySQL. Its GTID format (domain-server-sequence), JSON storage (an alias for LONGTEXT), replication and authentication plugins differ, and it has no Group Replication. Percona Server stays binary-compatible with upstream and adds open-source equivalents of some Enterprise features (audit log filter, data masking, keyring vault components).

Security Model¶

MySQL security has five layers: who can connect (authentication), what they can do (privileges and roles), how traffic is protected (TLS), how data at rest is protected (keyring and tablespace encryption), and what is recorded (audit). Plugin, privilege and keyring tables are in Reference. Setup commands are in How-to Guides.

Threat Model¶

Threat	Main control
Credential theft or brute force	`caching_sha2_password`, `validate_password`, account locking (`FAILED_LOGIN_ATTEMPTS`, `PASSWORD_LOCK_TIME`)
Network sniffing or MITM	TLS with `require_secure_transport=ON`, `REQUIRE X509` for privileged accounts
Stolen disks, backups or snapshots	InnoDB tablespace, redo, undo and binlog encryption with a keyring
Over-privileged application account (SQL injection impact)	Least-privilege grants, roles, no `SUPER`, `local_infile=OFF`, `secure_file_priv`
Insider misuse, compliance	Audit logging (Enterprise Audit, Percona or MariaDB audit plugins)
Unpatched server	Stay on a supported series. 8.0 has received no fixes since 2026-04-30.

Authentication¶

MySQL authenticates a connection by the account 'user'@'host', where the host part can be a name, an IP address, a netmask or a wildcard, and by the authentication plugin assigned to that account. Accounts live in the mysql system schema.

caching_sha2_password has been the default since 8.0. It stores a salted, multi-round SHA-256 hash. After a successful full authentication the server keeps a fast SHA-256 digest in memory, so later logins do a cheap challenge-response. The full authentication must not reveal the password, so it requires a secure channel. The sequence shows both paths.

sequenceDiagram
    participant C as Client connector
    participant S as mysqld
    C->>S: Connect, handshake response with scrambled password
    alt Fast path (account digest cached)
        S->>S: Verify scramble against in-memory SHA-256 digest
        S-->>C: OK
    else Full authentication (first login or cache flushed)
        S-->>C: Request full authentication
        alt TLS or Unix socket
            C->>S: Send cleartext password over the secure channel
        else Plain TCP
            C->>S: Request server RSA public key (or use a pinned key)
            S-->>C: RSA public key
            C->>S: Password encrypted with RSA
        end
        S->>S: Verify against stored salted hash, cache digest
        S-->>C: OK
    end

If neither TLS nor RSA is available the login fails. This is why old connectors that only speak mysql_native_password break against 8.4 and 9.x: that SHA-1 plugin is disabled by default in 8.4 and removed in 9.0.

Roles and Privileges¶

Privileges are hierarchical (global, database, table, column, routine). Since 8.0 many global powers that used to require SUPER are split into dynamic privileges such as BINLOG_ADMIN or CONNECTION_ADMIN, so administrators can grant just what a job needs. Roles (8.0+) are named privilege bundles granted to accounts. Default roles are activated at login (SET DEFAULT ROLE), and mandatory_roles can give a role to every account.

TLS¶

MySQL supports TLS 1.2 and TLS 1.3. TLS 1.3 has been available since 8.0.16, and TLS 1.0/1.1 were removed in 8.0.28. The server auto-generates self-signed certificates at initialization if none are configured, so connections are encrypted by default but not verified. Clients must use --ssl-mode=VERIFY_IDENTITY with a real CA to get MITM protection. Since 8.0.16, TLS material can be reloaded without restart (ALTER INSTANCE RELOAD TLS). 26.7.0 adds post-quantum key exchange when built against OpenSSL 3.5+.

Encryption at Rest¶

InnoDB uses two-tier keys. Each encrypted tablespace has its own tablespace key, stored in the tablespace header and encrypted with a single master key held in a keyring component. Rotating the master key (ALTER INSTANCE ROTATE INNODB MASTER KEY) only re-encrypts the small tablespace keys, not the data. Tablespace data is encrypted with AES (CBC for data pages). Redo and undo log encryption (8.0.17+), binary log encryption (8.0.14+) and default_table_encryption extend coverage. In 8.4 the old keyring_file plugin is gone and keyrings are components loaded through a manifest file, so they are available before InnoDB starts.

Auditing¶

MySQL Enterprise Audit records connections, queries and administrative events with rule-based JSON filters. Community Server has no audit plugin. Common alternatives are Percona's audit log filter component and the MariaDB audit plugin.

SQL Injection Prevention¶

The server cannot tell legitimate SQL from injected SQL, so prevention lives in the application: prepared statements or parameterized queries keep user input as data, never SQL text. The database limits the blast radius: an application account with only SELECT, INSERT, UPDATE on its own schema cannot drop tables, read other schemas, or write files, even if an injection succeeds.

Benchmarks¶

No controlled, sourced benchmark is recorded for this topic. The rough sysbench, buffer pool and Group Replication figures kept from earlier research are in Reference: Benchmarks and Capacity Figures, with a warning. What matters for performance is structural: whether the working set fits in the buffer pool, the durability settings (innodb_flush_log_at_trx_commit, sync_binlog), redo capacity versus write rate, and, for Group Replication, network round-trip time between members. Each commit needs one consensus round to a majority.