MySQL Database Administration

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: THE SERVER, CLIENTS AND TOOLS

• Introduction
• The versions of MySQL
• An Overview of MySQL Architecture
• The MySQL Server
• MySQL Client Connections
• MySQL Client Programs
• MySQL Client Libraries
• The mysql Command Line Tool
• The mysqladmin Program

Session 2: MYSQL WORKBENCH

• Installation of MySQL Workbench
• Connecting to MySQL Workbench
• MySQL Workbench Screens

Session 3: OBTAINING METADATA

• What is Metadata?
• The mysqlshow Client Program
• The SHOW and DESCRIBE Commands
• The INFORMATION_SCHEMA
• MySQL 8 INFORMATION_SCHEMA Changes
• Generate Statements using the INFORMATION_SCHEMA

Session 4: CONFIGURE THE SERVER

• Start and Stop the Server
• Status Files
• Time Zone Tables
• An Overview of MySQL Storage Engines
• MySQL Server Options and Variables
• MySQL Status Variables
• Configuring MySQL Enterprise Audit

Session 5: SQL MODES, LOG FILES AND BINARY LOGGING

• MySQL Error Messages
• The SHOW Errors Statement
• The SHOW Warnings Statement
• SQL Modes
• Note Messages
• The perror Utility
• The General Log
• The Error Log
• The Slow Query Log
• The Binary Logs

Session 6: MYSQL ARCHITECTURE

• Communication Protocols used to connect a Client to the Server
• The SQL Parser and Storage Engine Tiers
• Installing and Uninstalling Plugins
• How MySQL Uses Disk Space
• How MYSQL Uses Memory

Session 7: THE INNODB STORAGE ENGINE

• Features of Innodb
• Transactions and Referential Integrity
• Physical Characteristics of Innodb Tables
• System Tablespace Configuration
• File per Table Tablespace
• Log File and Buffer Configuration
• Undo Logs and Undo Tablespaces
• General Tablespaces
• Temporary Table Tablespaces
• Innodb Status
• InnoDB Shutdown Options

Session 8: OTHER STORAGE ENGINES

• Check Engine Usage
• The MYISAM Engine
• The Merge Engine
• Other Engines: Archive, Memory, Blackhole, CSV
• Mixing Storage Engines

Session 9: TABLE MAINTENANCE

• Table Maintenance Operations
• Check Table
• Repair Table
• Analyze Table
• Optimize Table
• MySQL Check
• Repair InnoDB Tables
• MyISAM Table Maintenance and Repair Utilities

Session 10: BACKUP AND RECOVERY

• The Advantages and Disadvantages of Different Backup Methods
• Backup Tool Overview
• Binary Backups of MYISAM Tables
• Binary Backups of Innodb Tables
• Performing Hot Backups
• Performing Recovery
• Import and Export Operations
• Exporting and Importing using SQL
• Exporting from the Command Line using mysqldump
• Importing from the Command Line using mysqlimport
• Additional Backup Tools

Session 11: USER MANAGEMENT

• Introduction
• User Accounts
• Creating Users
• Renaming Users
• Change a User Password
• Dropping Users
• Granting Privileges
• The User Table
• Connection Validation
• Password Validation Plugins

Session 12: PRIVILEGES

• Types of Privileges
• Granting and Revoking Privileges
• Setting Resource Limits
• MySQL 8 Resource Management
• MySQL 8 Role Management
• The MySQL Database
• The Show Grants Command

Session 13: TRANSACTIONS AND LOCKING

• Locking Concepts
• Levels of Locking
• Implicit Table Locking with MyISAM and InnoDB
• Explicit Table Locking
• Advisory Locking
• Monitoring Locks
• Turning Autocommit on and off
• Using Transaction Blocks
• Handling Concurrency Problems and Deadlocks

Session 14: TUNING AND TABLES

• General Table Optimizations
• MyIsam Specific Optimizations
• InnoDB Specific Optimizations
• Other Engine Specific Optimizations

Session 15: TUNING THE SERVER

• Status Variables
• Server Variables
• System Variables
• Per Client Variables
• Performance Schema Overview
• The Query Cache
• MySQL 8 Desupport of the Query Cache

Session 16: THE EVENT SCHEDULER

• Event scheduler concepts
• Event scheduler configuration
• Creating, altering and dropping events
• Event scheduler monitoring
• Events and privileges

Session 17: MYSQL SERVER INSTALLATION AND UPGRADE

• Installing MySQL on Linux and UNIX
• Upgrade the MySQL Server
• Deployment Security
• Database Server Access

Session 18: OVERVIEW OF HIGH AVAILABILITY

• High Availability Goals
• High Availability Concepts
• Design for High Availability
• Definition of Availability
• High Availability Terminology

Session 19: CONFIGURE MASTER SLAVE REPLICATION

• Replication Overview
• When to Use Replication
• Disadvantages of Replication
• Replication Factors
• Replication Architecture
• Complex Replication Topologies
• Replicating from InnoDB to MyISAM Tables
• Testing Replication
• Monitoring Replication Threads
• Excluding Databases or Tables from Replication
• Example: Setting Up a Master Slave Replication
• Replication Using GTIDs
• Controlled Switchover
• Configure Replication using MySQL Workbench

Session 20: ADMINISTER A REPLICATION TOPOLOGY

• MySQL Replication and Failover Utilities
• Access the Utilities from MySQL Workbench
• Replication Files and Threads
• Configure a Lagging Slave
• Monitoring Replication
• Troubleshooting Replication
• Semi-synchronous Replication
• Configure MySQL Failover