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
• The versions of MariaDB
• An Overview of MariaDB Architecture
• MySQL Server
• MySQL Client Connections
• MySQL Client Programs
• Mysql Command Line
• Mysqladmin

Session 2: MYSQL WORKBENCH

• Installation of MySQL Workbench
• Connecting to MySQL Workbench
• MySQL Workbench Screens

Session 3: OBTAINING METADATA

• What is Metadata?
• The mysqlshow Client Program
• The SHOW and DESCRIBE Commands
• The Information_Schema
• Show Command and Information_Schema Differences
• Exercises: Obtaining Information about MariaDB

Session 4: CONFIGURE THE SERVER

• Start and Stop the Server
• Status Files
• Time Zone Tables
• An Overview of MySQL Storage Engines
• MariaDB Server Options and Variables
• MariaDB Status Variables
• Configuring MariaDB Enterprise Audit

Session 5: 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 6: 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
• Exercises: Examining the Architecture

Session 7: 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

Session 8: 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 9: TABLE MAINTENANCE

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

Session 10: 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
• Performing Hot Backups
• Performing 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

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 Privileges
• Revoking Privileges
• Resource Limits
• Resource Management
• Role Management
• The mysql Database
• The Show Grants Command
• Role management

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
• Handling Concurrency Problems and Deadlocks

Session 14: TUNING AND TABLES

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

Session 15: TUNING THE SERVER

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

Session 16: THE EVENT SCHEDULER

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

Session 17: MARIADB SERVER INSTALLATION AND UPGRADE

• Installing MariaDB on Linux and UNIX
• Upgrade the MariaDB Server
• Deployment Security
• Database Server Access

Session 18: OVERVIEW OF HIGH AVAILABILITY

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

Session 19: 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

Session 20: ADMINISTER A REPLICATION TOPOLOGY

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