Course Code: sqldba
Duration: 28 hours
Prerequisites:

Substantial experience of a relational database management system such as Oracle or Microsoft SQL Server is required.

Note: We can ofer this as a 5 day course for delegates who do not meet the pre-requsites.

Overview:

This "MySQL for Database Administrators" course is designed to provide practical experience in:

  • Setting up and maintaining a MySQL database server including:
    • backing up 
    • recovery
    • configuration
    • tuning

Who should attend:

Anyone with experience of a relational database management system who need the skills to be able to administer, monitor and support MySQL databases and servers.

Course Outline:

DAY 1

Introduction

  • Administration and Course Materials
  • Course Structure and Agenda
  • Delegate and Trainer Introductions

Clients & Tools

  • Introduction
  • MySQL
  • MySQL Server and Server-Startup Programs
  • Understanding mysqld/mysqld_safe/mysql.server/mysqld_multi
  • MySQL Admin
  • MySQL Client Programs
  • Exercises: Using Client Programs

Obtaining Metadata

  • What is Metadata?
  • The mysqlshow Client Program
  • The SHOW and DESCRIBE Commands
  • The Information_Schema
  • Show Command and Information_Schema Differences
  • Exercises: Obtaining Information about MySQL

Configuring the Server

  • MySQL Server Options and Variables
  • MySQL Status Variables
  • Configuring MySQL Enterprise Audit
  • Exercises: Configuring the Server

MySQL Server Installation

  • 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

SQL Modes

  • 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

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

Tables, Data Types & Character Set Support

  • Table Properties
  • Creating Tables
  • Altering Tables
  • Dropping Tables
  • Emptying Tables
  • Obtaining Table Metadata
  • Column Attributes
  • Bit Data Type
  • Numeric Data Types
  • Character String Data Types
  • Binary String Data Types
  • Enum and Set Data Types
  • Temporal Data Types
  • Auto_Increment
  • Handling Missing or Invalid Data Values
  • Exercises: Creating and Maintaining Tables

Globalisation

  • Character Set Support
  • Setting the Error Message Language
  • Adding a Character Set
  • Adding a Collation to a Character Set
  • Character Set Configuration
  • MySQL Server Time Zone Support
  • MySQL Server Locale Support

DAY 2

Transactions & Locking

  • Locking Concepts
  • Explicit Table Locking
  • Advisory Locking
  • Exercises: Locking

Storage Engines

  • Introduction
  • The MYISAM Engine
  • The Merge Engine
  • Other Engines: Archive, Memory, Federated, Blackhole, CSV
  • Cluster Engine Overview
  • Overview of High Availability Techniques
  • Memcached and NoSQL Overview
  • Exercises: Using Storage Engines

The InnoDB Engine

  • Introduction
  • Features of Innodb
  • Transactions
  • Referential Integrity
  • Physical Characteristics of Innodb Tables
  • System Tablespace Configuration
  • Log File and Buffer Configuration
  • Innodb Status
  • Exercises Using the InnoDB Engine

Table Maintenance

  • Table Maintenance Operations
  • Check Table
  • Repair Table
  • Analyze Table
  • Optimize Table
  • MySQL Check
  • MYISAMCHK
  • Repairing Innodb Tables
  • Enabling MYISAM AutoRepair
  • Exercises: Maintaining Tables

Backup & Recovery

  • The Advantages and Disadvantages of Different Methods
  • Binary Backups of MYISAM Tables
  • Binary Backups of Innodb Tables
  • Recovery
  • Import and Export Operations
  • Exporting Using SQL
  • Importing Using SQL
  • Exporting from the Command Line using mysqldump
  • Importing from the Command Line using mysqlimport
  • Exercises: Backing up and Recovery

DAY 3

User Management

  • Introduction
  • User Accounts
  • Creating Users
  • Renaming Users
  • Changing Passwords
  • Dropping Users
  • Granting Privileges
  • The User Table
  • Connection Validation
  • Exercises: Creating, Managing and Dropping Users

Privileges

  • Introduction
  • Types of Privileges
  • Revoking Privileges
  • Resource Limits
  • The MySQL Database
  • The Show Grants Command
  • Exercises: Granting and Revoking Privileges

User Variables & Prepared Statements

  • User Variables
  • Prepared Statements
  • Exercises: User Variables and Prepared Statements

Stored Routines for Administrations

  • Types of Stored Routines
  • Benefits of Stored Routines
  • Stored Routines Features
  • Stored Routine Maintenance
  • Obtaining Stored Routine Metadata
  • Stored Routine Privileges and Execution Security

Triggers

  • DML Triggers

DAY 4

Security

  • Security Risks
  • Users, Operating System, File System and Network Security
  • Using SSL With MySQL
  • Remote Connecting to the MySQL Server Using SSH
  • Exercises: Securing the Server

MySQL Performance SAchema

  • Performance Schema Build and Startup Configuration
  • Performance Schema Runtime Configuration
  • Performance Schema Queries
  • Performance Schema Status Monitoring
  • Performance Schema General Table Characteristics
  • Performance Schema Table Descriptions
  • Using the Performance Schema to Diagnose Problems

Tuning

  • Tuning Overview
  • Identifying Candidates for Query Analysis
  • Using Explain to Analyze Queries
  • Meaning of Explain Output
  • Explain Extended
  • Exercises: Explaining and Tuning Queries

Tuning Indexes (a)

  • Indexes for Performance
  • Creating and Dropping Indexes
  • Obtaining Index Metadata
  • Indexing Principles
  • Exercises: Creating and Using Indexes

Tuning Indexes (b)

  • Indexing and Joins
  • Fulltext Indexes
  • MyISAM Index Caching
  • Exercises: Creating and Using Indexes

Tuning & Tables

  • General Table Optimizations
  • Myisam Specific Optimizations
  • Innodb Specific Optimizations
  • Other Engine Specific Optimizations
  • Exercises: Tuning Tables

Tuning the Server

  • Status Variables
  • Server Variables
  • Performance Schema Overview
  • The Query Cache
  • Exercises: Tuning the Server

Event Scheduler

  • Event scheduler concepts
  • Event scheduler configuration
  • Creating, altering and dropping events
  • Event scheduler monitoring
  • Events and privileges
  • Exercises: Using the event scheduler

Partitioned Tables

  • Partitioned tables concepts
  • Obtaining Partitioned Table Metadata
  • Types of Partitioning
  • Subpartitioning
  • Partition Pruning
  • Maintenance of partitioned tables
  • Restrictions and Limitations on Partitioning
  • Exercises: Using partitioned tables

Replication

  • Replication Overview
  • Testing Replication
  • Monitoring and Troubleshooting Replication
  • Files and Threads Involved in Replication
  • Excluding Databases or Tables From Replication
  • Complex Replication Topologies
  • Example: Setting Up a Master Slave Replication
  • Replication Using GTIDs
  • MySQL Replication Utilities
  • Controlled Switchover
  • Exercises: Setting Up and Testing Replication

MySQL Workbench

  • Installation
  • Connecting
  • Screens
  • Exercises: Using MySQL Workbench