Big Data AcademyQuery · move · process · govern
All 22 chapters available

Stage 02 · Relational Databases

MySQL

A complete MySQL path from installation and SQL fundamentals through InnoDB internals, indexing, optimizer behavior, replication, Group Replication, security, backup, observability, upgrades, and production architecture.

Start first lesson View all chapters
22chapters
110lesson paths
Beginner → Advancedlearning level
Publishedcourse state
Coverage baselineMySQL 8.4 LTS plus the Innovation release model

Course brief

From engine fundamentals to advanced production operations.

A complete MySQL path from installation and SQL fundamentals through InnoDB internals, indexing, optimizer behavior, replication, Group Replication, security, backup, observability, upgrades, and production architecture.

This syllabus deliberately separates conceptual foundations, hands-on SQL, engine internals, operational tooling, reliability, security, performance, and capstone work so future lesson generation can go deep without compressing advanced subjects into generic chapters.

By the end

You will be able to

  • Model and query MySQL databases with correct transactional semantics and indexing
  • Explain InnoDB storage, MVCC, redo/undo, locking, and optimizer evidence
  • Design replication or InnoDB Cluster high availability and safe failover
  • Secure, back up, restore, observe, tune, upgrade, and troubleshoot production MySQL
  • Integrate MySQL with applications using reliable pooling, migrations, retries, and deployment patterns

Complete syllabus

22 chapters · 110 lesson paths.

All 110 lesson paths are available and linked below. The curriculum moves from MySQL platform foundations through SQL, InnoDB internals, administration, reliability, security, replication, observability, performance engineering, upgrades, specialized capabilities, and a production capstone.

01

Chapter 1

MySQL Platform Foundations, Editions, Releases, and Lab Setup

5 lessons
01
What MySQL Is: Server Architecture, Client/Server Boundaries, and Common WorkloadsPublished lesson
Open 02
Community vs Enterprise Capabilities, Licensing Awareness, and Deployment ChoicesPublished lesson
Open 03
LTS vs Innovation Releases: Version Selection, Support Windows, and CompatibilityPublished lesson
Open 04
Install MySQL on Windows, Linux, Containers, and Verify the ToolchainPublished lesson
Open 05
Build a Reproducible Course Lab with Sample Data, Accounts, and Safety ConventionsPublished lesson
Open
02

Chapter 2

Server Architecture, Processes, Files, Connections, and Configuration

5 lessons
01
mysqld Startup, Data Directory, Configuration Files, and Option PrecedencePublished lesson
Open 02
Connection Lifecycle, Threads, Sessions, Timeouts, and Resource ConsumptionPublished lesson
Open 03
System Variables, Status Variables, Dynamic Configuration, and SET PERSISTPublished lesson
Open 04
Character Sets, Collations, Time Zones, Locales, and Server DefaultsPublished lesson
Open 05
Configuration Validation, Startup Failures, Error Logs, and a Baseline Server ProfilePublished lesson
Open
03

Chapter 3

Schemas, Tables, Data Types, Keys, Constraints, and SQL Modes

5 lessons
01
Databases and Schemas in MySQL: Namespaces, Metadata, and Object OwnershipPublished lesson
Open 02
Numeric, String, Temporal, JSON, Spatial, and Binary Data TypesPublished lesson
Open 03
PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT, and Generated ColumnsPublished lesson
Open 04
AUTO_INCREMENT, Surrogate vs Natural Keys, and Identifier DesignPublished lesson
Open 05
SQL Modes, Strictness, Implicit Conversion, Truncation, and Data-Quality BoundariesPublished lesson
Open
04

Chapter 4

Core SQL Querying: Filtering, Joins, Subqueries, CTEs, and Set Operations

5 lessons
01
SELECT Execution Basics, Projection, Predicates, NULL Semantics, and OrderingPublished lesson
Open 02
INNER, OUTER, CROSS, and Self Joins with Cardinality ReasoningPublished lesson
Open 03
Scalar, Correlated, EXISTS, IN, and Derived-Table SubqueriesPublished lesson
Open 04
Common Table Expressions, Recursive CTEs, and Hierarchical QueriesPublished lesson
Open 05
UNION, INTERSECT, EXCEPT, Query Composition, and Readability PatternsPublished lesson
Open
05

Chapter 5

