Training Course

MySQL Database Administration

PDF
5 Days

Course Overview

This MySQL Database Administration course is designed for MySQL Database Administrators who have a basic understanding of a MySQL database and a good working knowledge of SQL commands.

The course provides an understanding of MySQL Architecture and provides practical experience in the routine maintenance, administration, and backup and recovery of data in a MySQL server. Experience will be gained in the installation, configuration, and maintenance of a MySQL server This MySQL Database Administration course also covers MySQL replication and its role in high availability and scalability.

Similarly, the course is targeted to closely follow the official Oracle MySQL curriculum for certification.

Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.

Versions supported 8.

Course Prerequisites

    • A working knowledge of MySQL is required. Delegates should have knowledge of relational database concepts; have knowledge of MySQL data types; be able run Queries using SQL; join tables and run basic DDL and DML statements. This knowledge can be gained by attendance on the Introduction to MySQL course.

      This course is run on a Linux operating system platform, therefore, a good working knowledge of Linux/UNIX is strongly recommended. This knowledge can be gained by attendance on the Linux Introduction course.

      Pre-Requisite Courses
      Introduction to MySQL
      Linux Introduction

Course Certification

The MySQL Database Administration course is an excellent choice for delegates preparing to take the following exam:
Oracle Certified Professional MySQL 8.0 Database Administrator: 1Z0-908

Attending these courses and studying the associated reference materials can form part of the preparation to achieve certification. Experience, practice, self-study and aptitude are also key requirements for exam success.

Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.

Exams are not included as part of the course.

Target Audience for this Course

    • Database Administrators, Database Developers and Technical Consultants who need to setup, administer, monitor and support MySQL databases and servers.

Skills Gained from this Training


    • The delegate will learn and acquire skills as follows:
      Using Client Programs for MySQL DBA activities
      Using MySQL Workbench to administer MySQL
      Obtain metadata
      Install the MySQL Server and client programs
      Configure the MySQL Server
      Administer and tune a server
      Setting up Character Set Support
      Configure log files and use them to monitor database activity
      Interpret error messages
      Lock and transaction handling
      Use different storage engines
      Table Maintenance
      Implement a backup strategy
      Perform physical and logical backups of data
      Administer Users and Privileges
      Use the Event Scheduler
      Configure simple replication topologies
      Administer a replication topology

Included with this course

    • Comprehensive course materials and exercises

Course Content

  • Session 1: THE SERVER, CLIENTS AND TOOLS
    Introduction to MySQL
    The versions of MySQL
    MySQL Database Server Releases
    MySQL Database Versions
    The MySQL Server
    MySQL Client Connections
    MySQL Client Programs
    MySQL Client Libraries
    The mysql Command Line Tool
    The mysqladmin Program

    Session 2: MYSQL WORKBENCH
    Installation of MySQL Workbench
    Connecting to MySQL Workbench
    MySQL Workbench Screens

    Session 3: OBTAIN METADATA
    What is Metadata?
    The mysqlshow Client Program
    The SHOW and DESCRIBE Commands
    Obtaining data from the INFORMATION_SCHEMA
    MySQL 8 INFORMATION_SCHEMA Changes
    Generate Statements using the INFORMATION_SCHEMA

    Session 4: CONFIGURE THE SERVER
    Start and Stop the MySQL Server
    Status Files
    An Overview of MySQL Storage Engines
    MySQL Server Options and Variables
    MySQL Status Variables
    Configure the MySQL Enterprise Edition Auditing Plugin

    Session 5: SQL MODES, LOG FILES AND BINARY LOGGING
    SQL Modes
    The General Log
    The Error Log
    The Slow Query Log
    The Binary Logs

    Session 6: MYSQL ARCHITECTURE
    Communication Protocols used to connect a Client to the Server
    The SQL Parser and Storage Engine Tiers
    Installing and Uninstalling Plugins
    How MySQL Uses Disk Space
    How MYSQL Uses Memory

    Session 7: THE INNODB STORAGE ENGINE
    Features of the Innodb Engine
    The System Tablespace
    Transactions and Referential Integrity
    Physical Characteristics of Innodb Tables
    System Tablespace Configuration
    File per Table Tablespace
    Log File and Buffer Configuration
    Undo Logs and Undo Tablespaces
    General Tablespaces
    Buffer and Lof File Configuration
    Temporary Table Tablespaces
    Innodb Status
    InnoDB Shutdown Options

    Session 8: OTHER STORAGE ENGINES
    Check Engine Usage
    The MYISAM Engine
    The Merge Engine
    Other Engines: Archive, Memory, Blackhole, CSV, Spider, MyRocks
    Mixing Storage Engines

    Session 9: TABLE MAINTENANCE
    Table Maintenance Operations
    Check Table
    Repair Table
    Analyze Table
    Optimize Table
    MySQL Check
    Repair InnoDB Tables
    MyISAM Table Maintenance and Repair Utilities

    Session 10: BACKUP AND RECOVERY
    The Advantages and Disadvantages of Different Backup Methods
    Backup Tool Overview
    What Should be Backed up?
    Binary Backups of MYISAM Tables
    Binary Backups of Innodb Tables
    Performing Hot Backups
    Performing Recovery
    Import and Export Operations
    Export and Import using SQL
    Export from the Command Line using mysqldump
    Import from the Command Line using mysqlimport
    Additional Backup Tools

    Session 11: USER MANAGEMENT
    Introduction
    User Accounts
    Creating Users
    Renaming Users
    Change a User Password
    Dropping Users
    Granting Privileges
    The User Table
    Connection Validation
    Password Validation Plugins

    Session 12: PRIVILEGES
    Types of Privileges
    Granting and Revoking Privileges
    Setting Resource Limits
    Resource Management
    Role Management
    The MySQL Database
    The Show Grants Command

    Session 13: TRANSACTIONS AND LOCKING
    Locking Concepts
    Levels of Locking
    Implicit Table Locking with MyISAM and InnoDB
    Explicit Table Locking
    Advisory Locking
    Monitoring Locks
    Turning Autocommit on and off
    Using Transaction Blocks
    Possible Causes of Lock Contention
    Handling Concurrency Problems and Deadlocks

    Session 14: TUNING THE SERVER
    Status Configuration Variables
    Status Variables
    System Variables
    Per Client Variables
    Performance Schema Overview


    Session 15: THE EVENT SCHEDULER
    Event scheduler concepts
    Event scheduler configuration
    Creating, altering and dropping events
    Event scheduler monitoring
    Events and privileges

    Session 16: OVERVIEW OF HIGH AVAILABILITY
    High Availability Goals
    High Availability Concepts
    Design for High Availability
    Definition of Availability
    High Availability Terminology

    Session 17: CONFIGURE MASTER SLAVE REPLICATION
    Replication Overview
    When to Use Replication
    Disadvantages of Replication
    Replication Factors
    Replication Architecture
    Complex Replication Topologies
    Replicating from InnoDB to MyISAM Tables
    Testing Replication
    Monitoring Replication Threads
    Excluding Databases or Tables from Replication
    Example: Setting Up a Master Slave Replication
    Replication Using GTIDs
    Run Replication from a Remote Host
    Controlled Switchover

    Session 18: ADMINISTER A REPLICATION TOPOLOGY
    MySQL Replication and Failover Utilities
    Replication Files and Threads
    Configure a Lagging Slave
    Monitoring Replication
    Troubleshooting Replication
    Semi-synchronous Replication
    Configure MySQL Failover

    Session 19: MYSQL SERVER INSTALLATION, UPGRADE DEPLOYMENT SECURITY
    MySQL Distributions
    Installing MySQL on Linux and UNIX
    The Installation Directories
    Set a Password for the mysql root user
    Upgrade the MySQL Server
    Deployment Security

 

