MySQL Administration Training Course

Introduction

• Overview of MySQL, including its products and services
• Service and support options available for MySQL
• Supported operating systems and environments
• Recommended training curriculum pathways
• Accessing MySQL documentation and resources

MySQL Architecture

• The client/server model structure
• Communication protocols used by MySQL
• Functionality of the SQL Layer
• Functionality of the Storage Layer
• Mechanisms by which the server integrates storage engines
• Utilization of memory and disk space by MySQL
• The MySQL plug-in interface capabilities

System Administration

• Comparing and selecting different MySQL distributions
• Procedures for installing the MySQL Server
• Understanding the installation file structure of the MySQL Server
• Processes for starting and stopping the MySQL server
• Strategies for upgrading MySQL versions
• Configuration of multiple MySQL servers on a single host

Server Configuration

• Review of MySQL server configuration options
• Management of system variables
• Implementation of SQL Modes
• Identification and management of available log files
• Configuration of binary logging

Clients and Tools

• Available clients for performing administrative tasks
• Use of MySQL administrative clients
• Utilizing the mysql command-line client
• Utilizing the mysqladmin command-line client
• Using the MySQL Workbench graphical interface
• Exploration of other MySQL tools
• Overview of available APIs, including drivers and connectors

Data Types

• Major categories of data types
• Understanding the significance of NULL values
• Definition of column attributes
• Integration of character sets with data types
• Criteria for selecting appropriate data types

Obtaining Metadata

• Methods for accessing metadata
• Structure and contents of INFORMATION_SCHEMA
• Commands used to view and retrieve metadata
• Distinctions between SHOW statements and INFORMATION_SCHEMA tables
• Utilization of the mysqlshow client program
• Leveraging INFORMATION_SCHEMA queries to generate shell commands and SQL statements

Transactions and Locking

• Use of transaction control statements for concurrent SQL execution
• Understanding ACID properties in transactions
• Explanation of transaction isolation levels
• Application of locking mechanisms to protect transactions

Storage Engines

• Overview of storage engines in MySQL
• In-depth look at the InnoDB storage engine
• Management of InnoDB system and file-per-table tablespaces
• Integration of NoSQL concepts and the Memcached API
• Efficient configuration of tablespaces
• Use of foreign keys to ensure referential integrity
• Locking behaviors within InnoDB
• Comparison of features across available storage engines

Partitioning

• Concept of partitioning and its application in MySQL
• Advantages and reasons for using partitioning
• Different types of partitioning strategies
• Process for creating partitioned tables
• Implementation of subpartitioning
• Retrieving partition metadata
• Modifying partitions to enhance performance
• Storage engine compatibility with partitioning features

User Management

• Standards for user authentication
• Use of SHOW PROCESSLIST to monitor active threads
• Operations for creating, modifying, and dropping user accounts
• Exploration of alternative authentication plugins
• Requirements for user authorization
• Hierarchy of access privileges for users
• Categorization of privilege types
• Processes for granting, modifying, and revoking user privileges

Security

• Identification of common security threats
• Security risks specific to MySQL installations
• Mitigation strategies for network, operating system, filesystem, and user-related security issues
• Techniques for protecting data integrity
• Implementation of SSL for secure server connections
• Use of SSH for secure remote access to the MySQL server
• Sources for additional information on common security concerns

Table Maintenance

• Categories of table maintenance operations
• SQL commands used for table upkeep
• Client and utility programs for maintaining tables
• Specific maintenance procedures for alternative storage engines
• Processes for exporting and importing data
• Methods for exporting data
• Methods for importing data

Programming Inside MySQL

• Creation and execution of Stored Routines
• Security considerations for stored routine execution
• Development and deployment of triggers
• Management of events, including creation, alteration, and deletion
• Scheduling of event execution

MySQL Backup and Recovery

• Fundamentals of backup strategies
• Classes of backup methods
• Tools and utilities for performing backups
• Creation of binary and text-based backups
• The function of log and status files in the backup process
• Strategies for data recovery

Replication

• Administration of the MySQL Binary Log
• Understanding replication threads and associated files
• Setup of a basic MySQL Replication Environment
• Design of complex replication topologies
• Implementation of Multi-Master and Circular Replication
• Execution of controlled failover or switchover
• Monitoring and troubleshooting of MySQL Replication
• Replication leveraging Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Analysis of queries using EXPLAIN
• General strategies for table optimization
• Monitoring status variables that impact performance
• Configuration and interpretation of MySQL server variables
• Introduction to Performance Schema

Conclusion

Q&A Session