MySQL Training: MySQL 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: 643
Duration: 3 days
view class outline

MySQL Administration Training Overview

Accelebrate’s MySQL Administration Training teaches attendees how to install, configure, secure, administer, and tune MySQL.

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 Administration Training Prerequisites

All attendees should have some prior experience running and using MySQL, as well as an understanding of basic SQL query writing and relational database principles.

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 Administration Training Materials

All MySQL training students receive

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 Administration Training Objectives

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 Administration Training Outline

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

Optimizing Databases and Their Objects

Optimizing Databases
Improving Application Design
Precomputing Queries
Materialized Views
Transactions
Indexing

Types Of Indexes
Indexes are not used when ...
Client-Server Interactions

MyISAM Index Collecting Statistics

Optimizing and Tuning Queries

Query Execution Plan
Analyzing the output from EXPLAIN Statement

Explain Table

Join Optimization

Cartesian Product
Match Elimination: Filtering Joins
Using Joins in Aggregation
Optimizing Joins
Driving Tables

When to Index

Index Types

SQL Queries: Other Optimizations

Optimizing Data Retrieval
Cardinality of a Table
Using the OPTIMIZE TABLE Statement

The Slow Query Log

Tuning MySQL for Performance

System Characteristics

Disk I/O Speed
Memory
How MySQL Uses Memory
Processor Speed
Network Bandwidth

Tuning Server Parameters
MySQL Query Cache
The MyISAM Key Cache
Examining Thread Information

Replication in MySQL

Replicating MySQL Databases
MySQL Replication Model

Availability
Load Balancing

Setting up Replication

Replication Files on the Slave
Replication Chaining

Implementing Replication - Details

Set up Replication User
Making Initial Backup
Configuration Changes on Master
Configuration Changes on the Slave
Restore Backup on Slave
Set up Connection to Master
Start Replication on Slave
Verifying Replication

Managing Replication

Managing the Master Server
Managing the Slave Server

Replication Configuration Options

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