MySQL Administration Training Course

Introduction

• Overview of MySQL Ecosystem, Products, and Services
• MySQL Service and Support Frameworks
• Supported Operating System Environments
• Professional Training Curriculum Pathways
• Access to Official MySQL Documentation Resources

MySQL Architecture

• The Client-Server Architectural Model
• Network Communication Protocols
• Structure of the SQL Processing Layer
• Organization of the Storage Layer
• Server Mechanisms for Supporting Storage Engines
• Memory and Disk Space Utilization Strategies
• The MySQL Plug-in Architecture Interface

System Administration

• Selection Criteria for MySQL Distribution Types
• Procedures for Installing the MySQL Server
• Analysis of MySQL Server File System Structure
• Processes for Initiating and Terminating MySQL Services
• Methodologies for Upgrading MySQL Versions
• Deployment of Multiple MySQL Instances on a Single Host

Server Configuration

• Configuration Parameters for the MySQL Server
• Management of System Variables
• Definition and Application of SQL Modes
• Catalog of Available Log Files
• Implementation of Binary Logging

Clients and Tools

• Client Utilities Available for Administrative Tasks
• Core MySQL Administrative Clients
• Functionality of the mysql Command-Line Interface
• Functionality of the mysqladmin Command-Line Interface
• Use of MySQL Workbench as a Graphical Interface
• Integrated MySQL Utility Tools
• Available Application Programming Interfaces (Drivers and Connectors)

Data Types

• Primary Categories of Data Types
• Semantics and Implications of NULL Values
• Definition of Column Attributes
• Integration of Character Sets with Data Types
• Criteria for Selecting Appropriate Data Types

Obtaining Metadata

• Available Methods for Metadata Access
• Structural Components of INFORMATION_SCHEMA
• Commands for Viewing Metadata Details
• Comparative Analysis of SHOW Statements and INFORMATION_SCHEMA Tables
• Utilization of the mysqlshow Client Program
• Generating Shell Commands and SQL Statements from INFORMATION_SCHEMA Queries

Transactions and Locking

• Application of Transaction Control Statements for Concurrent SQL Execution
• ACID Properties Governing Transaction Integrity
• Definitions of Transaction Isolation Levels
• Use of Locking Mechanisms to Safeguard Transactions

Storage Engines

• Overview of Storage Engines in MySQL
• Specifications of the InnoDB Storage Engine
• InnoDB System Tablespaces and File-Per-Table Configurations
• Integration of NoSQL Features and the Memcached API
• Strategies for Efficient Tablespace Configuration
• Use of Foreign Keys to Ensure Referential Integrity
• InnoDB Locking Behaviors
• Characteristics of Available Storage Engine Options

Partitioning

• Concept of Partitioning and Its Application in MySQL
• Rationale for Implementing Table Partitioning
• Classification of Partitioning Types
• Procedures for Creating Partitioned Tables
• Implementation of Subpartitioning
• Methods for Retrieving Partition Metadata
• Modification of Partitions to Enhance Performance

User Management

• Standards for User Authentication Requirements
• Use of SHOW PROCESSLIST to Monitor Active Threads
• Processes for Creating, Modifying, and Removing User Accounts
• Integration of Alternative Authentication Plugins
• Standards for User Authorization Requirements
• Hierarchical Levels of User Access Privileges
• Classification of Privilege Types
• Processes for Granting, Modifying, and Revoking Privileges

Security

• Identification of Common Security Threats
• Security Risks Specific to MySQL Deployments
• Countermeasures for Network, Operating System, Filesystem, and User Security
• Strategies for Data Protection
• Implementation of SSL for Secure Server Connections
• Use of SSH for Secure Remote Access to the MySQL Server
• Resources for Investigating Common Security Issues

Table Maintenance

• Categorization of Table Maintenance Operations
• SQL Commands for Performing Table Maintenance
• Client and Utility Programs for Table Maintenance
• Maintenance Procedures for Non-InnoDB Storage Engines
• Data Export and Import Protocols
• Procedures for Data Export
• Procedures for Data Import

Programming Inside MySQL

• Development and Execution of Stored Routines
• Security Considerations for Stored Routine Execution
• Creation and Implementation of Triggers
• Creation, Modification, and Deletion of Events
• Scheduling Mechanisms for Event Execution

MySQL Backup and Recovery

• Fundamentals of Database Backup
• Classification of Backup Types
• Available Backup Tools and Utilities
• Creation of Binary and Text-Based Backups
• Role of Log and Status Files in Backup Integrity
• Procedures for Data Recovery

Replication

• Management of the MySQL Binary Log
• Components of MySQL Replication Threads and Files
• Establishment of a MySQL Replication Environment
• Design of Complex Replication Topologies
• Implementation of Multi-Master and Circular Replication
• Execution of Controlled Failover and Switchover
• Monitoring and Troubleshooting of MySQL Replication
• Replication Using Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Use of EXPLAIN for Query Analysis
• General Optimization Strategies for Tables
• Monitoring of Status Variables Affecting Performance
• Configuration and Interpretation of MySQL Server Variables
• Overview of the Performance Schema

Conclusion

Q&A Session