First Technology Transfer

Standard and Advanced Technical Training, Consultancy and Mentoring

DB687 MariaDB Database Administration

Duration: 5 Days

Intended Audience

This course is for those who will be controlling and administering MariaDB Database servers. Attendees must have a good basic understanding of SQL and using it on a MariaDB database. Some knowledge and experience of designing databases would be useful but is not absolutely necessary, as the relevant details will be covered.

Synopsis and Course Objectives

This MariaDB Database Administration course is designed for MariaDB Database Administrators who have a basic understanding of a MariaDB database and SQL commands.

The course provides practical experience in setting up and maintaining a MariaDB server, including backing up, recovery, configuration and tuning.

Objectives

The course aims to teach the skills necessary to administer, monitor and support MariaDB databases and servers. As well as covering theory and concepts the course will include practical work that will involve performing the following tasks that are needed to perform the duties of database administrator

  • Using Client Programs for MariaDB DBA Work
  • Obtaining and Interpreting Metadata
  • Configuring the Server both from the database and the operating system perspective
  • Server Installation, Administration and Upgrading
  • Setting up Character Set Support
  • Configuring Log Files
  • Understanding the causes of and Interpreting Error Messages
  • Creating, Altering and Dropping Databases and Tables
  • Locking and Transactions and dealing with some of the problems encountered in this context.
  • Using Storage Engines, including the MyISAM and InnoDB engines
  • Table Maintenance
  • Backup and Recovery, including Exporting (MariaDBdump) and Importing (MariaDBimport)
  • 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
  • Working with MariaDB Workbench

Course Outline

  • The roles and responsibilities of a Database Administrator
    • Database performance and tuning expertise
    • Database design expertise
    • Database backup and recovery expertise
    • Database problem troubleshooting expertise
    • Database administration expertise
    • Advisor and mentor for both developers and users
  • MariaDB Clients and Tools
    • Introduction
    • MariaDB
    • MariaDB Admin
    • Database administration using client programs

    Obtaining Metadata

    • The need for Metadata
    • The MariaDBshow Client Program
    • The SHOW and DESCRIBE Commands
    • The Information_Schema
    • Show Command and Information_Schema Differences
  • MariaDB Database Server Configuration
    • MariaDB Server Options and Variables
    • MariaDB Status Variables
    • Configuring MariaDB Enterprise Audit
  • MariaDB Server Installation and Configuration
    • MariaDB Distributions
    • Installing MariaDB on Windows
    • Installing MariaDB on Linux and MacOSX
    • Starting and Stopping MariaDB on Windows
    • Starting and Stopping MariaDB on Linux and MacOSX
    • Status Files
    • Upgrading the Server
    • Time Zone Tables
  • SQL Modes, Log Files and Binary Logging
    • MariaDB 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
    • 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
  • Tables, Data Types and 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
    • Maintaining Tables
  • Transactions and Locking
    • The concept of a Lock
    • Atomic operations on a Lock
    • Granularity of Locking
    • Explicit Table Locking
    • Advisory Locking
  • Storage Engines
    • Introduction
    • The MYISAM Engine
    • The Merge Engine
    • Other Engines: Archive, Memory, Federated, Blackhole, CSV
    • Cluster Engine Overview
    • Overview of High Availability Techniques
    • Memcached and NoSQL - An Overview
  • The Innodb Engine
    • History of Innodb
    • Features of Innodb
    • Transactions
    • Referential Integrity
    • Physical Characteristics of Innodb Tables
    • System Tablespace Configuration
    • Log File and Buffer Configuration
    • Innodb Status
  • Table Maintenance
    • Table Maintenance Operations
    • Check Table
    • Repair Table
    • Analyze Table
    • Optimize Table
    • MariaDB Check
    • MYISAMCHK
    • Repairing Innodb Tables
    • Enabling MYISAM AutoRepair
  • Backup and Recovery
    • Approaches and Strategies - Pros and Cons
    • 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 MariaDBdump
    • Importing from the Command Line using MariaDBimport
  • User Management
    • Introduction
    • User Accounts
    • Creating Users
    • Renaming Users
    • Changing Passwords
    • Dropping Users
    • Granting Privileges
    • The User Table
    • Connection Validation
  • Privileges
    • Users and Roles
    • Types of Privileges
    • Revoking Privileges
    • Resource Limits
    • The MariaDB Database
    • The Show Grants Command
  • User variables and Prepared Statements
    • User Variables
    • Prepared Statements
  • Stored Routines for Administration Purposes
    • 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
    • Overview of SQL Injection Attacks
  • Tuning Queries
    • Tuning Overview
    • Identifying Candidates for Query Analysis
    • Using Explain to Analyze Queries
    • Meaning of Explain Output
    • Explain Extended
  • Tuning and Indexes
    • Indexes for Performance
    • Creating and Dropping Indexes
    • Obtaining Index Metadata
    • Indexing Principles
  • Tuning and Indexes - More Advanced Topics
    • Indexing and Joins
    • Fulltext Indexes
    • MyISAM Index Caching
  • Tuning and Tables
    • General Table Optimizations
    • Myisam Specific Optimizations
    • Innodb Specific Optimizations
    • Other Engine Specific Optimizations
    • Practical Strategies for Tuning Tables
  • Tuning the Server
    • Status Variables
    • Server Variables
    • Performance Schema Overview
    • The Query Cache
    • Practical strategies and heuristics for Server Tuning
  • The Event Scheduler
    • Event scheduler concepts
    • Event scheduler configuration
    • Creating, altering and dropping events
    • Event scheduler monitoring
    • Events and privileges
  • Partitioned Tables
    • Partitioned tables concepts
    • Obtaining Partitioned Table Metadata
    • Types of Partitioning
    • Subpartitioning
    • Maintenance of partitioned tables
  • Replication
    • Replication - An 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
    • MariaDB Replication Utilities
    • Controlled Switchover
    • Setting Up, Testing and Troubleshooting Replication
  • MariaDB Workbench
    • Installation
    • Connecting
    • Screens