MySQL & MariaDB database administration

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 MySQL or MariaDB database server;
  • have a thorough technical insight into the physical and logical characteristics of a MySQL or MariaDB server;
  • be able to use the "client" administration tools: command line interfaces, standard graphical 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.

Schedule

No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested? Please let us know.

Intended for

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

Background

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

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

Main topics

  • PART 1: Database Implementation
  • overview of the versatile tasks of a DBA.
  • physical design and implementation of MySQL/MariaDB objects:
  • Object definition: decision rules
  • Possibilities for the implementation of a logical design: datatypes, 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 MySQL or MariaDB
  • prepare the database for performance analysis
  • access control to MySQL & MariaDB objects:
  • defining users; managing passwords;
  • scenarios for efficient security management and auditing requirements
  • the MySQL/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; defragmentation; check and repair
  • (re)partitioning of tables; moving data to other disks; changing the engine
  • use of data compression
  • hardware-tuning of the MySQL/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.

Duration

4 days.

Course leader

Peter Vanroose.


SESSION INFO AND ENROLMENT