MySQL Administration Training Course

Introduction

• MySQL Overview, Products, and Services
• MySQL Services and Support Ecosystem
• Supported Operating Systems
• Training Curriculum Paths
• MySQL Documentation Resources

MySQL Architecture

• The client/server model
• Communication protocols
• The SQL Layer
• The Storage Layer
• Server support for storage engines
• Memory and disk space utilization in MySQL
• The MySQL plug-in interface

System Administration

• Selecting the appropriate MySQL distribution
• Installing the MySQL Server
• MySQL Server installation file structure
• Starting and stopping the MySQL server
• Upgrading MySQL versions
• Running multiple MySQL instances on a single host

Server Configuration

• MySQL server configuration options
• System variables
• SQL Modes
• Available log files
• Binary logging mechanisms

Clients and Tools

• Clients available for administrative tasks
• MySQL administrative clients
• The mysql command-line client
• The mysqladmin command-line client
• The MySQL Workbench graphical interface
• Essential MySQL tools
• Available APIs (drivers and connectors)

Data Types

• Primary categories of data types
• Understanding the NULL value
• Column attributes
• Character set implementation with data types
• Selecting appropriate data types

Obtaining Metadata

• Methods for accessing metadata
• Structure of INFORMATION_SCHEMA
• Commands for viewing metadata
• Distinctions between SHOW statements and INFORMATION_SCHEMA tables
• The mysqlshow client program
• Leveraging INFORMATION_SCHEMA queries for shell commands and SQL statements

Transactions and Locking

• Transaction control statements for concurrent SQL execution
• ACID properties of transactions
• Transaction isolation levels
• Using locking mechanisms to secure transactions

Storage Engines

• Overview of MySQL storage engines
• The InnoDB storage engine
• InnoDB system and file-per-table tablespaces
• NoSQL integration and the Memcached API
• Efficient configuration of tablespaces
• Implementing foreign keys for referential integrity
• InnoDB locking mechanisms
• Features of various storage engines

Partitioning

• Concepts and application of partitioning in MySQL
• Benefits of using partitioning
• Different types of partitioning
• Creating partitioned tables
• Subpartitioning techniques
• Retrieving partition metadata
• Modifying partitions to enhance performance
• Storage Engine support for partitioning

User Management

• Requirements for user authentication
• Monitoring active threads using SHOW PROCESSLIST
• Creating, modifying, and removing user accounts
• Alternative authentication plugins
• Requirements for user authorization
• Hierarchical access privileges
• Categorization of privileges
• Granting, modifying, and revoking user privileges

Security

• Identifying common security risks
• MySQL-specific security vulnerabilities
• Countermeasures for network, OS, filesystem, and user security
• Data protection strategies
• Implementing SSL for secure MySQL connections
• Using SSH for secure remote access
• Resources for resolving common security issues

Table Maintenance

• Categories of table maintenance operations
• SQL statements for maintaining tables
• Client and utility programs for maintenance
• Maintaining tables across different storage engines
• Exporting and Importing Data
• Processes for Data Export
• Processes for Data Import

Programming Inside MySQL

• Creating and executing Stored Routines
• Security implications of stored routine execution
• Creating and executing triggers
• Managing events (create, alter, drop)
• Scheduling event execution

MySQL Backup and Recovery

• Fundamentals of backup
• Different backup types
• Backup tools and utilities
• Creating binary and text backups
• The role of log and status files in backup processes
• Data Recovery procedures

Replication

• Managing the MySQL Binary Log
• MySQL replication threads and associated files
• Establishing a MySQL Replication Environment
• Designing Complex Replication Topologies
• Multi-Master and Circular Replication setups
• Executing a Controlled Switchover
• Monitoring and troubleshooting MySQL Replication
• Replication utilizing Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Query analysis using EXPLAIN
• General table optimization techniques
• Monitoring status variables impacting performance
• Configuring and interpreting MySQL server Variables
• Overview of the Performance Schema

Conclusion

Q&A Session