This course provides the knowledge and skills needed to install, configure, secure, monitor, and maintain MySQL 8.0 databases. It covers server architecture, storage management, user administration, backup and recovery, performance tuning, and replication.

The course prepares participants for routine database administration and common operational problems. High availability and upgrade planning are included at an introductory level.

 

Duration 5 Day – 35 hrs.

 

Objectives

• Explain MySQL 8.0 architecture and core server components.
• Install MySQL Server and configure essential server settings.
• Manage databases, tables, storage, and database metadata.
• Administer user accounts, roles, privileges, and secure connections.
• Explain InnoDB transactions, locking, and concurrency behavior.
• Perform database backups, restores, and point-in-time recovery.
• Monitor server activity and investigate common database problems.
• Identify query performance issues and apply appropriate tuning techniques.
• Configure and monitor source–replica replication.
• Describe high availability options and plan routine maintenance and upgrades.

 

Target Audience 

• Database administrators and aspiring MySQL administrators.
• System administrators responsible for database servers.
• Database support engineers and technical support specialists.
• Application developers with database administration responsibilities.
• IT operations engineers managing MySQL environments.
• Technical professionals transitioning from other relational database platforms.

 

Prerequisites 

• A basic understanding of relational databases, tables, keys, and relationships.
• Working knowledge of SQL, including queries, joins, and data modification statements.
• Familiarity with Windows or Linux operating systems.
• Basic command-line skills and an understanding of files, permissions, and services.
• Basic networking knowledge, including IP addresses, ports, and client–server connections.
• Prior MySQL administration experience is not required.

Course Outline 

Day 1: MySQL Architecture, Installation, and Configuration

Module 1: MySQL 8.0 Architecture and Administration Fundamentals

• Database administrator responsibilities.
• MySQL client–server architecture.
• Server components and connection handling.
• Storage engine overview and the role of InnoDB.
• Data dictionary, system databases, and server directories.
• MySQL administration tools and utilities.

 Module 2: Installing and Managing MySQL Server

• Installation requirements and deployment considerations.
• Installing MySQL 8.0 on Linux and Windows.
• Initializing the data directory.
• Starting, stopping, and checking server services.
• Initial account setup and installation security.
• Connecting through command-line clients and MySQL Workbench.

 Module 3: Server Configuration and Database Management

• Configuration files and option processing.
• Global, session, and persisted system variables.
• Configuring connections, networking, and SQL modes.
• Character sets and collations.
• Creating and managing databases and tables.
• Using metadata to inspect database objects.

  

Day 2: Storage, Transactions, and Security

Module 4: InnoDB Storage Administration

• InnoDB architecture and storage organization.
• Tablespaces and data files.
• Buffer pool, redo logs, and undo logs.
• File-per-table configuration.
• Managing table growth and disk usage.
• Table maintenance and online DDL considerations.

 Module 5: Transactions, Locking, and Concurrency

• ACID properties and transaction management.
• Autocommit, commit, rollback, and savepoints.
• Transaction isolation levels.
• Multiversion concurrency control.
• Row locks, metadata locks, and lock waits.
• Detecting and resolving deadlocks and long-running transactions.

 Module 6: User Administration and Database Security

• Creating, modifying, locking, and removing accounts.
• Authentication and password management.
• Granting and revoking privileges.
• Creating and assigning roles.
• Applying least-privilege access.
• Configuring encrypted connections with TLS.
• Restricting network access and protecting database files.

  

Day 3: Backup, Recovery, and Data Movement

Module 7: Backup Planning and Methods

• Recovery point and recovery time requirements.
• Logical and physical backup concepts.
• Full and incremental backup concepts and tool requirements.
• Consistent backups for transactional tables.
• Backup storage, retention, and protection.
• Selecting backup tools and understanding edition requirements.

 Module 8: Database Restore and Point-in-Time Recovery

• Creating and restoring logical backups with mysqldump.
• Backing up database objects and stored programs.
• Binary log configuration and retention.
• Inspecting binary logs with mysqlbinlog.
• Restoring a backup and replaying binary logs.
• Recovering from accidental data changes.
• Verifying restored data and documenting recovery procedures.

 Module 9: Import, Export, and Data Transfer

• Exporting and importing SQL and delimited data.
• Using LOAD DATA and related utilities.
• MySQL Shell dump and load utilities.
• Handling character sets and data compatibility.
• Managing large data transfers.
• Checking data completeness after migration.

  

Day 4: Monitoring, Performance, and Troubleshooting

Module 10: Server Monitoring and Diagnostics

• Server status variables and connection activity.
• Error logs, slow query logs, and general query logs.
• Performance Schema and the sys schema.
• Monitoring CPU, memory, disk, and connections.
• Establishing performance baselines.
• Identifying blocked sessions and resource bottlenecks.

 Module 11: Query and Index Performance

• Identifying expensive SQL statements.
• Reading execution plans with EXPLAIN.
• Using EXPLAIN ANALYZE where supported by the installed 8.0 release.
• Index selection and composite index behavior.
• Optimizer statistics and table analysis.
• Identifying redundant or unused indexes.
• Common query patterns that reduce performance.

 Module 12: Server Tuning and Operational Troubleshooting

• Buffer pool sizing and memory considerations.
• Connection limits and session resource usage.
• Redo logging, flushing, and durability trade-offs.
• Diagnosing connection and authentication failures.
• Investigating startup errors and storage problems.
• Managing slow queries, lock waits, and excessive connections.
• Validating configuration changes against measured results.

  

Day 5: Replication, Availability, and Lifecycle Management

Module 13: MySQL Replication Configuration and Administration

• Source–replica architecture and replication components.
• Binary logs, relay logs, and replication formats.
• Global transaction identifiers.
• Preparing and configuring a replica.
• Monitoring replication status and lag.
• Troubleshooting common replication failures.
• Read-only settings and protecting replica consistency.

 Module 14: High Availability Fundamentals

• Availability requirements and failure scenarios.
• Asynchronous and semisynchronous replication concepts.
• Introduction to Group Replication.
• MySQL InnoDB Cluster and MySQL Router overview.
• Failover and switchover considerations.
• Distinguishing replication from backup protection.

 Module 15: Maintenance, Upgrade Planning, and Operational Readiness

• Routine administration and maintenance schedules.
• Capacity planning and storage forecasting.
• Log retention and disk-space management.
• Upgrade compatibility and readiness checks.
• Planning maintenance windows and recovery contingencies.
• Post-upgrade verification.
• Maintaining configuration records and operational runbooks.