MySQL DBA course

MySQL Database Administration (DBA)

Course Description

This course is designed to provide a comprehensive understanding of MySQL database administration. It covers fundamental concepts, advanced topics, and practical skills required to manage, optimize, and maintain MySQL databases in production environments. The course is suitable for beginners as well as experienced professionals aiming to strengthen their MySQL DBA expertise.

Course Objectives

• Understand MySQL architecture and internal components
• Install and configure MySQL on Linux systems
• Manage databases, users, and security
• Perform backup and recovery (logical & physical)
• Implement replication and high availability
• Optimize database performance and troubleshoot issues
• Configure monitoring and automation tools

Who Should Attend

• Database Administrators (DBAs)
• Developers working with MySQL
• System administrators managing databases
• IT professionals interested in database management

Prerequisites

• Basic knowledge of databases
• Familiarity with SQL
• Basic Linux command-line knowledge

 Course Content (23 Modules)

Module 1: MySQL Architecture

• MySQL architecture overview
• Client-server model
• Thread architecture
• Memory components
• Query processing flow

Module 2: MySQL Installation

• Installation on AlmaLinux / Linux
• Directory structure
• Service management
• Upgrade methods and tools

Module 3: MySQL Filesystem & Directory Layout:

• MySQL base directories in Linux

• /var/lib/mysql/ (data directory)
• /etc/my.cnf (configuration file)
• /etc/mysql/conf.d/ (additional configs)
• /var/log/mysql/ (log files)
• /usr/sbin/mysqld (server binary)
• /usr/share/mysql/ (support files)
• /run/mysqld/ (PID & socket files)

• Data directory structure (datadir)

• Stores databases, system tables, logs, metadata

• InnoDB system files

• ibdata1 (system tablespace, metadata, doublewrite buffer)
• ib_logfile0, ib_logfile1 (redo logs for crash recovery)
• undo_001, undo_002 (undo tablespaces for MVCC & rollback)

• Global metadata files

• auto.cnf (server UUID for replication)
• SSL files (server-cert.pem, server-key.pem)

• Database-level storage

• Each database stored as a directory
• db.opt (charset & collation settings)
• .ibd (InnoDB table data & indexes)
• .frm (table structure in MySQL ≤ 5.7)

• MyISAM storage files

• .MYD (data file)
• .MYI (index file)

• Temporary files

• /tmp/#sql_* (temporary query operations)
• ibtmp1 (InnoDB temporary tablespace)

• Log files

• Error log (startup, shutdown, crashes)
• General log (all queries)
• Slow query log (long-running queries)
• Binary logs (mysql-bin.*) for replication & PITR
• Relay logs (relay-log.*) for replicas

• Runtime files

• mysqld.pid (process ID)
• mysqld.sock (Unix socket for local connections)

• Plugin directory

• /usr/lib64/mysql/plugin/ (authentication, storage, security plugins)

• Support and script files

• /usr/share/mysql/ (timezone, help tables, scripts, charsets)

• mysql system database

• Stores users, privileges, metadata, and replication info
• Key tables: user, db, tables_priv, columns_priv, procs_priv
• InnoDB stats tables and replication tables

Module 4: Storage Engines

• InnoDB
• MyISAM
• MEMORY
• CSV
• ARCHIVE
• FEDERATED
• InnoDB vs MyISAM
• Innodb storage engine architecture
• Use cases and limitations

Module 5: InnoDB Architecture

• Buffer Pool
• Redo Logs
• Undo Logs
• Tablespaces
• Doublewrite buffer

Module 6: Transactions & Isolation

• ACID properties
• Isolation levels
• Transaction handling
• Consistency models

Module 7: InnoDB Locking

• Row-level locking
• Gap locks
• Next-key locks
• Deadlock detection and resolution

Module 8: User Management & Security

• User creation and management
• Authentication plugins
• Privileges and roles
• Security best practices

Module 9 : Database, Tablespace and Table management

•  Database creation, selection, and management (CREATE, DROP, USE)
• InnoDB tablespace architecture (system tablespace, file-per-table, general tablespaces)
• Table creation with constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY)
• Table modifications and online DDL (ALTER TABLE, INPLACE/INSTANT operations)

Module 10: Logical Backup & Restore

• mysqldump usage
• mysqlpump overview
• Export and import strategies
• Backup consistency

Module 11: Physical Backup

• Hot vs cold backup
• File system backup
• Introduction to XtraBackup

Module 12: Binlogs & PITR

• Binary log configuration
• Binlog formats
• Point-in-Time Recovery (PITR)
• Log maintenance

Module 13: Replication Basics

• Replication architecture
• Source and replica setup
• Binary log flow

Module 14: Advanced Replication

• Multi-source replication
• Chain replication
• Replication filters

Module 15: Replication Troubleshooting

• Replication lag
• Relay log issues
• Error handling
• Skip errors

Module 16: GTID Replication

• GTID concepts
• Advantages over traditional replication
• Setup and management

Module 17: Backup Tools & Parallel Dumping

• mydumper and myloader
• mysqlpump
• Performance comparison

Module 18: Performance Tuning

• Buffer pool tuning
• Redo log tuning
• Query optimization basics

Module 19: OS-Level Tuning

• CPU and memory tuning
• Disk I/O optimization
• File descriptor limits

Module 20: High Availability & Clustering

• Innodb Replica set
• InnoDB Cluster
• Group Replication
• Galera Cluster
• Failover concepts

Module21 : Table Partitioning in MySQL

• Introduction to table partitioning

• Concept and purpose of partitioning
• Benefits: performance, manageability, scalability

• Types of partitioning in MySQL

• RANGE partitioning
• LIST partitioning
• HASH partitioning

Module 22: Major and Minor upgrades

• Cross version upgrades
• Minor version upgrades

Module 23: Monitoring, Proxy & Automation

• Monitoring with PMM
• MySQL Orchestrator
• Troubleshooting and automation