MySQL Database Administration — A 5 day course

Synopsis

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

To provide the skills necessary to administer, monitor and support MySQL databases and servers. The delegates will practise:

Using Client Programs for MySQL DBA Work
Obtaining Metadata
Configuring the Server
Server Installation, Administration and Upgrading
Setting up Character Set Support
Configuring Log Files
Interpreting Error Messages
Creating, Altering and Dropping Databases and Tables
Locking and Transactions
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
Using MySQL Workbench

Suitable For

Anyone who needs to administer, monitor and support MySQL databases and servers.

Follow-On Course

MySQL Performance & Tuning

Prerequisites

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

Examination

The MySQL Database Administration course will teach the major topics required for the delegate to work towards achieving the Oracle Certified Professional MySQL 5.6 Database Administrator Exam (1Z0-883).

Following attendance on the relevant courses, delegates will need to dedicate time for self study and researching the topics covered on the course. They will also need to gain hands-on experience so they can put into practice what they have learnt on the courses.

Attending courses alone will not guarantee exam success, previous experience, practice, self study and aptitude are also key requirements.

Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.

Exams are not included as part of the course nor in the course fee.

Book your exam with Pearson VUE Testing Services.

Publicly scheduled dates, locations, and prices

A schedule of dates for this subject is not currently available. Please call 0333 210 0140 or use our contact form to enquire about places and availability.

Clients and Tools

Introduction
MySQL
MySQL Admin
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 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

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

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

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

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
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
Exercises: Securing the Server

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

Tuning and Indexes 1

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

Tuning and Indexes 2

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

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

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
Obtaining Partitioned Table Metadata
Types of Partitioning
Subpartitioning
Maintenance of partitioned tables
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