MySQL Database Administration

Session 7: MYSQL ARCHITECTURE

• Client/Server Overview
• Communication Protocols
• The SQL Parser and Storage Engine Tiers
• How MySQL Uses Disk Space
• How MYSQL Uses Memory
• Exercises: Examining the Architecture of MySQL

Session 8: 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
• Exercises: Locking Tables and Rows

Session 9: THE INNODB STORAGE ENGINE

• Introduction
• Features of Innodb
• Transactions
• Referential Integrity
• Physical Characteristics of Innodb Tables
• System Tablespace Configuration
• Log File and Buffer Configuration
• Undo Logs and Undo Tablespaces
• General Tablespaces
• Temporary Table Tablespaces
• Innodb Status
• InnoDB Shutdown Options
• Exercises: Using the InnoDB Engine

Session 10: OTHER STORAGE ENGINES

• Introduction
• The MYISAM Engine
• The Merge Engine
• Other Engines: Archive, Memory, Blackhole, CSV
• Cluster Engine Overview
• Exercises: Using Storage Engines

Session 11: TABLE MAINTENANCE

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

Session 12: BACKUP AND RECOVERY

• Designing a Backup and Recovery Strategy
• The Advantages and Disadvantages of Different Backup Methods
• Backup Tool Overview
• Binary Backups of MYISAM Tables
• Binary Backups of Innodb Tables
• Performing Hot Backups
• 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
• Exercises: Backing Up and Recovery

Session 13: USER MANAGEMENT

• Introduction
• User Accounts
• Creating Users
• Renaming Users
• Changing Passwords
• Dropping Users
• Granting Privileges
• The User Table
• Connection Validation
• Password Validation Plugins
• Exercises: Creating, Managing and Dropping Users

Session 14: PRIVILEGES

• Introduction
• 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
• Exercises: Granting and Revoking Privileges

Session 15: SECURITY AND UPGRADES

• Signs of Poor Security
• Security Risks
• Users, Operating System, File System and Network Security
• Using SSL With MySQL
• Remote Connecting to the MySQL Server Using SSH
• Upgrade the Server
• MySQL 8 Upgrade Checker
• Deployment Security
• Database Server Access
• Exercises: Securing the Server

Session 16: TUNING QUERIES

• Tuning Overview
• Identifying Candidates for Query Analysis
• Using Explain to Analyze Queries
• Meaning of Explain Output
• Explain Extended
• Exercises: Explaining and Tuning Queries

Session 17: TUNING AND INDEXES

• Indexes for Performance
• Creating and Dropping Indexes
• Obtaining Index Metadata
• Indexing Principles
• Indexing and Joins
• Prefixed Indexes
• Composite Indexes
• Fulltext Indexes
• Invisible Indexes
• Index Hints
• Conditions that Prevent Index Usage
• InnoDB Cached Indexes Information
• MyISAM Index Caching
• Exercises: Creating and Using Indexes

Session 18: TUNING AND TABLES

• General Table Optimizations
• MyIsam Specific Optimizations
• InnoDB Specific Optimizations
• Other Engine Specific Optimizations
• Exercises: Tuning Tables

Session 19: 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
• Exercises: Tuning the Server

Session 20: THE EVENT SCHEDULER

• Event scheduler concepts
• Event scheduler configuration
• Creating, altering and dropping events
• Event scheduler monitoring
• Events and privileges
• Exercises: Using the event scheduler

Session 21: PARTITIONED TABLES

• Partitioned tables concepts
• Obtaining Partitioned Table Metadata
• Types of Partitioning
• Subpartitioning
• Maintenance of partitioned tables
• MySQL 8 Partitioning Changes
• Exercises: Using partitioned tables

Session 22: OVERVIEW OF CLUSTER STORAGE ENGINES

• Overview of Clustering and Performance
• Advantages Performance Advantages of Clustering
• Performance Issues and Clustering
• NDBCluster Engine Overview
• The Galera Cluster
• The Percona XtraDB Cluster
• MySQL InnoDB Cluster
• The Federated Engine
• NOSQL and MemCached Overview

Session 23: OVERVIEW OF HIGH AVAILABILITY

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

Session 24: 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
• Exercises: Setting Up and Testing GTID and non-GTID Replication

Session 25: 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
• Exercises: Failover from Master to Slave