====== 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         | [[dienste:bytecluster0002:nextcloud|Nextcloud]]          |
| paste                      | paste           | [[paste]]                                                |
| wordpress                  | wp_user         | [[dienste:bytecluster0002:wordpress|Wordpress]]          |
| wordpress_technikkultur    | wordpress_tk    | [[wordpress-technikkultur|Wordpress Technikkultur]]      |
| wordpress_freifunk_erfurt  | wordpress_ffef  | [[wordpress-freifunk-erfurt|Wordpress Freifunk Erfurt]]  |

===== Betrieb =====
==== Datenbank und Benutzer anlegen ====

  - Zur Datenbank verbinden
    * **sudo mysql**
  - Datenbank anlegen und Benutzer mit Passwort zuweisen<code|MySQL-Konsole "MariaDB [(none)]">
CREATE DATABASE databasename;
GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
</code>

==== Passwort für Benutzer ändern ====

  - Zur Datenbank verbinden
    * **sudo mysql**
  - Benutzer neues Passwort zuweisen<code|MySQL-Konsole "MariaDB [(none)]">
ALTER USER 'username'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
</code>

===== 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<file|/etc/mysql/mariadb.conf.d/99-bytecluster.cnf>
[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 
</file>
  - 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<code>
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</code>
  - Konfiguration anpassen (USERNAME, SERVERNAME, SSH-PORT ersetzen)<file|/etc/borgmatic/config.yaml>
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
    ...
</file>
  - Borg-Repository initialisieren
    * **<nowiki>sudo borgmatic init --encryption keyfile</nowiki>**
  - Verschlüsselungsinformationen sicher verwahren 
    - Verschlüsselungs-Passwort ermitteln
      * **<nowiki>sudo grep "encryption_passphrase:" /etc/borgmatic/config.yaml</nowiki>** 
    - Verschlüsselungs-Schlüssel ermitteln 
      * **sudo cat /root/.config/borg/keys/SERVERNAME-MIT-UNTERSTRICHEN__borg**
  - Erstes Backup initialisieren
    * **<nowiki>sudo borgmatic create --progress --stats</nowiki>**<code>
------------------------------------------------------------------------------                                                                                                                        
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
------------------------------------------------------------------------------
</code>
  - Backupinhalt nochmal prüfen
    * **<nowiki>sudo borgmatic list  --archive latest</nowiki>**<code>
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
...
</code>
  - Cronjob einrichten
    * **<nowiki>echo -e "0 3 * * *\troot\t$(which borgmatic) --syslog-verbosity 1" | sudo tee /etc/cron.d/borgmatic > /dev/null</nowiki>**