MySQL Database Administration

Prerequisites:

A working knowledge of MySQL is required. This can be gained by attendance on the Introduction to MySQL course.

Course Description:

This MySQL Database Administration course is designed for MySQL Database Administrators who have a basic understanding of a MySQL database and SQL commands. The course provides practical experience in setting up and maintaining a MySQL server, including backing up, recovery, configuration and optimization.

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

This course includes the following modules:

Client Programs for DBA Work

• 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

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

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

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 aand 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
• Exercises: Using Replication