MySQL Server & Router

Overview

MySQL is a widely-used open-source relational database management system developed by Oracle. This repository contains the complete source code for MySQL Server, a high-performance SQL database engine that powers millions of applications worldwide.

Core Purpose

MySQL Server provides a robust, scalable, and reliable platform for storing and retrieving structured data. It implements the SQL standard and supports ACID transactions, complex queries, and advanced features like replication, clustering, and pluggable storage engines.

Architecture Overview

Loading diagram...

Major Components

Core Server (sql/ directory)

• Query parser, optimizer, and executor
• Session and connection management
• Authentication and authorization
• Binary logging and replication

Storage Engines (storage/ directory)

• InnoDB: Default transactional engine with ACID support
• MyISAM: Fast, non-transactional engine
• NDB: Distributed cluster engine
• Additional engines: Archive, CSV, Federated, Heap, Blackhole

Client Libraries (libmysql/, sql-common/)

• C/C++ client API for connecting to MySQL Server
• Connection pooling and session management
• Protocol handling and authentication

Replication (sql/rpl_*.cc)

• Master-slave replication with binary logs
• Multi-threaded replica support
• GTID (Global Transaction ID) tracking
• Group Replication for high availability

Router (router/ directory)

• MySQL Router for connection routing and load balancing
• REST API service for database access
• Connection pooling and failover management

Plugins & Components (plugin/, components/)

• Pluggable authentication methods (LDAP, Kerberos, WebAuthn)
• Audit logging and monitoring
• Keyring management for encryption
• Performance Schema instrumentation

Build System

The project uses CMake for cross-platform compilation. Key build features include:

• Support for multiple platforms: Linux, Windows, macOS, Solaris
• Configurable storage engines and plugins
• Sanitizer support (ASAN, LSAN, TSAN, UBSAN)
• Link-time optimization (LTO) support
• Comprehensive testing framework with unit tests and integration tests

Key Technologies

• Language: C++ with C compatibility
• Concurrency: Multi-threaded architecture with mutex and RWLock synchronization
• Performance Schema: Built-in instrumentation for monitoring
• Security: SSL/TLS support, password hashing, privilege system
• Extensibility: Plugin architecture for custom functionality

Architecture & Core Components

MySQL's architecture follows a layered design with clear separation between query processing, optimization, and storage. The system is organized into distinct components that interact through well-defined interfaces.

Core Components

Thread Handler (THD)

The THD class is the central context object representing a single client connection. It maintains:

• Query state and execution context
• Memory allocation arena for the current statement
• Lexical analysis state (LEX)
• Transaction information and savepoints
• Security context and user privileges
• Session variables and connection attributes

Each thread has a thread-local current_thd pointer, enabling efficient access without global locks.

Parser & Lexer

SQL parsing occurs in two stages:

1. Lexical Analysis (sql_lex.h): Tokenizes input using Lex_input_stream and Parser_state. The lexer handles character sets, comments, and multi-statement queries.

2. Syntax Analysis: The Bison-based parser builds an Abstract Syntax Tree (AST) represented as LEX and Query_block structures. The parser validates syntax and semantic constraints.

Query Optimizer

The hypergraph join optimizer (join_optimizer/) transforms the query plan:

• Converts table references into a hypergraph representation
• Enumerates legal subplans using dynamic programming
• Assigns costs using the cost model
• Selects the cheapest execution plan as an AccessPath

Access paths represent execution strategies (table scans, index lookups, joins, aggregations, etc.).

Execution Engine

The executor converts access paths into iterators that produce rows:

• RowIterator implementations handle each access path type
• Supports nested loop joins, hash joins, and materialization
• Manages temporary tables for sorting and grouping

Storage Engine Abstraction

The handler class provides the interface between SQL and storage engines:

class handler {
  virtual int open(const char *name, int mode, uint test_if_locked);
  virtual int close();
  virtual int index_read_map(uchar *buf, const uchar *key, key_part_map keypart_map);
  virtual int rnd_next(uchar *buf);
  virtual int write_row(uchar *buf);
  virtual int update_row(const uchar *old_data, uchar *new_data);
  virtual int delete_row(const uchar *buf);
};

The handlerton singleton provides engine-level operations (transactions, recovery, statistics). Multiple storage engines (InnoDB, MyISAM, etc.) implement this interface.

