MariaDB

Siehe auch

https://www.mariadbtutorial.com/ und https://www.mysqltutorial.org
MariaDB System-Variablen
Ansible-Rolle MariaDB-Server:

https://github.com/Linuxfabrik/lfops/tree/main/roles/mariadb
Ansible-Rolle MariaDB-Client:

https://github.com/Linuxfabrik/lfops/tree/main/roles/mariadb-client
Icinga-Checks:

https://github.com/Linuxfabrik/monitoring-plugins/tree/main/check-plugins/

Bemerkung

Ein Server mit MariaDB 10.6+ (egal ob dediziert oder zusammen mit einer Applikation) sollte heute mit mindestens 4 GB RAM betrieben werden.

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.

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.11

wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
./mariadb_repo_setup --mariadb-server-version="mariadb-$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.

Passwort ändern - man beachte, dass „flush“ zuerst ausgeführt werden muss:

flush privileges;
alter user 'root'@'localhost' identified by 'myn3wp4ssw0rd';

Migration

Umzug einer MariaDB-Installation von VMalt nach VMneu, ohne langwierigen SQL-Ex- und Import der Datenbanken, sondern durch Kopie der Datenverzeichnisse. Die Ziel-Maschine kann dabei auch eine neuere MariaDB-Version fahren (ein bereits installiertes MariaDB ist Voraussetzung); ein Downgrade dagegen funktioniert nicht.

Wo	Was
VMalt	`dnf -y install rsync`
VMalt	`systemctl stop mariadb`
VMneu	`dnf -y install rsync`
VMneu	`systemctl stop mariadb`
VMneu	`mv /var/lib/mysql /var/lib/mysql.orig`
VMneu	`rsync --archive --progress --human-readable --rsync-path='sudo rsync' vmalt:/var/lib/mysql/ /var/lib/mysql/`
VMneu	`chown -R mysql:mysql /var/lib/mysql; restorecon -Fvr /var/lib/mysql; systemctl start mariadb` (evtl. auftretende Fehler wegen nächstem Schritt zunächst ignorieren)
VMneu	`mysql_upgrade --user=mariadb-admin --password` (Passwort von VMalt verwenden)
VMneu	Meldungen wie `error : Table rebuild required` fixen
VMneu	`systemctl restart mariadb`

Tipp

Wenn die importierte Datenbank keinen mariadb-admin-User hat, und das root-Passwort unbekannt ist, lässt sich der MariaDB-Server ohne User-Schicht wie folgt starten: sudo -u mysql mariadbd --skip-grant-tables&.

Upgrading

Ein Upgrade von beispielsweise MariaDB 10.2 direkt auf MariaDB 10.6 ist möglich. Das Upgrade-Prozedere wird hier anhand der aus den MariaDB-Repos installierten Datenbank beschrieben.

