Container 'mariadb'

Ressourcen

1 GB RAM

2 Cores

8 GB HDD (root-fs)

System

interne IPs

10.2.0.100, fd00:10:2:0::100

10.3.0.100, fd00:10:3:0::100 (MariaDB)

Dienste

MariaDB 10

Datenbanken

Datenbank	Benutzer	Verwendung
cloud	cloud	cloud
etherpadlite	etherpadlite	pad
nextcloud	nc_user	Nextcloud
paste	paste	paste
wordpress	wp_user	Wordpress
wordpress_technikkultur	wordpress_tk	Wordpress Technikkultur
wordpress_freifunk_erfurt	wordpress_ffef	Wordpress Freifunk Erfurt

Betrieb

Datenbank und Benutzer anlegen

Zur Datenbank verbinden

sudo mysql

Datenbank anlegen und Benutzer mit Passwort zuweisen

CREATE DATABASE databasename;
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Passwort für Benutzer ändern

Zur Datenbank verbinden

sudo mysql

Benutzer neues Passwort zuweisen

ALTER USER 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Installation

Standard-Template mit Benutzern

MariaDB

MariaDB-Server installieren

apt-get install mariadb-server

MariaDB - Erstkonfiguration

mysql_secure_installation

Set root password? [Y/n]: Y

New password: PASSWORT

Re-enter new password: PASSWORT

Remove anonymous users? [Y/n]: Y

Disallow root login remotely? [Y/n]: Y

Remove test database and access to it? [Y/n]: Y

Reload privilege tables now? [Y/n]: Y

Benutzerdefinierte Konfiguration anlegen

[mysqld]
# An lokale IP binden
bind-address = 10.3.0.100
# Binlog deaktivieren
skip-log-bin
# InnoDB verwenden
default_storage_engine = InnoDB
# InnoDB-Optimierungen
innodb_buffer_pool_size = 256M
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

MariaDB neustarten

systemctl restart mariadb.service

Backup mit Borgmatic

Borgmatic installieren

sudo apt-get update

sudo apt-get install borgmatic

pwgen installieren

sudo apt-get install pwgen

Borgmatic-Konfiguration in der Datei /etc/borgmatic/config.yaml erzeugen

sudo generate-borgmatic-config

SSH-Key erzeugen

sudo ssh-keygen -a100 -t ed25519 -f /root/.ssh/id_borgbackup

SSH-Key bei Backupserver hinterlegen

Zufälliges Passwort in Konfiguration erzeugen

sudo sed -i -e "s|^ # \(encryption_passphrase: \"\).*\(\"\)$| \1PASS_TO_REPLACE\2|" /etc/borgmatic/config.yaml
sudo sed -i "s|PASS_TO_REPLACE|$(pwgen -cnysB -1 32 -r \"\^\|\\\\)|" /etc/borgmatic/config.yaml

Konfiguration anpassen (USERNAME, SERVERNAME, SSH-PORT ersetzen)

location:
...
source_directories:
- /etc
- /home
- /root
- /usr/local
- /var/log
...
repositories:
- USERNAME@SERVERNAME:~/borg
...
one_file_system: true
...
exclude_caches: true
...
storage:
...
encryption_passphrase: "ENCRYPTION-PASSPHRASE"
...
compression: zlib,9
...
ssh_command: ssh -i /root/.ssh/id_borgbackup -p SSH-PORT
...
retention:
...
keep_daily: 7
...
keep_weekly: 4
...
keep_monthly: 6
...
keep_yearly: 1
...
consistency:
...
checks:
- repository
- archives
...
hooks:
before_backup:
- dpkg-query -f '${binary:Package}\n' -W > /root/package.list
...
mysql_databases:
- name: all
...

Borg-Repository initialisieren

sudo borgmatic init --encryption keyfile

Verschlüsselungsinformationen sicher verwahren

Verschlüsselungs-Passwort ermitteln

sudo grep "encryption_passphrase:" /etc/borgmatic/config.yaml

Verschlüsselungs-Schlüssel ermitteln

sudo cat /root/.config/borg/keys/SERVERNAME-MIT-UNTERSTRICHEN__borg

Erstes Backup initialisieren

sudo borgmatic create --progress --stats

------------------------------------------------------------------------------
Archive name: mariadb-2020-12-05T17:36:48.502653
Archive fingerprint: 9a9c7f769dcd9af1e7f28158e4a3b0d05cb3faae25c45a6e9930591a1414eaa3
Time (start): Sat, 2020-12-05 17:36:49
Time (end): Sat, 2020-12-05 17:36:55
Duration: 6.57 seconds
Number of files: 472
Utilization of max. archive size: 0%
------------------------------------------------------------------------------
Original size Compressed size Deduplicated size
This archive: 71.08 MB 2.72 MB 2.53 MB
All archives: 71.08 MB 2.72 MB 2.53 MB
Unique chunks Total chunks
Chunk index: 437 464
------------------------------------------------------------------------------

Backupinhalt nochmal prüfen

sudo borgmatic list --archive latest

USERNAME@SERVERNAME:~/borg: Listing archives
drwxr-xr-x root root 0 Wed, 2020-11-25 19:49:45 etc
-rw-r--r-- root root 767 Fri, 2016-03-04 11:00:00 etc/profile
...

Cronjob einrichten

echo -e "0 3 * * *\troot\t$(which borgmatic) --syslog-verbosity 1" | sudo tee /etc/cron.d/borgmatic > /dev/null