skip to content
Enroll Now
headerbannervision

Course #: 15010e - MySQL Admin

The MySQL Admin course teaches the configuration, security management, administration, backup and restore, and optimization of MySQL databases.


Course Goals:

  •     Understand the MySQL Architecture.
  •     Learn to start and shutdown the MySQL server.
  •     Learn to install MySQL.
  •     Learn to configure MySQL components.
  •     Learn to use different storage engines supported in MySQL.
  •     Learn to secure the elements of a MySQL installation.
  •     Learn to maintain security of a MySQL installation via user management and access rights.
  •     Learn to work with the MySQL Administrator Graphical User Interface.
  •     Learn to perform backup and restore operations using multiple MySQL tools.
  •     Learn to optimize MySQL at various levels - installation, database and queries.
  •     Learn to perform database replication in MySQL.

Course Outline

Introduction to Database Concepts and MySQL

  • Features of a Relational Database
  • Where does SQL Fit in?
  • Database Access
  • Why MySQL?
  • The History of MySQL   

Installation, Configuration, and Upgrading

  • MySQL Software
  • MySQL Software Features
  • Preparing to Install MySQL
  • Available Client Software
  • After the Download
  • Configuring the Server
  • Starting the Server
  • The Initial User Accounts
  • Verifying Server Operation
  • Upgrading
  • Copying a Database Between Architectures
  • Environment Variables

Database Design

  • Developing the Design of a Database
  • Database Entities
  • The Primary Key
  • Foreign Key Relationships
  • Data Models and Normalization
  • Second Normal Form (2NF)
  • Third Normal Form (3NF) and Beyond
  • Translating a Data Model into a Database Design

Using the mysql Command-Line Tool

  • Running the mysql Client
  • Customizing the mysql Prompt
  • mysql Commands
  • Using the Help Command
  • Some Useful mysql Options
  • Working with a Database
  • Examining Table Definitions
  • Other SHOW Options

DDL: Data Definition Language

  • DDL and DML Overview
  • Building Table Definitions
  • Identifiers
  • Column Definitions
  • Numeric Datatypes
  • ENUM and SET Types
  • Date and Time Datatypes
  • AUTO_INCREMENT
  • UNIQUE Constraints
  • Primary Keys
  • Modifying Tables
  • Foreign Keys
  • Renaming and Dropping Tables

MySQL Storage Engines

  • Storage Engine Overview
  • Other Storage Engine Types
  • The Basics of Commonly Used Storage
  • Engines
  • MyISAM Limits and Features
  • MyISAM Data File Format
  • InnoDB and Hardware Limitations
  • InnoDB Shared Tablespace
  • Configuration
  • InnoDB Per-Table Tablespaces
  • InnoDB Data Management
  • MEMORY and FEDERATED
  • MERGE and ARCHIVE

Utilities

  • Client Overview
  • Specifying Options for Command-Line
  • Clients
  • Client Option Files
  • Checking Tables with myisamchk and
  • mysqlchk
  • Using myisamchk and mysqlchk for
  • Repairs
  • mysqlshow and mysqlimport
  • Using mysqldump
  • The Query Browser
  • MySQL Query Browser: Deeper
  • MySQL Administrator: Basic
  • Operations
  • MySQL Administrator: Monitoring the
  • Server and User Administration
  • Third Party Tools

Administering a Database and Users

  • The Server-Side Programs
  • Starting the MySQL Server
  • Using SET for Server Options
  • Table Management
  • Server Log Files
  • mysqladmin
  • Backup and Restore
  • Miscellaneous Functions
  • User Account Management
  • Understanding User Privileges
  • User Account Rights Management
  • User Account Privileges
  • Managing Access to the Database
  • Environment

Database Programmability

  • Stored Routines: Basic Concepts
  • Routine Creation and Use
  • Flow Control Statement
  • Writing Blocks of Code
  • Triggers
  • Stored Routines, Triggers, and the
  • Binary Log
  • Table HANDLERs
  • Prepared Statements

Optimization and Performance

  • Tuning
  • Hardware Limitations
  • Optimizing the MySQL Server's
  • Interaction with the External
  • World
  • Adjusting the MySQL Server
  • Configuration
  • Optimizing Your Database
  • Optimizing Queries
  • The Use of Indexes to Support Queries
  • Thinking about JOIN Queries
  • Query Sorts, Indexes, and ShortCircuiting
  • INSERT, UPDATE, DELETE, and
  • Table Locks
  • Some General Optimizations
  • Optimizations Specific to MyISAM
  • Optimizations Specific to InnoDB

MySQL Programming Interfaces

  • Database Application Architectures
  • Connecting MySQL to ODBC
  • Connecting MySQL to MS/Office and
  • MS/Access
  • Connecting to MySQL from Perl
  • Programming Perl to MySQL
  • Connecting to MySQL from PHP
  • Programming PHP to MySQL

Recommended Prerequisites:

  • There are no mandatory prerequisites. Work experience in IT services is recommended.