First Technology Transfer

Standard and Advanced Technical Training, Consultancy and Mentoring

DB587 MySQL Database Administration

Duration: 5 Days

Intended Audience

This course is for those who will be controlling and administering MySQL Database servers. Attendees must have a good basic understanding of SQL and using it on a MySQL database. Some knowledge and experience of designing databases would be useful but is not absolutely necessary, as the relevant details will be covered.

Synopsis and Course Objectives

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

Objectives

The course aims to teach the skills necessary to administer, monitor and support MySQL databases and servers. As well as covering theory and concepts the course will include practical work that will involve performing the following tasks that are needed to perform the duties of database administrator

  • Using Client Programs for MySQL DBA Work
  • Obtaining and Interpreting Metadata
  • Configuring the Server both from the database and the operating system perspective
  • Server Installation, Administration and Upgrading
  • Setting up Character Set Support
  • Configuring Log Files
  • Understanding the causes of and Interpreting Error Messages
  • Creating, Altering and Dropping Databases and Tables
  • Locking and Transactions and dealing with some of the problems encountered in this context.
  • Using Storage Engines, including the MyISAM and InnoDB engines
  • Table Maintenance
  • Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport)
  • Administering Users and Privileges
  • Using Stored Procedures and Triggers for Database Administration Tasks
  • Securing the Server
  • Tuning Queries
  • Tuning Tables and Indexes
  • Tuning the Server
  • Using the Event Scheduler
  • Creating Partitioned Tables
  • Replication
  • Working with MySQL Workbench

Course Outline

  • The roles and responsibilities of a Database Administrator
    • Database performance and tuning expertise
    • Database design expertise
    • Database backup and recovery expertise
    • Database problem troubleshooting expertise
    • Database administration expertise
    • Advisor and mentor for both developers and users
  • MySQL Clients and Tools
    • Introduction
    • MySQL
    • MySQL Admin
    • Database administration using client programs

    Obtaining Metadata

    • The need for Metadata
    • The mysqlshow Client Program
    • The SHOW and DESCRIBE Commands
    • The Information_Schema
    • Show Command and Information_Schema Differences
  • MySQL Database Server Configuration
    • MySQL Server Options and Variables
    • MySQL Status Variables
    • Configuring MySQL Enterprise Audit
  • MySQL Server Installation and Configuration
    • MySQL Distributions
    • Installing MySQL on Windows
    • Installing MySQL on Linux and MacOSX
    • Starting and Stopping MySQL on Windows
    • Starting and Stopping MySQL on Linux and MacOSX
    • Status Files
    • Upgrading the Server
    • Time Zone Tables
  • SQL Modes, Log Files and Binary Logging
    • MySQL Error Messages
    • The SHOW Errors Statement
    • The SHOW Warnings Statement
    • SQL Modes
    • Note Messages
    • The PERROR Utility
    • The General Log
    • The Error Log
    • The Slow Query Log
    • The Binary Logs
    • Interpreting Errors and Configuring the Logs
  • MySQL Architecture
    • Client/Server Overview
    • Communication Protocols
    • The SQL Parser and Storage Engine Tiers
    • How MySQL Uses Disk Space
    • How MYSQL Uses Memory
  • 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
    • Maintaining Tables
  • Transactions and Locking
    • The concept of a Lock
    • Atomic operations on a Lock
    • Granularity of Locking
    • Explicit Table Locking
    • Advisory Locking
  • Storage Engines
    • Introduction
    • The MYISAM Engine
    • The Merge Engine
    • Other Engines: Archive, Memory, Federated, Blackhole, CSV
    • Cluster Engine Overview
    • Overview of High Availability Techniques
    • Memcached and NoSQL - An Overview
  • The Innodb Engine
    • History of Innodb
    • Features of Innodb
    • Transactions
    • Referential Integrity
    • Physical Characteristics of Innodb Tables
    • System Tablespace Configuration
    • Log File and Buffer Configuration
    • Innodb Status
  • Table Maintenance
    • Table Maintenance Operations
    • Check Table
    • Repair Table
    • Analyze Table
    • Optimize Table
    • MySQL Check
    • MYISAMCHK
    • Repairing Innodb Tables
    • Enabling MYISAM AutoRepair
  • Backup and Recovery
    • Approaches and Strategies - Pros and Cons
    • 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
  • User Management
    • Introduction
    • User Accounts
    • Creating Users
    • Renaming Users
    • Changing Passwords
    • Dropping Users
    • Granting Privileges
    • The User Table
    • Connection Validation
  • Privileges
    • Users and Roles
    • Types of Privileges
    • Revoking Privileges
    • Resource Limits
    • The MySQL Database
    • The Show Grants Command
  • User variables and Prepared Statements
    • User Variables
    • Prepared Statements
  • Stored Routines for Administration Purposes
    • Types of Stored Routines
    • Benefits of Stored Routines
    • Stored Routines Features
    • Stored Routine Maintenance
    • Obtaining Stored Routine Metadata
    • Stored Routine Privileges and Execution Security
  • Triggers
    • DML Triggers
  • Security
    • Security Risks
    • Users, Operating System, File System and Network Security
    • Using SSL With MySQL
    • Remote Connecting to the MySQL Server Using SSH
    • Overview of SQL Injection Attacks
  • Tuning Queries
    • Tuning Overview
    • Identifying Candidates for Query Analysis
    • Using Explain to Analyze Queries
    • Meaning of Explain Output
    • Explain Extended
  • Tuning and Indexes
    • Indexes for Performance
    • Creating and Dropping Indexes
    • Obtaining Index Metadata
    • Indexing Principles
  • Tuning and Indexes - More Advanced Topics
    • Indexing and Joins
    • Fulltext Indexes
    • MyISAM Index Caching
  • Tuning and Tables
    • General Table Optimizations
    • Myisam Specific Optimizations
    • Innodb Specific Optimizations
    • Other Engine Specific Optimizations
    • Practical Strategies for Tuning Tables
  • Tuning the Server
    • Status Variables
    • Server Variables
    • Performance Schema Overview
    • The Query Cache
    • Practical strategies and heuristics for Server Tuning
  • The Event Scheduler
    • Event scheduler concepts
    • Event scheduler configuration
    • Creating, altering and dropping events
    • Event scheduler monitoring
    • Events and privileges
  • Partitioned Tables
    • Partitioned tables concepts
    • Obtaining Partitioned Table Metadata
    • Types of Partitioning
    • Subpartitioning
    • Maintenance of partitioned tables
  • Replication
    • Replication - An Overview
    • Testing Replication
    • Monitoring and Troubleshooting Replication
    • Files and Threads Involved in Replication
    • Excluding Databases or Tables From Replication
    • Complex Replication Topologies
    • Example: Setting Up a Master Slave Replication
    • Replication Using GTIDs
    • MySQL Replication Utilities
    • Controlled Switchover
    • Setting Up, Testing and Troubleshooting Replication
  • MySQL Workbench
    • Installation
    • Connecting
    • Screens