ITTrainingNow

MySQL Administration

Learn to configure, secure, maintain, recover, and tune MySQL using a training database based on Microsoft Northwind data.

Course overview

Progress from MySQL fundamentals to advanced database administration techniques during this three-day instructor-led course. Work with an instructor-provided MySQL adaptation of Microsoft's Northwind sample database, managing products, customers, orders, and order details for a trading company. Start with server architecture, InnoDB, configuration, and access control, practise monitoring and tested backup recovery, then investigate performance, configure a replica, and plan safe upgrades. Guided exercises use isolated training servers on a supported MySQL LTS release and a larger synthetic Northwind order workload so participants can observe administration and performance behaviour without touching production systems.

3 daysCourse length
12 modulesGuided exercises and practice
Basics to advancedSQL familiarity required
Instructor-ledNorthwind administration labs

Course coverage

Build a practical administration workflow from the first connection to recovery, performance analysis, and availability planning.

  • 01 MySQL architecture, InnoDB, storage, redo logs, and binary logs
  • 02 Installation, services, mysql client, Workbench, and configuration
  • 03 Northwind schemas, data loading, constraints, and AUTO_INCREMENT
  • 04 User accounts, host matching, roles, privileges, authentication, and TLS
  • 05 InnoDB transactions, isolation, locks, deadlocks, and maintenance
  • 06 Performance Schema, sys schema, logs, and routine monitoring
  • 07 Consistent logical backups with mysqldump and verified restores
  • 08 Binary log retention, mysqlbinlog, and point-in-time recovery
  • 09 EXPLAIN ANALYZE, indexes, statistics, and measured tuning
  • 10 Source-replica replication, GTIDs, row-based logging, and lag
  • 11 Controlled failover, recovery objectives, and availability limits
  • 12 Updates, upgrade checks, rehearsal, and operational runbooks

Participants will learn to

  • Explain MySQL and InnoDB architecture and administer a training server.
  • Load and verify a MySQL adaptation of Northwind data.
  • Apply least-privilege accounts and roles and secure client connections.
  • Monitor activity and investigate slow queries, blocking, and deadlocks.
  • Create consistent logical backups and verify data and access after restore.
  • Recover a database to a chosen point using retained binary logs.
  • Measure query performance and test indexing and configuration changes.
  • Configure a replica and document failover and upgrade procedures.
Practical outcome: Administer a Northwind MySQL environment with tested access controls, a maintenance checklist, verified backup and recovery procedures, a measured query-tuning exercise, and a replication replica. Leave with a lab runbook covering monitoring, recovery objectives, controlled failover, and upgrade planning.

Three-day course outline

Day 1 - MySQL foundations, configuration, and security: Understand server processes, databases and schemas, storage engines, and InnoDB tables, indexes, redo and undo logs. Distinguish InnoDB crash recovery from binary logging for replication and point-in-time recovery. Follow a guided installation and service-management exercise, connect with the mysql client and MySQL Workbench, and inspect option files, system variables, and which settings require a restart or can be persisted dynamically. Load the MySQL-compatible Northwind schema and data, inspect Products, Categories, Customers, Orders, and Order Details relationships, and verify row counts, foreign keys, character sets, collations, and AUTO_INCREMENT values. Create host-qualified user accounts and roles, apply least-privilege grants and default roles, and review caching_sha2_password authentication and TLS with server identity verification for remote connections. Labs: prepare Northwind and verify allowed and denied operations with reader, application, and administrator accounts.

Day 2 - Maintenance, monitoring, backup, and recovery: Explore InnoDB transaction isolation, multiversion concurrency control (MVCC), blocking, and deadlocks. Use Performance Schema, the sys schema, and InnoDB status output to investigate a blocked Northwind order update. Inspect the error and slow query logs, table statistics, disk growth, and routine maintenance needs. Create a logical backup with mysqldump, using --single-transaction for a consistent InnoDB snapshot while preventing concurrent schema changes; explain why this does not guarantee consistency for nontransactional tables. Include required routines, events, and triggers, and capture accounts, roles, and grants separately rather than copying system tables across versions. Restore into an isolated environment and verify data and privileges. Configure binary log retention and preserve the backup's matching log coordinates and required log sequence. Use mysqlbinlog to replay complete transactions to a position just before a simulated accidental order deletion. Labs: restore Northwind, practise point-in-time recovery (PITR), and compare expected records. Relate backup retention and restore testing to recovery point and recovery time objectives (RPO/RTO), and introduce physical backup options and their consistency requirements.

Day 3 - Performance, replication, and advanced administration: Analyse a larger synthetic Northwind order workload with Performance Schema statement summaries, the sys schema, and EXPLAIN ANALYZE, noting that it executes the statement and using safe read-only lab queries. Compare query plans before and after appropriate indexes and ANALYZE TABLE; review measured changes to the InnoDB buffer pool, connection limits, and durability settings rather than applying universal tuning values. Configure GTID-based source-replica replication with row-based binary logging on prepared servers, verify read-only access, and inspect replication status, lag, and errors. Discuss asynchronous replication, possible data loss, and protecting replicas from unintended writes. Rehearse controlled manual failover by stopping or fencing the old source, checking applied transactions, enabling writes on the selected replica, and redirecting clients; outline how to rejoin the former source. Finish with supported upgrade paths, MySQL Shell upgrade checks, compatibility testing, staging rehearsals, validation, and backup-based fallback planning rather than assuming an in-place downgrade is supported. Final lab: document a Northwind operations runbook with tuning evidence, replica checks, recovery objectives, and failover and upgrade checklists.

Training scope: The instructor supplies MySQL-compatible Northwind scripts, a synthetic workload, and isolated source, replica, and recovery environments. Advanced topics are guided introductions, not a substitute for production experience. Replication is not a backup, and a replica alone does not provide automatic failover. MariaDB-specific administration, production migrations, and automated high-availability orchestration are outside this course.

Format and audience

For: New database administrators, developers supporting MySQL applications, system administrators, and technical teams responsible for database reliability and performance.

Prerequisites: Some familiarity with SQL, including SELECT, filtering, joins, and relational tables. The ITTrainingNow course SQL For Beginners - Essential Query Skills, or equivalent experience, provides the recommended preparation. Comfort working with files and basic command-line commands is helpful; no prior MySQL administration experience is required.

Lab setup: A computer with a modern browser, a terminal or SSH client, and instructor-approved MySQL client tools including the mysql client, MySQL Workbench, and MySQL Shell. The instructor provides isolated Linux-based MySQL training environments on a supported LTS release, administrative lab access, and setup instructions. Windows and macOS participants can connect remotely; local virtual-machine options and network requirements are confirmed before class.

How it works: Three days of instructor-led explanations, demonstrations, and hands-on Northwind administration labs. Contact us to discuss dates and virtual or onsite delivery.

Explore the course

Request a quote for the three-day MySQL Administration course and discuss your team's training needs.

Request a Quote

Back to database training