MariaDB database administration

Obtain a deep, broad understanding of MariaDB during this four-day intensive ABIS training. Some working knowledge of MySQL or MariaDB and of the SQL SELECT statement is needed.

The database administrator (DBA) plays an important role in the data management process. The DBA is the planner and the producer during the design, the implementation and the maintenance of physical database structures. The DBA is the prime responsible for a well performing system.

At the end of this course, the participant will

• have a good idea of the different tasks of a DBA in the context of a MariaDB database server;
• have a thorough technical insight into the physical and logical characteristics of a MariaDB server;
• be able to use the "client" administration tools: more specifically the command line interfaces;
• be ready to execute scenarios for the management, monitoring and support of server tasks like security management, backup & recovery, replication, cluster setup, and performance analysis.

Intended for

New database administrators, application DBA's, and other users of MariaDB responsible for (part of) the tasks of a DBA.

Backgroud

Some practical experience with MySQL or MariaDB as a relational database system and with SQL (see MySQL & MariaDB fundamentals course and SQL workshop).

Although MariaDB is often used in the context of a web service, for this course knowledge of PHP or an other programming language is not necessary.

You can test for yourself to see if you have enough background by filling out the on-line self-test "SQL Intermediate" and the on-line self-test "MySQL/MariaDB".

Main topics

• PART 1: Database Implementation
  • overview of the versatile tasks of a DBA.
  • physical design and implementation of MariaDB objects:
    • Object definition: decision rules
    • Possibilities for the implementation of a logical design: data types, primary/foreign keys, indexes, triggers, auto-incremented columns, LOBs, views, storage engines, partitioning, ...
    • storage considerations: files, directories, disks
    • implementing a test environment & preparing for production: standard DDL (create/alter/drop), but also extensions and deviations to the SQL standard which are particular to MariaDB
    • prepare the database for performance analysis
  • access control to MariaDB objects:
    • defining users; managing passwords;
    • scenarios for efficient security management and auditing requirements
  • the MariaDB data dictionary
    • the "mysql" database
    • the object catalog: the INFORMATION_SCHEMA database
    • displaying system information and metadata
• PART 2: Database maintenance, integrity control, performance optimization
  • maintenance considerations:
    • shutting down and restarting the server
    • configuring data caching and query caching
    • benchmarking, profiling, capacity planning
    • Utilities: export/import of external data; de-fragmentation; check and repair
    • (re)partitioning of tables; moving data to other disks; changing the engine
    • use of data compression
    • use of data encryption
  • hardware-tuning of the MariaDB server
    • scaling and high availability architectures
  • query analysis and index tuning
  • data integrity, locking and transactions
  • setting up a database cluster (e.g. Galera); master/slave replication
• PART 3: Database recovery
  • logging and replication
  • backup and recovery techniques:
    • copying databases to an other machine
    • point-in-time recovery and disaster recovery: best practices

Training method

Theory (classroom instruction) with labs and ample room for practice.

Delivered as a live, interactive training – available in-person or online, or in a hybrid format. Training can be implemented in English, Dutch, or French.

Certificate

At the end of the session, the participant receives a "Certificate of Completion".

Duration
4 days.