MySQL Training: Introduction to MySQL Development and Administration Training

Recent Training Venues

Accelebrate has recently trained for clients in the following cities:

Huntsville, Alabama

Montgomery / Birmingham, Alabama

Anchorage, Alaska

Calgary, Alberta

Phoenix, Arizona

Fayetteville / Little Rock, Arkansas

Oakland / San Jose / San Francisco, California

Sacramento, California

Oceanside / San Diego, California

Pasadena / Orange County / Los Angeles, California

San Bernardino / Riverside, California

Boulder / Colorado Springs / Denver, Colorado

Washington, DC

Wilmington, Delaware

Manchester / London, England

DeLand / Orlando, Florida

Fort Lauderdale / Miami, Florida

Gainesville / Jacksonville, Florida

Saint Petersburg / Tampa, Florida

Titusville & Melbourne, Florida

Alpharetta & Atlanta, Georgia

Augusta & Savannah, Georgia

Macon & Columbus, Georgia

Boise, Idaho

Bloomington, Illinois

Chicago, Illinois

Indianapolis, Indiana

Cedar Rapids / Des Moines, Iowa

Dublin, Ireland

Kingston, Jamaica

Wichita, Kansas

Paducah / Lexington / Louisville, Kentucky

Baton Rouge, Louisiana

Valletta, Malta

Hagerstown & Frederick, Maryland

Greenbelt / Silver Spring / Baltimore, Maryland

Boston / Cambridge, Massachusetts

Hartford, Connecticut / Springfield, Massachusetts

Taunton, Massachusetts / Providence, Rhode Island

Ann Arbor / Farmington Hills / Detroit, Michigan

Grand Rapids, Michigan

Lansing, Michigan

Saginaw / Flint / Bay City / Midland, Michigan

Saint Paul / Minneapolis, Minnesota

Jackson, Mississippi

Gulfport / Biloxi, Mississippi

St. Louis, Missouri

Whiteman Air Force Base / Kansas City, Missouri

Lincoln / Omaha, Nebraska

Reno and Las Vegas, Nevada

Santa Fe / Albuquerque, New Mexico

Newark, New Jersey

Princeton, New Jersey & Philadelphia, Pennsylvania

White Plains / New York City, New York

Charlotte, North Carolina

Durham / Raleigh, North Carolina

Bowling Green / Toledo, Ohio

Canton / Akron, Ohio

Cincinnati, Ohio

Cleveland & Columbus, Ohio

Dayton, Ohio

Tulsa / Oklahoma City, Oklahoma

Toronto, Ontario

Portland, Oregon

Pittsburgh, Pennsylvania

Providence, Rhode Island

Edinburgh / Glasgow, Scotland

Columbia & Charleston, South Carolina

Chattanooga / Knoxville, Tennessee

Memphis / Jackson / Nashville, Tennessee

College Station and Houston, Texas

El Paso, Texas / Ciudad Juarez, Mexico

San Antonio / Austin, Texas

Wichita Falls & Dallas, Texas

Ogden / Salt Lake City, Utah

Burlington, Vermont

Fairfax / Dulles / McLean / Herndon / Reston, Virginia

Richmond / Alexandria / Arlington, Virginia

Virginia Beach / Norfolk, Virginia

Tacoma / Seattle, Washington

Madison / Milwaukee, Wisconsin

Course Number: 644 (combination of 642 and 643)
Duration: 5 days
view class outline

MySQL Development and Administration Training Overview

Accelebrate's MySQL Development and Administration training combines our MySQL Development and MySQL Administration courses, and teaches attendees the blend of development and administration skills they need to be successful implementing MySQL in their organizations.

Location and Pricing

Most Accelebrate courses are taught on-site at our clients' locations worldwide for groups of 3 or more attendees and are customized to their specific needs. Please visit our client list to see organizations for whom we have recently delivered training. To receive a customized proposal and price quote, please contact us.

In addition, some courses are available as live, online classes for individuals. To see a schedule of online courses, please visit http://www.accelebrate.com/online_training/xml_html_js.htm.

MySQL Development and Administration Training Prerequisites

All attendees should be experienced systems administrators with general relational database and SQL experience.

Hands-on/Lecture Ratio

This MySQL training class is 65% hands-on, 35% lecture, with the longest lecture segments lasting for 20 minutes. Students "learn by doing," with immediate opportunities to apply the material they learn to real-world problems.

MySQL Development and Administration Training Materials

All MySQL training students receive comprehensive courseware and a related textbook.

Software Needed on Each Student PC

MySQL 5.0 or later (class can be adapted for earlier versions upon request)
If you have purchased this course, please contact us for additional setup instructions

MySQL Development and Administration Training Objectives