Query Execution Flow

Loading diagram...

Memory Management

MySQL uses arena-based allocation (MEM_ROOT) for statement execution. Each statement gets its own arena, enabling efficient bulk deallocation when the statement completes. The Query_arena class manages this lifecycle.

Transaction Handling

Transactions are managed through Transaction_ctx, which tracks:

• Savepoints and rollback information
• Registered storage engines participating in the transaction
• Two-phase commit coordination
• Transaction isolation level and read-only state

The Ha_trx_info structure maintains per-engine transaction state for proper cleanup and recovery.

Query Execution & Optimization

Overview

Query execution in MySQL follows a two-phase model: optimization (planning) and execution (iteration). The optimizer generates an AccessPath tree representing the optimal execution strategy, which is then converted into a chain of RowIterator objects that actually fetch and process rows.

Access Paths: The Execution Plan

An AccessPath is a tree structure that describes how to execute a query. Each node represents either a table access method or a composite operation:

Basic Access Paths (table access methods):

• TABLE_SCAN – Full table scan
• INDEX_SCAN – Index scan with optional ordering
• INDEX_RANGE_SCAN – Range scan using index bounds
• REF / EQ_REF – Index lookup by key value
• CONST_TABLE – Single-row result
• FULL_TEXT_SEARCH – Full-text index search

Join Access Paths:

• NESTED_LOOP_JOIN – Nested loop join
• HASH_JOIN – Hash join (memory or spill-to-disk)
• BKA_JOIN – Batch key access join

Composite Access Paths (operations on results):

• FILTER – Apply WHERE conditions
• SORT – ORDER BY
• AGGREGATE – GROUP BY / aggregation
• LIMIT_OFFSET – LIMIT clause
• MATERIALIZE – Temporary table materialization

The Hypergraph Join Optimizer

The modern optimizer (FindBestQueryPlan()) uses a hypergraph-based approach to find the best join order and access methods:

1. Hypergraph Construction – Convert query tables and join conditions into a hypergraph
2. Subplan Enumeration – Explore all legal join orderings, keeping only the cheapest variant of each subplan
3. Predicate Pushdown – Apply WHERE conditions as early as possible in the plan
4. Post-Join Operations – Add ORDER BY, GROUP BY, LIMIT, etc.

Cost Model

The cost model estimates execution time using calibrated constants. Costs are measured in microseconds relative to a unit cost (reading one row from a table scan):

Row read cost = kReadOneRowCost + 
                kReadOneFieldCost * num_fields + 
                kReadOneByteCost * row_bytes

Key cost components:

• Index lookup cost – Fixed cost per index access
• Row read cost – Per-row cost based on fields and bytes
• IO cost – Block reads from disk (InnoDB-specific)
• Hash join cost – Build phase, probe phase, spill-to-disk overhead
• Aggregation cost – Per-row cost for GROUP BY operations

Range Optimizer

The range optimizer (test_quick_select()) analyzes WHERE conditions to determine if an index can be used efficiently:

• QUICK_RANGE – Represents a single index range [min_key, max_key]
• Range analysis – Converts predicates like col > 5 AND col < 10 into index ranges
• Index merge – Combines multiple index ranges using UNION or INTERSECTION

Row Iterators: Execution

Once the access path is finalized, it is converted into a chain of RowIterator objects. Each iterator:

• Initializes via Init() – Opens files, allocates buffers
• Reads rows via Read() – Returns 0 (success), –1 (EOF), or 1 (error)
• Stores data in table->record[0] – The row buffer

Iterators compose: a SortingIterator wraps another iterator to sort its output; a HashJoinIterator wraps two child iterators for the build and probe sides.

EXPLAIN Output

The opt_explain.h module converts access paths into human-readable EXPLAIN output, showing:

• Access method (scan type)
• Estimated rows and cost
• Join order and type
• Filters and conditions applied

Storage Engines

Architecture Overview

MySQL uses a pluggable storage engine architecture where each engine implements a common interface defined by the handler class in sql/handler.h. Every storage engine registers itself via a handlerton structure, which is a singleton containing engine-level operations like transaction management, table creation, and schema operations.

Loading diagram...

Core Components

Handler Class (sql/handler.h)

