Course Code: bastadv
Duration: 16 hours
Course Outline:

Day 1: MySQL Fundamentals & Core Administration Tasks


1. Introduction to MySQL & RDBMS Concepts

  • Overview of MySQL architecture and storage engines
  • Understanding RDBMS vs. NoSQL
  • Installing and configuring MySQL
  • MySQL Workbench and CLI tools

2. Working with Databases and Tables

  • Creating databases and tables
  • Data types and constraints
  • Importing/exporting data
  • Backup and restore basics

Hands-on:

  • Install MySQL locally or access via cloud
  • Create a database and sample tables
  • Practice data import/export using .sql and .csv files


3. SQL Basics and Query Optimization

  • SELECT, INSERT, UPDATE, DELETE
  • Joins, subqueries, and aggregate functions
  • Using EXPLAIN for query plans
  • Basic indexing principles

4. User Management and Security

  • Creating and managing users
  • Granting and revoking privileges
  • Password policies and roles
  • Understanding the MySQL privilege system

Hands-on:

  • Run performance tests on queries
  • Set up users with different access levels
  • Query optimization exercises
     

Day 2: Advanced Administration and Performance Tuning


5. Advanced Backup and Recovery

  • Logical vs. physical backups
  • mysqldump, mysqlpump, mysqlhotcopy
  • Point-in-time recovery
  • Working with binary logs

6. Monitoring and Troubleshooting

  • Monitoring tools: SHOW PROCESSLIST, INFORMATION_SCHEMA, performance_schema
  • Slow query log
  • Troubleshooting common issues: connection, locking, long-running queries

Hands-on:

  • Perform different types of backups
  • Analyze slow queries and optimize
  • Simulate and recover from failure scenarios


7. Storage Engines and Table Optimization

  • InnoDB vs. MyISAM and others
  • Configuring storage engine settings
  • Table maintenance: ANALYZE, OPTIMIZE, REPAIR

8. Replication and High Availability (Intro)

  • Concepts of master-slave replication
  • Setting up basic replication
  • Overview of clustering and failover options (e.g., MySQL Group Replication, Galera Cluster)

Hands-on:

  • Compare InnoDB and MyISAM performance
  • Set up basic replication environment (if time/resources permit)

Wrap-Up & Final Q&A 

  • Review key topics
  • Best practices for database administration
  • Recommended tools and learning resources
  • Open Q&A