About This Course
MySQL remains the world's most widely deployed open-source relational database management system, powering everything from high-traffic web applications and e-commerce platforms to enterprise reporting systems and mobile back-ends. For developers, a deep command of MySQL is not optional — it directly determines the performance, reliability, and scalability of the applications they build. Yet many developers who write SQL daily have significant gaps in areas such as query optimisation, indexing strategy, transaction management, and secure database design.
TrainingComplex's MySQL for Developers Training is a rigorous, ten-day programme designed to close those gaps and elevate participants from competent SQL writers to confident MySQL professionals. The curriculum is carefully sequenced to build knowledge progressively: early modules establish a solid architectural and data-modelling foundation, mid-programme modules focus on advanced querying, indexing, and stored logic, and later modules tackle performance tuning, replication, security hardening, and integration with application frameworks.
Every concept is reinforced through hands-on laboratory exercises using live MySQL instances. Participants will design and normalise schemas, write complex multi-table queries, analyse execution plans, build stored procedures and triggers, configure user privileges, and connect MySQL to application code using industry-standard connectors. By the end of the programme, delegates will be equipped to make sound architectural decisions, write highly efficient SQL, and maintain secure and scalable MySQL-backed systems in professional development environments.
Who Should Attend
- Software developers and back-end engineers who work with or plan to work with MySQL-powered applications
- Database administrators transitioning into a developer-focused or full-stack role
- Full-stack developers seeking to deepen their database design and optimisation skills
- Data engineers responsible for building and maintaining MySQL-based data pipelines
- Technical leads and solution architects who oversee database design decisions
- QA and DevOps engineers who need to understand MySQL behaviour for testing and deployment pipelines
- IT graduates and junior developers looking to build professional-grade MySQL competence
Learning Objectives
- Explain the MySQL architecture, storage engines, and the role of the query optimiser
- Design normalised, efficient relational schemas following best-practice data-modelling principles
- Write complex SQL queries using joins, subqueries, window functions, and common table expressions
- Apply effective indexing strategies and interpret EXPLAIN output to diagnose and resolve performance issues
- Implement stored procedures, functions, triggers, and events to encapsulate business logic within the database
- Manage transactions and apply appropriate isolation levels to ensure data consistency in concurrent environments
- Configure MySQL user accounts, roles, and privilege structures following the principle of least privilege
- Secure MySQL installations against common vulnerabilities including SQL injection, unauthorised access, and data exposure
- Integrate MySQL with application code using connectors for Python, Node.js, and Java
- Implement and manage MySQL replication and understand high-availability patterns
- Use MySQL backup, recovery, and monitoring tools to maintain operational database health
- Apply partitioning and query caching techniques to support large-scale, high-throughput workloads
Course Modules
Module 1: MySQL Architecture and Environment Setup
- MySQL server architecture: client/server model, connection handling, and the query processing pipeline
- Storage engines compared: InnoDB vs MyISAM vs Memory — choosing the right engine for the workload
- Installing and configuring MySQL 8.x on Linux and Windows; using MySQL Workbench and the CLI
- Key configuration parameters in my.cnf/my.ini and their impact on performance and behaviour
- MySQL system variables: understanding global vs session scope, and using SET PERSIST to make runtime changes durable across restarts
- The InnoDB buffer pool, redo log, and undo log: how each component contributes to crash recovery and overall throughput
Module 2: Relational Data Modelling and Schema Design
- Entity-relationship modelling: identifying entities, attributes, and relationships for a real business domain
- Normalisation from 1NF through 3NF and BCNF with practical worked examples
- Choosing appropriate data types: integer families, VARCHAR vs TEXT, DATE/DATETIME, JSON, and spatial types
- Implementing primary keys, foreign keys, unique constraints, and check constraints to enforce data integrity
- When and how to deliberately denormalise: summary tables, redundant columns, and the read-performance trade-offs that justify each decision
- Designing for changeability: using surrogate keys, avoiding business-data primary keys, and structuring schemas to accommodate evolving requirements without cascading rewrites
Module 3: Core SQL for Developers
- Advanced SELECT: aliases, expressions, CASE statements, and conditional aggregation with GROUP BY and HAVING
- Multi-table queries: INNER, LEFT, RIGHT, and CROSS joins plus self-joins for hierarchical data
- Subqueries: correlated and non-correlated subqueries in SELECT, FROM, and WHERE clauses
- Set operations: UNION, UNION ALL, INTERSECT, and EXCEPT for combining result sets
- Data modification statements: INSERT INTO ... SELECT, multi-row INSERT, UPDATE with JOIN, and DELETE with JOIN for efficient bulk operations
- Handling NULLs correctly: IS NULL, IS NOT NULL, COALESCE, NULLIF, and the three-valued logic implications for WHERE and JOIN conditions
Module 4: Advanced SQL: CTEs and Window Functions
- Common Table Expressions (CTEs): syntax, use cases, and how CTEs improve query readability and structure
- Recursive CTEs for traversing hierarchical and graph-like data structures
- Window functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE, and their PARTITION BY and ORDER BY clauses
- Aggregate window functions: running totals, moving averages, and lead/lag analysis with LEAD and LAG
- Frame specification with ROWS and RANGE clauses: controlling which rows contribute to each window calculation for precise analytical results
- Combining CTEs and window functions in multi-step analytical queries: structuring complex reporting logic for both clarity and optimizer efficiency
Module 5: Indexing Strategy and Query Optimisation
- How InnoDB B-tree indexes work: clustered vs secondary indexes and the concept of index selectivity
- Using EXPLAIN and EXPLAIN ANALYZE to read execution plans and identify full table scans and inefficient joins
- Composite indexes, covering indexes, and prefix indexes — when to use each and pitfalls to avoid
- Common query anti-patterns: implicit type conversion, functions on indexed columns, and SELECT * misuse
- Index maintenance overhead: how INSERT, UPDATE, and DELETE operations affect index fragmentation, and when to use ANALYZE TABLE and OPTIMIZE TABLE
- Using the sys schema views such as schema_unused_indexes and schema_redundant_indexes to audit and rationalise index sets in production schemas
Module 6: Stored Procedures, Functions, and Control Flow
- Creating and calling stored procedures: parameters (IN, OUT, INOUT), local variables, and error handling with DECLARE ... HANDLER
- User-defined functions (UDFs): deterministic vs non-deterministic functions and appropriate use cases
- Control flow programming: IF/ELSEIF/ELSE, CASE, WHILE, REPEAT, and LOOP constructs
- Cursors for row-by-row processing and the trade-offs compared to set-based SQL operations
- Debugging stored routines: using temporary logging tables, simulated debug output, and the MySQL Workbench routine debugger to trace execution
- Managing stored routine security: DEFINER vs INVOKER rights, privilege implications of SQL SECURITY clauses, and minimising attack surface in multi-tenant schemas
Module 7: Triggers and Scheduled Events
- Trigger fundamentals: BEFORE and AFTER triggers on INSERT, UPDATE, and DELETE operations
- Using OLD and NEW row references to audit changes, enforce complex business rules, and maintain derived data
- MySQL Event Scheduler: creating, enabling, and managing recurring and one-time scheduled events
- Potential pitfalls of triggers — performance overhead, debugging challenges, and cascading trigger risks
- Implementing a robust audit trail with triggers: writing change history to a dedicated audit table including user, timestamp, operation type, and before/after values
- Testing and documenting trigger logic: strategies for isolating trigger behaviour in test environments and maintaining accurate schema documentation as trigger definitions evolve
Module 8: Transactions, Locking, and Concurrency Control
- ACID properties and how InnoDB implements atomicity, consistency, isolation, and durability
- Transaction management: START TRANSACTION, COMMIT, ROLLBACK, and SAVEPOINT
- Isolation levels (READ UNCOMMITTED through SERIALIZABLE) and the phenomena they prevent: dirty reads, non-repeatable reads, and phantom reads
- Row-level locking, deadlock detection, and strategies for minimising lock contention in concurrent applications
- Optimistic vs pessimistic concurrency control: when to use SELECT ... FOR UPDATE vs application-level versioning with a row-version or timestamp column
- Diagnosing live locking issues using INFORMATION_SCHEMA.INNODB_TRX, INNODB_LOCKS, and the Performance Schema data_locks table to identify blocking transactions
Module 9: MySQL Security and User Management
- MySQL privilege system: creating users, granting and revoking object-level and global privileges, and using roles in MySQL 8
- Securing the MySQL installation: removing anonymous accounts, disabling remote root login, and applying the validate_password component
- Defending against SQL injection: parameterised queries, prepared statements, and stored procedure boundaries
- Encryption at rest and in transit: enabling TLS/SSL connections and understanding InnoDB tablespace encryption
- Auditing database activity with the MySQL Enterprise Audit plugin or the open-source MariaDB Audit Plugin: configuring audit logs and filtering by user, host, or event type
- Implementing least-privilege application accounts: separating read-only, read-write, and administrative credentials and rotating passwords without application downtime using MySQL roles
Module 10: Partitioning and Performance for Large Datasets
- Table partitioning types: RANGE, LIST, HASH, and KEY partitioning — use cases and partition pruning behaviour
- Query caching strategies and the role of the Performance Schema and sys schema in identifying bottlenecks
- MySQL optimizer hints and the use of FORCE INDEX, STRAIGHT_JOIN, and optimizer_switch flags
- Analysing slow queries with the slow query log and pt-query-digest from Percona Toolkit
- Partition management operations: adding, dropping, reorganising, and exchanging partitions with ALTER TABLE, and the impact on maintenance windows for large tables
- InnoDB buffer pool tuning for large datasets: sizing the buffer pool, configuring multiple buffer pool instances, and using the buffer pool load/dump feature to warm the cache after restart
Module 11: MySQL Replication and High Availability
- Binary log-based replication: how MySQL replication works, replication formats (ROW, STATEMENT, MIXED), and setting up a primary/replica topology
- GTID-based replication: benefits over traditional log-file/position replication and failover simplification
- MySQL InnoDB Cluster and Group Replication: architecture overview and use cases for automatic failover
- Read/write splitting patterns in application code and connection pooling with ProxySQL
- Monitoring replication health: tracking seconds_behind_source, replication lag alerting, and using Performance Schema replication tables to diagnose apply-thread bottlenecks
- Handling schema changes in a live replication topology: online DDL with ALTER TABLE ALGORITHM=INPLACE/INSTANT, and using pt-online-schema-change to avoid replication lag on large tables
Module 12: Backup, Recovery, and Monitoring
- Logical backups with mysqldump and mysqlpump: options, scheduling, and consistent backup of InnoDB tables
- Physical backups with MySQL Enterprise Backup and Percona XtraBackup: hot backup procedures and point-in-time recovery
- MySQL Performance Schema: key tables for monitoring queries, waits, memory, and connection activity
- Setting up alerting and dashboarding with Prometheus and Grafana for MySQL operational health
- Backup validation and restoration testing: verifying backup integrity with checksums, rehearsing full and point-in-time restores in a staging environment, and documenting recovery time objectives
- Binary log management for point-in-time recovery: configuring binlog retention with binlog_expire_logs_seconds, replaying binary logs with mysqlbinlog, and identifying the precise stop position for targeted recovery
Module 13: Application Integration and Capstone Project
- Connecting MySQL to Python applications using mysql-connector-python and SQLAlchemy ORM — connection pooling and best practices
- Integrating MySQL with Node.js using the mysql2 library: async/await patterns, prepared statements, and error handling
- Database migration management: using tools such as Flyway or Liquibase to version-control schema changes
- Capstone project: participants design, implement, optimise, and present a complete MySQL database solution for a defined business scenario, incorporating schema design, stored logic, indexing, security, and application connectivity
- Handling MySQL errors gracefully in application code: mapping server error codes to application exceptions, implementing retry logic for transient deadlock and lock-wait-timeout errors, and surfacing meaningful messages to end users
- Connection pool sizing and management in production: understanding the relationship between max_connections, thread_stack, and application-side pool settings to prevent connection exhaustion under high concurrency
Requirements & Prerequisites
Participants should have a working knowledge of at least one programming language (Python, Java, Node.js, or similar) and some prior exposure to basic SQL concepts such as SELECT, INSERT, UPDATE, and DELETE. Familiarity with the concepts of tables, rows, and relational data is assumed. Prior formal training in database administration is not required, but basic comfort navigating a command-line interface will be beneficial.
Training Methodology
- Hands-on laboratory sessions using live MySQL 8.x instances where participants execute, test, and refine real queries and database objects throughout every module
- Schema design workshops in which participants model and normalise a running case-study database — a realistic e-commerce and order-management system — that evolves across all ten days
- EXPLAIN and performance profiling exercises where learners diagnose slow queries, identify missing indexes, and compare execution plans before and after optimisation
- Code integration labs connecting MySQL to Python (mysql-connector-python / SQLAlchemy) and Node.js (mysql2) applications, reinforcing how database decisions affect application-layer code
- Peer code-review sessions where participants critique each other's stored procedures, trigger logic, and indexing strategies against defined criteria
- Security audit walkthroughs simulating common attack vectors (SQL injection, privilege escalation) and applying hardening countermeasures
- Group discussion and Q&A sessions at the close of each day to consolidate learning and address environment-specific challenges raised by participants
- A capstone project in the final module requiring each participant to design, build, optimise, and document a MySQL-backed database solution for a given business scenario
Organisational Benefits
- Faster, more efficient applications resulting from better-optimised queries and smarter indexing decisions made by in-house developers
- Reduced database-related incidents and downtime through proper transaction management and security hardening
- Lower reliance on external database consultants as developers gain the skills to resolve performance and design issues independently
- Improved code quality as developers learn to integrate MySQL correctly and securely within application architectures
- Stronger data integrity and compliance posture through proper use of constraints, privileges, and access controls
- A common language and best-practice standard across development and DBA teams, reducing miscommunication and rework
- Accelerated onboarding for new developers joining MySQL-backed projects
- Better architectural decisions at the design stage, reducing costly refactoring later in the development lifecycle
Where Is MySQL for Developers Training Offered?
This course runs in-person across: Johannesburg, Mombasa, Nakuru, Lagos, Arusha, Accra, Kisumu, Riyadh, Pretoria, Dar es Salaam, Addis Ababa, Kampala, Naivasha, Dubai, Zanzibar, Abuja, Nairobi, Cape Town, Kigali, plus Live Virtual (online) cohorts. Browse live dates for your city below.
Upcoming Schedules
| Location | Format | Dates | Price |
|---|
| Johannesburg, South Africa |
In-Person |
5 Oct 2026 – 16 Oct 2026 |
USD 7,000 |
| Mombasa, Kenya |
In-Person |
5 Oct 2026 – 16 Oct 2026 |
KES 230,000 |
| Nakuru, Kenya |
In-Person |
12 Oct 2026 – 23 Oct 2026 |
KES 230,000 |
| Lagos, Nigeria |
In-Person |
12 Oct 2026 – 23 Oct 2026 |
USD 5,000 |
| Arusha, Tanzania |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 4,200 |
| Accra, Ghana |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 7,600 |
| Kisumu, Kenya |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
KES 240,000 |
| Riyadh, Saudi Arabia |
In-Person |
19 Oct 2026 – 30 Oct 2026 |
USD 9,000 |
| Pretoria, South Africa |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 6,600 |
| Live Virtual |
Online |
19 Oct 2026 – 28 Oct 2026 |
KES 160,000 |
| Dar es Salaam, Tanzania |
In-Person |
19 Oct 2026 – 30 Oct 2026 |
USD 4,600 |
| Addis Ababa, Ethiopia |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 4,800 |
| Kampala, Uganda |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
USD 3,800 |
| Naivasha, Kenya |
In-Person |
19 Oct 2026 – 28 Oct 2026 |
KES 240,000 |
| Live Virtual |
Online |
24 Oct 2026 – 13 Dec 2026 |
KES 160,000 |
+235 more dates — view live schedule →
Frequently Asked Questions
Do I need to be an experienced DBA to attend this course?
No, this course is designed for developers rather than DBAs. You need a working knowledge of at least one programming language and basic familiarity with SQL concepts such as SELECT, INSERT, UPDATE, and DELETE. Prior DBA experience is not required.
Which version of MySQL does the course cover?
The course is built on MySQL 8.x, the current long-term support release. All lab exercises, syntax examples, and feature discussions reflect MySQL 8 capabilities, including roles, window functions, CTEs, and JSON support.
Will I receive a certificate upon completion?
Yes. Participants who attend the full ten-day programme and complete the capstone project will receive a TrainingComplex Certificate of Completion in MySQL for Developers, which can be shared on professional profiles and submitted as evidence of CPD.
What software do I need to bring or have installed?
For in-person sessions, TrainingComplex provides configured laboratory workstations with MySQL 8, MySQL Workbench, and all required connectors pre-installed. For the Live Virtual cohort, participants will receive a pre-course environment setup guide to install the necessary tools on their own machines before Day 1.
Is this course suitable for full-stack developers who use an ORM and rarely write raw SQL?
Absolutely — in fact, this course is particularly valuable for ORM-heavy developers. Understanding what MySQL actually executes behind ORM-generated queries is essential for diagnosing performance problems and making sound schema decisions. The course includes dedicated coverage of how ORMs interact with MySQL at the query level.
