We use cookies to offer you a better experience and to analyse how visitors use our site, you may consent to our cookies by continuing to use our site, or you may view more information and choose to reject cookies by clicking here. Thank you

Contact us online or call us on 0191 377 8377

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields

PDF
Course MySQL Database Administration
  • Public Scheduled Price£1495 +VAT
  • Course Duration5 Days
  • Course Options
    Private
    Private
    Flexible content, flexible date, flexible location, variable 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 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!

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 practical experience in setting up, configuring and maintaining a MySQL server, backing up and recovering data and optimising query performance. This MySQL Database Administration course also covers MySQL replication and its role in high availability and scalability. This course is also suitable for administrators of MySQL 5.6 and 5.7.
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 can be gained by attendance on the Introduction to MySQL course.

      This course is run on a Linux operating system, a basic knowledge of UNIX/Linux is recommended but is not essential.
Objectives
    • At the end of the MySQL Database Administration course, delegates will have the knowledge to use the features available to administer, monitor and support MySQL 5.6, 5.7 and 8 databases and servers.
Certification
The MySQL Database Administration course will teach the major topics required for the delegate to work towards achieving the following examination:

Oracle Certified Professional MySQL 5.7 Database Administrator Exam (1Z0-888)
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
    • Database Administrators, Database Developers and Technical Consultants who need to setup, administer, monitor and support MySQL databases and servers.
Skills Gained
    • Delegates will lean and acquire skills as follows:

      Using Client Programs for MySQL DBA work
      Using MySQL Workbench
      Obtaining metadata
      Configuring the MySQL Server
      Install and configure the MySQL Server and client programs
      Administering and tuning a server
      Upgrading a Running Server
      Setting up Character Set Support
      Configuring log files and using them to monitor database activity
      Interpreting error messages
      Locking and transactions
      Using different storage engines
      Table Maintenance
      Implementing a backup strategy
      Performing physical and logical backups of data
      Administering Users and Privileges
      Securing the Server from common security threats
      Identify and Optimise poorly tuned queries
      Tuning Tables and Indexes
      Using the Event Scheduler
      Creating Partitioned Tables
      Replication
      Configure simple and complex replication topologies
      Administer a replication topology
What's included
    • For courses held on our premises, complimentary tea, coffee and biscuits are available. Lunch is not provided, however, there is a wide variety of eateries within a short walk of the centre.
    • Comprehensive course materials and exercises
