MySQL Database Administrator (DBA) Course

Lesson 1 • MySQL Server Architecture Overview

Examines the connection layer, query cache, optimizer, and storage engine interface. Provides the mental model needed to diagnose performance and behavior issues.

Lesson 2 • Installing MySQL on Major Platforms

Guides installation on Linux, Windows, and macOS using packages and binary distributions. Ensures students can deploy MySQL in any standard enterprise environment.

Lesson 3 • Connecting to MySQL Server

Covers the mysql CLI, MySQL Workbench, and programmatic connection strings. Students verify connectivity and understand authentication flow.

Lesson 4 • Relational Database Core Concepts

Covers tables, rows, columns, keys, and relationships as MySQL's data model foundation. Establishes vocabulary used throughout the entire course.

Lesson 1 • Querying Data with SELECT

Builds SELECT skills including filtering, sorting, grouping, and aggregate functions. Enables DBAs to inspect data and validate application queries.

Lesson 2 • Data Definition Language Essentials

Covers CREATE, ALTER, and DROP for databases, tables, and indexes. Directly supports schema management tasks performed throughout the course.

Lesson 3 • Joins and Set Operations

Explains INNER, LEFT, RIGHT, and CROSS joins plus UNION and INTERSECT. Equips DBAs to analyze multi-table relationships and audit data integrity.

Lesson 4 • Transactions and Locking Basics

Introduces BEGIN, COMMIT, ROLLBACK, and isolation levels with InnoDB locking. Lays the groundwork for concurrency management covered in later chapters.

Lesson 1 • Roles and Privilege Groups

Introduces MySQL roles to bundle privileges and simplify large-scale access management. Students reduce administrative overhead while maintaining security consistency.

Lesson 2 • MySQL Authentication Architecture

Explains authentication plugins, password hashing, and the mysql.user table structure. Provides the foundation for all account creation and policy decisions.

Lesson 3 • Privilege System and GRANT/REVOKE

Teaches global, database, table, and column-level privileges with GRANT and REVOKE. Enables precise access control for multi-tenant and regulated environments.

Lesson 4 • Securing Network and File Access

Covers SSL/TLS enforcement, encrypted connections, and file privilege restrictions. Protects data in transit and prevents unauthorized file system access.

Lesson 1 • Choosing the Right Storage Engine

Compares InnoDB, MyISAM, MEMORY, CSV, and ARCHIVE for specific use cases. Students select engines that match workload requirements and data durability needs.

Lesson 2 • Data Types and Column Attributes

Covers numeric, string, date/time, and JSON data types with storage implications. Correct type selection reduces storage costs and prevents silent data truncation.

Lesson 3 • InnoDB Architecture Deep Dive

Examines the buffer pool, redo log, undo log, and tablespace structure in InnoDB. Directly informs tuning decisions made in the performance chapter.

Lesson 4 • Indexes: Types and Design Principles

Explains B-tree, full-text, spatial, and composite indexes with design guidelines. Students build index strategies that accelerate queries without excessive write overhead.

Lesson 1 • Logical Backups with mysqldump

Covers mysqldump flags, single-transaction option, and output formats for InnoDB. Students produce portable, human-readable backups suitable for migration and archiving.

Lesson 2 • Physical Backups with MySQL Enterprise Backup

Teaches hot backup, incremental backup, and apply-log operations using mysqlbackup. Physical backups minimize downtime for large databases.

Lesson 3 • Backup Types and Recovery Objectives

Defines full, incremental, and differential backups alongside RTO and RPO metrics. Aligns backup strategy with business continuity requirements.

Lesson 4 • Backup Automation and Testing

Covers scheduling backups with cron, verifying restores, and documenting recovery runbooks. Untested backups provide false confidence; this section closes that gap.

Lesson 1 • Monitoring and Troubleshooting Replication

Covers SHOW REPLICA STATUS, replication errors, and lag monitoring techniques. Students diagnose and resolve common replication failures quickly.

Lesson 2 • Replication Fundamentals and Architecture

Explains binary log-based replication, the IO thread, SQL thread, and relay log. Establishes the conceptual model for all replication configurations.

Lesson 3 • Setting Up Source-Replica Replication

Walks through GTID-based and position-based replication setup from scratch. Students build a working replication pair and verify data synchronization.

Lesson 4 • ProxySQL and Load Balancing

Configures ProxySQL for read/write splitting, connection pooling, and failover routing. Completes the high-availability stack with intelligent traffic management.

Lesson 1 • Concurrency, Locking, and Deadlock Resolution

Analyzes InnoDB lock waits, deadlock graphs, and transaction isolation trade-offs. Students reduce contention and resolve deadlocks without sacrificing data integrity.

Lesson 2 • EXPLAIN and Query Execution Plans

Teaches EXPLAIN, EXPLAIN ANALYZE, and FORMAT=JSON output interpretation. Students identify full table scans, bad joins, and missing indexes systematically.

Lesson 3 • InnoDB Buffer Pool and Memory Tuning

Tunes innodb_buffer_pool_size, instances, chunk size, and related memory variables. Proper memory allocation is the highest-impact single tuning action for InnoDB.

Lesson 4 • Index Optimization Techniques

Covers covering indexes, index merges, invisible indexes, and index hints. Students eliminate redundant indexes and add targeted ones to cut query cost.

Lesson 1 • MySQL Upgrade Planning and Execution

Guides in-place and logical upgrade paths between major MySQL versions with pre-checks. Students upgrade safely without data loss or extended downtime.

Lesson 2 • Schema Change Management

Compares online DDL, pt-online-schema-change, and gh-ost for zero-downtime changes. Students alter large tables in production without blocking application traffic.

Lesson 3 • Automating DBA Tasks with Shell and Python

Builds automation scripts for backups, health checks, and alerts using Bash and Python. Reduces toil and enforces consistent operational procedures.

Lesson 4 • MySQL Enterprise Audit and Compliance

Configures audit logging to capture connection and query events for compliance reporting. Students produce audit trails required by data governance frameworks.