MariaDB

MariaDB ist ein multi-user, multi-threaded, binärkompatibler Fork der relationalen Datenbank MySQL. Da Oracle inzwischen die Markenrechte an MySQL hält, musste ein neuer Name gefunden werden - „MariaDB“ und die zugrundeliegende Storage-Engine namens „Aria“ gehen auf den Namen der jüngeren Tochter des ehemaligen MySQL-Hauptentwicklers zurück, der auch den Fork initiiert hat.

In Fedora ist MySQL Ende 2012 durch MariaDB ersetzt worden, in Folge dadurch auch in und seit RHEL 7. Das Wikipedia-Projekt hat sich unter der Haube im April 2013 von MySQL verabschiedet.

Bis MariaDB 5.5 heisst der Service noch mysql, danach mariadb. Wird ein MariaDB 5 auf MariaDB 10 aktualisiert, bleibt es aber bis einschliesslich 10.3 beim Service-Namen mysql.

Seit MariaDB 10.5 werden die Begriffe master und slave durch primary und replica ersetzt.

Versionen (RHEL-Repo)
  • RHEL 8: 10.3

  • RHEL 7: 5.5

  • RHEL 6: 5.1

Versionen (Hersteller-Repo)
  • MariaDB 10.5: RHEL 7 bis 7.7, RHEL 8+

  • MariaDB 10.4: RHEL 6 bis 6.9, RHEL 7 bis 7.7, RHEL 8+

  • MariaDB 10.3: RHEL 6 bis 6.9, RHEL 7 bis 7.7, nur RHEL 8.0

  • MariaDB 10.2: RHEL 6 bis 6.9, RHEL 7 bis 7.7

  • MariaDB 10.1: RHEL 6 bis 6.9, RHEL 7 bis 7.7

  • MariaDB 10.0: RHEL 6 bis 6.9, RHEL 7 bis 7.4

  • MariaDB 5.5: RHEL 6 bis 6.9, RHEL 7 bis 7.7

  • MariaDB 5.3: nur RHEL 5

In MariaDB sind „Minor-Versionen“ beispielsweise 10.4.12 und 10.4.13.

Abkürzungen
  • AI = Auto-Increment

  • BIN = Binary

  • G = Generated Always As ()

  • NN = NOT NULL

  • PK = Primary Key

  • UN = Unsigned

  • UQ = Unique

  • ZF = Zerofill

Es gibt jede Menge Detail-Unterschiede zwischen MySQL 5 und MariaDB 10. Eine winzige Auswahl aus dem Betrieb:

  • Die Unterschiede in den Default-Einstellungen (my.cnf, SHOW VARIABLES) sind gigantisch.

  • MariaDB 10 ist restriktiver.

  • String-Längenbeschränkungen: in v5 wurden längere Strings beim INSERT in die Tabelle einfach abgeschnitten, in v10 ergibt das einen Fehler.

  • Integer: in v5 war es möglich, leere Strings in ein Integer-Feld zu schreiben; in v10 führt das zu einem Fehler.

Installation & Konfiguration

Wir empfehlen die Installation direkt aus dem Hersteller-, und nicht aus den RHEL-Repos. Unbedingt auf die Gross-/Kleinschreibung bei yum install MariaDB-server achten, damit der Server aus dem Hersteller-Repo geholt wird.

Repository Configuration Tool: https://downloads.mariadb.org/mariadb/repositories

#VER=10.2
#VER=10.3
#VER=10.4
#VER=10.5
VER=10.6
#VER=10.7
#VER=10.8

curl -LsS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version=$VER

yum -y install MariaDB-server

systemctl enable --now mariadb

# optional
yum -y install MariaDB-client MariaDB-backup
yum -y install maxscale

Egal welche $VER für das Repo herangezogen wird: jede baseurl liefert neben $VER immer auch die aktuellste Version, die damit standardmässig installiert wird.

Tipp

Wer eine spezifische Version wie z.B. v 5.5 einsetzen muss, lässt sich mit yum list MariaDB-server --show-duplicates zunächst anzeigen, welche Minor-Versionen verfügbar sind. Die Installation erfolgt dann per yum -y install MariaDB-server-5.5.66.

WICHTIG: da hier eine spezifische, tiefere Version installiert wird, würde diese beim nächsten yum update aktualisiert werden. Um das zu verhindern, muss die installierte Version per yum -y install yum-plugin-versionlock; yum versionlock mariadb* gelockt werden.

Anschliessend kann man dem MariaDB-Standardbenutzer „root“ ein komplexes Passwort zuteilen sowie den Gast-Zugang und die zusätzlich installierte Datenbank „test“ löschen:

mariadb-secure-installation

Es erhöht die Sicherheit, die root-Benutzer nach der Installation umzubenennen - es sind bis zu vier, je nach dem, ob die Maschine über einen voll-qualifizierten Hostnamen verfügt oder nicht:

  • einen für den Zugang von ::1 (root@‘::1‘)

  • einen für den Zugang von 127.0.0.1 (root@‘127.0.0.1‘)

  • einen für den Zugang von hostname (root@‘hostname‘)

  • einen für den Zugang von hostname.fqdn (root@‘hostname.fqdn‘)

mysql --user=root --password

Zugriff dann per

mysql --user=root

root-Benutzer umbenennen - bis 10.3:

update mysql.user set user = 'mariadb-admin' where user = 'root';
flush privileges;
quit

Ab 10.4 ist mysql.user neu eine View, und das Verfahren hat sich geändert. Hier gilt:

rename user 'root'@'localhost' to 'mariadb-admin'@'localhost';

