MariaDB Database Administration

Overview

This MariaDB Database Administration course is designed for MariaDB Database Administrators who have a basic understanding of a MariaDB or MySQL database and SQL commands. The course provides practical experience in setting up and maintaining a MariaDB server, including backing up, recovery, configuration and tuning.

Learning Objectives

To provide the skills necessary to administer, monitor and support MariaDB databases and servers. The delegates will practise:

Using Client Programs for MariaDB DBA Work
Obtaining Metadata
Configuring the Server
Server Installation, Administration and Upgrading
Setting up Character Set Support
Configuring Log Files
Interpreting Error Messages
Creating, Altering and Dropping Databases and Tables
Locking and Transactions
Using Storage Engines, including the MyISAM and InnoDB engines
Table Maintenance
Backup and Recovery, including Exporting (mysqldump) and Importing (mysqlimport)
Administering Users and Privileges
Using Stored Procedures and Triggers for Database Administration Tasks
Securing the Server
Tuning Queries
Tuning Tables and Indexes
Tuning the Server
Using the Event Scheduler
Creating Partitioned Tables
Replication
Using MySQL Workbench

Course duration

5 Days

Course outline

Clients & Tools

Introduction
The mysql client
mysqladmin
Exercises: Using Client Programs

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

Configuring The Server

MariaDB Server Options and Variables
MariaDB Status Variables
Exercises: Configuring the Server

MariaDB Server Installation & Configuration

MariaDB Distributions
Installing on Windows
Installing on Linux and UNIX
Starting and Stopping on Windows
Starting and Stopping on UNIX/Linux
Status Files
Upgrading the Server
Time Zone Tables
Exercises: Installing, stopping and starting

SQL Modes, Log Files & 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
Exercises: Interpreting Errors and Configuring the Logs

MariaDB Architecture

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

Tables, Data Types & Character Set Support

Table Properties
Creating Tables
Altering Tables
Dropping Tables
Emptying Tables
Obtaining Table Metadata
Column Attributes
Bit Data Type
Numeric Data Types
Character String Data Types
Binary String Data Types
Enum and Set Data Types
Temporal Data Types
Auto_Increment
Handling Missing or Invalid Data Values
Exercises: Creating and Maintaining Tables

Transactions & Locking

Locking Concepts
Explicit Table Locking
Advisory Locking
Exercises: Locking

Storage Engines

Introduction
The MYISAM Engine
The Merge Engine
The Aria Engine
Other Engines: Archive, Memory, Federated, Blackhole, CSV
Cluster Engine Overview
Overview of High Availability Techniques
Exercises: Using Storage Engines

The INNODB Engine

Introduction
Features of Innodb
Transactions
Referential Integrity
Physical Characteristics of Innodb Tables
System Tablespace Configuration
Log File and Buffer Configuration
Innodb Status
Exercises: Using the InnoDB Engine

Table Maintenance

Table Maintenance Operations
Check Table
Repair Table
Analyze Table
Optimize Table
mysqlcheck
MYISAMCHK
Repairing Innodb Tables
Enabling MYISAM AutoRepair
Exercises: Maintaining Tables

Backup & Recovery

The Advantages and Disadvantages of Different Methods
Binary Backups of MYISAM Tables
Binary Backups of Innodb Tables
Recovery
Import and Export Operations
Exporting Using SQL
Importing Using SQL
Exporting from the Command Line using mysqldump
Importing from the Command Line using mysqlimport
Exercises: Backing Up and Recovery

User Management

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

Privileges

Introduction
Types of Privileges
Revoking Privileges
Resource Limits
The MySQL Database
The Show Grants Command
Exercises: Granting and Revoking Privileges

User Variables & Prepared Statements

User Variables
Prepared Statements
Exercises: User Variables and Prepared Statements

Stored Routines For Administration

Types of Stored Routines
Benefits of Stored Routines
Stored Routines Features
Stored Routine Maintenance
Obtaining Stored Routine Metadata
Stored Routine Privileges and Execution Security

Triggers

DML Triggers

Security

Security Risks
Users, Operating System, File System and Network Security
Using SSL With MariaDB
Remote Connecting to the MariaDB Server Using SSH
Exercises: Securing the Server

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

Tuning & Indexes - Part 1

Indexes for Performance
Creating and Dropping Indexes
Obtaining Index Metadata
Indexing Principles
Exercises: Creating and Using Indexes

Tuning & Indexes - Part 2

Indexing and Joins
Fulltext Indexes
MyISAM Index Caching
Exercises: Creating and Using Indexes

Tuning & Tables

General Table Optimizations
Myisam Specific Optimizations
Aria Specific Optimizations
Innodb Specific Optimizations
Other Engine Specific Optimizations
Exercises: Tuning Tables

Tuning The Server

Status Variables
Server Variables
Performance Schema Overview
The Query Cache
Exercises: Tuning the Server

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

Partitioned Tables

Partitioned tables concepts
Obtaining Partitioned Table Metadata
Types of Partitioning
Subpartitioning
Maintenance of partitioned tables
Exercises: Using partitioned tables

Replication

Replication Overview
Testing Replication
Monitoring and Troubleshooting Replication
Files and Threads Involved in Replication
Excluding Databases or Tables From Replication
Complex Replication Topologies
Example: Setting Up a Master Slave Replication
Replication Using GTIDs
Replication Utilities
Controlled Switchover
Exercises: Setting Up and Testing Replication

MySQL Workbench

Installation
Connecting
Screens
Other GUI Tools
Exercises: Using MySQL Workbench