MySQL Administration Training Course

Introduction

• Overview of MySQL, its products, and services
• MySQL services and support options
• Supported operating systems
• Training curriculum paths
• 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 utilization in MySQL
• The MySQL plug-in interface

System Administration

• Selecting the appropriate MySQL distribution
• Installing the MySQL Server
• 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 options
• 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 client
• MySQL tools
• Available APIs (drivers and connectors)

Data Types

• Primary categories of data types
• The significance of NULL
• Column attributes
• Character set usage with data types
• Selecting the most appropriate data type

Obtaining Metadata

• Methods for accessing metadata
• Structure of INFORMATION_SCHEMA
• Using commands to view metadata
• Differences between SHOW statements and INFORMATION_SCHEMA tables
• The mysqlshow client program
• Utilizing 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
• Employing locking to safeguard transactions

Storage Engines

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

Partitioning

• Partitioning concepts and their application in MySQL
• Reasons to use partitioning
• Types of partitioning
• Creating partitioned tables
• Subpartitioning
• Retrieving partition metadata
• Modifying partitions for performance enhancement
• Partitioning support across storage engines

User Management

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

Security

• Identifying common security risks
• Security risks specific to MySQL installations
• Addressing security issues in networks, operating systems, filesystems, and user management
• Data protection strategies
• Securing MySQL server connections with SSL
• Enabling secure remote connections to the MySQL server via SSH
• Sources for additional information on common security issues

Table Maintenance

• Categories of table maintenance operations
• SQL statements for table maintenance
• Client and utility programs for table maintenance
• Maintaining tables for non-InnoDB storage engines
• Data Export and Import procedures
• Exporting Data
• Importing Data

Programming Inside MySQL

• Creating and executing Stored Routines
• Security considerations for stored routine execution
• Creating and executing triggers
• Creating, altering, and dropping events
• Scheduling event execution

MySQL Backup and Recovery

• Fundamentals of backups
• Types of backup methods
• Backup tools and utilities
• Creating binary and text backups
• The role of log and status files in backups
• Data Recovery processes

Replication

• Managing the MySQL Binary Log
• MySQL replication threads and associated files
• Setting up a MySQL Replication Environment
• Designing Complex Replication Topologies
• Multi-Master and Circular Replication setups
• Performing a Controlled Switchover
• Monitoring and Troubleshooting MySQL Replication
• Replication with 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