Advanced SQL: Aggregation, Window Functions, JSON, and Analytical Patterns

5 lessons
01
GROUP BY, HAVING, Functional Dependence, ROLLUP, and Aggregation DesignPublished lesson
Open 02
Window Frames, Ranking, Running Totals, Gaps-and-Islands, and Top-N per GroupPublished lesson
Open 03
JSON Documents, Paths, JSON_TABLE, Indexable JSON Patterns, and ValidationPublished lesson
Open 04
Date/Time, String, Regular Expression, Numeric, and Conditional FunctionsPublished lesson
Open 05
Reporting Queries, Cohort-Style Analysis, Pivots by Conditional Aggregation, and Reusable ViewsPublished lesson
Open
06

Chapter 6

Data Modification, Transactions, Locking, and Concurrency Semantics

5 lessons
01
INSERT, Multi-Row Writes, INSERT ... SELECT, and Generated ValuesPublished lesson
Open 02
UPDATE, DELETE, Multi-Table DML, and Guardrails Against Wide ChangesPublished lesson
Open 03
Transactions, Autocommit, COMMIT, ROLLBACK, SAVEPOINT, and Error HandlingPublished lesson
Open 04
Isolation Levels, Consistent Reads, Locking Reads, MVCC, and AnomaliesPublished lesson
Open 05
Deadlocks, Lock Waits, Retry Design, Idempotency, and Concurrency TestingPublished lesson
Open
07

Chapter 7

InnoDB Storage Architecture and Transaction Internals

5 lessons
01
InnoDB Tablespaces, Pages, Extents, Segments, and Row FormatsPublished lesson
Open 02
Clustered Indexes, Secondary Indexes, Hidden Columns, and Record LayoutPublished lesson
Open 03
Buffer Pool Architecture, Change Buffer, Adaptive Hashing, and Caching BehaviorPublished lesson
Open 04
Redo Logs, Undo Logs, Doublewrite, Checkpoints, and Crash RecoveryPublished lesson
Open 05
Purge, History List, Long Transactions, MVCC Cleanup, and Storage HealthPublished lesson
Open
08

Chapter 8

Index Engineering and Access Path Design

5 lessons
01
B-Tree Index Structure, Selectivity, Cardinality, and Leftmost-Prefix RulesPublished lesson
Open 02
Composite Index Ordering, Covering Indexes, Prefix Indexes, and Redundant IndexesPublished lesson
Open 03
Functional, Multi-Valued, FULLTEXT, and Spatial Index Use CasesPublished lesson
Open 04
Index Condition Pushdown, Loose/Tight Index Scans, and Skip-Scan ConceptsPublished lesson
Open 05
Designing an Index Portfolio from Workload Evidence and Write-Cost ConstraintsPublished lesson
Open
09

Chapter 9

Optimizer, EXPLAIN, Statistics, and Query Plan Engineering

5 lessons
01
How the MySQL Optimizer Builds Access Paths, Join Orders, and Cost EstimatesPublished lesson
Open 02
EXPLAIN, EXPLAIN ANALYZE, FORMAT=TREE/JSON, and Iterator TimingPublished lesson
Open 03
Histograms, Persistent Statistics, Cardinality Errors, and ANALYZE TABLEPublished lesson
Open 04
Join Algorithms, Semijoin/Antijoin Transformations, Derived Tables, and MaterializationPublished lesson
Open 05
Plan Regressions, Optimizer Hints, Invisible Indexes, and Evidence-Driven TuningPublished lesson
Open
10

Chapter 10

Stored Programs, Views, Triggers, Events, and Server-Side Logic

5 lessons
01
Views, Updatability, SQL SECURITY, Definer Context, and Dependency ManagementPublished lesson
Open 02
Stored Procedures, Functions, Parameters, Condition Handlers, and DiagnosticsPublished lesson
Open 03
Triggers, OLD/NEW Rows, Ordering, Auditing Uses, and Hidden Coupling RisksPublished lesson
Open 04
Event Scheduler, Recurring Maintenance Jobs, Failure Handling, and ObservabilityPublished lesson
Open 05
When Server-Side Logic Helps—and When Application Logic Is the Better BoundaryPublished lesson
Open
11

Chapter 11

Users, Roles, Authentication, Authorization, and Least Privilege