Wer möchte, dass man sich von jedem Rechner aus (Adresse: „%“, dem Wildcard in SQL) mit MariaDB und root-Rechten verbinden darf, fügt einen Benutzer (hier per GRANT-Statement) hinzu:

grant all privileges on *.* to 'mariadb-admin'@'%'
    identified by 'password'
    with grant option;
flush privileges;
quit

Wer selektiver vorgeht und beispielsweise nur Zugriffe aus dem 10.26.6er-Netz zulassen möchte, verwendet create user 'mariadb-admin'@'10.26.6.%' ....

Bemerkung

Wer auf einer Maschine den Kommandozeilen-Client mysql benötigt, installiert ihn mit yum -y install mariadb (damit wird nicht der MariaDB-Server installiert). Der Client landet immer auf der Maschine, sobald man den MariaDB-Server installiert.

Konfigurationsdateien unterhalb von /etc/my.cnf.d werden erwartungsgemäss alphabetisch ausgewertet. Einstellungen in der /etc/my.cnf.d/mysettings.cnf werden also durch eine /etc/my.cnf.d/z_mysettings.cnf bei Bedarf überschrieben.

Upgrading

Wir beschreiben das Upgrade-Prozedere anhand der aus den MariaDB-Repos installierten Datenbank.

  • Backups aller Datenbanken erzeugen.

  • Evtl. Konfiguration in /etc/my.cnf.d an die zukünftige Version anpassen.

  • Passwort des DBA bereithalten.

  • Auf die DB-bezogene Cronjobs deaktivieren.

  • MariaDB stoppen: systemctl stop mariadb

  • MariaDB deinstallieren: yum remove MariaDB-server

  • Die bisher verwendeten /etc/yum.repos.d/mariadb.repo-Dateien deaktivieren.

  • yum clean all durchführen.

  • Repository-Datei unter /etc/yum.repos.d auf die neue Version anpassen.

  • yum clean all

  • Aktuellere Version installieren: yum install MariaDB-server

  • /etc/my.cnf bzw. /etc/my.cnf.d/* anpassen. Beispielsweise sollten alle Einstellungen entfernt werden, die nicht mehr unterstützt werden. Siehe dazu auch die Tabelle am Ende dieses Dokuments.

  • MariaDB starten: systemctl enable mariadb; systemctl start mariadb

  • Upgrade-Skript ausführen: mysql_upgrade --user=mariadb-admin --password - es passt System-Tabellen so an, dass sie mit der neuen Version kompatibel sind, prüft darüber hinaus alle Tabellen auf Konsistenz und markiert sie als mit der neuen Version kompatibel.

  • MariaDB restarten: systemctl restart mariadb

Clients

Tipp

Verbinden über einen SSH-Tunnel: ssh -L 3333:localhost:3306 mariadb-host

MySQL Workbench

Für die Administration von MySQL-Datenbanken eignet sich MySQL Workbench auf einer Admin-Workstation. Für MariaDB eignet sich das Tool Stand 2021-04 noch, jedoch nicht mehr in allen Bereichen, beispielsweise in der Benutzer-Administration. Die RPM-Pakete für die Yum-Repositories des Herstellers finden sich auf http://dev.mysql.com/downloads/repo/yum/.

Am Beispiel Fedora Workstation:

#VER=mysql80-community-release-fc33-1
VER=mysql80-community-release-fc34-1

dnf -y install https://dev.mysql.com/get/$VER.noarch.rpm
dnf -y install mysql-workbench-community

Konfiguration liegt unter ~/.mysql/workbench.

Backup und Restore

Physische Backups

Im Prinzip Sicherung des Datenverzeichnisses /var/lib/mysql. In der Regel schnell, aber nur auf der gleichen MariaDB-Version verwendbar.

Logische Backups

Es werden universelle SQL-Dateien (ASCII) geschrieben, die auch auf anderer Hardware, anderen MariaDB-Versionen oder (unter Umständen) auf anderen DB-Systemen zum Import verwendet werden können. Der Nachteil: das dauert, auch auf moderner Hardware.

  • Dump: realistisch ungefähr 1 GB pro Minute

  • Restore: realistisch ungefähr 200 MB pro Minute Wichtig zu wissen: die innodb_buffer_pool-Caches werden durch die beim Backup stattfindenden Full Table Scans zugemüllt - hier sind Massnahmen zu treffen, um diese vorher zu sichern.

$MYSQL --host=$HOST --user=$USER --password=$PASSWORD \
    --execute='SET GLOBAL innodb_buffer_pool_dump_now = ON;'

# do the backup

$MYSQL --host=$HOST --user=$USER --password=$PASSWORD \
    --execute='SET GLOBAL innodb_buffer_pool_load_now = ON;'

Tipp

Angaben im SQL-Dump der Form /*!40014 SET ... */; sind Conditional Comments. Im Beispiel wird SET ... nur auf einem MySQL 4.0.14 oder später ausgeführt.

Für globale Locks ist mindestens das RELOAD-Privileg nötig.

mysql-Client

Der Restore mit dem Kommandozeilen-Client:

mysql --user=root --password mydb < mybackup.sql

Fortschrittsanzeige mit Hilfe des „pipe viewers“:

dnf -y install pv
pv mybackup.sql | mysql --user=root --password mydb

Import dauert ewig? Ein schnellerer Restore, der einige Checks abschaltet und damit um Faktoren beschleunigt abläuft, geschieht mittels:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

here comes the backup_file.sql

COMMIT;

