Audience

This course is ideal for delegates who needs to administer, monitor and support MySQL databases and services.

Prerequisites

A working knowledge of MySQL is required. Attending our MySQL Introduction to Querying class would be beneficial.

Duration

5 days. Hands on.

Course Objectives

This course is designed to give administrators the knowledge and skills needed to maintain successful and efficient operations of MySQL databases and services. Administrators will gain practical experience in setting up, administering and troubleshooting the database.

On completion of the course, delegates will understand how to:

• Start, Stop and Configure MySQL
• Security-Related Configuration
• Create Databases
• Use Client Programs for DBA Work
• Set up Character Set Support
• Locking
• Use Storage Engines
• Table Maintenance
• Obtain Data from the Information_Schema Database
• Backup and Recovery
• Use Stored Procedures for Database Administration Tasks
• User Management
• Secure the Server
• Upgrade-Related Security Issues
• Optimise Queries and Schemas
• Optimise the Server - The Event Scheduler
• Partitioned Tables
• Interprete Error Messages
• Optimise the Environment
• Scale MySQL

Course Content

Introduction
Course Intro
Client Program Limitations
mysql
MySQL Admin

The Information_Schema Database
What is Metadata?
The mysqlshow Utility
The SHOW and DESCRIBE Commands
The Information_Schema Database

MySQL Administrator
Installation
Connecting
Server Information
Service Control
User Administration
Privileges
Health
Backup and Restore
Catalogs

Interpreting Error and Diagnostic Information
MySQL Error Messages
The SHOW Statement
SQL Modes
The PERROR Utility
The Log
The Error Log
The Slow Query Log

MySQL Architecture
Client/Server Overview
Communication Protocols
The SQL Parser and Storage Engine Tiers
MySQL Disk Space Use
MYSQL Memory Use

Installing, Configuring, Starting and Stopping
MySQL Distributions
Installing on Windows, Linux and UNIX
Starting and Stopping on Windows, UNIX & Linux
Configuration
Log and Status Files
The Default SQL Mode
Time Zone Tables
Some Security Issues
Upgrading

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
Performance Issues with Character sets
Choosing Data Types for Character Columns

Locking
Locking Concepts
Explicit Table Locking
Advisory Locking
Preventing Locking Problems

Storage Engines
Introduction
The MYISAM Engine
Locking with MYISAM Tables
The Merge Engine
Other Engines

The Innodb Engine
Introduction
Features of Innodb
Transactions
Referential Integrity
Physical Characteristics of Innodb Tables
Tablespace Configuration
Log File and Buffer Configuration
Innodb Status

Table Maintenance
Table Maintenance Operations
Check Table
Repair Table
Analyze Table
Optimize Table
MySQL Check
MYISAMCHK
Repairing Innodb Tables
Enabling MYISAM AutoRepair

Backup and Recovery
Planning and Implementing a Backup and Recovery Strategy
Defining a Disaster Recovery Plan
Testing a Backup and Recovery Plan
The Advantages and Disadvantages of Different Methods
Binary Backups of MYISAM Tables
Binary Backups of Innodb Tables
Recovery
Import and Export Operations
Exporting and Importing Using SQL
Exporting and Importing from the Command Line

User Management
Introduction
User Accounts
Creating Users
Renaming Users
Changing Passwords
Dropping Users
Granting Privileges
The User Table
Connection Validation

Privileges
Introduction
Types of Privileges
Revoking Privileges
Resource Limits
The MySQL Database
The Show Grants Command

User Variables and Prepared Statements
User Variables
Prepared Statements

Stored Routines for Administration
Types of Stored Routines
Benefits of Stored Routines
Stored Routines Features
Stored Routine Maintenance
Stored Routine Privileges and Execution Security

Triggers
DML Triggers
The Create Trigger Statement
Managing Triggers

Securing the Server
Security Issues
Operating System Security
Filesystem Security
Log Files and Security
Network Security
Upgrade-related Security Issues
Upgrading the Privilege Tables
Security-Related SQL_Mode Values

Optimizing Queries
Optimization Overview
Optimization Process
Planning a Routine Monitoring Regime
Setting Suitable Goals
Identifying Candidates for Query Analysis
Using Explain to Analyze Queries
Meaning of Explain Output
Using Explain Extended

Optimization and Indexes
Indexes for Performance
Creating and Dropping Indexes
Obtaining Index Metadata
Indexing Principles
Indexing and Joins
MyIsam Index Caching

Optimising Schemas
Normalisation
General Table Optimizations
Myisam Specific Optimizations
Innodb Specific Optimizations
Other Engine Specific Optimizations

Optimising the Server
Measuring Server Load
System Factors
Server Parameters
Query Optimizer Performance
The Query Cache

Optimising the Environment
Choosing the Platform
Hardware Configurations
Disk Issues on Linux
Symbolic Links
Optimising the Operating System

The Event Scheduler
Event scheduler concepts
Event scheduler configuration
Creating, altering and dropping events
Monitoring
Events and privileges

Partitioned tables
Partitioned tables concepts
Range partitioning
Hash partitioning
Key partitioning
List partitioning
Composite partitioning or subpartitioning
Maintenance of partitioned tables

Scaling MySQL
Using Multiple Servers
Replication