MariaDB Database Administration

Session 7: MARIADB ARCHITECTURE

• Client/Server Overview
• Communication Protocols
• The SQL Parser and Storage Engine Tiers
• Installing and Uninstalling Plugins
• How MariaDB Uses Disk Space
• How MariaDB Uses Memory
• Exercises: Examining the Architecture

Session 8: TRANSACTIONS AND LOCKING

• Locking Concepts
• Levels of Locking
• Explicit Table Locking
• Advisory Locking
• Implicit Locking with InnoDB
• Exercises: Locking

Session 9: THE INNODB STORAGE ENGINE

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

Session 10: OTHER STORAGE ENGINES

• Introduction
• The MYISAM Engine
• The Merge Engine
• Other Engines: Archive, Aria, Memory, Blackhole, CSV
• Cluster Engine Overview
• Overview of the ColumnStore, MyRocks and Spider Engines
• Exercises: Using Storage Engines

Session 11: TABLE MAINTENANCE

• Table Maintenance Operations
• Check Table
• Repair Table
• Analyze Table
• Optimize Table
• MySQL Check
• 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
• Overview of Backup Tools
• Binary Backups of MYISAM Tables
• Binary Backups of Innodb Tables
• 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 Privileges
• Revoking Privileges
• Resource Limits
• Resource Management
• Role Management
• The mysql Database
• The Show Grants Command
• Role management
• 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
• Data Encryption
• Upgrade the Server
• 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
• Prefixed Indexes
• Composite Indexes
• Fulltext Indexes
• Index Hints
• Conditions that Prevent Index Usage
• Index Only Searches
• Hints to Use Indexes
• MyISAM Index Caching
• Indexing and Joins
• Specify the Join Order
• 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
• 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
• Partitioning for Specific Storage Engines
• Maintenance of partitioned tables
• Exercises: Using partitioned tables

Session 22: OVERVIEW OF CLUSTER STORAGE ENGINES

• Overview of Clustering and Performance
• The Galera Cluster
• The Percona XtraDB Cluster
• NOSQL 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 Architecture
• Complex Replication Topologies
• Testing Replication
• Excluding Databases or Tables from Replication
• Example: Setting Up a Master Slave Replication
• Replication Using GTIDs
• Controlled Switchover
• Exercises: Setting Up and Testing Replication and Switchover

Session 25: ADMINISTER A REPLICATION TOPOLOGY

• Replication Files and Threads
• Lagging Slave
• Monitoring Replication
• Troubleshooting Replication
• Semi-synchronous Replication
• Failover
• Replication and Failover Utilities
• Exercises: Failover from Master to Slave