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

---

Please contact your training representative for more details on having this course delivered onsite or online

Training Outlines - the one stop shopping center for IT training.
© Training Outlines All rights reserved