Retrieve data using the SELECT statement.
Troubleshoot typical warnings and errors.
Change or add data.
Understand MySQL data validation.
Delete data from tables.
Generate aggregate query data using various criteria.
Connect data from multiple table rows using various types of JOIN constructs.
Use several different types of sub-queries.
Extensive coverage of MySQL Functions and expressions.
Use expressions in SQL statements for more functional and flexible retrieval.
Learn to export and import data.
Understand MySQL storage engines, transactions, and features.
Create views.
Perform bulk data import and export operations.
Create user defined variables, prepared statements, and stored routines.
Create and manage triggers.
Use the INFORMATION_SCHEMA database to access metadata.
Understand the MySQL architecture.
Perform common database administration tasks.
Utilize the various MySQL administration programs.
Learn the MySQL Administrator GUI.
Use the INFORMATION_SCHEMA database to access metadata.
Install and Upgrade MySQL for the most common operating systems.
Start and shutdown MySQL.
Configure MySQL server options at runtime.
Set up and manage logs.
Learn various data types, their constraints, and their implications for performance.
Understand data locking concepts at different levels in MySQL.
Understand the features of various MySQL storage engines.
Create, maintain and manage MySQL tables.
Perform backup and restore operations utilizing multiple MySQL tools.
Maintain security of a MySQL installation via user management and access rights.
Improve performance through query optimization.
Perform other optimizations.

Note: MySQL training students interested in learning how to use MySQL to build database-driven Web sites are also encouraged to consider our PHP Training: Introduction to PHP and MySQL.

MySQL Development and Administration Training Outline

MySQL Programs and Executables

MySQL Programs and Executables
Common Options to Various Commands
Specifying Options
Options on the Command Line
my_print_defaults: Display options from files
error: Explain Error Codes

User Interfaces MySQL

Overview of User Interfaces
Establishing a Connection
MySQL Query Browser

Keyboard Shortcuts
Main Query Window
Query Toolbar
Result Area
Advanced Toolbar
SQL commands with Mouse Clicks
History and Bookmarks for SQL Commands
Using Scripts: Executing Several Commands
MySQL Help - Information Browser
Object Browser
The Script Editor
Changing Data in SELECT Results
Stored Procedures

MySQL Administrator
MySQL Migration Toolkit

A Brief Overview of the Migration Process

phpMyAdmin

MySQL Monitor

Using MySQL Monitor (SQL Command Interpreter)
Some Useful Options
Formatting and Output Options
Commands for Interactive Mode
Executing a Script from the Monitor
Using the mysql Command to run a Script
See Database Status

Designing Databases

Relational Database Concepts

Brief History of SQL
Relational Database Management System
Popular Databases
Tables
Rows
Columns
Relationships
Datatypes
Primary Keys
Foreign Keys
Valid Object References
SQL Statements

Database Design Principles
Film Rentals - sakila
Basic Modeling Process: Creating a Data Model

Logical versus Physical Model
The Entity-Relationship Model
Entities And Entity Sets
Attributes
Relationships
Relationship Types
Graphical Representation of a Model
Physical Model: Mapping The ER Model To A Relational Database

Database Normalization

First Normal Form
Warning: Abnormal Normal Form!
Second Normal Form
Third Normal From

Creating Databases and Components

Creating Databases

Deleting Databases

Creating Tables

Dropping Tables
Defining Table Types
Altering Existing Table Structures
Add a column
Renaming Tables
Temporary Tables

Creating Column Definitions
Data Types in MySQL

Numeric Data Types
String Data Types
Character Set Fundamentals
Set Types
Date/Time Data Types
Options and Attributes

Constraints

NOT NULL Constraint: Defining a Column's Nullability
UNIQUE Constraint
PRIMARY KEY Constraint: Defining a Master Key
Defining Auto-Increment Columns
Referential Integrity Constraints: Defining Foreign Keys
Constraint Checking on a Transaction
Deferred Checking
Defining Default Values

Managing Indexes

Defining Indexes when Creating Tables
CREATE INDEX Statement
Full-Text Indexes
Deleting Indexes

Identifiers in MySQL

Identifiers in MySQL

Identifier Names

Schema Object Names

Using Double Quotes
Using Quote Within Names
Using Digits

Some Restrictions
Identifier Qualifiers
Mapping of Identifiers to Filenames
Identifier Case Sensitivity

lower_case_table_names Setting
Avoiding Data Transfer Problems

Show Database Information

SHOW and DESCRIBE Statements

Using Table-Related SHOW Statements
Using DESCRIBE Statement

INFORMATION_SCHEMA Tables

Showing Databases

MySQL Table Types

MySQL Table Types (Storage Engines)
Setting/Changing Engine
MyISAM Tables

Full-Text Search on MyISAM Tables
Creating a Full-Text Index
Using Full-Text Search

