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