Course Content
    • DAY 1
      Session 1: THE SERVER, CLIENTS AND TOOLS
      Introduction
      The versions of MySQL and MariaDB
      An Overview of MySQL Architecture
      MySQL Server
      MySQL Tools
      MySQL Admin
      Exercises: Using Client Programs


      Session 2: MYSQL WORKBENCH
      Installation
      Connecting
      Screens
      Exercises: Using MySQL Workbench


      Session 3: OBTAINING METADATA
      What is Metadata?
      The mysqlshow Client Program
      The SHOW and DESCRIBE Commands
      The INFORMATION_SCHEMA
      MySQL 8 INFORMATION_SCHEMA Changes
      Show Command and Information_Schema Differences
      Exercises: Obtaining Information about MySQL


      Session 4: MYSQL SERVER INSTALLATION AND CONFIGURATION
      MySQL Distributions
      Installing on Windows
      Installing on Linux and UNIX
      Starting and Stopping on Windows
      Starting and Stopping on UNIX/Linux
      Status Files
      Upgrading the Server
      Time Zone Tables
      Exercises: Installing, stopping and starting


      Session 5: CONFIGURING THE SERVER
      MySQL Server Options and Variables
      MySQL Status Variables
      Configuring MySQL Enterprise Audit
      Exercises: Configuring the Server


      Session 6: SQL MODES, LOG FILES AND BINARY LOGGING
      MySQL Error Messages
      The SHOW Errors Statement
      The SHOW Warnings Statement
      SQL Modes
      Note Messages
      The PERROR Utility
      The General Log
      The Error Log
      The Slow Query Log
      The Binary Logs
      Exercises: Interpreting Errors and Configuring the Logs
    • DAY 2
      Session 7: MYSQL ARCHITECTURE
      Client/Server Overview
      Communication Protocols
      The SQL Parser and Storage Engine Tiers
      How MySQL Uses Disk Space
      How MYSQL Uses Memory
      Exercises: Examining the Architecture


      Session 8: TRANSACTIONS AND LOCKING
      Locking Concepts
      Explicit Table Locking
      Advisory Locking
      Exercises: Locking


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


      Session 10: OTHER STORAGE ENGINES
      Introduction
      The MYISAM Engine
      The Merge Engine
      Other Engines: Archive, Aria, Memory, Blackhole, CSV
      Cluster Engine Overview
      Overview of the ColumnStore, MyRocks and Spider Engines
      Exercises: Using Storage Engines


      Session 11: TABLE MAINTENANCE
      Table Maintenance Operations
      Check Table
      Repair Table
      Analyze Table
      Optimize Table
      MySQL Check
      MyISAM Table Maintenance and Repair Utilitiesli>
      Exercises: Maintaining Tables
    • DAY 3
      Session 12: BACKUP AND RECOVERY
      Designing a Backup and Recovery Strategy
      The Advantages and Disadvantages of Different Backup Methods
      Binary Backups of MYISAM Tables
      Binary Backups of Innodb Tables
      Recovery
      Import and Export Operations
      Exporting and Importing using SQL
      Exporting from the Command Line using mysqldump
      Importing from the Command Line using mysqlimport
      Additional Backup Tools
      Exercises: Backing Up and Recovery


      Session 13: USER MANAGEMENT
      Introduction
      User Accounts
      Creating Users
      Renaming Users
      Changing Passwords
      Dropping Users
      Granting Privileges
      The User Table
      Connection Validation
      Password Validation Plugins
      Exercises: Creating, Managing and Dropping Users


      Session 14: PRIVILEGES
      Introduction
      Types of Privileges
      Revoking Privileges
      Resource Limits
      MySQL 8 Resource Management
      MySQL 8 Role Management
      The MySQL Database
      The Show Grants Command
      Exercises: Granting and Revoking Privileges


      Session 15: SECURITY AND UPGRADES
      Signs of Poor Security
      Security Risks
      Users, Operating System, File System and Network Security
      Using SSL With MySQL
      Remote Connecting to the MySQL Server Using SSH
      Upgrade the Server
      MySQL 8 Upgrade Checker
      Exercises: Securing the Server
    • DAY 4
      Session 16: TUNING QUERIES
      Tuning Overview
      Identifying Candidates for Query Analysis
      Using Explain to Analyze Queries
      Meaning of Explain Output
      Explain Extended
      Exercises: Explaining and Tuning Queries


      Session 17: TUNING AND INDEXES
      Indexes for Performance
      Creating and Dropping Indexes
      Obtaining Index Metadata
      Indexing Principles
      Indexing and Joins
      Prefixed Indexes
      Composite Indexes
      Fulltext Indexes
      Invisible Indexes
      Index Hints
      Conditions that Prevent Index Usage
      InnoDB Cached Indexes Information
      MyISAM Index Caching
      Exercises: Creating and Using Indexes


      Session 18: TUNING AND TABLES
      General Table Optimizations
      Myisam Specific Optimizations
      Innodb Specific Optimizations
      Other Engine Specific Optimizations
      Exercises: Tuning Tables


      Session 19: TUNING THE SERVER
      Status Variables
      Server Variables
      Performance Schema Overview
      The Query Cache
      MySQL 8 Desupport of the Query Cache
      Exercises: Tuning the Server


      Session 20: THE EVENT SCHEDULER
      Event scheduler concepts
      Event scheduler configuration
      Creating, altering and dropping events
      Event scheduler monitoring
      Events and privileges
      Exercises: Using the event scheduler


      Session 21: PARTITIONED TABLES
      Partitioned tables concepts
      Obtaining Partitioned Table Metadata
      Types of Partitioning
      Subpartitioning
      Maintenance of partitioned tables
      MySQL 8 Partitioning Changes
      Exercises: Using partitioned tables
    • DAY 5
      Session 22: OVERVIEW OF CLUSTER STORAGE ENGINES
      Overview of Clustering and Performance
      NDBCluster Engine Overview
      The Galera Cluster
      The Percona XtraDB Cluster
      MySQL InnoDB Cluster
      The Federated Engine
      The FederatedX Engine
      Overview of Other High Availability Techniques
      NOSQL and MemCached Overview
      Features of Memcached with MySQL


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


      Session 24: CONFIGURE MASTER SLAVE REPLICATION
      Replication Overview
      When to Use Replication
      Testing Replication
      Excluding Databases or Tables from Replication
      Example: Setting Up a Master Slave Replication
      Replication Using GTIDs
      Controlled Switchover
      Exercises: Setting Up and Testing Replication and Switchover


      Session 25: ADMINISTER A REPLICATION TOPOLOGY
      Failover
      MySQL Replication and Failover Utilities
      Replication Files and Threads
      Monitoring Replication
      Troubleshooting Replication
      Exercises: Failover from Master to Slave
      Session 26: GROUP REPLICATION
      Overview of Group Replication
      Single Primary Mode
      Multi-primary Mode
      Configure Group Replication
      Monitor Group Replication

 

IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk

Printed: 23 Aug 19

 

 

 

Contact us

  • Name*:*
  • Organisation:
  • Telephone:
  • Email:*
  • Message*:*
  • Send me a copy of this email
  • Send me course updates from time to time via email

    Your subscription will naturally terminate after your chosen time limit. We will get in touch just before the end so that you can subscribe again if you would like to continue receiving information after that point.

* Required Fields