MySQL Training

MySQL Training for Administrators & Developers

MySQL Training has two streams, a developer training course and a administrator training course.

DescriptionDaysPrice (ex vat)
MySQL Administrator Training4ZARUSD

R 7,000

$ 700

MySQL Developer Training

4R 7,000$ 700

* Includes text book, lunch and tea. All Classes start at 09H00-09H30 unless pre-arranged.

MySQL Developer Training Course Objectives

MySQL Client/Server Concepts

  • MySQL General Architecture
  • How MySQL Uses Disk Space
  • How MySQL Uses Memory
  • MySQL Clients
Invoking Client Programs
  • Using Option Files
  • The MySQL Client
  • MySQL Query Browser
  • MySQL Connectors
  • Third-Party APIs
Querying for Table Data
  • The SELECT Statement
  • Aggregating Query Results
  • Using UNION
Handling Errors and Warnings
  • SQL Modes
  • Handling Missing or Invalid Data Values
  • Interpreting Error Messages
SQL Expressions
  • SQL Comparisons
  • Functions in SQL Expressions
  • Comments in SQL Statements

Data Types

  • Data Type Overview
  • Numeric Data Types
  • Character String Data Types
  • Binary String Data Types
  • Temporal Data Types
  • NULLs
Obtaining Metadata
  • Metadata Access Methods
  • The INFORMATION_SCHEMA Database/Schema
  • Using SHOW and DESCRIBE
  • The mysqlshow Command
Databases
  • Database Properties
  • Good Design Practices
  • Identifiers
  • Creating Databases
  • Altering Databases
  • Dropping Databases
Tables
  • Creating Tables
  • Table Properties
  • Column Options
  • Creating Tables Based on Existing Tables
  • Altering Tables
  • Dropping Tables
  • Foreign Keys
Manipulating Table Data
  • The INSERT Statement
  • The DELETE Statement
  • The UPDATE Statement
  • The REPLACE Statement
  • INSERT with ON DUPLICATE KEY UPDATE
  • The TRUNCATE TABLE Statement
Transactions
  • What is a Transaction?
  • Transaction Commands
  • Isolation Levels
  • Locking
Joins
  • What is a Join?
  • Joining Tables in SQL
  • Basic Join Syntax
  • Inner Joins
  • Outer Joins
  • Other Types of Joins
  • Joins in UPDATE and DELETE statements
Subqueries
  • Types of Subqueries
  • Table Subquery Operators
  • Correlated and Non-Correlated Subqueries
  • Converting Subqueries to Joins
Views
  • What Are Views?
  • Creating Views
  • Updatable Views
  • Managing Views
  • Obtaining View Metadata
Prepared Statements
  • Why Use Prepared Statements?
  • Using Prepared Statements from the mysql Client
  • Preparing a Statement
  • Executing a Prepared Statement
  • Deallocating a Prepared Statement

Exporting and Importing Data

  • Exporting and Importing Data
  • Exporting and Importing Data Using SQL
  • Exporting and Importing Data Using MySQL Client Programs
  • Import Data with the SOURCE Command
Stored Routines
  • What is a Stored Routine?
  • Creating, Executing and Deleting Stored Routines
  • Compound Statements
  • Assign Variables
  • Parameter Declarations
  • Flow Control Statements
  • Declare and Use Handlers
  • Cursors

Triggers

  • What are Triggers?
  • Delete Triggers
  • Restrictions on Triggers
Storage Engines
  • SQL Parser and Storage Engine Tiers
  • Storage Engines and MySQL
  • The MyISAM Storage Engine
  • The InnoDB Storage Engine
  • The MEMORY Storage Engine
  • Other Storage Engines
Optimization
  • Overview of Optimization Principles
  • Using Indexes for Optimization
  • Using EXPLAIN to Analyze Queries
  • Query Rewriting Techniques
  • Optimizing Queries by Limiting Output
  • Using Summary Tables
  • Optimizing Updates
  • Choosing Appropriate Storage Engines  

Conclusion

MySQL Administrator Training Course Objectives

 

DescriptionDaysPrice (ex vat)
MySQL Administrator Training4ZARUSD