Backups aller Datenbanken anlegen.
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: dnf remove MariaDB-server. Das Verzeichnis /var/lib/mysql bleibt dabei erhalten.
Die bisher verwendeten /etc/yum.repos.d/mariadb.repo-Dateien deaktivieren.
Repository-Datei unter /etc/yum.repos.d auf die neue Version anpassen.
dnf clean all
Aktuellere Version installieren: dnf 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 (sonst erhält man Fehler der Art Couldn't execute '...': Column count of mysql... is wrong. Expected 21, found 20. The table is probably corrupted (1805)): 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 13306:localhost:3306 mariadb-host, Zugriff dann auf localhost:13306.

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 von Fedora Workstation:

VER=8.0.36-1.fc38
dnf -y install https://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-$VER.x86_64.rpm

Konfiguration liegt unter ~/.mysql/workbench.

Welche Berechtigungen stecken hinter den „Administrative Roles“?

MaintenanceAdmin: Event, Reload, Show_db, Shutdown, Super
ProcessAdmin: Reload, Super
UserAdmin: Create_user, Reload
SecurityAdmin: Grant, Create_user, Reload, Show_db, User_manage_attr
MonitorAdmin: Process
DBManager: Create, Drop, Grant, Event, Alter, Delete, Index, Insert, Select, Update, Create_tmp_table, Lock_tables, Trigger, Create_view, Show_view, Create_routine, Alter_routine, Show_db
DBDesigner: Create, Alter, Index, Trigger, Create_view, Show_view, Create_routine, Alter_routine, Show_db, Routine_manage_attr
ReplicationAdmin: Repl_client, Repl_slave, Super
BackupAdmin: Event, Select, Lock_tables, Show_db

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.

mysql-Client

Der Dump - realistisch ungefähr 1 GB pro Minute. Wichtig zu wissen: die innodb_buffer_pool-Caches werden durch die beim Backup stattfindenden Full Table Scans zugemüllt, was folgende Statements verhindern:

# save the innodb_buffer_pool
$MYSQL --host=$HOST --user=$USER --password=$PASSWORD --execute='SET GLOBAL innodb_buffer_pool_dump_now = ON;'

# do the backup
# for example with mydumper or mysqldump

# restore the innodb_buffer_pool
$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.

Der Restore mit dem Kommandozeilen-Client - realistisch ungefähr 200 MB pro Minute:

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

Fortschrittsanzeige mit Hilfe des „pipe viewers“:

dnf -y install pv
pv mydb.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/mydumper/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

VER=0.16.3-6

# RHEL 9:
dnf -y install https://github.com/mydumper/mydumper/releases/download/v$VER/mydumper-$VER.el9.x86_64.rpm

# RHEL 8:
dnf -y install compat-openssl10
dnf -y install https://github.com/mydumper/mydumper/releases/download/v$VER/mydumper-$VER.el8.x86_64.rpm

Full-Backup / Full-Dump:

HOST=localhost
USERNAME=root
PASSWORD=linuxfabrik
BACKUP_DIR=/backup
THREADS=4

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

# with more options:
mydumper --host=$HOST --user=$USER --password=$PASSWORD \
    --build-empty-files \
    --events \
    --logfile=$BACKUP_DIR/mydumper.log \
    --outputdir=$BACKUP_DIR \
    --routines \
    --threads=0 \
    --triggers \
    --verbose=3

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.
--database wählt bei Bedarf die zu exportierende Datenbank aus.

Tipp

Ein Dump-Benutzer benötigt die Rechte event, execute, lock_tables, process, reload, select, show databases, show view, super, trigger. Werden beispielsweise Views oder Trigger nicht exportiert, sollten die Berechtigungen des Dump-Users überprüft werden. execute braucht es für gewisse Tabellen in der sys-DB.

myloader

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

HOST=localhost
USERNAME=root
BACKUP_DIR=/backup
THREADS=0

myloader --host=$HOST --user=$USER --ask-password \
    --directory=$BACKUP_DIR \
    --overwrite-tables \
    --threads=$THREADS \
    --verbose=3

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

HOST=localhost
USERNAME=root
BACKUP_DIR=/backup
NEW_NAME_OF_DB=restored-nextcloud
DB_TO_RESTORE=nextcloud
THREADS=0

myloader --host=$HOST --user=$USER --ask-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.

Full-Backup / Full-Dump:

   mysqldump --host=$HOST --port=3306 --user=$USER --password=$PASSWORD \
       --create-options \
       --default-character-set=utf8 \
       --events \
       --routines \
       --single-transaction=TRUE \
       --skip-dump-date \
       --skip-extended-insert \
       --triggers \
       --all-databases > /path/to/backup.sql

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

mysqldump --host=server_a --user=root --password --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:

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

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:

bis 10.4: Master und Slave
10.5+: 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, 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;

Bemerkung

Werden zwei oder mehr Privilegien auf Datenbank und Tabellen angegeben, wird immer die spezifischere Regel angewendet. Beispiel:

database.table:ALTER
database.*:DELETE,SELECT

Damit darf der Benutzer DELETE und SELECT auf alle Tabellen in database ausführen, auf die Tabelle table allerdings nur ein ALTER. Soll der Benutzer auf table zusätzlich ALTER ausführen dürfen, müssen die Regeln wie folgt aussehen:

database.table:ALTER,DELETE,SELECT
database.*:DELETE,SELECT

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
cat > /etc/my.cnf.d/zzz-password-recovery.cnf << 'EOF'
[server]
skip_grant_tables = ON
skip_networking = 1
EOF
systemctl start mariadb

mysql --user root

FLUSH PRIVILEGES;
SET PASSWORD FOR `mariadb-admin`@`%` = PASSWORD('new-password');

rm /etc/my.cnf.d/zzz-password-recovery.cnf
systemctl restart mariadb

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 kann im Gegensatz zu früher ab Version 10.5.4 direkt als Storage Engine in MariaDB laufen, muss aber nach wie vor separat installiert werden.

MariaDB 10.6 mit ColumnStore

/etc/sysctl.d/90-mariadb-columnstore.conf

# minimize swapping
vm.swappiness = 1

# 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:

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

MariaDB inklusive ColumnStore-Engine installieren:

dnf -y install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "367a80b01083c34899958cdd62525104a3de6069161d309039e84048d89ee98b mariadb_repo_setup" | sha256sum -c -
chmod +x mariadb_repo_setup
./mariadb_repo_setup --mariadb-server-version="mariadb-10.6"

dnf -y install epel-release
dnf -y install jemalloc
dnf -y install MariaDB-server MariaDB-backup MariaDB-shared MariaDB-client MariaDB-columnstore-engine

SELinux konfigurieren und aktivieren (dieses Vorgehen kann auch immer mal wieder im laufenden Betrieb nötig sein, bis die Policy steht):

dnf -y install policycoreutils python3-policycoreutils policycoreutils-python-utils

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

setenforce 1

Cross Engine Join-Benutzer in ColumnStore konfigurieren:

mcsSetConfig CrossEngineSupport Host 127.0.0.1
mcsSetConfig CrossEngineSupport Port 3306
mcsSetConfig CrossEngineSupport User cross_engine
mcsSetConfig CrossEngineSupport Password linuxfabrik

Start:

systemctl enable --now mariadb
systemctl enable --now mariadb-columnstore

Aufräumen:

mariadb-secure-installation

Benutzer anlegen auf ColumnStore im Single-Node-Betrieb (funktioniert bis zum Anlegen des „mariadb-admin“ anfangs nur lokal unter dem „root“-Benutzer, nicht per MySQL Workbench o.ä.):

mariadb

# new mariadb-admin
grant all privileges on *.* to 'mariadb-admin'@'localhost' identified by 'linuxfabrik' with grant option;

# cross-engine-join-user from above
grant select, process on *.* to `cross_engine`@`127.0.0.1` identified by 'linuxfabrik';
grant select, process on *.* to `cross_engine`@`localhost` identified by 'linuxfabrik';
flush privileges;

Test:

SHOW STATUS LIKE '%columnstore%';

CREATE DATABASE inventory;

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;

DROP DATABASE inventory;

Settings:

Columnstore.xml

<!-- better config -->
<VersionBuffer>
    <!-- VersionBufferFileSize must be a multiple of 8192.
    One version buffer file will be put on each DB root. -->
    <VersionBufferFileSize>4GB</VersionBufferFileSize>
</VersionBuffer>
<DBBC>
    <NumBlocksPct>25</NumBlocksPct>
</DBBC>
<HashJoin>
    <TotalUmMemory>50%</TotalPmUmMemory>
    <AllowDiskBasedJoin>Y</AllowDiskBasedJoin>
</HashJoin>

Restart:

systemctl restart mariadb-columnstore.service

Siehe auch https://mariadb.com/docs/ent/deploy/topologies/single-node/community-columnstore-cs10-6

ColumnStore bis 1.2.5 (bis RHEL 7)

/etc/sysctl.d/90-mariadb-columnstore.conf

# minimize swapping
vm.swappiness = 1

# 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:

localedef -i en_US -f UTF-8 en_US.UTF-8

ColumnStore installieren:

yum -y install boost
yum -y install expect perl perl-DBI openssl zlib file sudo libaio rsync snappy net-tools numactl-libs nmap
wget https://downloads.mariadb.com/ColumnStore/1.2.5/centos/x86_64/7/mariadb-columnstore-1.2.5-1-centos7.x86_64.rpm.tar.gz
tar xvzf mariadb-columnstore-*-centos7.x86_64.rpm.tar.gz
yum -y install mariadb-columnstore-*.rpm

/usr/local/mariadb/columnstore/bin/quick_installer_single_server.sh

source /etc/profile.d/columnstoreAlias.sh

/etc/logrotate.d/columnstore

#
# MariaDB ColumnStore Log Rotate file that gets installed in /etc/logrotate.d
# as part of the package installation
#

/var/log/mariadb/columnstore/*.log {
    missingok
    rotate 7
    daily
    dateext
    copytruncate
    olddir /var/log/mariadb/columnstore/archive
    su root root
}
#/usr/local/mariadb/columnstore/etc/Columnstore.xml {
#    daily
#    dateext
#   copy
#    olddir /usr/local/mariadb/columnstore/etc/
#}
/usr/local/mariadb/columnstore/mysql/db/*.err {
    missingok
    rotate 7
    daily
    dateext
    copytruncate
    olddir /usr/local/mariadb/columnstore/mysql/db
    su root root
}

Settings finden sich in zwei Dateien (empfohlene Einstellungen):

/usr/local/mariadb/columnstore/etc/Columnstore.xml

<!-- better config -->
<VersionBuffer>
    <!-- VersionBufferFileSize must be a multiple of 8192.
    One version buffer file will be put on each DB root. -->
    <VersionBufferFileSize>4GB</VersionBufferFileSize>
</VersionBuffer>
<DBBC>
    <NumBlocksPct>25</NumBlocksPct>
</DBBC>
<HashJoin>
    <TotalUmMemory>50%</TotalPmUmMemory>
    <AllowDiskBasedJoin>Y</AllowDiskBasedJoin>
</HashJoin>

/usr/local/mariadb/columnstore/mysql/my.cnf

[client]
...
default-character-set=utf8

[mysqld]
...
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = 'SET NAMES utf8'
max_allowed_packet = 4M
max_length_for_sort_data = 4M

/usr/local/mariadb/columnstore/bin/setConfig SystemConfig SystemLang en_US.utf8
mcsadmin restartSystem y

SELinux konfigurieren und aktivieren:

yum -y install policycoreutils-python

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

setenforce 1

Start:

systemctl enable --now columnstore

Aufräumen:

/usr/local/mariadb/columnstore/mysql/bin/mysql_secure_installation

Benutzer anlegen auf ColumnStore im Single-Node-Betrieb, z.B. in MySQL Workbench:

/usr/local/mariadb/columnstore/mysql/bin/mysql --user root --password

# new mariadb-admin
grant all privileges on *.* to 'mariadb-admin'@'localhost' identified by 'linuxfabrik' with grant option;

Test:

CREATE DATABASE inventory;
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;
DROP DATABASE inventory;

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

Benutzer in ColumnStore anlegen:

mcsmysql -u root
# or in MySQL Workbench, for example

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

SQL

Typen von SQL-Statements:

Data Manipulation Language (DML): SELECT, INSERT, UPDATE, DELETE, MERGE
Data Definition Language (DDL): CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT
Data Control Language (DCL): GRANT, REVOKE
Transaction Control: COMMIT, ROLLBACK, SAVEPOINT

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):

Hilfe: https://dev.mysql.com/doc/employee/en/
DB-Name: employees

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_x“

Hilfe: https://dev.mysql.com/doc/world-x-setup/en/
DB-Name: 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“

designed to represent a DVD rental store
Hilfe: https://dev.mysql.com/doc/sakila/en/
DB-Name: 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

Slow Query Log untersuchen

# summarize the output file and sort the output by rows affected or average rows affected
mariadb-dumpslow -s t log_file

mysqltuner

dnf -y install mysqltuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt --output-document=basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv --output-document=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 2024-07-16