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 database server;
• have a thorough technical insight into the physical and logical characteristics of a MySQL 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.

• PART 1: Database Implementation
  • overview of the versatile tasks of a DBA.
  • physical design and implementation of MySQL 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 MySQL
    • prepare the database for performance analysis
  • access control to MySQL objects:
    • defining users; managing passwords;
    • scenarios for efficient security management and auditing requirements
  • the MySQL 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 MySQL server
    • scaling and high availability architectures
  • query analysis and index tuning
  • data integrity, locking and transactions
  • setting up a database cluster (e.g. NDB Cluster); 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

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.

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 is often used in the context of a web service, for this course knowledge of PHP or an other programming language is not necessary.

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