MariaDB Database Administration

Course Introduction

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

Session 1: THE SERVER, CLIENTS AND TOOLS

• Introduction
• MariaDB Database Release Versions
• MariaDB Distributioins
• An Overview of MariaDB Architecture
• The MariaDB Server
• MariaDB Client Connections
• MariaDB Client Programs
• The MariaDB Command Line
• The mariadb-admin Utility

Session 2: OBTAINING METADATA

• What is Metadata?
• The mariadb-slow or mysqlshow Client Program
• The SHOW and DESCRIBE Commands
• The Information_Schema
• Show Command and Information_Schema Differences

Session 3: CONFIGURE THE SERVER

• Start and Stop the MariaDB Server
• Status Files
• An Overview of MariaDB Storage Engines
• MariaDB Server Options and Variables
• MariaDB Status Variables
• Configuring MariaDB Audit Plugin

Session 4: SQL MODES, LOG FILES AND BINARY LOGGING

• 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 5: MARIADB ARCHITECTURE

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

Session 6: THE INNODB STORAGE ENGINE

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

Session 7: OTHER STORAGE ENGINES

• Check Engine Usage
• The MYISAM Engine
• The Merge Engine
• Other Engines: Archive, Aria, Memory, Blackhole, CSV
• Mixing Storage Engines
• Overview of the ColumnStore, MyRocks and Spider Engines

Session 8: TABLE MAINTENANCE

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

Session 9: BACKUP AND RECOVERY

• The Advantages and Disadvantages of Different Backup Methods
• Overview of Backup Tools
• Binary Backups of MYISAM Tables
• Binary Backups of InnoDB Tables
• View the Binary Log
• Recover using the Binary Logs
• Performing Hot Backups with MariaDB Backup
• Performing Recovery with MariaDB Backup
• Exporting and Importing using SQL
• Exporting from the Command Line using mariadb-dump
• Importing from the Command Line using mariadb-import
• Additional Backup Tools

Session 10: USER MANAGEMENT

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

Session 11: PRIVILEGES

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

Session 12: 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 13: TUNING AND TABLES

• General Table Optimizations
• Myisam Specific Optimizations
• Innodb Specific Optimizations
• Other Engine Specific Optimizations

Session 14: TUNING THE SERVER

• Server Configuration Variables
• Status Variables
• Server Variables
• System Variables
• Per Client Variables
• Performance Schema Overview
• The Query Cache

Session 15: THE EVENT SCHEDULER

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

Session 16: MARIADB SERVER INSTALLATION AND UPGRADE

• MariaDB Distributions
• Installing MariaDB on Linux and UNIX
• The Installation Directories
• Set a Password for the mysql root Uer
• Upgrade the MariaDB Server
• Deployment Security
• Database Server Access
• SSL Encrypted Connections

Session 17: OVERVIEW OF HIGH AVAILABILITY

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

Session 18: CONFIGURE MASTER SLAVE REPLICATION

• Replication Overview
• When to Use Replication
• Disadvantages of Replication
• Replication Architecture
• Complex Replication Topologies
• Testing Replication
• Excluding or Including Databases or Tables from Replication
• Example: Setting Up a Master Slave Replication
• Replication Using GTIDs

Session 19: ADMINISTER A REPLICATION TOPOLOGY

• Replication Files and Threads
• Lagging Slave
• Monitoring Replication
• Troubleshooting Replication Problems
• Semi-synchronous Replication
• Controlled Switchover
• Failover
• MariaDB Replication Manager