MySQL Administration Training Course

Introduction

• Overview of MySQL, including its products and services
• MySQL support options and service levels
• Supported operating systems
• Available training curriculum paths
• Documentation resources for MySQL

MySQL Architecture

• Understanding 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 plugin interface

System Administration

• Selecting the appropriate MySQL distribution
• Installing the MySQL Server
• File structure of the MySQL Server installation
• Starting and stopping the MySQL server
• Upgrading MySQL installations
• Running multiple MySQL servers on a single host

Server Configuration

• MySQL server configuration options
• System variables
• SQL Modes
• Available log files
• Binary logging

Clients and Tools

• Administrative client options
• MySQL administrative clients
• The mysql command-line client
• The mysqladmin command-line client
• The MySQL Workbench graphical client
• MySQL utility tools
• Available APIs (drivers and connectors)

Data Types

• Major categories of data types
• Understanding the meaning of NULL
• Column attributes
• Using character sets with data types
• Choosing appropriate data types

Obtaining Metadata

• Methods for accessing metadata
• Structure of INFORMATION_SCHEMA
• Using available 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 for concurrent SQL execution
• ACID properties of transactions
• Transaction isolation levels
• Using locking mechanisms to protect transactions

Storage Engines

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

Partitioning

• Partitioning concepts and usage in MySQL
• Reasons for implementing partitioning
• Types of partitioning
• Creating partitioned tables
• Subpartitioning strategies
• Obtaining partition metadata
• Modifying partitions to enhance performance
• Storage engine support for partitioning

User Management

• Requirements for user authentication
• Using SHOW PROCESSLIST to monitor running threads
• Creating, modifying, and dropping user accounts
• Alternative authentication plugins
• Requirements for user authorization
• Levels of access privileges for users
• Types of privileges
• Granting, modifying, and revoking user privileges

Security

• Recognizing common security risks
• Security risks specific to MySQL installations
• Security problems and countermeasures for network, OS, filesystem, and users
• Data protection strategies
• Using SSL for secure MySQL server connections
• Leveraging SSH for secure remote connections to MySQL
• Resources for addressing common security issues

Table Maintenance

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

Programming Inside MySQL

• Creating and executing Stored Routines
• Securing stored routine execution
• Creating and executing triggers
• Creating, altering, and dropping events
• Event execution scheduling

MySQL Backup and Recovery

• Backup fundamentals
• Types of backups
• Backup tools and utilities
• Making 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 files
• Setting up a MySQL Replication Environment
• Designing Complex Replication Topologies
• Multi-Master and Circular Replication
• Performing a Controlled Switchover
• Monitoring and Troubleshooting MySQL Replication
• Replication with Global Transaction Identifiers (GTIDs)

Introduction to Performance Tuning

• Using EXPLAIN to Analyze Queries
• General Table Optimizations
• Monitoring status variables that affect performance
• Setting and Interpreting MySQL server Variables
• Overview of Performance Schema

Conclusion

Q&A Session