MySQL Administration Training Course

Introduction

• Overview of MySQL products and services
• MySQL services and support options
• Supported operating systems
• Training curriculum pathways
• Accessing MySQL documentation resources

MySQL Architecture

• The client-server model
• Communication protocols
• The SQL layer
• The storage layer
• Server support for storage engines
• MySQL memory and disk space usage
• The MySQL plug-in interface

System Administration

• Selecting appropriate MySQL distributions
• Installing the MySQL server
• Understanding the server installation file structure
• Starting and stopping the MySQL server
• Upgrading MySQL
• 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

• Clients available for administrative tasks
• 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
• The meaning of NULL
• Column attributes
• Character set usage with data types
• Selecting appropriate data types

Obtaining Metadata

• Available metadata access methods
• The structure of INFORMATION_SCHEMA
• Viewing metadata using available commands
• 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
• The ACID properties of transactions
• Transaction isolation levels
• Using locking to protect 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 with foreign keys
• InnoDB locking mechanisms
• Features of available storage engines

Partitioning

• Partitioning and its application in MySQL
• Reasons for implementing partitioning
• Types of partitioning
• Creating partitioned tables
• Subpartitioning
• Retrieving partition metadata
• Modifying partitions to enhance performance
• 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
• Levels of user access privileges
• Types of privileges
• 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 security issues
• Data protection strategies
• Using SSL for secure MySQL server connections
• Enabling secure remote connections via SSH
• Locating resources for common security concerns

Table Maintenance

• Types of table maintenance operations
• SQL statements for table maintenance
• Client and utility programs for maintenance
• Maintaining tables for other storage engines
• Exporting and importing data
• Data export procedures
• Data import procedures

Programming Inside MySQL

• Creating and executing stored routines
• Defining security for stored routine execution
• Creating and executing triggers
• Managing events (create, alter, drop)
• Scheduling event execution

MySQL Backup and Recovery

• Foundations of backup strategies
• Types of backups
• 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 files
• Setting up a MySQL replication environment
• Designing complex replication topologies
• Multi-master and circular replication
• Executing controlled 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 impacting performance
• Configuring and interpreting MySQL server variables
• Overview of the Performance Schema

Conclusion

Q&A Session