MySQL Administration and Performance Tuning

• Understand MySQL Architecture
• Understand The Use Of Storage Engines
• Configure Network And Security Elements
• Learn MySQL Management Concepts Regrading Users / System Variables
• Learn Configuration File Best Practices (my.cnf)
• Learn MySQL Administration Using MySQL Built-In Tools
• Understand MySQL Locks Mechanism
• Perform Backup And Restore Operations
• Creating and Managing Master-Slave Replication Environment
• Improve And Optimize database performance And Enhance Reliability
• Perform Installations And Upgrades Tasks Step By Step
• Practicing In MySQL Development Using SQL Code, Procedures, Built-In and User Defined Functions And Events (Optional)

DevOps, Database Administrators, System Administrators, Web Application Developers, Architecture Designers

• Linux Basic Knowledge
• ANSI SQL Basic Knowledge
• RDBMS Basic Understanding

General Overview and Basic Elements

History and Introduction

• Overview
• Real Examples and Use Cases of Customers Using MySQL Database

Architecture Overview

• Sever
• Client

Storage Engines

• Engines Types
• Engines Characteristics
• InnoDB and MyISAM Extensive Overview

Networking and Security

• Operating System
• File System Security
• Network Security

MySQL with Linux Overview

• Basic Commands

Installation (Linux Only)

• Linux RPM Installation
• Linux Binary Installation
• Source Installation

Users Management

• User Account Management
• User Privileges
• Administrative Privileges
• Database Access Privileges

Database Management / Tables Maintenance / Backup & Recovery / High Availability Solutions

Database Management

• System Variables
• Altering Database Variables at Session / Global Level

Configuration File Best Practices (my.cnf)

• InnoDB Memory Buffers
• Log file size and log buffer
• Memory Consumption
• Memory Limitations

Using MySQL Built-In Tools

• MySQLAdmin
• MySQLBinlog
• MySQLDump
• MySQLImport

Table Maintenance

• Analyze Table
• Check Table
• Checksum Table
• Optimize Table
• Repair Table
• Moving Tables between Schemas / Servers / Tablespaces

Locking in MySQL

• MySQL Locking Overview
• Table Level Locking
• Row Level Locking
• Internal / External Locking
• Dead Locks

Backup and Recovery

• MySQL Backup
• MySQL Backup Types
• MySQL Backup and Recovery Tools
• Load data infile & select into outfile

Basic Replication

• Replication Architecture
• Replication Basics
• Replication Types
• Master-Slave Replication Setup
• Master-Slave Replication Filters

MariaDB and Galera Replication Architecture

• Master-Master Replication

Upgrading

• Pre Upgrade Steps
• Upgrade Steps and Check List
• Post Upgrade Commands

Database Monitoring / Performance Tuning Overview and Workshop

Monitoring Database Activity

• Working with Information Schema and Performance Schema

Various Logs in MySQL

• Error Log
• Query Log
• Slow Query Log
• Binary Log
• Relay Log
• General Query Log

Identifying Slow Queries

• Slow-Query-Log
• ProcessList
• InnoDB_TRX
• Using Show Warnings

Optimizing Queries

• Explaining the EXPLAIN
• Server Variables Check

Development

• Basic MySQL Queries
• Join / Sub-Query / Union
• Build-In Functions
• User Defined Functions
• Procedures

 Read more about MySQL Performance Tuning course