InnoDB Tables

InnoDB - Limitations and Drawbacks
InnoDB Disk Management
Preventing Problems
Moving Files

FOREIGN KEY Constraints in InnoDB

Checking Foreign Keys

Choosing - MyISAM or InnoDB?
HEAP or MEMORY Tables
Temporary Tables
MERGE Tables
Other Table Types

ARCHIVE or Compressed Tables (since MySQL 4.1)
CSV Type Tables(since MySQL 4.1)
NDB or Cluster Tables (since MySQL 4.1)
FEDERATED or External Tables (since MySQL 5.0)
BDB Tables

Table Files Storage

Basic Select Statements

Basic SQL Syntax Rules

Comments in SQL
Whitespace and Semi-colons
Case Sensitivity

SELECT Basics
Introduction to the sakila Database
Basic Select Examples

SELECTing All Columns in All Rows

SELECTing Specific Columns
The WHERE Clause and Basic Operator Symbols

Checking for NULL

WHERE and ORDER BY
Using Aliases
Selecting Distinct Records
Sorting Records
The LIMIT Clause

Processing the First N or Last N Records

Using Expressions in MySQL Statements

MySQL Expressions

Basic Elements of MySQL Expressions

Operators in MySQL

Calculated Fields

Concatenation
Arithmetic Operators
Operator Precedence
Comparison Operators
The WHERE Clause and Operator Words

The BETWEEN Operator
The IN Operator
The LIKE Operator
The REGEXP Operator
Use of BINARY with Strings
The NOT Operator

Using CASE
Logical Operators

Order of Evaluation
Summary

Using MySQL Functions
Functions to Compare Data
Control Flow Functions

Exercise: Using Control Flow Functions in a SELECT Statement
Data Conversion Functions

String Functions

Numeric Functions

Date/Time Functions

Current Date/Time
Date Addition/Subtraction Functions
Date Extraction Methods
Date Formatting Methods

Inserting, Updating and Deleting Records in MySQL

Inserting Data in a MySQL Database

<VALUES option> of INSERT
Add Multiple Rows
Maintaining Relationships
<set option> Alternative of INSERT

Using REPLACE Statement to Add Data
INSERT with ON DUPLICATE KEY UPDATE Syntax
Updating Data in MySQL

ORDER BY and LIMIT Clauses in UPDATE

Using DELETE Statement in MySQL

Some Advanced MySQL modifiers to DELETE statement
ORDER BY and LIMIT in DELETE Statement

Using TRUNCATE Statement to Delete Data
Joining Tables in an UPDATE Statement

Updating Multiple Tables using Joins

Joining Tables in a DELETE Statement

<FROM JOIN DELETE> Alternative
<using join delete> Clause
Deleting from multiple tables

Data Validation in MySQL

Data Validation
MySQL SQL Modes

Setting SQL mode
Some important SQL Modes

Strict SQL Mode

Strict Mode in Default Values

Special Combination Modes
Data Validation for Temporal Types

SHOW WARNINGS

Advanced Queries

More on The SELECT Statement
Using Variables in a SELECT Statement
Creating Joins in Your SQL Statements
Inner Joins and Cross Joins

Multi-table Joins

Outer Joins

Left Joins
Right Joins
Full Outer Joins
Creating Full Joins
Creating Basic Joins
Creating Straight Joins
Creating Natural Joins

Unions

UNION ALL
UNION Rules

Creating Views
Dropping Views
Benefits of Views
The SELECT Statement Options
Subqueries

SubQuery Variants
Nesting JOINS
SubSELECT Limitations
SubSelects as Join

Exporting and Importing Data in MySQL

EXPORTING: Data Out of a Table
Use Field and Line Separators
Dump a Row to a File
CTAS: Creating a New Table Using a ResultSet
Copying Data into an Existing Table
Using the LOAD DATA to Import Data

General Loading Rules
More Loading Examples

Using the mysqlimport Utility to Import Data

Using Separators with mysqlimport Utility

Installing MySQL

Installing MySQL
Operating System Selection
Downloading and Installing MySQL On Windows

Running the MySQL Configuration Wizard
Starting and stopping the MySQL server

Installing MySQL On Linux and Unix

Using RPM (Linux only)
MySQL Packaged RPMs
Using a Tar File to Install MySQL
Starting the Server Automatically

Installing MySQL On Mac
Verifying a Downloaded File
Verifying a MySQL Installation

mysqladmin: Obtain Server Status

Launching the MySQL Server
Installing From Source Files

Setting up MySQL

Versions of the MySQL Server

The mysqld_safe Wrapper

Mysql Directory Structure
Securing the MySQL Server: Set root Password
Delete Anonymous Accounts
Creating an Account for Basic Use
General Information using MySQL Monitor
Configuring MySQL