5 lessons
01
Accounts, Host Matching, Authentication Plugins, Password Policy, and Account LifecyclePublished lesson
Open 02
Privileges, Roles, DEFAULT ROLE, Dynamic Privileges, and Delegated AdministrationPublished lesson
Open 03
Object-Level vs Administrative Privilege Design and Separation of DutiesPublished lesson
Open 04
PROXY, DEFINER, SQL SECURITY, Stored Objects, and Privilege-Escalation RisksPublished lesson
Open 05
Design and Audit a Least-Privilege Access Model for Applications and OperatorsPublished lesson
Open
12

Chapter 12

Encryption, TLS, Secrets, Auditing, and Security Hardening

5 lessons
01
TLS for Client Connections, Certificate Validation, and Secure Transport EnforcementPublished lesson
Open 02
Data-at-Rest Encryption Concepts, Key Management Boundaries, and Backup EncryptionPublished lesson
Open 03
Secrets Handling for Applications, Option Files, Environment Variables, and RotationPublished lesson
Open 04
Audit Logging Options, General/Slow Logs, Privacy, and Forensic ConsiderationsPublished lesson
Open 05
Hardening Checklist: Network Exposure, Local Files, Plugins, Accounts, and Patch DisciplinePublished lesson
Open
13

Chapter 13

Backup, Restore, Binary Logs, and Point-in-Time Recovery

5 lessons
01
Logical vs Physical Backup: mysqldump, MySQL Shell, Snapshots, and TradeoffsPublished lesson
Open 02
Consistent Backups with Transactions, Locks, Replication Coordinates, and Large DatasetsPublished lesson
Open 03
Binary Log Formats, GTIDs, Retention, and Recovery PrerequisitesPublished lesson
Open 04
Point-in-Time Recovery from Full Backup plus Binary LogsPublished lesson
Open 05
Restore Testing, RPO/RTO Measurement, Corruption Drills, and Backup AutomationPublished lesson
Open
14

Chapter 14

Asynchronous Replication, GTIDs, Topologies, and Operational Safety

5 lessons
01
Source/Replica Architecture, Binary Log Flow, Relay Logs, and Replication ThreadsPublished lesson
Open 02
GTID-Based Replication, Auto-Positioning, Failover Readiness, and Errant TransactionsPublished lesson
Open 03
Statement, Row, and Mixed Logging Semantics with Determinism RisksPublished lesson
Open 04
Replication Filters, Delayed Replicas, Read Scaling, and Reporting WorkloadsPublished lesson
Open 05
Lag Diagnosis, Broken Replication, Data Drift, Re-Synchronization, and Promotion RunbooksPublished lesson
Open
15

Chapter 15

Group Replication, InnoDB Cluster, Router, and High Availability

5 lessons
01
Consensus-Oriented Group Replication Concepts, Membership, Quorum, and Failure ModesPublished lesson
Open 02
Single-Primary vs Multi-Primary Modes, Conflict Detection, and Workload ConstraintsPublished lesson
Open 03
MySQL Shell AdminAPI, InnoDB Cluster Lifecycle, Recovery, and Rejoin OperationsPublished lesson
Open 04
MySQL Router, Connection Routing, Read/Write Splitting, and Application FailoverPublished lesson
Open 05
Failure Injection, Split-Brain Avoidance, Maintenance, Upgrades, and HA Acceptance TestsPublished lesson
Open
16

Chapter 16

Performance Schema, sys Schema, Logs, Metrics, and Observability

5 lessons
01
Performance Schema Instruments, Consumers, Events, and Measurement OverheadPublished lesson
Open 02
sys Schema Views for Sessions, Statements, Waits, I/O, Memory, and Index UsagePublished lesson
Open 03
Slow Query Log, Error Log, General Log, and Structured Diagnostic WorkflowsPublished lesson
Open 04
Key OS and MySQL Metrics: CPU, I/O, Buffer Pool, Connections, Temp Tables, and RedoPublished lesson
Open 05
Build a Production Dashboard, Alert Thresholds, Baselines, and Incident TimelinesPublished lesson
Open
17

Chapter 17

Memory, I/O, Temporary Work, and Server Performance Engineering

