MariaDB Komplettkurs

Agenda

1. Architektur MariaDB

   • Architecture Server
   • Architecture Server (Steps)
   • Storage Engines

2. Installation

   • Installation (Ubuntu)
   • Installation Centos/RockyLinux
   • Start/Status/Stop/Enable von MariaDB
   • Does mariadb listen to the outside world

3. Configuration

   • Adjust configuration and restart
   • Set global server system variable

4. Administration / Troubleshooting

   • Create fresh datadir
   • Debug not starting service

5. Upgrade

   • MariaDB Upgrade 10.6. -> 10.11 (Debian/Ubuntu)
   • MariaDB Upgrade 10.6 -> 10.11 (RHEL)

6. Graphical Tools

   • Overview

7. Database Objects

   • Data Types
   • Create Database
   • Show structure of table
   • Show all tables within db

8. InnoDB - Storage Engine

   • InnoDB - Storage Engine - Structure
   • InnoDB Buffer Pool Size bestimmen und setzen inkl. free buffers
   • Important InnoDB - configuration - options to optimized performance
   • Calculate innodb logfile size

9. Training Data

   • Setup training data "contributions"
   • Setup sakila test db

10. User Rights / Users

    • Create User/Grant/Revoke - Management of users
    • Change password of user
    • Automatisches Einloggen ohne Passwort
    • Disable unix_socket authentication for user
    • Debug and Setup External Connection
    • Get Rights of user
    • Secure with SSL server/client
    • Auth with unix_socket
    • User- and Permission-concepts (best-practice)
    • Setup external access
    • Users zwingen sich neu anzumelden

11. User Authentication

    • ed25519 authentification

12. Binlog, Backup and Restore (Point-In-Time aka PIT)

    • binlog aktivieren und auslesen
    • binlog_format
    • Backup with mysqldump - best practices
    • backup user with mysqldump
    • PIT - Point in time Recovery - Exercise
    • Backup Single Database, Structure and only data
    • Flashback
    • mysqldump-vs-mariabackup
    • mariabackup
    • incrementelles backup mit mariadb
    • Delete binary logs

13. Logging

    • General Log
    • Binary Logs - Activating in Ubuntu
    • Binary Logs - Basics
    • Error logging (Output of start / stop of mariadb

14. Security

    • Table encryption

15. Performance

    • Slow Query Log
    • Percona-toolkit-Installation - Centos
    • pt-query-digest exercise (Hitliste von slow-query-log erstellen)
    • Umgang mit grossen Datenbeständen

16. Optimal use of indexes

    • Describe and indexes
    • Find out indexes
    • Index and Functions
    • Index and Likes
    • Find out cardinality without index

17. Monitoring

    • What to monitor?
    • Percona Management and Monitoring
    • Monitoring mit IBM

18. Replication

    • Slave einrichten - gtid (mit mariabackup)
    • Slave einrichten - old styke - masterpos
    • Binary Logs auf master bis slave-log-master-position ändern

19. Replication / Galera

    • FAQ

20. Galera

    • How does Galera transfer data IST/SST
    • Installation and Configuration (Centos/Redhat 8) with mariabackup as sst
    • Installation and Configuration (Ubuntu)
    • Installation and Configuration with mariabackup as sst (Ubuntu)
    • 1. Node started nicht nach Crash, z.B. Stromausfall
    • Upgrade Minor/Major
    • Determine rights size of gcache - e.g. for maintenance
    • Monitoring Galera - What to monitor

21. Tipps & Tricks

    • Set hostname on systemd-Systems
    • Frisches Datenverzeichnis anlegen
    • In den Root-Benutzer wechseln
    • Service Debuggen
    • online schema change without blocking

22. Locking

    • Table Locks
    • Implicit Locks
    • Identify Deadlocks in innodb

23. Optimal use of indexes

    • Index-Types
    • Describe and indexes
    • Find out indexes
    • Index and Functions
    • Index and Likes
    • profiling-get-time-for-execution-of.query
    • Find out cardinality without index

24. Dokumentation (Releases)

    • Identify Long-Term Support Releases

25. Dokumentation (Galera)

    • MariaDB Galera Cluster
    • MySQL Galera Cluster

26. Dokumentation (functions)

    • Built-In Functions

27. Misc

    • Bis zu welcher Größe taugt mariadb

28. Database Functions/Procedure/Triggers/Events

    • Events
    • Procedures
    • Functions
    • Triggers

Backlog

1. Architecture of MariaDB

   • Query Cache Usage and Performance
   • Optimizer-Basics

2. Installation

   • Installation SLES15
   • Does mariadb listen to the outside world

3. Backup

   • Use xtrabackup for MariaDB 5.5
   • Ready-made-back-scripts
   • Simple-Backup-Script

4. Information Schema / Status / Processes

   • Show server/session status
   • Kill long running process
   • Kill (kickout user) and stop server

5. User Rights

   • Debug and Setup External Connection
   • Get Rights of user
   • Secure with SSL server/client
   • Auth with unix_socket
   • Authentification and Authorization
   • User- and Permission-concepts (best-practice)
   • Setup external access

6. SELinux

   • Welche Ports sind freigegeben? (MariaDb startet damit)
   • Neues Datenverzeichnis SELinux bekanntmachen - semanage fcontext
   • Probleme mit SELinux erkennen und debuggen

7. Database - Objects

   • Triggers
   • Functions
   • Stored Procedure
   • Events

8. Upgrade

   • MariaDB Upgrade 10.3 (Centos) -> 10.4 (Mariadb.org)
   • MariaDB Upgrade 10.4 -> 10.5 (Centos)
   • MariaDB Upgrade 5.5 -> 10.5

9. Performance

   • io-Last/CPU-Last
   • Views and performance
   • Partitions and Explain
   • 3 Phases of DataSize
   • Slow Query Log
   • profiling-get-time-for-execution-of.query

10. Replication

    • Slave einrichten - Centos - old style (master_pos)
    • MaxScale installieren
    • Reference: MaxScale-Proxy mit Monitoring
    • Walkthrough:Automatic Failover Master Slave

11. Tools & Tricks

    • Percona-toolkit-Installation - Ubuntu

    • pt-query-digest under Windows

    • pt-query-digest - analyze slow logs

    • pt-online-schema-change howto

    • Ubuntu-with-Vagrant

    • mysql-client

    • Schweizer Such-Taschenmesser grep -r

    • Set timezone in Centos 7/8

    • Ist die Netzwerkkarte eingerichtet - nmtui

    • User anlegen und passwort vergeben (Centos/Redhat)

    • Scripts for deploying galera-cluster to Ubuntu 20.04

12. Extras

    • User Variables
    • Installation sakila-db

13. Diagnosis and measurement of performance

    • Best practices to narrow down performance problems

14. Performance and optimization of SQL statements

    • Do not use '*' whenever possible
    • Optimizer-hints (and why you should not use them)

15. Replication

    • Replikation Read/Write

16. Performance

    • Best Practices
    • Example sys-schema and Reference
    • Change schema online (pt-online-schema-change)
    • Optimizer-Hints

17. Documentation / Literature

    • MySQL - Peformance Blog
    • Server System Variables
    • Killing connection
    • MariaDB - One Script Installation
    • MariaDB - Information Schema Tables
    • MariaDB - slow query log
    • MariaDB - sys - vor 10.6
    • Differences Community / Enterprise Version - nearly the same
    • Hardware Optimization
    • Source-Code MariaDB
    • Effective MySQL
    • Last Training

18. Questions and Answers

    • Questions and Answers
    • Best filesystem for MariaDB

19. MySQL Do-Nots

    • mysql-do-nots