MySQL Database Administration

Skills Gained
The delegates will practise:

Starting, Stopping and Configuring MySQL
Security-Related Configuration
MySQL Database Creation
Using Client Programs for MySQL DBA Work
Setting up Character Set Support
Locking
Using Storage Engines, including the MyISAM and InnoDB engines
Table Maintenance
Obtaining Data from the Information_Schema Database
Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport
Using Stored Procedures and Triggers for Database Administration Tasks
User Management
Securing the Server
Upgrade-Related Security Issues
Optimizing Queries
Optimizing Schemas
Optimizing the Server
The Event Scheduler
Partitioned Tables
Interpreting Error Messages
Optimizing the MySQL Environment
Scaling MySQL, including Replication

Who will the Course Benefit?
Anyone who needs to administer, monitor and support MySQL databases and servers.

MySQL Database Administration Training Course
Course Contents - DAY 1
Course Introduction
Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions
Session 1: CLIENT PROGRAMS FOR DBA WORK
Introduction
Client Program Limitations
mysql
MySQL Admin
Exercises: Using Client Programs
Session 2: THE INFORMATION_SCHEMA DATABASE
What is Metadata?
The mysqlshow Utility
The SHOW and DESCRIBE Commands
The Information_Schema Database
Exercises: Obtaining Information about MySQL
Session 3: MYSQL ADMINISTRATOR
Installation
Connecting
Server Information
Service Control
User Administration
Privileges
Health
Backup and Restore
Catalogs
Exercises: Using the MySQL Administrator Tool
Session 4: INSTALLING, CONFIGURING, STARTING AND STOPPING
MySQL Distributions
Installing on Windows
Installing on Linux and UNIX
Starting and Stopping on Windows
Starting and Stopping on UNIX/Linux
Configuration
Log and Status Files
The Default SQL Mode
Time Zone Tables
Some Security Issues
Upgrading
Exercises: Installing, configuring, stopping and starting
Session 5: INTERPRETING ERROR AND DIAGNOSTIC INFORMATION
MySQL Error Messages
The SHOW Statement
SQL Modes
The PERROR Utility
The Log
The Error Log
The Slow Query Log
Exercises: Interpreting Error and Diagnostic Information


--------------------------------------------------------------------------------

MySQL Database Administration Training Course
Course Contents - DAY 2
Session 6: MYSQL ARCHITECTURE
Client/Server Overview
Communication Protocols
The SQL Parser and Storage Engine Tiers
How MySQL Uses Disk Space
How MYSQL Uses Memory
Exercises: Examining the Architecture
Session 7: TABLES, DATA TYPES AND CHARACTER SET SUPPORT
Table Properties
Creating Tables
Altering Tables
Dropping Tables
Emptying Tables
Obtaining Table Metadata
Column Attributes
Bit Data Type
Numeric Data Types
Character String Data Types
Binary String Data Types
Enum and Set Data Types
Temporal Data Types
Auto_Increment
Handling Missing or Invalid Data Values
Performance Issues with Character sets
Choosing Data Types for Character Columns
Exercises: Creating and Maintaining Tables
Session 8: LOCKING
Locking Concepts
Explicit Table Locking
Advisory Locking
Preventing Locking Problems
Exercises: Locking
Session 9: STORAGE ENGINES
Introduction
The MYISAM Engine
Locking with MYISAM Tables
The Merge Engine
Other Engines: Archive, Memory, Federated, Blackhole, NDBCluster
Exercises Using Storage Engines


--------------------------------------------------------------------------------

MySQL Database Administration Training Course
Course Contents - DAY 3
Session 10: THE INNODB ENGINE
Introduction
Features of Innodb
Transactions
Referential Integrity
Physical Characteristics of Innodb Tables
Tablespace Configuration
Log File and Buffer Configuration
Innodb Status
Exercises Using the InnoDB Engine
Session 11: TABLE MAINTENANCE
Table Maintenance Operations
Check Table
Repair Table
Analyze Table
Optimize Table
MySQL Check
MYISAMCHK
Repairing Innodb Tables
Enabling MYISAM AutoRepair
Exercises: Maintaining Tables
Session 12: BACKUP AND RECOVERY
Planning and Implementing a Backup and Recovery Strategy
Defining a Disaster Recovery Plan
Testing a Backup and Recovery Plan
The Advantages and Disadvantages of Different Methods
Binary Backups of MYISAM Tables
Binary Backups of Innodb Tables
Recovery
Import and Export Operations
Exporting Using SQL
Importing Using SQL
Exporting from the Command Line using mysqldump
Importing from the Command Line using mysqlimport
Exercises: Backing up and Recovery
Session 13: USER MANAGEMENT
Introduction
User Accounts
Creating Users
Renaming Users
Changing Passwords
Dropping Users
Granting Privileges
The User Table
Connection Validation
Exercises: Creating, Managing and Dropping Users


