DURATION
5 Days
Introduction
Is your MySQL database secure? Do your users have the correct privileges and access rights?
Is your MySQL database operating at optimal efficiency? Do you want to increase the performance of your database and make sure it scales?
Do you want to save time using features like Stored Procedures, Triggers and Views?
Do you want to utilize the new enterprise features of MySQL 5.0?
Then MySQL for DBAs is the class for you! An Authorized MySQL instructor will teach you how to properly install MySQL, create and execute Backup Strategies, create secure Stored Procedures to update and access data and more!
This instructor led, hands-on class will teach you how to secure users privileges, set resource limitations, and access controls. In addition, you will learn multiple new 5.0 features such as creating and using Stored Procedures, Triggers and Views.
Audience
This course is intended for DBAs and developers who want to administer MySQL
At Course Completion
After completing this course, students will be able to:
Describe the MySQL Architecture, general operational characteristics and resources utilized for running it.
Perform the most common Database Administration (DBA) tasks utilizing the various programs contained within the MySQL application
Utilize the MySQL Administrator Graphical User Interface (GUI) to manage a MySQL server
Use the INFORMATION_SCHEMA database to access metadata
Install and Upgrade MySQL 5.0 for the most common operating systems
Perform the MySQL start and shutdown operations
Configure MySQL server options at runtime
Activate and manage numerous server logs
Evaluate data types and character sets for performance issues
Understand data locking concepts and the different levels of locking in MySQL
Differentiate between the multiple storage engines available in MySQL
Create, Maintain and Manage MySQL tables
Perform backup and restore operations utilizing multiple MySQL tools
Manage user maintenance and access to a MySQL Server
Maintain integrity of a MySQL installation utilizing security protocols
Use stored routines and triggers for administration tasks
Manage, apply and understand the reason for using views
Improve performance through query optimization
Optimize Schemas (Databases) utilizing multiple techniques
Utilize MySQL monitoring tools to improve server performance
Compare multiple environment options to improve server performance
Scale MySQL operations up
Prerequisites
Before attending this course, students must have met at least one of the following prerequisites:
Some experience with Relational Databases and SQL.
MySQL 5.0 Database Administrator Certification Exams
Combined with sufficient practical experience, this course prepares for the MySQL 5.0 Database Administrator Certification Exams
Course Materials
Original courseware will be provided by MySQL AB.
Notepad and pencils will be provided
Module 1: Introduction
This module gives an introduction to MySQL products and related topics.
Module Objectives
- - MySQL Products
- - MySQL Services
- - Community vs. Enterprise
- - Supported Operating Services
- - MySQL Certification Program
- - Installing MySQL
Module 2: MySQL Architecture
This module gives students the opportunity learn followings
- - MySQL Architecture Overview
- - Client/Server Architecture
- - How MySQL Uses Disk Space
- - How MySQL Uses Memory
- - The MySQL Plug-In Interface
Module 3: The MySQL Server
- - Types of MySQL Distributions
- - Starting and Stopping MySQL on Windows
- - Starting and Stopping MySQL on Unix
- - Instance Manager
- - MySQL Configuration
- - Dynamic Server Variables
- - Server SQL Modes
- - Log and Status Files
- - Binary Logging
- - Upgrading MySQL
Module 4: MySQL Clients
- - Overview of Administrative Clients
- - Invoking MySQL Client Programs
- - Using the mysql ,mysqladmin Client
- - The MySQL Administrator GUI
- - MySQL Query Browser
- - MySQL Connectors
- - Third-Party API's
Module 5: Overview of Data Types
.75
- - Data Types
- - Numeric Data Types, Character String Data Types
- - Binary String Data Types
- - Temporal Data Types
- - NULLs
- - Column Attributes
Module 6: Obtaining Metadata
- - Metadata Access Methods
- - The INFORMATION_SCHEMA Database/Schema
- - Using SHOW and DESCRIBE
- - The mysqlshow Command
Module 7: Locking and Transactions
- - Locking Concepts
- - Explicit Locking
- - Advisory Locking
- - Transactions
- - Transaction Commands
- - Isolation Levels
Module 8: Storage Engines
- - Storage Engine Overview
- - MyISAM Storage Engine
- - InnoDB Storage Engine
- - MEMORY Storage Engine
- - Other Storage Engines
- - Choosing Appropriate Storage Engines
- - Using Multiple Storage Engines
5.5
Module 9: Partitioning
- - Partitioning Overview
- - Reasons for Partitioning
- - Creating a Partitioned Table
- - Obtaining Partition Information
- - Modifying Partitions
- - Removing Partitions
- - Partition Modification Effects
- - Partition Pruning
- - Storage Engine Partition Information
- - Partitioning and Locking
- - Partitioning Limitations
Module 10: Table Maintenance
- - Table Maintenance
- - SQL Statements
- - Client and Utility Programs
- - Table Maintenance per Storage Engine
Module 11: Exporting and Importing Data
- - Exporting and Importing Data
- - Exporting and Importing Data Using SQL
- - Import Data with the SQL scripts
Module 12: Data Back-Up and Recovery
- - Reasons to Backup MySQL Data
- - Planning for Recovery Backup
- - Introduction to MySQL Backup Types
- - Backup Tools Overview
- - Making Raw Backups
- - Making Logical (Text) Backups
- - Backup Log and Status Files
- - Replication as an Aid to Backup
- - Backup Method Summary
- - Data Recovery
5 Module 13: Stored Routines
- - What is a Stored Routine , Creating Stored Routines, Compound Statements , Assign Variables , Parameter Declarations
- - Execute Stored Routines , Stored Routine Characteristics, Examine Stored Routines, Delete Stored Routines
- - Flow Control Statements
- - Declare and Use Handlers
- - Cursors
- - Stored Routine Privileges and Security
- - Backing up Stored Routines
5 Module 14: Triggers and Events
- - Triggers , Events
Module 15: Security and User Management
- - Security Risks , Security Measures
- - Privileges
- - Access Level 1 - User Accounts
- - Access Level 2 – Databases
- - Access Level 3 – Tables
- - Access Level 4 - Columns
- - Access Level 5 - Procedures
- - User Account Maintenance
- - Client Access Control
- - Using Secure Connections
Module 16: Views
- - Why Use Views?
- - Creating Views
- - Updatable Views
- - Managing Views
5.25
Module 17: Query Performance
- - Query Optimization
- - Using Indexes for Optimization
- - Identifying Candidates for Query Analysis
-Using EXPLAIN to Analyze Queries
Module 18: Optimizing Databases
- - General Table Optimizations
- - Normalization
- - MyISAM-Specific Optimizations
-InnoDB-Specific Optimizations
-MEMORY-Specific Optimizations
Module 19: Server Optimization
- - Setting and Interpreting MySQL Server Variables
- - Measuring Server Load
- - Tuning Memory Parameters
- - Using the Query Cache 0.5 4 4 4 4
Module 20: Managing Multiple Servers
- - Using Multiple Servers
- - MySQL Replication
- - MySQL Cluster Replication
Programme scope, delivery arrangements and suitability are confirmed during enquiry.