MySQL Database Administration

DB587 MySQL Database Administration

Duration: 5 Days

Intended Audience

This course is for those who will be controlling and administering MySQL Database servers. Attendees must have a good basic understanding of SQL and using it on a MySQL database. Some knowledge and experience of designing databases would be useful but is not absolutely necessary, as the relevant details will be covered.

Synopsis and Course Objectives

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

Objectives

The course aims to teach the skills necessary to administer, monitor and support MySQL databases and servers. As well as covering theory and concepts the course will include practical work that will involve performing the following tasks that are needed to perform the duties of database administrator

Using Client Programs for MySQL DBA Work
Obtaining and Interpreting Metadata
Configuring the Server both from the database and the operating system perspective
Server Installation, Administration and Upgrading
Setting up Character Set Support
Configuring Log Files
Understanding the causes of and Interpreting Error Messages
Creating, Altering and Dropping Databases and Tables
Locking and Transactions and dealing with some of the problems encountered in this context.
Using Storage Engines, including the MyISAM and InnoDB engines
Table Maintenance
Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport)
Administering Users and Privileges
Using Stored Procedures and Triggers for Database Administration Tasks
Securing the Server
Tuning Queries
Tuning Tables and Indexes
Tuning the Server
Using the Event Scheduler
Creating Partitioned Tables
Replication
Working with MySQL Workbench

Course Outline

The roles and responsibilities of a Database Administrator

Database performance and tuning expertise
Database design expertise
Database backup and recovery expertise
Database problem troubleshooting expertise
Database administration expertise
Advisor and mentor for both developers and users

MySQL Clients and Tools

Introduction
MySQL
MySQL Admin
Database administration using client programs

Obtaining Metadata

The need for Metadata
The mysqlshow Client Program
The SHOW and DESCRIBE Commands
The Information_Schema
Show Command and Information_Schema Differences

MySQL Database Server Configuration

MySQL Server Options and Variables
MySQL Status Variables
Configuring MySQL Enterprise Audit

MySQL Server Installation and Configuration

MySQL Distributions
Installing MySQL on Windows
Installing MySQL on Linux and MacOSX
Starting and Stopping MySQL on Windows
Starting and Stopping MySQL on Linux and MacOSX
Status Files
Upgrading the Server
Time Zone Tables

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

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
Maintaining Tables

Transactions and Locking

The concept of a Lock
Atomic operations on a Lock
Granularity of Locking
Explicit Table Locking
Advisory 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 - An Overview

The Innodb Engine

History of Innodb
Features of Innodb
Transactions
Referential Integrity
Physical Characteristics of Innodb Tables
System 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

Approaches and Strategies - Pros and Cons
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

User Management

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

Privileges

Users and Roles
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 Purposes

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

Security

Security Risks
Users, Operating System, File System and Network Security
Using SSL With MySQL
Remote Connecting to the MySQL Server Using SSH
Overview of SQL Injection Attacks

Tuning Queries

Tuning Overview
Identifying Candidates for Query Analysis
Using Explain to Analyze Queries
Meaning of Explain Output
Explain Extended

Tuning and Indexes

Indexes for Performance
Creating and Dropping Indexes
Obtaining Index Metadata
Indexing Principles

Tuning and Indexes - More Advanced Topics

Indexing and Joins
Fulltext Indexes
MyISAM Index Caching

Tuning and Tables

General Table Optimizations
Myisam Specific Optimizations
Innodb Specific Optimizations
Other Engine Specific Optimizations
Practical Strategies for Tuning Tables

Tuning the Server

Status Variables
Server Variables
Performance Schema Overview
The Query Cache
Practical strategies and heuristics for Server Tuning

The Event Scheduler

Event scheduler concepts
Event scheduler configuration
Creating, altering and dropping events
Event scheduler monitoring
Events and privileges

Partitioned Tables

Partitioned tables concepts
Obtaining Partitioned Table Metadata
Types of Partitioning
Subpartitioning
Maintenance of partitioned tables

Replication

Replication - An 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
Setting Up, Testing and Troubleshooting Replication

MySQL Workbench