• Base class for all storage engines
• Defines per-table operations: read, write, update, delete, index operations
• Implements table-level locking and transaction support
• Each table instance gets its own handler object

Handlerton Structure (sql/handler.h)

• Singleton per storage engine
• Contains function pointers for engine-level operations
• Manages transactions, savepoints, recovery, and schema operations
• Registered as a plugin during server initialization

Major Storage Engines

InnoDB (storage/innobase/)

• Default transactional engine with full ACID support
• B-tree indexes, row-level locking, crash recovery
• Supports foreign keys and online DDL
• Mandatory engine in MySQL

MyISAM (storage/myisam/)

• Fast, non-transactional engine optimized for read-heavy workloads
• Table-level locking, full-text search support
• No crash recovery or foreign keys
• Legacy engine, still used for specific use cases

Heap/Memory (storage/heap/)

• In-memory storage using hash or B-tree indexes
• Ideal for temporary tables and session-specific data
• No persistence; data lost on server restart
• Registered as "MEMORY" engine

Archive (storage/archive/)

• Compressed, append-only storage for historical data
• Supports only INSERT and SELECT operations
• Excellent compression ratios using zlib
• No UPDATE or DELETE support

Other Engines

• CSV: Plain text CSV file storage for data interchange
• Federated: Remote MySQL table access via network
• Blackhole: "/dev/null" engine; discards all writes
• NDB: Distributed cluster engine for high availability
• TempTable: Optimized temporary table storage
• PerformanceSchema: System tables for performance monitoring

Plugin Registration

Storage engines register via mysql_declare_plugin() macro, providing metadata and initialization functions. The handlerton structure is populated during engine initialization with function pointers for all supported operations. Engines can be enabled/disabled via the disabled_storage_engines system variable.

Replication & Binlog

MySQL replication enables data synchronization across multiple servers using a source-replica architecture. The system combines binary logging (binlog) for recording changes and relay logs for applying those changes on replicas.

Architecture Overview

Loading diagram...

The replication system consists of two main threads on the replica:

• I/O Thread - Connects to the source, reads binlog events, and writes them to the relay log
• SQL Thread - Reads events from the relay log and applies them to the replica database

Binary Log (Binlog)

The binlog records all data modifications on the source server. Key components:

• Format Description Event - Describes the binlog format and checksum algorithm
• GTID Events - Global Transaction IDs for identifying transactions across servers
• Data Events - Query, Rows, and other modification events
• Rotation Events - Indicate when the binlog file rotates to a new file

The MYSQL_BIN_LOG class manages binlog operations including writing, rotation, and purging. It uses ordered commit with four stages: flush, sync, commit, and cleanup.

Relay Log

The relay log is a local copy of the source's binlog on the replica. It decouples the I/O thread from the SQL thread, allowing:

• Continued event retrieval even if the SQL thread is slow
• Recovery from replica restarts without re-fetching events
• Multi-threaded applier support for parallel event execution

The Relay_log_info class tracks relay log position and coordinates with the source's binlog position.

GTID (Global Transaction ID)

GTIDs uniquely identify transactions across the replication topology. Format: UUID:GNO where:

• UUID - Server identifier (Tsid)
• GNO - Global sequence number for that server

The Gtid_set class manages sets of GTIDs for tracking executed and lost transactions. This enables:

• Automatic position recovery after failover
• Verification that replicas have all required transactions
• Filtering of already-executed transactions

Replication Handlers

The Delegate class and its subclasses provide observer hooks for replication events:

• Trans_delegate - Transaction lifecycle hooks (before/after commit, rollback)
• Binlog_storage_delegate - Binlog flush and sync hooks
• Binlog_transmit_delegate - Source-side transmission hooks
• Binlog_relay_IO_delegate - Replica I/O thread hooks

These enable plugins to monitor and intercept replication operations.

Event Application

The SQL thread applies events through apply_event_and_update_pos(), which:

1. Checks if the event should be skipped (filtering, UNTIL conditions)
2. Applies the event to the database
3. Updates relay log position
4. For multi-threaded applier, queues work to parallel workers

Transaction boundaries are validated using Transaction_boundary_parser to ensure consistency.

Authentication & Security

Authentication Flow

MySQL uses a plugin-based authentication system where credentials are verified through pluggable authentication modules. The main entry point is acl_authenticate() in sql_authentication.cc, which orchestrates the authentication handshake:

