MySQL Database Administration

MySQL Database Administration 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 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.

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 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.

Course 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.

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

• • 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

Included with this course

• • Comprehensive course materials and exercises

MySQL Database Administration 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