Um SQL-Dumps im GB-Bereich zu bearbeiten, empfiehlt sich hexedit: mit Tab auf die rechte ASCII-Seite wechseln, mit Space den Anfang der Datei bis zu den ersten SQL-Anweisungen überschreiben, und anschliessend obige Statements einfügen. F2 speichert den Dump.

mydumper und myloader

Siehe https://github.com/maxbube/mydumper.

Geht durch Threading etwas schneller zu Werke als mysqldump. Von uns empfohlen.

Die Anzahl der --threads (Default: 4) kann auf dedizierten DB-Servern mit vielen kleinen Tabellen durchaus etwas höher als die Anzahl der CPU-Cores sein (ein Thread lastet einen CPU-Core auf einer Multiprozesser-Maschine meist nicht zu 100% aus), bringt aber keine Geschwindigkeitssteigerung. Wir empfehlen vier Threads, und sonst diese auf --threads=(Anzahl Cores - 2) zu setzen.

mydumper kann die User-Credentials aus der ~/.my.cnf, einer cnf-Datei aus /etc/my.cnf.d, Sektion [client], oder einer beliebigen Datei, die per --defaults-files=/path/to/file referenziert wird, verwenden:

[client]
user=mydumper_user
password=mydumper_s3cr3t_pass
mydumper

Installation

# needed on RHEL 8:
dnf -y install compat-openssl10
dnf -y install https://github.com/maxbube/mydumper/releases/download/v0.10.5/mydumper-0.10.5-1.el8.x86_64.rpm

# no further dependencies on RHEL 7
yum -y install https://github.com/maxbube/mydumper/releases/download/v0.10.5/mydumper-0.10.5-1.el7.x86_64.rpm

Full-Backup

# most used variant:
mydumper --host=$HOST --user=$USER --password=$PASSWORD \
    --compress \
    --outputdir=$BACKUP_DIR

mydumper --host=$HOST --user=$USER --password=$PASSWORD \
    --build-empty-files \
    --compress \
    --events \
    --less-locking \
    --logfile=$BACKUP_DIR/mydumper.log \
    --outputdir=$BACKUP_DIR \
    --routines \
    --threads=$THREADS \
    --triggers \
    --updated-since=1 \
    --use-savepoints \
    --verbose=3
  • --compress verwendet gzip mit der Standard-Kompressionsstufe 6.

  • wird --database weggelassen, werden alle Datenbanken gedumpt

  • --logfile $BACKUP_DIR/mydumper.log wird bei jedem Run überschrieben.

  • --outputdir $BACKUP_DIR verhindert das Erzeugen von Verzeichnissen der Art export-20201027-145722.

  • --updated-since 1 sichert nur noch Tabellen, die in den letzten 1 Tagen aktualisiert wurden.

  • --use-savepoints schaltet wie mysqldump das Binlog für die Dauer des Backups ab.

myloader

Restore: Full-Backup wiederherstellen (bereits existierende und nicht im Backup enthaltene Datenbanken bleiben dabei natürlich unangetastet):

# most used variant:
myloader --host=$HOST --user=$USER --password=$PASSWORD \
    --directory=$BACKUP_DIR \
    --overwrite-tables

Einzelne Datenbank(en) wiederherstellen (und dabei neu benennen):

myloader --host=$HOST --user=$USER --password=$PASSWORD \
    --directory=$BACKUP_DIR \
    --database=$NEW_NAME_OF_DB \
    --overwrite-tables \
    --queries-per-transaction=1000 \
    --source-db=$DB_TO_RESTORE \
    --threads=$THREADS \
    --verbose=3
  • --enable-binlog steht standardmässig auf False.

  • --overwrite-tables = DROP TABLE IF EXISTS, im Standard abgeschaltet. Backup-User benötigt sonst DROP-Rechte.

Der Restore muss mit einem Benutzer durchgeführt werden, der ausreichend Rechte zum Anlegen von Tabellen etc. besitzt. Falls in eine neue Datenbank wiederhergestellt werden soll, muss diese bereits angelegt sein. Falls der Restore Meldungen der Form ** (myloader:4191): CRITICAL **: 11:42:06.213: Error restoring db.table from file db.table-schema-view.sql.gz: Table 'db.other_table' doesn't exist bringt, einfach den Import erneut laufen lassen (da dann die vermissten Tabellen mit ziemlicher Sicherheit vorhanden sind).

Der Restore einzelner Tabellen gestaltet sich simpel, da diese als SQL-Datei vorliegen.

mysqldump, mariadb-dump

mysqldump erzeugt logische Dumps.

Um den Dump von Server A auf Server B zu bekommen, dieses auf Server B ausführen:

mysqldump --host=server_a -u root -p --single-transaction --flush-logs --hex-blob --source-data=2 --routines --all-databases | mysql
# --source-data causes the binary log position and filename to be appended to the output.
# If equal to 1, will print it as a CHANGE MASTER command.
# If equal to 2, that command will be prefixed with a comment symbol.

Ein DB-für-DB Backup-Script auf Basis von mysqldump:

echo Starting the logical backup...

if command -v zstd &>/dev/null
then
    PACKER=zstd
    EXTENSION=zst
else
    PACKER='gzip --fast'
    EXTENSION=gz
fi

# We do NOT lock the database by "BACKUP STAGE START; BACKUP STAGE BLOCK_COMMIT; DO SLEEP(very_long);"
# or similar, because it is too dangerous to run into a deadlock situation
# (client writes to a locked DB, and both client and the backup are blocked until the end of the sleep,
# and the backup is still not consistent after the end of the sleep).
# Instead, we use --single-transaction again. So, use the logical backup with care and don't expect
# it to be consistent (foreign keys etc.).

