MySQL Administration Training Course

Introduction

• Overview of MySQL, its products, and services
• Details on MySQL services and support
• Supported operating systems
• Recommended training curriculum paths
• Access to MySQL documentation resources

MySQL Architecture

• The client-server model
• Communication protocols
• The SQL Layer
• 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
• Installing the MySQL Server
• Understanding the MySQL Server installation file structure
• Procedures for starting and stopping the MySQL server
• Upgrading MySQL versions
• Running multiple MySQL instances on a single host

Server Configuration

• Reviewing MySQL server configuration options
• Managing system variables
• Configuring SQL Modes
• Utilizing available log files
• Implementing binary logging

Clients and Tools

• Identifying available clients for administrative tasks
• Overview of MySQL administrative clients
• Using the mysql command-line client
• Using the mysqladmin command-line client
• Utilizing the MySQL Workbench graphical client
• Application of various MySQL tools
• Integration of available APIs (drivers and connectors)

Data Types

• Major categories of data types
• Understanding the significance of NULL
• Defining column attributes
• Character set usage alongside data types
• Selecting the most appropriate data type

Obtaining Metadata

• Methods for accessing metadata
• Structure of the INFORMATION_SCHEMA
• Utilizing commands to view metadata
• Distinguishing between SHOW statements and INFORMATION_SCHEMA tables
• Using the mysqlshow client program
• Leveraging INFORMATION_SCHEMA queries for shell commands and SQL statements

Transactions and Locking

• Employing transaction control statements for concurrent SQL execution
• Understanding the ACID properties of transactions
• Transaction isolation levels
• Using locking mechanisms to protect transactions

Storage Engines

• Overview of storage engines in MySQL
• Details on 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
• Different types of partitioning
• Creating partitioned tables
• Subpartitioning techniques
• Retrieving partition metadata
• Modifying partitions to enhance performance
• Partitioning support across different storage engines

User Management

• Requirements for user authentication
• Using SHOW PROCESSLIST to monitor running threads
• Creating, modifying, and dropping user accounts
• Implementing alternative authentication plugins
• Requirements for user authorization
• 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
• Addressing security issues for networks, operating systems, filesystems, and users
• Strategies for protecting data
• Implementing SSL for secure MySQL server connections
• Using SSH to enable secure remote connections to the MySQL server
• Sourcing additional information for common security issues

Table Maintenance

• Types of table maintenance operations
• SQL statements for table maintenance
• Client and utility programs for table maintenance
• Maintaining tables for other storage engines
• Data Export and Import processes
• Exporting Data
• Importing Data

Programming Inside MySQL

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

MySQL Backup and Recovery

• Foundations of backup processes
• Categories of backups
• Backup tools and utilities
• Creating 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
• Implementing Multi-Master and Circular Replication
• Executing a Controlled Switchover
• Monitoring and troubleshooting MySQL Replication
• Replication using Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Analyzing queries using EXPLAIN
• General Table Optimizations
• Monitoring status variables that impact performance
• Configuring and interpreting MySQL server Variables
• Overview of the Performance Schema

Conclusion

Q&A Session