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