\rm -rf $BACKUP_DIR_LOGICAL
mkdir -p $BACKUP_DIR_LOGICAL

# go through all databases
echo Dumping...
for DB in $DBS
do
    if [ "$DB" == 'performance_schema' ]
    then
        continue
    fi

    CHARSET=$($MYSQL --host=$HOST --user=$USER --password=$PASSWORD \
            $DB --execute='SELECT @@character_set_database;' | \
            $AWK '{ print $1}' | $GREP -v '^@@character_set_database')

    # go through all tables in database
    TABLES=$($MYSQL --host=$HOST --user=$USER --password=$PASSWORD \
        --execute='show tables;' $DB | \
        $AWK '{ print $1}' | $GREP -v '^Tables' )
    for TABLE in $TABLES
    do
        if [ "$DB" == 'mysql' ]
        then
            if [ $TABLE == 'general_log' ]
            then
                continue
            fi
            if [ $TABLE == 'slow_log' ]
            then
                continue
            fi
            if [ $TABLE == 'transaction_registry' ]
            then
                continue
            fi
        fi

        $MYSQLDUMP --host=$HOST --user=$USER --password=$PASSWORD \
            --create-options \
            --default-character-set=$CHARSET \
            --routines \
            --single-transaction \
            --skip-dump-date \
            --skip-extended-insert \
            --triggers \
            $DB $TABLE | $PACKER > $BACKUP_DIR_LOGICAL/$DB.$TABLE.sql.$EXTENSION
        if [ $? -ne 0 ]
        then
            echo "Error dumping $DB.$TABLE" >&2
        else
            echo "$DB.$TABLE dumped"
        fi
    done
done

echo Done.

Mariabackup

Ab Version 10.1.23 kann das ursprünglich von Percona XtraBackup 2.3.8 geforkte Mariabackup verwendet werden, um InnoDB und XtraDB zu sichern. Unter InnoDB sind „hot online“-Backups möglich.

Wem es auf Performance ankommt: Unserer Erfahrung nach ist das Tool für ein erstmaliges Full Backup (und damit auch für die Einrichtung einer Replikation) nicht schneller als mysqldump oder mariadbdump, im Gegenteil - es ist nochmal langsamer, da es sich unter anderem ausgiebig mit den InnoDB redo logs beschäftigt, siehe Output weiter unten.

Full Backup erzeugen:

Siehe https://mariadb.com/kb/en/full-backup-and-restore-with-mariabackup/

