Advanced MySQL Performance and Security Course Africa

Course outline and learning expectations

This is a tutor-led course. The outline shows what your tutor will cover; teaching materials and examinations are provided directly to enrolled students.

What you will learn

• Explain key InnoDB architecture and configuration choices
• Analyse execution plans with EXPLAIN and EXPLAIN ANALYZE
• Design indexes for complex and high-volume workloads
• Diagnose locks, deadlocks and transaction contention
• Use Performance Schema and sys schema for monitoring
• Harden accounts, privileges and encrypted connections
• Create tested backup and point-in-time recovery procedures
• Configure and monitor MySQL source-replica replication

Modules

1. 1

   MySQL architecture and InnoDB internals

   Understand how server and storage-engine behaviour affects reliability and speed

   Server architectureBuffer poolRedo and undo logsMVCCCheckpointingConfiguration variables
2. 2

   Query execution and optimizer analysis

   Read execution plans and identify expensive operations

   EXPLAIN formatsEXPLAIN ANALYZEJoin algorithmsOptimizer statisticsHistogramsQuery rewrites
3. 3

   Advanced indexing and data layout

   Design indexes and table structures for demanding workloads

   Composite indexesCovering indexesSelectivityInvisible indexesFull-text indexesPartitioning decisions
4. 4

   Transactions, locking and deadlocks

   Diagnose concurrency problems in payment, booking and inventory workflows

   Isolation levelsRow and gap locksMetadata locksDeadlock analysisTransaction design
5. 5

   Performance monitoring and tuning

   Use built-in instrumentation to find and resolve database bottlenecks

   Performance Schemasys schemaSlow query logResource analysisWorkload baselinesCapacity indicators
6. 6

   Security hardening and access control

   Reduce database exposure through layered security controls

   Accounts and rolesLeast privilegePassword policiesTLS connectionsNetwork controlsSecure configuration
7. 7

   Backup, restore and point-in-time recovery

   Build and test recovery procedures for operational continuity

   Logical backupsPhysical backup conceptsBinary logsRestore validationPoint-in-time recoveryRecovery runbooks
8. 8

   Replication and resilient database capstone

   Deploy, monitor and troubleshoot a replicated MySQL environment

   Binary log replicationGTIDsReplica provisioningReplication lagFailover planningRegional production scenario