MySQL Training for Administrators & Developers

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

MySQL Developer Training Course Objectives

MySQL Client/Server Concepts

• MySQL General Architecture
• How MySQL Uses Disk Space
• How MySQL Uses Memory
• MySQL Clients

• Using Option Files
• The MySQL Client
• MySQL Query Browser
• MySQL Connectors
• Third-Party APIs

• The SELECT Statement
• Aggregating Query Results
• Using UNION

• SQL Modes
• Handling Missing or Invalid Data Values
• Interpreting Error Messages

• 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

• Metadata Access Methods
• The INFORMATION_SCHEMA Database/Schema
• Using SHOW and DESCRIBE
• The mysqlshow Command

• Database Properties
• Good Design Practices
• Identifiers
• Creating Databases
• Altering Databases
• Dropping Databases

• Creating Tables
• Table Properties
• Column Options
• Creating Tables Based on Existing Tables
• Altering Tables
• Dropping Tables
• Foreign Keys

• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement
• The REPLACE Statement
• INSERT with ON DUPLICATE KEY UPDATE
• The TRUNCATE TABLE Statement

• What is a Transaction?
• Transaction Commands
• Isolation Levels
• Locking

• 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

• Types of Subqueries
• Table Subquery Operators
• Correlated and Non-Correlated Subqueries
• Converting Subqueries to Joins

• What Are Views?
• Creating Views
• Updatable Views
• Managing Views
• Obtaining View Metadata

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

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

• Defining views
• Reasons to use views
• Altering, checking and removing a view
• Setting privileges for views

• 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

• 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

• Using EXPLAIN to Analyze Queries
• General Table Optimizations
• Monitoring status variables that affect performance
• Setting and Interpreting MySQL server Variables