--------------------------------------------------------------------------------

MySQL Database Administration Training Course
Course Contents - DAY 4
Session 14: PRIVILEGES
Introduction
Types of Privileges
Revoking Privileges
Resource Limits
The MySQL Database
The Show Grants Command
Exercises: Granting and Revoking Privileges
Session 15: USER VARIABLES AND PREPARED STATEMENTS
User Variables
Prepared Statements
Exercises: User Variables and Prepared Statements
Session 16: STORED ROUTINES FOR ADMINISTRATION
Types of Stored Routines
Benefits of Stored Routines
Stored Routines Features
Stored Routine Maintenance
Stored Routine Privileges and Execution Security
Exercises: Creating and Using Stored Routine
Session 17: TRIGGERS
DML Triggers
The Create Trigger Statement
Managing Triggers
Exercises: Creating and Using Triggers


--------------------------------------------------------------------------------

MySQL Database Administration Training Course
Course Contents - DAY 4 ( continued)
Session 18: SECURING THE SERVER
Security Issues
Operating System Security
Filesystem Security
Log Files and Security
Network Security
Upgrade-related Security Issues
Upgrading the Privilege Tables
Security-Related SQL_Mode Values
Exercises: Securing the Server
Session 19: OPTIMIZING QUERIES
Optimization Overview
Optimization Process
Planning a Routine Monitoring Regime
Setting Suitable Goals
Identifying Candidates for Query Analysis
Using Explain to Analyze Queries
Meaning of Explain Output
Using Explain Extended
Exercises: Explaining and Optimizing Queries
Session 20: OPTIMIZATION AND INDEXES
Indexes for Performance
Creating and Dropping Indexes
Obtaining Index Metadata
Indexing Principles
Indexing and Joins
MyIsam Index Caching
Exercises: Using Indexes for Optimization
Session 21: OPTIMIZING SCHEMAS
Normalisation
General Table Optimizations
Myisam Specific Optimizations
Innodb Specific Optimizations
Other Engine Specific Optimizations
Exercises: Optimizing Schemas
Session 22: OPTIMIZING THE SERVER
Measuring Server Load
System Factors
Server Parameters
Query Optimizer Performance
The Query Cache
Exercises: Optimizing the Server


--------------------------------------------------------------------------------

MySQL Database Administration Training Course
Course Contents - DAY 5
Session 23: OPTIMIZING THE ENVIRONMENT
Choosing the Platform
Hardware Configurations
Disk Issues on Linux
Symbolic Links
Optimizing the Operating System
Exercises: Optimizing the Environment
Session 24: THE EVENT SCHEDULER
Event scheduler concepts
Event scheduler configuration
Creating, altering and dropping events
Event scheduler monitoring
Events and privileges
Exercises: Using the event scheduler
Session 25: PARTITIONED TABLES
Partitioned tables concepts
Range partitioning
Hash partitioning
Key partitioning
List partitioning
Composite partitioning or subpartitioning
Maintenance of partitioned tables
Exercises: Using partitioned tables
Session 26: SCALING MYSQL
Using Multiple Servers
Replication
Exercises: Using Replication

StayAhead Training established 1992, centres throughout the UK...... with a team of full time dedicated professional training consultants......


StayAhead Training has been established since 1992 and is one of the UK's leading independent providers of Oracle, Unix, Linux, MySQL, Java, PHP Web Deveopment, ITIL & Business Analysis training.

Scheduled courses are available in 6 centres throughout the UK including London, Birmingham, Manchester, Bristol, Leeds and Edinburgh. We also provide training on our client's premises, this training can be tailored to the specific needs of the client.

Our courses have been designed to ensure that people can fulfil their job role and also help prepare delegates for certification in Oracle, Red Hat, Solaris, MySQL, Linux, SUSE Linux, Java, ITIL, Business Analysis and HP-UX .

Working in association with both the Oracle (OUG) and Hewlett Packard (HPUG) User Groups, StayAhead Training have a reputation of providing high quality value for money training at a cost effective price.

Our quality is assured by a team of dedicated professional training consultants who work full time for StayAhead Training and who blend their training skills with a wealth of hands-on experience.