MySQL Database Administration

Syllabus – Key points

Course Contents – DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: THE SERVER, CLIENTS AND TOOLS

• Introduction
• The versions of MySQL and MariaDB
• An Overview of MySQL Architecture
• MySQL Server
• MySQL Tools
• MySQL Admin
• Exercises: Using Client Programs

Session 2: MYSQL WORKBENCH

• Installation
• Connecting
• Screens
• Exercises: Using MySQL Workbench

Session 3: OBTAINING METADATA

• What is Metadata?
• The mysqlshow Client Program
• The SHOW and DESCRIBE Commands
• The Information_Schema
• Show Command and Information_Schema Differences
• Exercises: Obtaining Information about MySQL

Session 4: MYSQL SERVER INSTALLATION AND CONFIGURATION

• MySQL Distributions
• Installing on Windows
• Installing on Linux and UNIX
• Starting and Stopping on Windows
• Starting and Stopping on UNIX/Linux
• Status Files
• Upgrading the Server
• Time Zone Tables
• Exercises: Installing, stopping and starting

Session 5: CONFIGURING THE SERVER

• MySQL Server Options and Variables
• MySQL Status Variables
• Configuring MySQL Enterprise Audit
• Exercises: Configuring the Server

Session 6: 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
• Exercises: Interpreting Errors and Configuring the Logs

Course Contents – DAY 2

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

Session 8: TRANSACTIONS AND LOCKING

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

Session 9: THE INNODB STORAGE ENGINE

• Introduction
• Features of Innodb
• Transactions
• Referential Integrity
• Physical Characteristics of Innodb Tables
• System Tablespace Configuration
• Log File and Buffer Configuration
• Innodb Status
• Exercises: Using the InnoDB Engine

Session 10: OTHER STORAGE ENGINES

• Introduction
• The MYISAM Engine
• The Merge Engine
• Other Engines: Archive, Aria, Memory, Blackhole, CSV
• Cluster Engine Overview
• Overview of the ColumnStore, MyRocks and Spider Engines
• Exercises: Using Storage Engines

Session 11: TABLE MAINTENANCE

• Table Maintenance Operations
• Check Table
• Repair Table
• Analyze Table
• Optimize Table
• MySQL Check
• MyISAM Table Maintenance and Repair Utilitiesli>
• Exercises: Maintaining Tables

Course Contents – DAY 3

Session 12: BACKUP AND RECOVERY

• Designing a Backup and Recovery Strategy
• The Advantages and Disadvantages of Different Backup Methods
• Binary Backups of MYISAM Tables
• Binary Backups of Innodb Tables
• Recovery
• Import and Export Operations
• Exporting and Importing using SQL
• Exporting from the Command Line using mysqldump
• Importing from the Command Line using mysqlimport
• Additional Backup Tools
• Exercises: Backing Up and Recovery

Session 13: 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

Session 14: PRIVILEGES

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

Session 15: SECURITY AND UPGRADES

• Signs of Poor Security
• Security Risks
• Users, Operating System, File System and Network Security
• Using SSL With MySQL
• Remote Connecting to the MySQL Server Using SSH
• Exercises: Securing the Server

Course Contents – DAY 4

Session 16: TUNING QUERIES

• Tuning Overview
• Identifying Candidates for Query Analysis
• Using Explain to Analyze Queries
• Meaning of Explain Output
• Explain Extended
• Exercises: Explaining and Tuning Queries

Session 17: TUNING AND INDEXES

• Indexes for Performance
• Creating and Dropping Indexes
• Obtaining Index Metadata
• Indexing Principles
• Indexing and Joins
• Fulltext Indexes
• MyISAM Index Caching
• Exercises: Creating and Using Indexes

Session 18: TUNING AND TABLES

• General Table Optimizations
• Myisam Specific Optimizations
• Innodb Specific Optimizations
• Other Engine Specific Optimizations
• Exercises: Tuning Tables

Session 19: TUNING THE SERVER

• Status Variables
• Server Variables
• Performance Schema Overview
• The Query Cache
• Exercises: Tuning the Server

Session 20: 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

Session 21: PARTITIONED TABLES

• Partitioned tables concepts
• Obtaining Partitioned Table Metadata
• Types of Partitioning
• Subpartitioning
• Maintenance of partitioned tables
• Exercises: Using partitioned tables

Course Contents – DAY 5

Session 22: OVERVIEW OF CLUSTER STORAGE ENGINES

• Overview of Clustering and Performance
• NDBCluster Engine Overview
• The Galera Cluster
• The Percona XtraDB Cluster
• MySQL InnoDB Cluster
• The Federated Engine
• The FederatedX Engine
• Overview of Other High Availability Techniques
• NOSQL and MemCached Overview
• Features of Memcached with MySQL

Session 23: OVERVIEW OF HIGH AVAILABILITY

• High Availability Goals
• High Availability Concepts
• Design for High Availability
• Definition of Availability
• High Availability Terminology

Session 24: CONFIGURE MASTER SLAVE REPLICATION

• Replication Overview
• When to Use Replication
• Testing Replication
• Excluding Databases or Tables from Replication
• Example: Setting Up a Master Slave Replication
• Replication Using GTIDs
• Controlled Switchover
• Exercises: Setting Up and Testing Replication and Switchover

Session 25: ADMINISTER A REPLICATION TOPOLOGY

• Failover
• MySQL Replication and Failover Utilities
• Replication Files and Threads
• Monitoring Replication
• Troubleshooting Replication
• Exercises: Failover from Master to Slave

Session 26: GROUP REPLICATION

• Overview of Group Replication
• Single Primary Mode
• Multi-primary Mode
• Configure Group Replication
• Monitor Group Replication