MySQL Administration Training Course

Introduction

• Overview of MySQL, its products, and services
• MySQL service offerings and support channels
• Supported operating systems
• Training curriculum pathways
• Accessing MySQL documentation resources

MySQL Architecture

• The client/server model fundamentals
• Communication protocols in use
• Functionality of the SQL layer
• Functionality of the storage layer
• Server support mechanisms for storage engines
• Utilization of memory and disk space by MySQL
• The MySQL plug-in interface

System Administration

• Selecting the appropriate MySQL distribution type
• Procedures for installing the MySQL server
• Understanding the MySQL server file structure
• Techniques for starting and stopping the MySQL server
• Upgrading MySQL versions
• Deploying multiple MySQL servers on a single host

Server Configuration

• Reviewing MySQL server configuration options
• Managing system variables
• Understanding SQL modes
• Managing available log files
• Configuring binary logging

Clients and Tools

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

Data Types

• Major categories of data types
• Understanding the significance of NULL
• Column attributes and their impact
• Character set usage with data types
• Strategies for choosing 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
• Using the mysqlshow client program
• Generating shell commands and SQL statements via INFORMATION_SCHEMA queries

Transactions and Locking

• Concurrent execution of multiple SQL statements using transaction control
• ACID properties of transactions
• Understanding transaction isolation levels
• Implementing locking to protect transactions

Storage Engines

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

Partitioning

• Concept of partitioning in MySQL
• Benefits of using partitioning
• Different types of partitioning
• Creating partitioned tables
• Implementing subpartitioning
• Retrieving partition metadata
• Optimizing performance by modifying partitions
• Storage engine support for partitioning

User Management

• User authentication requirements
• Monitoring running threads with SHOW PROCESSLIST
• Creating, modifying, and dropping user accounts
• Alternative authentication plugins
• User authorization requirements
• Hierarchy of user access privileges
• Categorization of privilege types
• Granting, modifying, and revoking user privileges

Security

• Identifying common security risks
• Security risks specific to MySQL installations
• Counter-measures for network, OS, filesystem, and user-related security issues
• Strategies for protecting data
• Using SSL for secure MySQL server connections
• Enabling secure remote connections via SSH
• Sources for resolving common security issues

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 procedures
• Exporting data workflows
• Importing data workflows

Programming Inside MySQL

• Creating and executing Stored Routines
• Security considerations in stored routine execution
• Creating and executing triggers
• Managing events: creation, alteration, and deletion
• Scheduling event execution

MySQL Backup and Recovery

• Foundations of backup strategies
• Different types of backups
• Backup tools and utilities
• Performing binary and text backups
• The role of log and status files in the backup process
• Data recovery procedures

Replication

• Managing the MySQL Binary Log
• MySQL replication threads and associated files
• Setting up a MySQL Replication Environment
• Designing complex replication topologies
• Implementing Multi-Master and Circular Replication
• Executing controlled failover/switchover
• Monitoring and troubleshooting MySQL Replication
• Replication using Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

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

Conclusion

Q&A Session