MySQL Administration Training Course

Introduction

• Overview of MySQL Products and Services
• MySQL Support Offerings
• Supported Operating Systems
• Training Curriculum Paths
• Resources for MySQL Documentation

MySQL Architecture

• The Client/Server Model
• Communication Protocols
• The SQL Layer
• The Storage Layer
• Server Support for Storage Engines
• Memory and Disk Space Management
• The MySQL Plug-in Interface

System Administration

• Selecting MySQL Distributions
• Installing the MySQL Server
• Server Installation File Structure
• Starting and Stopping the MySQL Server
• Upgrading MySQL
• Running Multiple MySQL Instances on a Single Host

Server Configuration

• MySQL Server Configuration Options
• System Variables
• SQL Modes
• Available Log Files
• Binary Logging

Clients and Tools

• Administrative Clients Overview
• MySQL Administrative Utilities
• The mysql Command-Line Client
• The mysqladmin Command-Line Client
• MySQL Workbench Graphical Client
• Additional MySQL Tools
• Available APIs (Drivers and Connectors)

Data Types

• Major Categories of Data Types
• Understanding NULL Values
• Column Attributes
• Character Set Usage with Data Types
• Selecting Appropriate Data Types

Obtaining Metadata

• Methods for Accessing Metadata
• Structure of 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 Execution
• ACID Properties of Transactions
• Transaction Isolation Levels
• Locking Strategies to Protect Transactions

Storage Engines

• Overview of MySQL Storage Engines
• InnoDB Storage Engine
• InnoDB System and File-Per-Table Tablespaces
• NoSQL and the Memcached API
• Efficient Tablespace Configuration
• Achieving Referential Integrity with Foreign Keys
• InnoDB Locking Mechanisms
• Features of Available Storage Engines

Partitioning

• Partitioning Concepts in MySQL
• Benefits of Using Partitioning
• Types of Partitioning
• Creating Partitioned Tables
• Subpartitioning Techniques
• Accessing Partition Metadata
• Modifying Partitions for Performance Improvement
• Storage Engine Support for Partitioning

User Management

• User Authentication Requirements
• Using SHOW PROCESSLIST to Monitor Running Threads
• Managing User Accounts: Creation, Modification, and Deletion
• Alternative Authentication Plugins
• User Authorization Requirements
• Access Privilege Levels for Users
• Types of Privileges
• Granting, Modifying, and Revoking User Privileges

Security

• Identifying Common Security Risks
• Security Risks Specific to MySQL Installations
• Security Challenges and Countermeasures for Networks, Operating Systems, Filesystems, and Users
• Data Protection Strategies
• Using SSL for Secure Server Connections
• SSH for Secure Remote MySQL Access
• Resources for Resolving Common Security Issues

Table Maintenance

• Types of Table Maintenance Operations
• SQL Statements for Maintenance
• Client and Utility Programs for Maintenance
• Maintaining Tables for Other Storage Engines
• Data Exporting and Importing
• Exporting Data Techniques
• Importing Data Procedures

Programming Inside MySQL

• Creating and Executing Stored Routines
• Security Considerations for Stored Routine Execution
• Creating and Executing Triggers
• Managing Events: Creation, Alteration, and Deletion
• Scheduling Event Execution

MySQL Backup and Recovery

• Backup Fundamentals
• Types of Backups
• Backup Tools and Utilities
• Performing 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 Files
• Setting Up a MySQL Replication Environment
• Designing Complex Replication Topologies
• Multi-Master and Circular Replication
• Performing Controlled Switchover Operations
• Monitoring and Troubleshooting MySQL Replication
• Replication Using Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Analyzing Queries with EXPLAIN
• General Table Optimization Techniques
• Monitoring Status Variables Affecting Performance
• Setting and Interpreting MySQL Server Variables
• Overview of the Performance Schema

Conclusion

Q&A Session