Card, PayPal or bank transfer · Morocco and worldwideتعلَّم · مارِس · أتقِن

Catalogue / Infrastructure & systems

Database administration: MariaDB, MySQL and PostgreSQL in production

Install, tune, back up, replicate, monitor and troubleshoot MariaDB, MySQL and PostgreSQL like a production database administrator, all the way to point-in-time recovery and the incidents that wake you up at night.

  • Intermediate to advanced
  • 12 lessons
  • 13 h 55 min
  • Versions MariaDB 10.11, MariaDB 11.4, MariaDB 11.8, MySQL 8.4, PostgreSQL 15, PostgreSQL 16, PostgreSQL 17, PostgreSQL 18, MariaDB 10.11 à 11.8, PostgreSQL 15 à 18, MariaDB Galera 10.11 à 11.8, Patroni 3 / 4, HAProxy 2.8+, PgBouncer 1.2x, postgres_exporter, mysqld_exporter

A MasterLinux course · Original text

What you will learn

  • Install MariaDB, MySQL and PostgreSQL for production and master my.cnf, postgresql.conf, pg_hba.conf and pg_ident.conf
  • Create least-privilege service accounts, encrypt connections with TLS and log activity with audit plugins
  • Write reliable logical backups (mariadb-dump, pg_dump, pg_dumpall) that fail loudly when something goes wrong
  • Take physical backups (mariadb-backup, pg_basebackup) and restore to the exact minute with binlogs and WAL
  • Set up MariaDB replication with GTID and PostgreSQL streaming replication with replication slots
  • Understand and operate a Galera cluster, a Patroni cluster and their HAProxy front end
  • Find costly queries (slow query log, pg_stat_statements, EXPLAIN) and tune memory (buffer pool, shared_buffers, work_mem)
  • Master VACUUM and autovacuum, monitor with Prometheus and prepare a major version upgrade without surprises
  • Solve real incidents - broken replication, disk full of binlogs or WAL, blocking locks, saturated connections

Course content

12 lessons · 13 h 55 min
Section 1 — Install and configure3 lessons · 2 h 50 min
  1. MariaDB and MySQL in production: installation, files and my.cnf Preview55 min
  2. PostgreSQL in production: clusters, postgresql.conf, pg_hba.conf and pg_ident.conf 55 min
  3. Accounts, privileges and security: least privilege, TLS, encryption and auditing 1 h
Section 2 — Back up and restore2 lessons · 2 h 10 min
  1. Reliable logical backups: mariadb-dump, pg_dump, pg_dumpall and automatic checks 1 h
  2. Physical backups and point-in-time restore: mariadb-backup, pg_basebackup, binlogs and WAL 1 h 10 min
Section 3 — Replication and high availability3 lessons · 3 h 20 min
  1. MariaDB and MySQL replication: primary, replica and GTID 1 h 5 min
  2. PostgreSQL streaming replication: standby, replication slots and failover 1 h 5 min
  3. High availability: Galera cluster, Patroni cluster and HAProxy proxy 1 h 10 min
Section 4 — Performance and operations2 lessons · 2 h 20 min
  1. Performance in operations: slow queries, EXPLAIN, online indexes, memory and connection pooling 1 h 10 min
  2. Maintenance, monitoring and version upgrades: VACUUM, autovacuum, alerts and pg_upgrade 1 h 10 min
Section 5 — Incidents and project2 lessons · 3 h 15 min
  1. Production incidents: full disk, blocking lock, lagging autovacuum, saturated connections 1 h 15 min
  2. Final project: putting the Tangier agency's database server into production 2 h

Requirements

  • The “Fundamentals” course (included) or solid hands-on experience with the Linux command line
  • Able to administer a Debian or Rocky server (packages, services, configuration files)
  • Basic SQL knowledge (SELECT, INSERT, CREATE TABLE); the “SQL” course is a good companion

Who is this course for?

  • System administrators who inherit their company's databases
  • Operations and on-call technicians who want to know how to react to a database incident
  • Back-end developers and DevOps engineers who host MariaDB or PostgreSQL themselves
  • Students aiming for a DBA or production engineer job at an IT services company