Private Training

Flexible dates, location and content. All prices exclude VAT.

  • Private Courses

    Flexible dates, location and content. All prices exclude VAT.
  • Call us on
    0191 377 8377
    to discuss your requirements.
    Mon-Fri 9am-5pm.
    Ask a Question
    Keep me up to date with offers and news
    By submitting this form, you consent to IT Training Solutions Ltd storing your information in order to respond to your request.
  • MySQL Database Administration Course Options
    Private
    Private
    Flexible course content, flexible training date(s), flexible location(s), variable course length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
    Public Scheduled
    Public Scheduled
    Fixed course content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!
  • Virtual Training

 

Course Reviews
Which part of the course did you find particularly valuable "Learning how to use the formula to manipulate data "

P.C. NHS Fleet Solutions, Crystal Reports Level 1

"I have picked up lots of shortcuts and things I didn't know before today which I will use going forward"

K.M. Kaefer, Excel Level 3

"[The trainer] unveiled a lot of well hid little sneak cheats on making life a lot easier whilst working in Word. Really enjoyed the training, I work mainly in Excel, so it was great to learn about Word, and I'm no longer afraid to work in it."

D.I. Houghton International, Word Essentials

Following this training what are you going to do differently "Use software more efficiently "

P.M. Teesside Gas and Liquids, Microsoft Project Essentials

Following this training what are you going to do differently "My general usage of InDesign will be much improved! I'm a much more confident and competent user of the platform now, and feel ready to explore other platforms within the Adobe Creative Cloud suite."

L.O. Cambrex, Adobe InDesign Level 1

Following this training what are you going to do differently "Learned how to do speedier shortcuts"

J.R. Sealy UK, Excel Level 2

"I found the whole course very interesting and informative. I will take a completely different approach to meetings in the future."

S. HMP Isle of Wight, Minute Taking

Are there any aspects of the course which you feel require improvement "No, I found it to be excellent."

J.F. London Borough of Merton, SharePoint and Teams Overview

 

Private Courses

Flexible dates, location and content. All prices exclude VAT.

 

Please contact us to discuss your MySQL Database Administration course, we have many solutions available to ensure you receive the best training possible.
 You can choose your learning path and delivery style.  Courses can be delivered at your office or at one of our training venues, it's your choice. Small course sizes. Course materials included. Flexible MySQL Database Administration course content. Expert trainers and tried & tested training methods. Many course materials available online using your personal training account.  You can master SQL, please contact us to book a place or request a quote for your MySQL Database Administration course.
MySQL Database Administration trainng courses in Durham, Newcastle, Washington England, Northumberland, Darlington, Gateshead, Middlebrough, Leeds and throughout the North East of England.
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:6/18/2025 3:22:20 PM