rm -rf /backup/mariadb-dump/*
mariabackup --host=$HOST --user=$USER --password=$PASSWORD \
    --backup \
    --target-dir=/backup/mariadb-dump/full

Der Output:

[00] 2020-10-19 15:20:37 Connecting to MySQL server host: localhost, user: mariadb-admin, password: set, port: not set, socket: not set
[00] 2020-10-19 15:20:37 Using server version 10.5.6-MariaDB-log
mariabackup based on MariaDB server 10.5.6-MariaDB Linux (x86_64)
[00] 2020-10-19 15:20:37 uses posix_fadvise().
[00] 2020-10-19 15:20:37 cd to /var/lib/mysql/
[00] 2020-10-19 15:20:37 open files limit requested 0, set to 1024
[00] 2020-10-19 15:20:37 mariabackup: using the following InnoDB configuration:
[00] 2020-10-19 15:20:37 innodb_data_home_dir =
[00] 2020-10-19 15:20:37 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2020-10-19 15:20:37 innodb_log_group_home_dir = ./
[00] 2020-10-19 15:20:37 InnoDB: Using Linux native AIO
2020-10-19 15:20:37 0 [Note] InnoDB: Number of pools: 1
[00] 2020-10-19 15:20:37 mariabackup: Generating a list of tablespaces
2020-10-19 15:20:37 0 [Warning] InnoDB: Allocated tablespace ID 2727 for mysql/innodb_table_stats, old maximum was 0
[00] 2020-10-19 15:20:40 >> log scanned up to (31993329429)
[01] 2020-10-19 15:20:40 Copying ibdata1 to /backup/mariadb-dump/ibdata1
[00] 2020-10-19 15:20:41 >> log scanned up to (31993329705)
[00] 2020-10-19 15:20:42 >> log scanned up to (31993329722)
...
[00] 2020-10-19 15:20:50 >> log scanned up to (31993330151)
[00] 2020-10-19 15:20:51 >> log scanned up to (31993330151)
2020-10-19 15:20:52 0 [Note] InnoDB: Read redo log up to LSN=31993330688
[00] 2020-10-19 15:20:52 >> log scanned up to (31993330444)
[00] 2020-10-19 15:20:53 >> log scanned up to (31993330444)
...
[00] 2020-10-19 15:20:59 >> log scanned up to (31993330865)
[00] 2020-10-19 15:21:00 >> log scanned up to (31993330929)
[01] 2020-10-19 15:21:01         ...done
[01] 2020-10-19 15:21:01 Copying ./mysql/innodb_table_stats.ibd to /backup/mariadb-dump/mysql/innodb_table_stats.ibd
[01] 2020-10-19 15:21:01         ...done
...

Full Backup wiederherstellen:

mkdir /backup/mariadb-dump/temp
cd /backup/mariadb-dump/temp
tar --use-compress-program zstd -xvf ../full/server.tar.zst

# make the full backup to be point-in-time consistent:
mariabackup --prepare --target-dir=/backup/mariadb-dump/temp

systemctl stop mariadb
rm -rf /var/lib/mysql/*
mariabackup --copy-back --target-dir=/backup/mariadb-dump/full
chown -R mysql:mysql /var/lib/mysql/
restorecon -Fvr /var/lib/mysql/
systemctl start mariadb

Einzelne DB aus einem Full Backup wiederherstellen:

Siehe https://mariadb.com/resources/blog/how-to-restore-a-single-database-from-mariadb-backup/

mkdir /backup/mariadb-dump/temp
cd /backup/mariadb-dump/temp
tar --use-compress-program zstd -xvf ../full/server.tar.zst
mariabackup --prepare --export --target-dir=/backup/mariadb-dump/temp --databases $DB
  • Optional Datenbank manuell erzeugen: CREATE DATABASE ...

  • Tabelle identisch zum Original manuell erzeugen, jedoch OHNE Constraints: CREATE TABLE ...

  • Tablespace löschen: ALTER TABLE ... DISCARD TABLESPACE;

  • Backup zurückkopieren:

    \cp /backup/mariadb-dump/temp/$DB/*.{ibd,cfg,frm} /var/lib/mysql/$DB/
    chown -R mysql:mysql /var/lib/mysql/
    restorecon -Fvr /var/lib/mysql/
    
  • Tablespace importieren: ALTER TABLE ... IMPORT TABLESPACE;

  • Constraints wiederherstellen: ALTER TABLE .... ADD CONSTRAINT ....

Partielle Backups (einzelne Datenbank sichern):

mariabackup --host=$HOST --user=$USER --password=$PASSWORD \
    --backup \
    --target-dir=$BACKUP_DIR_PARTIAL/datadir \
    --databases="$DB"

Eine einzelne Datenbank aus dem Full Backup wiederherstellen ist möglich, aber viel zu kompliziert. Wir setzen hier auf logische Backups.

Inkrementelle Backups:

columnstoreBackup

Aus dem MariaDB ColumnStore Tools Paket. Wird verwendet, um ColumnStore-Tabellen zu sichern.

Siehe https://mariadb.com/kb/en/backup-and-restore-for-mariadb-columnstore-110-onwards/

Replikation

Das Standard-Replikationsszenario besteht aus einem Primary- und (mindestens) einem Replica-Server. Der MariaDB Galera Cluster ist ein Multi-Primary-Cluster.

Primary/Replica

Begriffe:

  • früher: Master und Slave

  • heute: Primary und Replica

In diesem Standardszenario wird nur auf dem Primary geschrieben; Änderungen sind beinahe instant auf dem oder den Replica-Servern verfügbar. Die Replica können daher auch für reine Lese-Anforderungen eingesetzt werden. Die Replicas kommen (anders als früher) gut damit zurecht, wenn sie runtergefahren werden, auch wenn sich währenddessen auf dem Primary sowohl Schemata als auch Daten ändern.

Primary und Replica kommunizieren wenn nicht anders konfiguriert über Port 3306, also Firewall-Rules auf den beteiligten Hosts anpassen.

Achtung

Ein Replica übernimmt neben den Datenbanken auch die Benutzer-Accounts des Primary - also auch das Passwort für root, mariadb-admin, Backup-Benutzer und andere.

Siehe auch:

Auf dem Primary:

Binary Logging einschalten, das optimale Binary Logging Format (optional) und eine im Netz eindeutige Server-ID auf einen Wert zwischen 1 und 4294967295 setzen:

/etc/my.cnf.d/server.cnf
binlog_format = MIXED
log_bin = ON
server_id = 1

Jetzt einen Replikations-Benutzer mit Passwort und REPLICATION SLAVE-Berechtigung anlegen:

GRANT REPLICATION SLAVE ON *.* TO 'mariadb-replica'@'replica-host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;``

MariaDB-Server neu starten.

Auf dem Replica:

Im Netz eindeutige Server-ID vergeben:

/etc/my.cnf.d/server.cnf
server_id = 2

Replica-Server neu starten.

Tipp

Unsere Ansible-Rollen für den MariaDB-Server leisten die Installation bis zu obigem Punkt. Anschliessend ist Handarbeit angesagt.

Auf dem Primary:

Backup der Datenbanken beispielsweise mit mariadb-dump oder Mariabackup erzeugen und auf den Replica kopieren.

Um falsch ermittelten Binlog-Positionen und inkonsitenten Backups vorzubeugen, den Parameter --master-data verwenden. Dieser …

  • schaltet --lock-tables ab und --lock-all-tables an (solange --single-transaction nicht angegeben wird)

  • setzt also ein Read-Lock (FLUSH TABLES WITH READ LOCK)

  • ermittelt die aktuelle Position des Binary-Logs (sonst per SHOW MASTER STATUS; ermittelt) und schreibt diese in den Dump

  • gibt die DB nach dem Dump wieder frei (UNLOCK TABLES;)

# This sets a database read lock (due to --master-data)!
mysqldump --verbose --user=$DBUSER --password --master-data --opt --comments --hex-blob --dump-date --no-autocommit --flush-privileges --all-databases | gzip -1 > all-databases.sql.gz

scp all-databases.sql.gz ...
rm -f all-databases.sql.gz

Achtung

Wir hatten schon den Fall, dass mysqldump bei grossen Datenbanken (>= 100 GB) selbst mit --master-data keinen konsistenten/verwendbaren Dump erzeugen konnte, der als Import auf einem Replika funktioniert hätte. Stattdessen hat sich mariabackup --backup --user $USER --password $PASSWORD --target-dir /backup/mariadb-dump/physical/ bewährt.

Auf dem Replica:

Falls vorher schon mal importiert wurde, nicht vergessen, die Datenbanken auf dem Replika zu entfernen. Sonst einfach die Datenbanken importieren, zum Beispiel per

nice --adjustment 19 zcat all-databases.sql.gz | mysql --user=$DBUSER --password
CHANGE MASTER TO
  MASTER_HOST='primary.domain.com',
  MASTER_USER='mariadb-replica',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_CONNECT_RETRY=10,
  MASTER_USE_GTID = slave_pos;

Replica starten:

START SLAVE;

Replikation prüfen:

# prior to 10.5.1: SHOW SLAVE STATUS;
SHOW REPLICA STATUS;

Der Replica bzw. die Replikation funktioniert nur, wenn diese Werte zurückkommen:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Ein „Slave_SQL_Running=No“ heisst, dass die Replikation kaputt ist. Während Queueing master event to the relay log wird der Sync für die Replikation vorbereitet. Ein 'Waiting for master to send event' in der Spalte Slave_IO_State bedeutet lediglich, dass der Replica auf neue Inputs vom Primary wartet.

Auf dem Primary: tail -f /var/log/messages /var/lib/mysql/*.log

Auf dem Replica: tail -f /var/log/messages /var/lib/mysql/*.log

Falls auf dem Replica etwas falsch konfiguriert wurde, den Replica mit STOP SLAVE; stoppen (evtl. hilft noch ein RESET SLAVE;), mit CHANGE MASTER TO umkonfiguren und per START SLAVE; wieder starten.

Umgang mit MariaDB

/var/lib/mysql initialisieren

systemctl stop mariadb
rm -rf /var/lib/mysql
mkdir -p /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql
restorecon -r /var/lib/mysql

mysql_install_db --user=mysql \
     --basedir=/usr \
     --datadir=/var/lib/mysql
systemctl start mariadb
mysql_secure_installation

Binary-Logs aufräumen

Im Replika-Set werden auf dem Primary laufend die Binary-Logdateien in der Form /var/lib/mysql/ON.000001 usw. geschrieben. So räumt man diese auf, ohne die Replikation zu zerstören:

  • Auf dem Primary: SHOW BINARY LOGS;

  • Auf allen Replikas: SHOW REPLICA STATUS; (Inhalt der Spalte „Master_Log_File“ merken)

  • Das älteste von einem Replika verwendete Log-File muss erhalten bleiben, alle älteren davor können gelöscht werden.

  • alle Log-Dateien vor ‚ON.000063‘ löschen: PURGE BINARY LOGS TO 'ON.000063';

Benutzer

Der initiale DBA im System heisst root bzw. genauer root@localhost. Einen root@127.0.0.1 gibt es nicht.

Benutzer auflisten / list users / show users:

# MariaDB 10.5+
select * from mysql.global_priv;

# earlier versions:
select user, host, grant_priv from mysql.user;

Rechte eines Benutzers ermitteln:

show grants for 'user';

Benutzer erstellen und keinerlei brauchbare Rechte geben:

grant usage on *.* to 'user'@'host' identified by 'password';
flush privileges;

Benutzer mit SELECT-Rechten auf sämtliche Tabellen einer „accounting“-Datenbank anlegen:

grant select on accounting.* to 'user'@'host' identified by 'password';
flush privileges;

Benutzer mit minimalen Backup-Rechten auf eine DB anlegen, die auch Events, Triggers, Views etc. verwendet:

grant select, event, lock tables, process, reload, show view, super, trigger on *.* to 'user'@'host' identified by 'password';
flush privileges;

Benutzer mit minimalen Backup- UND Restore-Rechten anlegen (der Restore sollte nach Möglichkeit jedoch mit einem anderen, administrativen Account durchgeführt werden):

grant select, create view, drop, event, insert, lock tables, process, reload, show view, super, trigger on *.* to 'user'@'host' identified by 'password';
flush privileges;

Benutzer alle Berechtigungen entziehen:

revoke all privileges, grant option from 'user'@'host';

Passwort eines Benutzers ändern/zurücksetzen - man beachte, dass flush zuerst ausgeführt werden muss:

flush privileges;
set password for `user`@`host` = password('new-password');

Passwort des root-Benutzers zurücksetzen

Ab 10.4:

systemctl stop mariadb
mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
SET PASSWORD FOR `mariadb-admin`@`%` = PASSWORD('new-password');

Den Notfall-Mariadb-Prozess per kill beenden und MariaDB neu starten.

Bash und MariaDB-Sessions

Einige SQL-Befehle verlangen, dass die Session für nachfolgende Befehle offen bleibt, z.B. FLUSH TABLES WITH READ LOCK - doch wie macht man das in einem Bash-Script? Der Trick ist, das Kommando auszuführen, genügend lange in SQL zu warten, das Konstrukt in den Hintergrund zu schicken, anschliessend seine Arbeit zu erledigen und am Ende den Hintergrundprozess zu killen:

echo Locking the database for max. 10 minutes by sleeping in background task...
$MYSQL --host=$HOST --user=$USER --password=$PASSWORD \
    --execute "BACKUP STAGE START; BACKUP STAGE BLOCK_COMMIT; DO SLEEP(600);" &
sleep 1

# do whatever you want to do

echo Finished, killing the previous background task to unlock.
kill $! 2>/dev/null
wait $! 2>/dev/null

Storage Engines

ColumnStore

ColumnStore kann im Gegensatz zu früher ab Version 10.5.4 direkt als Storage Engine in MariaDB laufen, muss aber nach wie vor separat und wie folgt installiert werden.

/etc/sysctl.d/90-mariadb-columnstore.conf
# minimize swapping
vm.swappiness = 10

# optimize Linux to cache directories and inodes
vm.vfs_cache_pressure = 10

# Increase the TCP max buffer size
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

# Increase the TCP buffer limits
# min, default, and max number of bytes to use
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# don't cache ssthresh from previous connection
net.ipv4.tcp_no_metrics_save = 1

# for 1 GigE, increase this to 2500
# for 10 GigE, increase this to 30000
net.core.netdev_max_backlog = 2500
sysctl --load=/etc/sysctl.d/90-mariadb-columnstore.conf

Während der Installation SELinux auf Permissive setzen:

setenforce 0

Character Encoding:

yum -y install glibc-locale-source glibc-langpack-en
localedef -i en_US -f UTF-8 en_US.UTF-8

ColumnStore installieren:

yum -y install epel-release
yum -y install jemalloc
yum -y install MariaDB-columnstore-engine

SELinux konfigurieren und aktivieren:

dnf -y install policycoreutils-python-utils

grep mysqld /var/log/audit/audit.log | audit2allow -M mariadb_local
semodule -i mariadb_local.pp

setenforce 1

Start:

systemctl enable --now mariadb-columnstore
systemctl restart mariadb

Test:

CREATE TABLE inventory.products (
   product_name varchar(11) NOT NULL DEFAULT '',
   supplier varchar(128) NOT NULL DEFAULT '',
   quantity varchar(128) NOT NULL DEFAULT '',
   unit_cost varchar(128) NOT NULL DEFAULT ''
) ENGINE=Columnstore DEFAULT CHARSET=utf8;

Siehe auch https://mariadb.com/docs/deploy/community-single-columnstore/.

Benutzer in ColumnStore anlegen:

use mysql;
grant select on newuser.* to `newuser`@`localhost` identified by 'password';
flush privileges;
grant create temporary tables on infinidb_vtable.* to `newuser`@`localhost`;

SQL

Test-Datenbanken

Die hier genannten Beispiel-Datenbanken werden teilweise auch für MySQL-Zertifizierungen und Schulungen verwendet.

Datenbank „employees“ (167 MB, inkl. gewünschter Inkonsistenzen):
git clone https://github.com/datacharmer/test_db.git
cd test_db/
mysql --user=$DBUSER --password  < employees.sql

Datenbank-Tabelle „employees“ für Tests 4mal pro Sekunde laufend mit Daten füllen - ideal, um eine Ausgangsbasis für Backup- und Replikationstests zu haben:

for i in {1000000..2000000}; do
    echo $i
    mysql --user=$DBUSER --password=$DBPASS \
        --execute="INSERT INTO \`employees\`.\`employees\` (\`emp_no\`, \`birth_date\`, \`first_name\`, \`last_name\`, \`gender\`, \`hire_date\`) VALUES ('$i', '2020-12-31', '$i', '$i', 'M', '2020-12-31');"
    sleep 0.25
done
Datenbank „world“
  • The world sample database provides a set of tables containing information on the countries and cities of the world and is useful for basic queries, but lacks structures for testing MySQL-specific functionality and features found in MySQL 5 and higher.

  • Hilfe: https://dev.mysql.com/doc/world-setup/en/

  • DB-Name: world

wget https://downloads.mysql.com/docs/world.sql.gz
gunzip world.sql.gz
mysql --user=$DBUSER --password < world.sql
Datenbank „world_x“
wget https://downloads.mysql.com/docs/world_x-db.tar.gz
tar xvzf world_x-db.tar.gz
mysql --user=$DBUSER --password < world_x-db/world_x.sql
Datenbank „sakila“
wget https://downloads.mysql.com/docs/sakila-db.tar.gz
tar xvzf sakila-db.tar.gz
mysql --user=$DBUSER --password < sakila-db/sakila-schema.sql
mysql --user=$DBUSER --password < sakila-db/sakila-data.sql
Datenbank „menagerie“
  • Hilfe: nicht verfügbar

  • DB-Name: menagerie

wget https://downloads.mysql.com/docs/menagerie-db.tar.gz
tar xvzf menagerie-db.tar.gz
mysql --user=$DBUSER --password --execute='create database menagerie;'
mysql --user=$DBUSER --password menagerie < menagerie-db/cr_pet_tbl.sql
mysqlimport --user=$DBUSER --password --local menagerie menagerie-db/pet.txt
mysql --user=$DBUSER --password menagerie < menagerie-db/ins_puff_rec.sql
mysql --user=$DBUSER --password menagerie < menagerie-db/cr_event_tbl.sql
mysqlimport --user=$DBUSER --password --local menagerie menagerie-db/event.txt

Tabellen optimieren und reparieren

Achtung: das Kommando lockt die Tabellen, daher nur bei Bedarf und zu Randzeiten ausführen.

mysqlcheck --user=root --password=password --optimize --all-databases
mysqlcheck --user=root --password=password --repair   --all-databases

JSON in SQL

JSON-Text in Tabelle data beispielsweise {"email":{"value":"info@example.com"}}.

Passende SQL-Query:

select json_extract(data, '$.email.value') from ...

Cluster

Master/Master Active/Passive-Setup einrichten

Auf Master 1:

show master status;

Auf Master 2:

show master status\G;
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='master1.example.com',
  MASTER_USER='mariadb-replica',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000002',     # from Master 1
  MASTER_LOG_POS=664;                       # from Master 1
START SLAVE;
show slave status\G

Zurück auf Master 1, hier aber die Informationen von Master 2 eintragen:

STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='mariadb-replica',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000002',     # from Master 2
  MASTER_LOG_POS=664;                       # from Master 2
START SLAVE;

Security und Auditing

MariaDB Audit-Plugin

Grundsätzlich besteht der Zweck des MariaDB Audit Plugin darin, die Aktivitäten des Servers zu protokollieren. Für jede Client-Sitzung wird aufgezeichnet, wer sich mit dem Server verbunden hat (d.h. Benutzername und Host), welche Abfragen ausgeführt wurden, auf welche Tabellen zugegriffen wurde und welche Servervariablen geändert wurden. Diese Informationen werden in einer rotierenden Log-Datei gespeichert oder können an den lokalen syslogd gesendet werden.

Installation:

/etc/my.cnf.d/server.cnf
[mysqld]
plugin_load_add = server_audit

# Prohibiting Uninstallation:
server_audit = FORCE_PLUS_PERMANENT

server_audit_events = "CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL,QUERY_DML_NO_SELECT"
server_audit_excl_users =
server_audit_file_path = server_audit.log
server_audit_file_rotate_now = off
server_audit_file_rotate_size = 10M
server_audit_file_rotations = 9
server_audit_incl_users =
server_audit_logging = on
server_audit_output_type = file
server_audit_query_log_limit = 1024
server_audit_syslog_facility = LOG_USER
server_audit_syslog_ident = mysql-server_auditing
server_audit_syslog_info =
server_audit_syslog_priority = LOG_INFO

Anschliessend:

SHOW GLOBAL VARIABLES LIKE 'server_audit%';

Log-Datei findet sich mit obiger Konfiguration auf einem RHEL-basierten System unter /var/lib/mysql/server_audit.log. Beispiel für den Inhalt:

timestamp        ,server,username,host,ConnID,queryid,operation, database, object,             retcode
20211115 09:04:01,server,username,host,15,    35,     QUERY,     database, 'SET NAMES latin1', 0

Mehr Info hier: https://mariadb.com/kb/en/mariadb-audit-plugin/.

Tuning und Troubleshooting

mysqltuner

yum -y install mysqltuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

mysqltuner --user mariadb-admin --pass mypass --passwordfile basic_passwords.txt --cvefile vulnerabilities.csv

MySQL sollte da schon 24h oder länger gelaufen sein.

Die Ausgaben bedeuten:

  • [--]: „Info Print“

  • [OK]: „Good Print“

  • [!!]: „Bad Print“

Beispielausgabe:

 >>  MySQLTuner 1.7.21 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 10.3.30-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file ./mariadb-error.log doesn't exist

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 10.5M (Tables: 4)
[--] Data in InnoDB tables: 1.5G (Tables: 906)
[!!] Total fragmented tables: 2

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User ''root'@%' does not specify hostname restrictions.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 14h 48m 46s (48M q [343.594 qps], 192K conn, TX: 501G, RX: 7G)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is disabled
[--] Physical Memory     : 7.6G
[--] Max MySQL memory    : 4.3G
[--] Other process memory: 0B
[--] Total buffers: 1.3G global + 19.7M per thread (151 max threads)
[--] P_S Max memory usage: 104M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.7G (21.62% of installed RAM)
[OK] Maximum possible memory usage: 4.3G (56.06% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/48M)
[OK] Highest usage of available connections: 9% (14/151)
[OK] Aborted connections: 0.00%  (1/192942)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (44 temp sorts / 10M sorts)
[!!] Joins performed without indexes: 2912
[!!] Temporary tables created on disk: 34% (272K on disk / 783K total)
[OK] Thread cache hit rate: 99% (14 created / 192K connections)
[OK] Table cache hit rate: 99% (1K open / 1K opened)
[!!] table_definition_cache(400) is lower than number of tables(1585)
[OK] Open file limit used: 0% (67/32K)
[OK] Table locks acquired immediately: 100% (5K immediate / 5K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 104.0M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.3.30-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/3.3M
[OK] Read Key buffer hit rate: 98.8% (1K cached / 21 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/1.5G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 128.0M * 2/1.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (2181003656 hits/ 2181088118 total)
[!!] InnoDB Write Log efficiency: 75.7% (1591920 hits/ 2102867 total)
[OK] InnoDB log waits: 0.00% (0 waits / 510947 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 98.6% (18M cached / 264K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `mydb`.`http_error_log`; -- can free 100 MB
      OPTIMIZE TABLE `mydb`.`versions`; -- can free 5382 MB
    Total freed space after theses OPTIMIZE TABLE : 5482 Mb
    Restrict Host for 'root'@% to 'root'@LimitedIPRangeOrLocalhost
    RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB
Variables to adjust:
    join_buffer_size (> 1.0M, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_definition_cache(400) > 1585 or -1 (autosizing if supported)
    innodb_buffer_pool_size (>= 1.5G) if possible.

Zu prüfende Einstellungen auf einem dedizierten MariaDB-System:

binlog_format = MIXED
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 80% vom verfügbaren ("available") RAM
innodb_file_format = barracuda
innodb_file_per_table = on
innodb_io_capacity = 4000
innodb_large_prefix = on
innodb_log_file_size = 25% von innodb_buffer_pool_size / 2
innodb_stats_on_metadata = off
join_buffer_size = 1M
log_error = hostname.err
max_heap_table_size = 256M
performance_schema = on
query_cache_limit = 2M
query_cache_size = 0
query_cache_type = off
skip_name_resolve = on
thread_cache_size = 256
tmp_table_size = 256M

Built on 2022-06-03