MySQL 8.0 Database Administrator

Inquire now

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.

 

Inquire now

Best selling courses

Duration: 5 days – 35 hrs   Overview The “SOC Network and Threat Detection and Analysis” training course is designed to equip Security Operations Center (SOC) analysts and IT security professionals with the skills and knowledge required to detect, analyze, and respond to network threats effectively. This comprehensive course covers essential topics such as threat...

Duration 1 day – 7 hrs   Overview   This 1-day training builds upon basic warehouse operations knowledge and introduces key logistics concepts involved in the movement and coordination of goods—especially wet and dry food items—within and outside the warehouse. Participants will explore transport logistics, inbound and outbound coordination, documentation practices, and cold chain considerations,...

Duration 2 days – 14 hrs   Overview   This hands-on course provides an introduction to Splunk, a powerful platform for searching, monitoring, and analyzing machine-generated data. The training focuses on how developers and QA professionals can leverage Splunk to gain insights from logs and metrics, improve application observability, detect anomalies, and support test validation....

Duration 3 days – 21 hrs   Overview.   This course is designed for fresh graduates aspiring to build a career in Data Science. It introduces the fundamentals of data science, focusing on data analysis, visualization, and basic machine learning concepts using Python. The course provides hands-on practice with real-world datasets, equipping participants with the...

Duration 5 days – 35 hrs   Overview   The Microsoft Certified: Azure Administrator Associate (AZ-104) Training Course is designed for IT professionals who want to develop expertise in managing Microsoft Azure environments. This course provides hands-on training on deploying, managing, and monitoring Azure services, including virtual machines, networking, storage, identity, and security.   Objectives...

ARTIFICIAL INTELLIGENCE / MACHINE LEARNING / DEEP LEARNING

Capstone Workshop: Build Your Own GenAI Toolkit TECH AI

GenAI Toolkit Training is a practical, hands-on capstone workshop designed to help participants bring their generative AI knowledge together into a personalized toolkit of AI tools, prompts, workflows, and practical solutions. Rather than simply learning individual AI platforms, participants will learn how to select and combine the right tools to address real-world technical and professional...

In this course, you will gain practical foundation level training that enables immediate and effective participation in big data and other analytics projects. Learn ways of storing data that allow for efficient processing and analysis, and gain the skills you need to store, manage, process, and analyze massive amounts of unstructured data to create an appropriate data lake.

Advanced Laravel Engineering Scalable Architecture, Performance, Testing, Queues & DevOps equips developers with the skills to design scalable Laravel applications, optimize performance, implement automated testing, manage queues, and integrate DevOps practices for reliable, production-ready deployments.   Duration 5 Days – 35 hrs   Overview This advanced-level bootcamp is designed for teams building large-scale or mission-critical...

// BG EARTH WHEN NOT PLAYING

We use cookies on our website to personalize your experience by storing your preferences and recognizing repeat visits. By clicking “Accept”, you agree to the use of all cookies. You can also select “Cookie Settings” to adjust your preferences and provide more specific consent. Cookie Policy