1. Initial Handshake: Server sends a scramble (random challenge) to the client
2. Plugin Selection: Server determines which auth plugin to use (default: caching_sha2_password)
3. Credential Verification: Plugin validates the client's response using the scramble
4. Multi-Factor Auth: If enabled, additional authentication factors are verified
5. Security Context Setup: User's privileges are loaded into the session

The MPVIO_EXT structure encapsulates the authentication session context, including the scramble, plugin reference, and ACL user data.

Authorization & Access Control

Authorization is enforced through a hierarchical privilege system implemented in sql_authorization.cc:

• Global Privileges: Apply to all databases (stored in mysql.user table)
• Database Privileges: Scoped to specific databases (stored in mysql.db table)
• Table & Column Privileges: Fine-grained access control (stored in mysql.tables_priv and mysql.columns_priv)
• Dynamic Privileges: Custom privileges for plugins and components

The check_access() function validates whether a user has required permissions for an operation. Access checks use bitmasks (Access_bitmask) for efficient permission evaluation.

Role-Based Access Control

Roles enable privilege grouping and inheritance. The Role_activation class (in roles.h) manages role activation with four modes:

• ROLE_DEFAULT: Activate default roles assigned to the user
• ROLE_ALL: Activate all granted roles
• ROLE_NAME: Activate specific named roles
• ROLE_NONE: Deactivate all roles

Roles are stored as a directed acyclic graph (DAG) using Boost Graph Library, allowing role composition and privilege inheritance chains.

Security Context

The Security_context class maintains per-session security state:

• Current user and host identity
• Active roles and their privileges
• Database-specific access masks
• Privilege caches for performance

// Example: Check if user has SELECT privilege on database
bool has_select = sctx->check_access(SELECT_ACL, "mydb");

Password Management

MySQL supports multiple password hashing algorithms:

• SHA2-256: Default (caching_sha2_password plugin)
• SHA256: Legacy support (sha256_password plugin)
• Native: Older MySQL versions

RSA key pairs (managed by Rsa_authentication_keys) enable secure password transmission over unencrypted connections.

Connection Lifecycle

The Connection_handler abstract class manages how connections map to OS threads. When a connection arrives:

1. Channel_info object is created with connection metadata
2. Connection_handler::add_connection() processes the connection
3. Authentication occurs in the connection thread
4. Security_context is initialized with user privileges
5. Connection remains active until client disconnects or timeout occurs

Loading diagram...

Key Security Features

• Password Salting: Random salt per user prevents rainbow table attacks
• Partial Revokes: Deny specific privileges while granting broader ones
• Host-Based Access: Restrict connections by hostname/IP patterns
• Account Locking: Automatic lockout after failed login attempts
• Audit Logging: Track authentication and authorization events

Plugins & Components

MySQL supports two complementary extension mechanisms: traditional plugins and modern components. Both allow extending server functionality, but they differ in architecture, lifecycle management, and service discovery.

Traditional Plugins

Plugins are dynamically loadable modules that extend MySQL functionality. Each plugin implements a specific interface defined by its type (authentication, storage engine, audit, etc.). Plugins are declared using the st_mysql_plugin structure, which specifies:

• Type: Determines the plugin category (e.g., MYSQL_AUTHENTICATION_PLUGIN, MYSQL_DAEMON_PLUGIN)
• Lifecycle callbacks: init() called on load, deinit() called on unload
• Metadata: Author, description, license information

Plugin loading occurs during server startup via the plugin_initialize() function, which:

1. Loads the plugin binary (.so or .dll)
2. Calls type-specific initialization handlers
3. Registers system variables and status variables
4. Executes the plugin's init() callback

Examples include Group Replication (plugin/group_replication/src/plugin.cc) and the X Protocol plugin (plugin/x/src/module_mysqlx.cc).

Components and Service Registry

Components represent a modern, service-oriented architecture. Unlike plugins, components:

• Declare dependencies on required services
• Provide services that other components can consume
• Use a central registry for service discovery and lifecycle management

The Service Registry (implemented in components/libminchassis/registry.cc) maintains:

• A list of available service implementations
• Reference counts to prevent unloading in-use services
• Support for multiple implementations per service with a default variant

Component Lifecycle

