MySQL Database Administration Training

MySQL Database Administration Online Training

Course Introduction

  • Administration and Course Materials
  • Course Structure and Agenda
  • Delegate and Trainer Introductions
  • Introduction
  • Client Program Limitations
  • mysql
  • MySQL Admin
  • Exercises: Using Client Programs

The Information_Schema Database

  • What is Metadata?
  • The mysqlshow Utility
  • The SHOW and DESCRIBE Commands
  • The Information_Schema Database
  • Exercises: Obtaining Information about MySQL

Mysql Administrator

  • Installation
  • Connecting
  • Server Information
  • Service Control
  • User Administration
  • Privileges
  • Health
  • Backup and Restore
  • Catalogs
  • Exercises: Using the MySQL Administrator Tool

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

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 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

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

Locking

  • Locking Concepts
  • Explicit Table Locking
  • Advisory Locking
  • Preventing Locking Problems
  • Exercises: Locking

Storage Engines

  • Introduction
  • The MYISAM Engine
  • Locking with MYISAM Tables
  • The Merge Engine
  • Other Engines: Archive, Memory, Federated, Blackhole, NDBCluster
  • Exercises Using Storage Engines

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

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

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

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

Privileges

  • Introduction
  • Types of Privileges
  • Revoking Privileges
  • Resource Limits
  • The MySQL Database
  • The Show Grants Command
  • Exercises: Granting and Revoking Privileges

User Variables and Prepared Statements

  • User Variables
  • Prepared Statements
  • Exercises: User Variables and Prepared Statements

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

Triggers

  • DML Triggers
  • The Create Trigger Statement
  • Managing Triggers
  • Exercises: Creating and Using Triggers

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

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

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

Optimizing Schemas

  • Normalisation
  • General Table Optimizations
  • Myisam Specific Optimizations
  • Innodb Specific Optimizations
  • Other Engine Specific Optimizations
  • Exercises: Optimizing Schemas

Optimizing the Server

  • Measuring Server Load
  • System Factors
  • Server Parameters
  • Query Optimizer Performance
  • The Query Cache
  • Exercises: Optimizing the Server

Optimizing the Environment

  • Choosing the Platform
  • Hardware Configurations
  • Disk Issues on Linux
  • Symbolic Links
  • Optimizing the Operating System
  • Exercises: Optimizing the Environment

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]

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

Scaling Mysql

  • Using Multiple Servers
  • Replication

Send Enquiry

Your Name (required)

Your Email (required)

Your Phone No. (required)

Subject

Your Message