# MySQL Database Administration Certification

## Overview

The MySQL for Database Administrators training is designed for DBAs and other database professionals. Expert instructors will teach you how to install and configure the MySQL Server, set up replication and security, perform database backups and performance tuning and protect MySQL databases.

## Duration

5 days full time.

## Technical Skill

You should be comfortable working on the command line and using command line utilities. The MySQL server will run on Linux but you can use the MySQL client tools from Windows, Linux or Mac OS X.

## Private Training

The is course is primarily offered as a private course. The course can be run onsite or on our site. We have offices in Cape Town and Johannesburg. The course price is R9 500 onsite and R12 599 on our premises. There is no set date to run the course, we schedule the date that suits your team. A minimum of 4 delegates is required to schedule the course.

## Course Curriculum

## Introduction to MySQL Server

MySQL Overview, Products, Services\
MySQL Services and Support\
Supported Operating Services\
MySQL Certification Program Training\
Curriculum Paths\
MySQL Documentation Resources

## MySQL Architecture

Describe the Client\\/Server Model\
Understand the Communication protocols that Clients Use to Connect to the MySQL\
Understand How the Server Supports Storage Engines\
Describe the Relationship Between the MySQL Storage Engines and its SQL parser and Optimizer\
MySQL SQL Layer\
MySQL Storage Layer\
Describe How MySQL Uses Memory and Disk Space\
Describe the MySQL Plugin Interface

## Server Configuration

Set up MySQL Server Configuration Files\
Explain the Purpose of Dynamic Server Variables\
Review the Server Status Variables Available\
Configure Operational Characteristics of the MySQL Server\
Describe the Available Log Files\
Explain Binary Logging

## System Administration

Types of MySQL Distributions\
Installing MySQL Server\
Starting and Stopping MySQL Server\
Running Multiple MySQL Servers on a Single Host\
Specifying Options for Server Runtime Configuration\
Log and Status Files\
Loading the Time Zone Tables for Named Time Zone Support\
Security-related Configurations Options\
Setting the Default `sql-mode`\
Upgrading an Older Installation to a Newer Version of MySQL

## Client and Utility Programs for DBA Work

MySQL Workbench Graphical Client\
The `mysql`\
The `mysqladmin` Command-line Client\
The `mysqlimport` Command-line Tool\
The `mysqldump` Command-line Tool\
The `mysqlcheck` Command-line Tool\
The `myisamchk` Command-line Tool\
The `mysqlhotcopy` Command-line Tool\
The `innochecksum` — Offline InnoDB File Checksum Utility\
The `myisam_ftdump` — Display Full-Text Index Information\
The `myisamchk` — MyISAM Table-Maintenance Utility\
The `myisamlog` — Display MyISAM Log File Contents\
The `myisampack` — Generate Compressed, Read-Only MyISAM Tables\
The `mysql_config_editor` — MySQL Configuration Utility\
The `mysqlbinlog` — Utility for Processing Binary Log Files\
The `mysqldumpslow` — Summarize Slow Query Log Files\
Available APIs and Drivers and Connectors

## User Management

Requirements for User Authentication\
Using `SHOW PROCELIST` to Show Which Threads are Running\
Creating, Modifying and Dropping User Accounts\
Alternative Authentication Plugins\
Requirements for User Authorizations\
Levels of Access Privileges for Users\
Types of Privileges\
Granting, Modfying, and Revoking User Privileges

## Data Types

Major Categories of Data Types\
Meaning of `NULL`\
Column Attributes\
Character Set Usage with Data Types\
Choosing an Appropriate Data Type

## Transactions and Locking

The `ACID` Properties of Transactions\
Transaction Isolation Levels\
Locking Concepts\
Using Explicit Table Locks\
Using Advisory Locks

## Obtaining Metadata

Available Metadata Access Methods\
Using `INFORMATION_SCHEMA` Compared to Using `SHOW` Statements\
Syntax for Accessing `INFORMATION_SCHEMA`\
Limitation of `INFORMATION_SCHEMA`\
The `mysqlshow` Client Program

## Storage Engines

An Overview of Storage Engines in MySQL\
`InnoDB` Storage Engine\
`InnoDB` System and `file-per-tablespaces`\
`NoSQL` and `Memcached` API\
Configuring Tablespaces Efficiently\
Using Foregin Keys to Attain Referential Integrity\
`InnoDB` Locking\
Features of Available Storage Engines\
`MERGE` Storage Engine\
`FEDERATED` Storage Engine\
`MEMORY` Storage Engine\
`MyISAM` Storage Engine

## InnoDB Storage Engine

Describe the InnoDB Storage Engine\
Set the Storage Engine to InnoDB\
Illustrate the InnoDB tablespace storage system\
Efficiently Configure the Tablespace\
Use Foreign Keys to Attain Referential Integrity\
Explain InnoDB Locking

## Partitioning

Partitions and its Use in MySQL\
Reasons for Using Partitioning\
Types of Partitioning\
Creating Partitioned Tables\
Subpartitioning\
Obtaining Partition Metadata\
Modifying Partitions to Improve Performance\
Storage Engine Support of Partitioning

## Security

Recognizing Common Security Risks\
Security Risks Specific to the MySQL Installation\
Security Problems and Counter Measures for Network, Operating Systems, Filsystems and Users\
Protecting your Data\
using SSL for secure MySQL server connections\
How SSH enables a secure remote connection to the MySQL Server

## Table Maintenance

Type of table maintenance operations\
SQL statements for table maintenance\
Client and utility programs for table maintenance\
Repairing InnoDB tables\
Maintaining tables for other storage engines

## Exporting and importing Data

Exporting Data\
Importing Data

## Programming MySQL

Creating and Executing Stored Procedures\
Describing Stored Routine Execution Security\
Creating and Executing Triggers\
Creating, Alerting and Dropping Events\
Event Execution Scheduling

## MySQL Data Backup and Recovery Methods

Types of Backups\
Backup Tools and Utilities\
Making Binary and Text Backups\
The Role of Log and Status Files in Backups\
Performing Data Recovery\
Using a Replication Slave for Backups

## Replication

Managing the MySQL Binary Log\
MySQL Replication Threads and Files\
Setting up a MySQL Replication Environment\
Desigining Complex Replication Topologies\
Multi-Master and Cluster Replication\
Performing a Controlled Switchover\
Monitoring and Troubleshooting Replication\
Replication with Global Transaction Identifiers(GTIDs)

## Introduction to Performance Tuning

Using `EXPLAIN` to Analyze Queries\
General Table Optimizations\
Monitoring Status Varaibles that Affect Performance\
Setting and Interpreting MySQL Server Variables\
Overview of `PERFORMANCE_SCHEMA`