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 to MySQL
• The versions of MySQL
• MySQL Database Server Releases
• MySQL Database Versions
• 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: OBTAIN METADATA

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

Session 4: CONFIGURE THE SERVER

• Start and Stop the MySQL Server
• Status Files
• An Overview of MySQL Storage Engines
• MySQL Server Options and Variables
• MySQL Status Variables
• Configure the MySQL Enterprise Edition Auditing Plugin

Session 5: SQL MODES, LOG FILES AND BINARY LOGGING

• SQL Modes
• 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 the Innodb Engine
• The System Tablespace
• 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
• Buffer and Lof File Configuration
• 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, Spider, MyRocks
• 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
• What Should be Backed up?
• Binary Backups of MYISAM Tables
• Binary Backups of Innodb Tables
• Performing Hot Backups
• Performing Recovery
• Import and Export Operations
• Export and Import using SQL
• Export from the Command Line using mysqldump
• Import 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
• Resource Management
• 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
• Possible Causes of Lock Contention
• Handling Concurrency Problems and Deadlocks

Session 14: TUNING THE SERVER

• Status Configuration Variables
• Status Variables
• System Variables
• Per Client Variables
• Performance Schema Overview

Session 15: THE EVENT SCHEDULER

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

Session 16: OVERVIEW OF HIGH AVAILABILITY

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

Session 17: 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
• Run Replication from a Remote Host
• Controlled Switchover

Session 18: ADMINISTER A REPLICATION TOPOLOGY

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

Session 19: MYSQL SERVER INSTALLATION, UPGRADE DEPLOYMENT SECURITY

• MySQL Distributions
• Installing MySQL on Linux and UNIX
• The Installation Directories
• Set a Password for the mysql root user
• Upgrade the MySQL Server
• Deployment Security