MariaDB Database Administration Training Course

Course Contents - DAY 1

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

MariaDB Database Administration Training Course

Course Contents - DAY 2

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

MariaDB Database Administration Training Course

Course Contents - DAY 3

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

MariaDB Database Administration Training Course

Course Contents - DAY 4

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

MariaDB Database Administration Training Course

Course Contents - DAY 5

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