MySQL Database Administration (MYSDBA) - Outline

Detailed Course Outline

Course Contents - DAY 1

Course Introduction

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

Session 1: CLIENTS AND TOOLS

  • Introduction
  • MySQL
  • MySQL Admin
  • Exercises: Using Client Programs

Session 2: 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

Session 3: CONFIGURING THE SERVER

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

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

Course Contents - DAY 2

Session 6: 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 7: TABLES, DATA TYPES AND 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

Session 8: TRANSACTIONS AND LOCKING

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

Session 9: 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

Course Contents - DAY 3

Session 10: 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

Session 11: 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

Session 12: BACKUP AND 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

Session 13: 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

Course Contents - DAY 4

Session 14: PRIVILEGES

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

Session 15: USER VARIABLES AND PREPARED STATEMENTS

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

Session 16: STORED ROUTINES FOR ADMINISTRATION

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

Session 17: TRIGGERS

  • DML Triggers

Session 18: 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

Session 19: 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 20: TUNING AND INDEXES (1)

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

Session 21: TUNING AND INDEXES (2)

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

Session 22: TUNING AND TABLES

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

Course Contents - DAY 5 Session 23: TUNING THE SERVER

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

Session 24: 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 25: PARTITIONED TABLES

  • Partitioned tables concepts
  • Obtaining Partitioned Table Metadata
  • Types of Partitioning
  • Subpartitioning
  • Maintenance of partitioned tables
  • Exercises: Using partitioned tables

Session 26: 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

Session 27: MYSQL WORKBENCH

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