The Dynamic Loader (components/libminchassis/dynamic_loader.cc) orchestrates component loading in phases:

Load Phase:
  1. Load component binaries by scheme (file://, etc.)
  2. Collect all provided services
  3. Check and acquire required dependencies
  4. Initialize components (call init())
  5. Register services in the registry
  6. Acquire required services for components

Unload Phase:
  1. Verify no active references to provided services
  2. Unload dependent components first (topological order)
  3. Unregister services
  4. Deinitialize components (call deinit())
  5. Unload component binaries

Service Acquisition and Release

Components use the my_service<T> RAII wrapper for safe service access:

my_service<SERVICE_TYPE(registry)> registry("registry");
my_h_service service_handle;
registry->acquire("service_name", &service_handle);
// Use service...
registry->release(service_handle);

Key Differences

The minimal chassis (components/libminchassis/minimal_chassis.cc) bootstraps the component system by initializing the registry and dynamic loader services, enabling all subsequent component loading.

MySQL Router & Connection Routing

Overview

MySQL Router acts as a transparent proxy that intercepts client connections and routes them to appropriate backend MySQL servers. The routing system is built on a plugin architecture with two main destination management strategies: static routing (for fixed server lists) and metadata cache routing (for dynamic cluster topologies).

Connection Routing Architecture

The routing pipeline consists of three key phases:

1. Connection Acceptance: The MySQLRouting class listens on configured TCP ports and Unix sockets, accepting incoming client connections via AcceptingEndpointTcpSocket and AcceptingEndpointUnixSocket.

2. Destination Selection: When a client connects, the DestinationManager selects a backend server using the configured routing strategy. Two implementations exist:

   • StaticDestinationsManager: For fixed server lists with strategies like round-robin or first-available
   • DestMetadataCacheManager: For dynamic clusters that refresh server lists from metadata

3. Connection Forwarding: A protocol-specific connection handler (MysqlRoutingClassicConnection or MysqlRoutingXConnection) is created to forward traffic bidirectionally between client and server.

Routing Strategies

MySQL Router supports multiple routing strategies, each with different failover behavior:

• First-Available: Tries servers in order; moves to next only on failure. Keeps current position on success.
• Next-Available: Cycles through all servers sequentially, moving to the next after each attempt.
• Round-Robin: Distributes connections evenly across servers; fails only when all servers are exhausted.
• Round-Robin-With-Fallback: For metadata cache only; tries primary group first, then falls back to secondary groups.

The strategy handler maintains state about the last connection attempt and failed server indices to implement these behaviors.

Destination Management

The DestinationManager interface abstracts destination selection. Key responsibilities include:

• Initialization: Validates that destinations are available and distinct from the bind address
• Selection: get_next_destination() returns the next server to try based on strategy and connection history
• Status Tracking: connect_status() records whether the last connection succeeded, informing future selections
• Refresh: Metadata cache managers periodically refresh the server list from cluster metadata

Connection Lifecycle

When a client connects:

1. Router accepts the socket and creates a connection object
2. create_connection() instantiates a protocol handler with client and server socket wrappers
3. The handler's async_run() method starts the connection flow processor
4. The processor manages authentication, command forwarding, and bidirectional data transfer
5. On disconnect, the connection is removed from the container and optionally returned to a connection pool

Protocol Support

Router supports both MySQL Classic Protocol (port 3306) and X Protocol (port 33060). Each protocol has dedicated connection classes that handle protocol-specific handshakes, authentication methods, and command forwarding. The routing logic remains protocol-agnostic through template-based abstractions.

Configuration Integration

Routing configuration is loaded from the router configuration file and includes:

• Bind address and port
• Destination server list or metadata cache reference
• Routing strategy selection
• Connection limits and timeouts
• SSL/TLS settings for client and server connections

The RoutingConfig structure encapsulates these settings and is passed to MySQLRouting during initialization.

Client Utilities & Tools

MySQL provides a comprehensive suite of client utilities and tools for database administration, backup, replication, and maintenance. These tools form the primary interface for users and administrators to interact with MySQL servers.

Core Client Applications

mysql is the interactive command-line client that allows users to execute SQL queries and MySQL commands. It supports batch mode for scripting, interactive mode with readline support, and features like command history, paging, and output formatting. The client handles connection management, authentication, and provides built-in commands for administrative tasks.

mysqldump performs logical backups by generating SQL statements that recreate database structures and data. It supports multiple output formats (SQL, CSV, XML), selective table/database dumping, and advanced features like single-transaction mode for consistent snapshots, GTID handling, and replica data capture. It can also dump users, grants, and stored routines.

mysqlbinlog reads and interprets MySQL binary logs (binlogs) and relay logs. It parses binary log events and outputs them in human-readable SQL format or raw format suitable for replication. It supports filtering by position, timestamp, GTID, and database, making it essential for point-in-time recovery and replication analysis.

mysqlcheck performs table maintenance operations including CHECK, REPAIR, ANALYZE, and OPTIMIZE. It can process all databases or specific tables, with options for fast checks, extended checks, and automatic repair. It integrates with the server to validate table integrity and fix corruption issues.

Client Library (libmysql)

The libmysql library provides the C API for client-server communication. It handles connection establishment, authentication (supporting multiple auth plugins), query execution, result set retrieval, and protocol management. The library supports both synchronous and asynchronous operations, compression, SSL/TLS encryption, and various connection options.

Connection Architecture

Loading diagram...

Key Features

• Authentication: Support for native password, caching SHA2, Kerberos, LDAP, and WebAuthn plugins
• Connection Options: SSL/TLS, compression (zstd, zlib), connection timeouts, and network namespaces
• Query Attributes: Client-side query attributes for application context
• Error Handling: Comprehensive error reporting with server error codes and messages
• Configuration: Support for option files (my.cnf) and command-line arguments

Testing & Quality Assurance

MySQL's testing infrastructure comprises three complementary layers: integration tests via the MySQL Test Framework, unit tests using Google Test and TAP, and stress testing tools. This multi-layered approach ensures comprehensive quality assurance across the entire codebase.

MySQL Test Framework (MTR)

The primary integration testing system is driven by mysql-test-run.pl, a Perl script that orchestrates test execution. MTR manages test lifecycle including server startup, test case execution, result verification, and cleanup. Tests are organized into suites (e.g., main, innodb, replication) with individual .test files containing SQL statements and test directives.

Key Components:

• mysqltest: The test client that interprets test language commands (SQL, assertions, synchronization primitives)
• SafeProcess: Platform-specific process management ensuring reliable server lifecycle control
• Test Collections: Predefined test suites for different scenarios (daily, weekly, push validation)
• Result Verification: Automatic comparison of actual output against expected .result files

Unit Testing Frameworks

Google Test (GUnit): Located in unittest/gunit/, this framework tests individual components in isolation. Tests follow the naming convention *-t.cc and are compiled into executables. The framework supports:

• Small tests (fast, minimal dependencies) linked with gunit_small
• Large tests (full server context) linked with gunit_large
• Merged test binaries (merge_small_tests-t, merge_large_tests-t) for efficient execution

TAP (Test Anything Protocol): Located in unittest/mytap/, this simpler framework provides basic unit testing for C code without heavy dependencies.

Test Execution & Reporting

MTR provides comprehensive reporting through:

• Parallel Execution: Tests run concurrently (configurable via --parallel flag)
• Timeout Management: Individual test timeouts prevent hangs
• Result Aggregation: Summary statistics and detailed failure reports
• XML Output: Machine-readable test results for CI/CD integration
• Suppression Files: Valgrind, ASAN, and TSAN suppressions for known issues

Quality Assurance Practices

Tests validate:

• Functional Correctness: SQL semantics, query execution, data consistency
• Replication: Master-slave synchronization, GTID handling, failover scenarios
• Storage Engines: Engine-specific functionality across InnoDB, MyISAM, Archive
• Security: Authentication, authorization, encryption
• Performance: Query optimization, index usage, memory efficiency
• Memory Safety: Valgrind and AddressSanitizer integration detects leaks and corruption

Running Tests

# Run default test suite
perl mysql-test-run.pl --force --parallel=4

# Run specific suite
perl mysql-test-run.pl --suite=innodb

# Run with external server
mysql-test-run --extern alias analyze

# Run unit tests
make test-unit

# Run with memory checking
ctest -D ExperimentalMemCheck

Test collections in mysql-test/collections/ define standard test configurations for different validation stages (push, daily, weekly).