5 lessons
01
Buffer Pool Sizing, Working Sets, Dirty Pages, Flushing, and Memory PressurePublished lesson
Open 02
Per-Connection Buffers, Sort/Join Buffers, Memory Multipliers, and OOM RiskPublished lesson
Open 03
Temporary Tables, Spill to Disk, tmpdir, Internal Temp Engines, and Query RewritePublished lesson
Open 04
Redo Capacity, I/O Capacity, Flush Methods, Storage Latency, and Checkpoint PressurePublished lesson
Open 05
Benchmarking Methodology, Warm vs Cold Cache, Concurrency, Percentiles, and RepeatabilityPublished lesson
Open
18

Chapter 18

Partitioning, Large Tables, Archiving, and Data Lifecycle

5 lessons
01
Partitioning Concepts, Supported Strategies, Partition Pruning, and Design ConstraintsPublished lesson
Open 02
Partition Keys, Unique-Key Rules, Hot/Cold Data, and Operational TradeoffsPublished lesson
Open 03
Online DDL, Instant/In-Place/Copy Algorithms, Locks, and Schema Change PlanningPublished lesson
Open 04
Archiving, Purging, Retention Jobs, and Avoiding Massive Delete PathologiesPublished lesson
Open 05
Managing Multi-Terabyte Tables: Capacity, Maintenance Windows, and Growth ForecastsPublished lesson
Open
19

Chapter 19

Application Integration, Connectors, Pools, ORMs, and Reliability Patterns

5 lessons
01
Connection Strings, TLS, Timeouts, Pools, and Session Initialization ContractsPublished lesson
Open 02
Prepared Statements, Parameter Binding, SQL Injection Defense, and Type MappingPublished lesson
Open 03
Transaction Boundaries in Application Code, Retries, Idempotency, and Lost ConnectionsPublished lesson
Open 04
ORM Query Generation, N+1 Problems, Pagination, Bulk Writes, and Plan VisibilityPublished lesson
Open 05
Health Checks, Graceful Degradation, Read Replicas, Migrations, and Deployment CoordinationPublished lesson
Open
20

Chapter 20

Schema Migration, Upgrades, Compatibility, and Zero/Low-Downtime Change

5 lessons
01
Versioned Schema Migrations, Expand/Contract Patterns, and Backward CompatibilityPublished lesson
Open 02
Online Schema Change Strategies, Native DDL Capabilities, and External Tool ConceptsPublished lesson
Open 03
Pre-Upgrade Checks, Deprecated Features, SQL Modes, Collations, and Authentication ChangesPublished lesson
Open 04
Rolling Upgrade Considerations for Replication and High-Availability TopologiesPublished lesson
Open 05
Rollback Planning, Data Validation, Canaries, and Post-Upgrade Performance VerificationPublished lesson
Open
21

Chapter 21

Specialized MySQL Capabilities: NDB, Document Store, Spatial, and Search

5 lessons
01
NDB Cluster Architecture, Distribution, Availability, and When It FitsPublished lesson
Open 02
X Protocol, MySQL Shell, Document Store Concepts, and CRUD APIsPublished lesson
Open 03
Spatial Types, SRIDs, Spatial Functions, R-Tree Indexing, and GIS WorkloadsPublished lesson
Open 04
FULLTEXT Search, Tokenization, Relevance, Stopwords, and Search-System BoundariesPublished lesson
Open 05
Choosing Native Features vs Dedicated Systems for Search, Analytics, and Distributed ScalePublished lesson
Open
22

Chapter 22

Production Capstone: Design, Secure, Scale, Tune, and Recover MySQL

5 lessons
01
Define SLOs, Workload, Data Model, Capacity Forecast, and Architecture Decision RecordPublished lesson
Open 02
Build the Production Schema, Index Portfolio, Security Model, and Migration PipelinePublished lesson
Open 03
Load Test, Capture Plans, Tune Queries/Server, and Establish Performance BaselinesPublished lesson
Open 04
Implement Backups, PITR, Replication/HA, Monitoring, Alerts, and Incident RunbooksPublished lesson
Open 05
Execute Failure/Recovery Drills and Defend the Final MySQL Production DesignPublished lesson
Open

Keep knowledge open

Help the academy stay free and grow.

If these curricula save you planning time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only
0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only assets compatible with the Ethereum/ERC-20 network. Do not send TRC-20/TRON assets.