MySQL for Database Administrators (Self-Study Course)

 

Prerequisites

  • Attend the MySQL for Beginners course or some experience with Relational Databases and SQL.

Course Objectives

  • Install and Upgrade MySQL for the most common operating systems
  • Utilize the MySQL Administrator Graphical User Interface (GUI) to manage a MySQL server
  • Use the INFORMATION_SCHEMA database to access metadata
  • Perform the MySQL start and shutdown operations
  • Configure MySQL server options at runtime
  • 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
  • Perform backup and restore operations utilizing multiple MySQL tools
  • 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

Product Description

  • Introduction
    • Describing MySQL
    • Listing MySQL Products and Professional Services
    • Describing MySQL Enterprise Subscription
    • Currently Supported Operating Systems
    • Describing MySQL Certification Program
    • Listing Available MySQL Courses
    • Describing Installation of MySQL
    • Describing Installation of world Database
  • MySQL Architecture
    • Client/Server Architecture
    • MySQL Architecture Overview
    • How MySQL Uses Disk Space
    • How MySQL Uses Memory
    • The MySQL Plug-In Interface
  • The MySQL Server
    • Types of MySQL Distributions
    • Starting and Stopping MySQL on Windows
    • Starting and Stopping MySQL on Linux
    • Upgrading MySQL
    • Managing Multiple Servers
  • Configuring the MySQL Server
    • MySQL Configuration
    • Dynamic Server Variables
    • Server SQL Modes
    • Log and Status Files
    • Binary Logging
  • MySQL Clients
    • Overview of Administrative Clients
    • Invoking MySQL Client Programs
    • Using the mysql Client
    • The mysqladmin Client
    • MySQL Connectors
    • Third-Party APIs
  • Overview of Data Types
    • Data Types
    • Numeric Data Types
    • Character String Data Types
    • Binary String Data Types
    • Temporal Data Types
    • NULLs
    • Column Attributes
  • Metadata
    • Metadata Access Methods
    • The INFORMATION_SCHEMA Database/Schema
    • Using SHOW and DESCRIBE
    • The mysqlshow Command
  • Storage Engines
    • Storage Engine Overview
    • MyISAM, InnoDB, and MEMORY Storage Engines
    • Other Storage Engines
    • Choosing Appropriate Storage Engines
    • Using Multiple Storage Engines
    • Storage Engine Comparison Chart
  • Partitioning
    • Overview of Partitioning and reasons for using Partitioning
    • Creating a Partitioned Table
    • Obtaining Partition Information
    • Modifying and Removing Partitions
    • Partition Modification Performance Effects
    • Partition Pruning
    • Storage Engine Partition Information
    • Partitioning and Locking and Limitations
  • Transactions and Locking
    • Transactions
    • Transaction Control statements
    • Isolation Levels
    • Locking
  • Security and User Management
    • Security Risks
    • Privileges
    • Access Levels, including: 1 - User Accounts, 2 – Databases, 3 – Tables, 4 – Columns, 5 - Stored Routines
    • User Account Maintenance
    • Client Access Control
    • Using Secure Connections
  • Table Maintenance
    • Table Maintenance
    • SQL Statements for maintenance operations
    • Client and Utility Programs for table maintenance
    • Table Maintenance per Storage Engine
  • Exporting and Importing Data
    • Exporting and Importing Data
    • Exporting and Importing Data Using SQL
    • Import Data with the SQL scripts
  • Programming with MySQL
    • Defining, Executing and Examining Stored Routines
    • Stored Routines and Execution Security
    • Defining, Creating, and Deleting Triggers
    • Trigger Restrictions and Privileges
    • Defining Events
    • Schedule Events
    • DBA's Use of MySQL Programming
    • Backup Stored Routines
  • Views
    • What is a view?
    • Creating Views
    • Updatable Views
    • Managing Views
  • Backup and Recovery
    • Planning for Recovery Backup
    • Backup Tools Overview
    • Making Raw Backups
    • Making Logical (Text) Backups
    • Backup Log and Status Files
    • Replication as an Aid to Backup
    • Backup Method Comparison
    • Data Recovery
  • Introduction to Performance Tuning
    • Using EXPLAIN to Analyze Queries
    • General Table Optimizations
    • Setting and Interpreting MySQL Server Variables
  • Introduction to High Availability
    • MySQL Replication
  • Conclusion
    • Course Overview
    • Training and Certification Website
    • Course Evaluation
    • Thank You!
    • Q&A Session

Price on request