R 7,000

$ 700

MySQL Developer Training

4R 7,000$ 700

* Includes text book, lunch and tea. All Classes start at 09H00-09H30 unless pre-arranged.

MySQL Architecture
  • Describe the client/server model
  • Understand communication protocols
  • Understand how the server supports storage engines
  • Explain the basics of how MySQL uses memory and disk space
System Administration
  • Choose between types of MySQL distributions
  • Install the MySQL Server
  • Describe the MySQL Server installation file structure
  • Start and stop the MySQL server
  • Upgrade MySQL
  • Run multiple MySQL servers on a single host
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
Clients and Tools
  • Describe the available clients for administrative tasks
  • Use MySQL administrative clients
  • Use the mysql command line clients
  • Use the mysqladmin for administrative tasks
  • Describe available MySQL tools
Data Types
  • Describe the major categories of data types
  • Explain the meaning of NULL
  • Describe column attributes
  • Explain character set usage with data types
  • Choose an appropriate data type
Obtaining Metadata
  • List the various metadata access methods available
  • Recognize the structure of the INFORMATION_SCHEMA database schema
  • Use the available commands to view metadata
  • Describe differences between SHOW statements and INFORMATION_SCHEMA tables
  • Use the mysqlshow client program
  • Use INFORMATION_SCHEMA to create shell commands and SQL statements
Transaction and Locking
  • Use transaction control statement to run multiple SQL statements concurrently
  • Explain the ACID properties
  • Describe the transaction isolation levels
  • Use locking to protect transactions

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
Other Storage Engines
  • Explain the general purpose of storage engines in MySQL
  • List the storage engines available for MySQL
  • Describe the key features of the MyISAM storage engine
  • Describe the key features of the MEMORY storage engine
  • Give an overview of other storage engines: FEDERATED, ARCHIVE, BLACKHOLE, NDBCLUSTER
  • Choose an appropriate storage engine
Partitioning
  • Define partitioning and its particular use in MySQL
  • Determine server partitioning support
  • List the reasons for using partitioning
  • Explain the types of partitioning
  • Create partitioned tables
  • Describe subpartitioning
  • Obtain partitioning metadata
  • Use partitioning to improve performance
User Management
  • Depict the user connection and query process
  • List requirements for user authentication
  • Use SHOW PROCESSLIST to show which threads are running
  • Create, modify and drop user accounts
  • List requirements for user authorization
  • Describe the levels of access privileges for users
  • List the types of privileges
  • Grant, modify and revoke user privileges
Security
  • Recognize common security risks
  • Describe security risks specific to the MySQL installation
  • List security problems and counter-measures for network, operating system, filesystem and users
  • Protect your data
  • Use SSL for secure MySQL server connections
  • Explain how SSH enables a secure remote connection to the MySQL server
  • Find additional information for common security issues
Table Maintenance
  • Recognize types of table maintenance operations
  • Execute SQL statements for table maintenance
  • Client and utility programs for table maintenance
  • Maintain tables according to specific storage engines
Exporting and Importing Data
  • Exporting Data using SQL
  • Importing Data using SQL

Programming Inside MySQL

  • Creating and executing Stored Routines
  • Describing stored routine execution security
  • Creating and executing triggers
  • Creating, altering and dropping events
  • Explaining even execution scheduling
Views
  • Defining views
  • Reasons to use views
  • Altering, checking and removing a view
  • Setting privileges for views
MySQL Backup and Recovery
  • Describing backup basics
  • Types of backups
  • Backup tools and utilities
  • Making binary and text backups
  • Role of log and status files in backups
  • Data Recovery
Introduction to Replication
  • Describing MySQL Replication
  • Managing the MySQL Binary Log
  • Explaining MySQL replication threads and files
  • Setting up a MySQL Replication Environment
  • Monitoring MySQL Replication
  • Troubleshooting MySQL Replication
Introduction to Performance Tuning
  • Using EXPLAIN to Analyze Queries
  • General Table Optimizations
  • Monitoring status variables that affect performance
  • Setting and Interpreting MySQL server Variables
Conclusion