Configuring MySQL and Specifying Options

Configuration Options
Viewing MySQL's Configuration Parameters
Some Examples of Option Usage

Managing Connection Loads
Setting the Data Directory Location
Setting the Default Storage Engine
Executing SQL Commands Automatically
Logging Potentially Nonoptimal Queries
Logging Slow Queries
Setting the Maximum Allowable Simultaneous Connections
Setting MySQL's Communication Port

Rules for Specifying Options

Session Specific Options
Turning Options On or Off
--loose Option
Memory Specifications
Precedence of Options

Setting Options in Configuration Files
Configuration Templates
Location of Configuration Files

Unix Configuration Files
Windows Configuration Files

Options in Environment/System Variables
Main Configuration Groups
Key Configuration Options
Client Configuration Options
Server Configuration Options

Logging Configuration Options

Engine-Specific Configuration Options
Buffer and Cache Allocation Configuration Options

Securing the MySQL Installation

First Things First

Apply Latest Trusted Patches
Cleanup restaged boxes
Audit OS User Accounts
Disable Unused System Services

Network

Add Firewall to Shutdown Access
Restrict host connections
Use IP addresses if your DNS is unreliable
Disable Networking

Securing the mysqld Daemon
Files, Directories, and Processes

Run MySQL as a non-root user
Protect the socket file
Set Data File Permissions
Consider an Encrypted File System
Avoid Symbolic Links

Securing Accounts

Set a good MySQL root user password
User Access
Requiring Passwords
Controlling Account Privileges
If you Forget the Root Password!
Limiting User Resources
Controlling Connections
Application Access

Data Storage and Encryption

One-Way Encryption
Signing Your Data
Two-Way Encryption
Don't Store Sensitive Data

Managing Users in MySQL

Managing Security and Access Control
The Two Stages of Access Control
Granting Privileges
Revoking Privileges
Managing Users

Adding Users to MySQL Access Control
Adding Privileges to an Existing User
Deleting Users

MySQL Privilege System

Tracing A Connection Request

Privilege Scope Levels - Where Is Access Information Stored?
The user Table - Global Privilege Scope
Database Privilege Scope - The db Table

The host Table
Working with DB Permissions

Table Privilege Scope - The tables_priv Table

Revoking Table-Level Permissions

Column Privilege Scope - The columns_priv Table

Revoking Column-Level Permissions

The Routine scope: The procs_priv Table
Tools for Setting the Access Privileges
Refreshing Grant Caches

Insert into user Table

Reviewing User Privileges

Using SHOW GRANTS
Listing Conflicts

The GRANT OPTION Privilege

Grant to All Users from a Host

Administering MySQL

View Information

Using MySQLshow

Using MySQLadmin Tool

Databases
View Server Status

Flush Caches and Settings
View and Kill Threads
Server Management
MySQL Administrator User Interface

Data Backup and Restore in MySQL

Managing Backup and Recovery
Backing up or Exporting Databases Using mysqldump

Copying Data Directory
Portability

Using mysqldump
mysqldump - Options

Formatting Options

Backing up a Single Database

Backing up the Entire Database

Contents of the Backup SQL Script

Set Variables
Enclosing Characters /*! and */
Set Names
Tables
Ignoring Foreign Key Constraints
Flush Logs
Backing up Individual Tables
Backing up Multiple Databases
Database Information in the Backup File
Backing up all Databases

Restoring Your Database

Reloading Your Database

Updating the Restored Database From Binary Log Files

Restoring Data Directly From a Binary Log
Selective Changes from Log Files
Restoring Binary Log Data From a Text File
Enabling and Disabling Binary Logging

Recovering Corrupt MyISAM Tables

mysqlhotcopy - A Database Backup Program

Backing Up and Recovering an InnoDB Database

Stop Background Processes - Forced Recovery
Recovery using Checkpoints
InnoDB Hot Backup

Conclusion

VB.NET, C#, ASP, & ASP.NET Training

Java Training

SQL Server Training
Adobe Training

PHP Training

XML Training

Perl Training

“Accelebrate,” “Accelerated Learning Celebrated Results,” and the Accelebrate logo are registered trademarks of Accelebrate, Inc. All other product names and trademarks are property of their respective owners. Course outline content may mirror or be derived from the book that each student receives with the course. Otherwise, all content is copyright © 2003- by Accelebrate, Inc. of Atlanta, Georgia, USA. To contact us, please use the contact form, email info@accelebrate.com us, or call 1 877 849 1850 in the US and Canada, 0808 234 2132 in the UK, 1 800 550 311 in Ireland, or +1 678 648 3113 from all other locations.

Complimentary MySQL or PHP Training Seminar