MySQL Administration Training Course

Introduction

• MySQL Products and Services Overview
• Support and Service Offerings for MySQL
• Compatible Operating Systems
• Structured Training Pathways
• Available Documentation and Resources

MySQL Architecture

• Client/Server Network Model
• Protocol Specifications
• SQL Processing Layer
• Data Storage Layer
• Storage Engine Integration
• Memory Management and Disk I/O Allocation
• Plugin Interface Framework

System Administration

• Evaluating MySQL Distribution Types
• Installing the MySQL Server
• Installation Directory Structure
• Server Start and Stop Procedures
• Version Upgrade Protocols
• Multiplexing Multiple MySQL Instances on a Single Host

Server Configuration

• Configuration Parameters
• System Variable Management
• SQL Mode Settings
• Log File Types and Locations
• Binary Log Configuration

Clients and Tools

• Administrative Client Options
• Dedicated Administrative Clients
• The mysql Command-Line Interface
• The mysqladmin Utility
• MySQL Workbench Graphical User Interface
• Supplementary MySQL Utilities
• Application Programming Interfaces (Drivers and Connectors)

Data Types

• Primary Data Type Categories
• NULL Value Handling
• Column Attribute Definitions
• Character Set Implementation
• Selecting Optimal Data Types

Obtaining Metadata

• Metadata Access Methods
• INFORMATION_SCHEMA Database Structure
• Metadata Retrieval Commands
• Comparison: SHOW Statements vs. INFORMATION_SCHEMA Tables
• The mysqlshow Utility
• Automating Tasks via INFORMATION_SCHEMA Queries

Transactions and Locking

• Transaction Control for Concurrent Operations
• ACID Transaction Properties
• Isolation Level Definitions
• Locking Mechanisms for Data Integrity

Storage Engines

• MySQL Storage Engine Options
• InnoDB Storage Engine Overview
• InnoDB System and File-Per-Table Tablespaces
• NoSQL Capabilities via Memcached API
• Tablespace Optimization Strategies
• Referential Integrity via Foreign Keys
• InnoDB Locking Granularity
• Feature Comparison of Available Engines

Partitioning

• Partitioning Concepts and Applications
• Benefits of Data Partitioning
• Partition Types
• Creating Partitioned Tables
• Subpartition Configuration
• Retrieving Partition Metadata
• Optimizing Performance via Partition Modifications
• Storage Engine Compatibility with Partitioning

User Management

• Authentication Requirements
• Active Thread Monitoring via SHOW PROCESSLIST
• User Account Lifecycle Management (Create, Modify, Drop)
• Alternative Authentication Plugins
• Authorization Frameworks
• Privilege Hierarchy Levels
• Privilege Classifications
• Privilege Granting, Modification, and Revocation

Security

• Identification of Common Security Vulnerabilities
• Installation-Specific Security Risks
• Mitigation Strategies for Network, OS, Filesystem, and User Threats
• Data Protection Measures
• SSL/TLS Implementation for Secure Connections
• SSH Tunneling for Remote Access
• Resources for Addressing Security Incidents

Table Maintenance

• Maintenance Operation Categories
• Maintenance SQL Statements
• Utility Programs for Table Health
• Maintenance Protocols for Non-InnoDB Engines
• Data Export and Import Procedures
• Exporting Data
• Importing Data

Programming Inside MySQL

• Stored Routine Development and Execution
• Security Contexts for Stored Routines
• Trigger Implementation and Execution
• Event Management (Create, Alter, Drop)
• Event Scheduling Mechanisms

MySQL Backup and Recovery

• Backup Fundamentals
• Backup Methodologies
• Backup Utilities and Tools
• Binary and Text-Based Backups
• Log File Roles in Recovery Processes
• Data Recovery Procedures

Replication

• Binary Log Management
• Replication Thread and File Architecture
• Replication Environment Setup
• Complex Topology Design
• Multi-Master and Circular Replication Models
• Controlled Failover Procedures
• Monitoring and Troubleshooting Replication
• Replication Using Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Query Analysis with EXPLAIN
• General Table Optimization Techniques
• Performance-affecting Status Variables Monitoring
• Variable Configuration and Interpretation
• Performance Schema Overview

Conclusion

Q&A Session