MySQL Administration Training Course

Introduction

• Overview of MySQL, products, and services
• Available MySQL services and support options
• List of supported operating systems
• Overview of training curriculum paths
• Accessing MySQL documentation resources

MySQL Architecture

• The client/server model
• Communication protocols
• The SQL Layer
• The Storage Layer
• How the server supports storage engines
• MySQL's use of memory and disk space
• The MySQL plug-in interface

System Administration

• Selecting between different MySQL distributions
• Installing the MySQL Server
• Understanding the MySQL Server file structure
• Starting and stopping the MySQL server
• Methods for upgrading MySQL
• Running multiple MySQL servers on a single host

Server Configuration

• Configuring MySQL server options
• Working with system variables
• Understanding SQL Modes
• Reviewing available log files
• Implementing binary logging

Clients and Tools

• Identifying clients for administrative tasks
• Using MySQL administrative clients
• The mysql command-line client
• The mysqladmin command-line client
• The MySQL Workbench graphical client
• Utilizing MySQL tools
• Available APIs (drivers and connectors)

Data Types

• Primary categories of data types
• The significance of NULL
• Understanding column attributes
• Character set usage with data types
• Selecting an appropriate data type

Obtaining Metadata

• Methods for accessing metadata
• Structure of the INFORMATION_SCHEMA
• Using commands to view 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 to execute multiple SQL statements concurrently
• The ACID properties of transactions
• Understanding transaction isolation levels
• Using locking mechanisms to protect transactions

Storage Engines

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

Partitioning

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

User Management

• Authentication requirements for users
• Using SHOW PROCESSLIST to identify running threads
• Creating, modifying, and dropping user accounts
• Utilizing alternative authentication plugins
• Authorization requirements for users
• Hierarchy of user access privileges
• Categorization of privileges
• Granting, modifying, and revoking user privileges

Security

• Identifying common security risks
• MySQL-specific security threats
• Counter-measures for network, OS, filesystem, and user-related security issues
• Strategies for protecting data
• Implementing SSL for secure MySQL server connections
• Using SSH for secure remote connections to the MySQL server
• Locating resources for common security issues

Table Maintenance

• Types of table maintenance operations
• SQL statements used for table maintenance
• Client and utility programs for maintenance
• Maintaining tables across different storage engines
• Data Export and Import workflows
• Techniques for exporting data
• Techniques for importing data

Programming Inside MySQL

• Creating and executing Stored Routines
• Understanding stored routine execution security
• Creating and executing triggers
• Managing events: creating, altering, and dropping
• Scheduling event execution

MySQL Backup and Recovery

• Fundamentals of backup strategies
• Classification of backup types
• Selection of backup tools and utilities
• Performing binary and text backups
• The role of log and status files in backup processes
• Methods for data recovery

Replication

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

Introduction to Performance Tuning

• Analyzing queries using EXPLAIN
• General table optimization techniques
• Monitoring status variables that impact performance
• Configuring and interpreting MySQL server variables
• An overview of the Performance Schema

Conclusion

Q&A Session