MySQL Administration Training Course

Introduction

• Overview of MySQL, its products, and services
• MySQL service offerings and support options
• Supported operating systems
• Recommended training curriculum paths
• Accessing 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 usage in MySQL
• The MySQL plugin interface

System Administration

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

Server Configuration

• MySQL server configuration parameters
• System variables
• SQL modes
• Available log files
• Binary logging

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
• MySQL utility tools
• Available APIs, drivers, and connectors

Data Types

• Primary categories of data types
• The concept of NULL
• Column attributes
• Character set application with data types
• Selecting appropriate data types

Obtaining Metadata

• Methods for accessing metadata
• Structure of the INFORMATION_SCHEMA
• Commands for viewing metadata
• Differences between SHOW statements and INFORMATION_SCHEMA tables
• The mysqlshow client program
• Using INFORMATION_SCHEMA queries to generate shell commands and SQL statements

Transactions and Locking

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

Storage Engines

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

Partitioning

• Partitioning concepts and usage in MySQL
• Benefits of using partitioning
• Types of partitioning
• Creating partitioned tables
• Subpartitioning
• Retrieving partition metadata
• Modifying partitions to enhance performance
• Storage engine support for partitioning

User Management

• User authentication requirements
• Using SHOW PROCESSLIST to view active threads
• Creating, modifying, and dropping user accounts
• Alternative authentication plugins
• User authorization requirements
• Hierarchies of user access privileges
• Categorization of privileges
• Granting, adjusting, and revoking user privileges

Security

• Identifying common security threats
• Security risks specific to MySQL installations
• Mitigating security issues related to networks, operating systems, file systems, and users
• Data protection strategies
• Using SSL for secure MySQL server connections
• Utilizing SSH for secure remote connections to the MySQL server
• Locating additional resources for common security concerns

Table Maintenance

• Categories of table maintenance operations
• SQL statements for table maintenance
• Client and utility programs for maintenance tasks
• Maintaining tables across different storage engines
• Data export and import processes
• Exporting data
• Importing data

Programming Inside MySQL

• Creating and executing stored routines
• Security considerations for stored routine execution
• Creating and executing triggers
• Managing events (creating, altering, and dropping)
• Scheduling event execution

MySQL Backup and Recovery

• Fundamentals of backup processes
• Different types of backups
• Backup tools and utilities
• Performing binary and text backups
• The role of log and status files in backups
• 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 performance-impacting status variables
• Configuring and interpreting MySQL server variables
• Overview of the Performance Schema

Conclusion

Q&A Session