Starting dates and places

Description

Prerequisites:

A working knowledge of MySQL is required. This can be gained by attendance on the Introduction to MySQL course.

Course Description:

This MySQL Database Administration course is designed for MySQL Database Administrators who have a basic understanding of a MySQL database and SQL commands. The course provides practical experience in setting up and maintaining a MySQL server, including backing up, recovery, configuration and optimization.

Frequently asked questions

Prerequisites:

A working knowledge of MySQL is required. This can be gained by
attendance on the Introduction to MySQL course.

Course Description:

This MySQL Database Administration course is designed for MySQL
Database Administrators who have a basic understanding of a MySQL
database and SQL commands. The course provides practical experience
in setting up and maintaining a MySQL server, including backing up,
recovery, configuration and optimization.

The delegate will practise:

Starting, Stopping and Configuring MySQL

Security-Related Configuration

MySQL Database Creation

Using Client Programs for MySQL DBA Work

Setting up Character Set Support

Locking

Using Storage Engines, including the MyISAM and InnoDB
engines

Table Maintenance

Obtaining Data from the Information_Schema Database

Backup and Recovery, including Exporting (mysqldump) and
Importing (mysqlimport

Using Stored Procedures and Triggers for Database
Administration Tasks

User Management

Securing the Server

Upgrade-Related Security Issues

Optimizing Queries

Optimizing Schemas

Optimizing the Server

The Event Scheduler

Partitioned Tables

Interpreting Error Messages

Optimizing the MySQL Environment

Scaling MySQL, including Replication

This course includes the following modules:

Client Programs for DBA Work

Introduction

Client Program Limitations

mysql

MySQL Admin

Exercises: Using Client Programs

The Information_Schema Database

What is Metadata?

The mysqlshow Utility

The SHOW and DESCRIBE Commands

The Information_Schema Database

Exercises: Obtaining Information about MySQL

MySQL Administrator

Installation

Connecting

Server Information

Service Control

User Administration

Privileges

Health

Backup and Restore

Catalogs

Exercises: Using the MySQL Administrator Tool

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

Exercises: Interpreting Error and Diagnostic Information

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

Installing, Configuring, Starting and
Stopping

MySQL Distributions

Installing on Windows

Installing on Linux and UNIX

Starting and Stopping on Windows

Starting and Stopping on UNIX/Linux

Configuration

Log and Status Files

The Default SQL Mode

Time Zone Tables

Some Security Issues

Upgrading

Exercises: Installing, configuring, stopping and starting

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

Exercises: Creating and Maintaining Tables

Locking

Locking Concepts

Explicit Table Locking

Advisory Locking

Preventing Locking Problems

Exercises: Locking

Storage Engines

Introduction

The MYISAM Engine

Locking with MYISAM Tables

The Merge Engine

Other Engines: Archive, Memory, Federated, Blackhole,
NDBCluster

Exercises: Using Storage 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

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 aand 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 Using SQL

Importing Using SQL

Exporting from the Command Line using mysqldump

Importing from the Command Line using mysqlimport

Exercises: Backing up and Recovery

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 and Prepared Statements

User Variables

Prepared Statements

Exercises: User Variables and 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

Exercises: Creating and Using Stored Routine

Triggers

DML Triggers

The Create Trigger Statement

Managing Triggers

Exercises: Creating and Using 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

Exercises: Securing the Server

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

Exercises: Explaining and Optimizing Queries

Optimization and Indexes

Indexes for Performance

Creating and Dropping Indexes

Obtaining Index Metadata

Indexing Principles

Indexing and Joins

MyIsam Index Caching

Exercises: Using Indexes for Optimization

Optimizing Schemas

Normalisation

General Table Optimizations

Myisam Specific Optimizations

Innodb Specific Optimizations

Other Engine Specific Optimizations

Exercises: Optimizing Schemas

Optimizing the Server

Measuring Server Load

System Factors

Server Parameters

Query Optimizer Performance

The Query Cache

Exercises: Optimizing the Server

Optimizing the Environment

Choosing the Platform

Hardware Configurations

Disk Issues on Linux

Symbolic Links

Optimizing the Operating System

Exercises: Optimizing the Environment

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

Partitioned Tables

Partitioned tables concepts

Range partitioning

Hash partitioning

Key partitioning

List partitioning

Composite partitioning or subpartitioning

Maintenance of partitioned tables

Exercises: Using partitioned tables

Scaling MySQL

Using Multiple Servers

Replication

Exercises: Using Replication

For online live training advice please visit our
Learning Advice Centre on our website. Be sure to follow us on
Twitter to receive special course offers, news and updates!