Seite wählen

NETWAYS Blog

MySQL-Datenbanken verwalten mit Sequel Pro

Eines meiner meist genutzten Apps am Mac ist Sequel Pro. Das kann man kennen, muss man aber nicht. Daher liest man – wenn man möchte – in den folgenden Zeilen eine kurze Vorstellung.
mehr lesen…

Florian Strohmaier
Florian Strohmaier
Senior UX Designer

Mit seinen Spezialgebieten UI-Konzeption, Prototyping und Frontendentwicklung unterstützt Florian das Dev-Team bei NETWAYS. Trotz seines Design-Backgrounds fühlt er sich auch in der Technik zuhause. Gerade die Kombination aus beidem hat für ihn einen besonderen Reiz.

Filter for Multiple Group Memberships in SQL

In the upcoming Icinga Web 2 release the filter functionality becomes even more powerful.
Version 2.6.0 introduces the possibility to exclude hosts and services that are member of specific groups.
You now filter for hosts that are not part of the production host group for example.
You want to filter for hosts that are member of the host groups linux and database? That will be possible as well.
I’d like to show you how the latter is done with an example. We have a database with user groups, users and their group
memberships:

CREATE TABLE user_group (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (id),
  UNIQUE KEY group_name (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;
CREATE TABLE user_group_membership (
  user_id int(10) unsigned NOT NULL,
  group_id int(10) unsigned NOT NULL,
  PRIMARY KEY (user_id,group_id),
  CONSTRAINT user_group_membership_user FOREIGN KEY (user_id) REFERENCES `user` (id),
  CONSTRAINT user_group_membership_group FOREIGN KEY (group_id) REFERENCES user_group (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_bin;
INSERT INTO user VALUES (1,'john'),(2,'marc'),(3,'peter');
INSERT INTO user_group VALUES (1,'admins'),(3,'dev'),(2,'support');
INSERT INTO user_group_membership VALUES (1,1),(2,2),(2,3),(3,2);

User john is part of the group admins. Marc is member of the groups dev and support while Peter is part of the dev
group only. We now have the task to filter for users that are at least part of the groups dev and support. In this example
it’s easy of course because we only have three users and know the result without executing any queries.
But anyway how would you achieve this with SQL? Easy, let’s just JOIN the tables and add a WHERE IN condition:

SELECT * FROM `user` u INNER JOIN user_group_membership m ON m.user_id = u.id
INNER JOIN user_group g ON g.id = m.group_id WHERE g.name IN ('dev', 'support');
+----+-------+---------+----------+----+---------+
| id | name  | user_id | group_id | id | name    |
+----+-------+---------+----------+----+---------+
|  2 | marc  |       2 |        3 |  3 | dev     |
|  2 | marc  |       2 |        2 |  2 | support |
|  3 | peter |       3 |        2 |  2 | support |
+----+-------+---------+----------+----+---------+

The result is not surprising. Because of the WHERE IN condition we also get peter who is only part of the dev group.
So, we have to filter for users that produce two or more rows. For this aggregation, HAVING helps:

SELECT * FROM `user` u INNER JOIN user_group_membership m ON m.user_id = u.id
INNER JOIN user_group g ON g.id = m.group_id WHERE g.name IN ('dev', 'support')
HAVING(COUNT(group_id) >= 2);
+----+-------+---------+----------+----+------+
| id | name  | user_id | group_id | id | name |
+----+-------+---------+----------+----+------+
|  2 | marc  |       2 |        3 |  3 | dev  |
+----+-------+---------+----------+----+------+

Looks good! It’s best to move this to a subquery in order to be flexible if the query becomes more complex later on:

SELECT * FROM `user` u WHERE EXISTS (
  SELECT 1 FROM user_group_membership m
  INNER JOIN user_group g ON m.group_id = g.id
  WHERE m.user_id = u.id AND g.name IN ('dev', 'support')
  HAVING COUNT(*) >= 2
);
+----+-------+
| id | name  |
+----+-------+
|  2 | marc |
+----+-------+

Bonus question: how to filter for users that are member of dev and support but no other groups?

Eric Lippmann
Eric Lippmann
CTO

Eric kam während seines ersten Lehrjahres zu NETWAYS und hat seine Ausbildung bereits 2011 sehr erfolgreich abgeschlossen. Seit Beginn arbeitet er in der Softwareentwicklung und dort an den unterschiedlichen NETWAYS Open Source Lösungen, insbesondere inGraph und im Icinga Team an Icinga Web. Darüber hinaus zeichnet er für viele Kundenentwicklungen in der Finanz- und Automobilbranche verantwortlich.

Einfaches verschlüsseltes Backup

Seit In­kraft­tre­ten der DSVGO  ist Datenschutz in aller Munde. Da wird es einmal Zeit auch den Datenschutz des Monitoring-Servers zu überdenken. Dabei denke ich in diesem Fall nicht an die diversen Härtungsmaßnamen wie SSL für Webserver und Datenbank. Auch Icinga2 erzwingt bei seinen API Verbindungen immer verschlüsselte Verbindungen.
Wo bleiben aber die Backup Dateien? Einmal erzeugt, verlassen sie den Server und liegen dann ‚woanders‘. Zum Glück ist es nicht unbedingt nötig, dass man dem File Server voll vertraut. Eventuell ist es günstig die Backup in irgendeine Cloud zu schieben, oder auf den semi public File Server der Unternehmens. Mit Hilfe von GPG kann man seine Daten einfach verschlüsseln und sicherstellen, dass alle Berechtigten sie auch wieder entschlüsseln können. Im folgenden wird erklärt wie man GPG benutzt um ein icinga2 Backup für eine Gruppe von Berechtigten zu verschlüsseln ohne das der private key einer Person den Monitoring Server oder den Backup Server berührt.

1.) gpg Schlüsselpaar erstellen

Am einfachsten benutzt man das CLI tool gpg um den key zu erzeugen. Das sollte man aber auf einem sicheren System machen, z.B. dem eigenen Laptop oder Arbeitsplatz PC. Anschließend wird der öffentliche Teil an einen Keyserver gesendet um den Schlüsselaustausch zu vereinfachen.

$ gpg --full-gen-key
[...]
Ihr Name ("Vorname Nachname"): Max Mustermann
Email-Adresse: max.mustermann@example.org
pub rsa2048 2018-05-31 [SC] [verfällt: 2023-05-30]
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX0BA2D8D6
Max Mustermann <max.mustermann@example.org>
$ gpg --keyserver pool.sks-keyservers.net --send-key 0BA2D8D6

2.) Monitoring Server mit Schlüsseln versorgen

Auf dem Server kann man mit Hilfe der gpg group Funktion die Daten mit den public keys einer ganzen Gruppe verschlüsseln. Hierfür muss man diese Gruppe in der ~/.gnupg/gpg.conf anlegen.

$ vim .gnupg/gpg.conf +80
group icingabackup = max.mustermann@example.org john.doe@example.org

Anschließend kann man die public keys vom keyserver laden und ihnen das Vertrauen aussprechen. Nur wenn man allen Schlüsseln „absolutes Vertrauen“ ausspricht läuft der encryption Prozess ohne weitere Rückmeldungen ab.

$ gpg --keyserver pool.sks-keyservers.net --search-keys max.mustermann@example.org
gpg: suche nach "max.mustermann@example.org" auf hkp-Server pool.sks-keyservers.net
(1)  Max Mustermann (Test) <mustermann@example.org>
      4096 bit RSA key 0BA2D8D6, erzeugt: 2013-11-18
$ gpg --keyserver pool.sks-keyservers.net --recv-keys 0BA2D8D6
$ gpg --edit 0BA2D8D6 trust
  5 = Ich vertraue ihm absolut
$ gpg --keyserver pool.sks-keyservers.net --search-keys johndoe@example.org
gpg: suche nach "johndoe@example.org" auf hkp-Server pool.sks-keyservers.net
(1)  John Doe (Work email) johndoe@example.org
      4096 bit RSA key 732D8994, erzeugt: 2018-04-20, verfällt: 2020-04-19
$ gpg --keyserver pool.sks-keyservers.net --recv-keys 732D8994
$ gpg --edit 732D8994 trust
  5 = Ich vertraue ihm absolut

3.) Backup erzeugen und verschlüssen

Das kurze bash Script sammelt Dateien von icinga2 und icingaweb, erzeugt einen mysqldump, packt alles zusammen und verschlüsselt es zum Schluss. Alle Schritte sind im Script kommentiert. Bitte lesen sie unbedingt auch die Hinweise in der icinga2 Dokumentation zu diesem Thema.

#!/bin/bash
DATE=`date +%Y%m%d%H%M`
# Backup script for icinga2 and icingaweb2
# Choose which parts will be backed up.
BACKUP_ICINGA2=true
BACKUP_ICINGAWEB2=true
BACKUP_MYSQL=true
ENABLE_ENCRYPTION=true
DELETE_OLD_FILES=true
# Backup target dir
BACKUPDIR=/data/icinga2_backup
# Backup retention time. Files will be deleted after 14 days
RETENTION_TIME=14
# Icinga2 settings
ICINGA2FILES="/etc/icinga2 /var/lib/icinga2 /etc/default/icinga2"
# Icingaweb2 settings
ICINGAWEB2FILES="/etc/icingaweb2 /usr/share/icingaweb2"
HTTPD_ETCDIR="/etc/apache2"
# mysql settings
MYSQL_DATABASES="mysql icinga icingaweb director"
MYSQL_ETCDIR="/etc/mysql"
MYSQL_DUMP="$BACKUPDIR/tmp/icingaMysqlDump.sql.gz"
# encryption settings
GPG_RECIPIENT=icingabackup
# Ensure Backupdir exists
[ ! -d $BACKUPDIR ] && mkdir -p $BACKUPDIR/tmp
# Add icinga2 folders
if [ $BACKUP_ICINGA2 = true ]; then
  BACKUPFILES+=" $ICINGA2FILES"
fi
# Add icingaweb2 folders
if [ $BACKUP_ICINGAWEB2 = true ]; then
  BACKUPFILES+=" $ICINGAWEB2FILES"
  BACKUPFILES+=" $HTTPD_ETCDIR"
fi
# Add my folders and mysqldump
if [ $BACKUP_MYSQL = true ]; then
  BACKUPFILES+=" $MYSQL_ETCDIR"
  if [ ! -z "$MYSQL_DATABASES" ]; then
    mysqldump --create-options --databases ${MYSQL_DATABASES} | gzip > $MYSQL_DUMP
    BACKUPFILES+=" $MYSQL_DUMP"
  fi
fi
# make archive
TAR=$BACKUPDIR/icingaBackup_${DATE}.tar.gz
if [ ! -z "$BACKUPFILES" ]; then
  # Archive all files and delete mysqldump
  tar -czf $TAR $BACKUPFILES 2> /dev/null && [ -e $MYSQL_DUMP ] && rm $MYSQL_DUMP
fi
# encrypt archive
if [ $ENABLE_ENCRYPTION = true ]; then
  gpg --encrypt --recipient icingabackup $TAR && rm $TAR
fi
# delete everything older than 14 days
if [ $DELETE_OLD_FILES = true ]; then
  find $BACKUPDIR -mtime +$RETENTION_TIME -exec rm \{\} \;
fi

4.) Cron

Um das Backupscript jeden Tag auszuführen kopiert man es auf den Server und trägt es im crontab ein:

root@icingaMaster# crontab -e
  @daily /root/backup_icinga2.sh

5.) Decrypt

Da beim verschlüsseln alle User der Gruppe icingabackup berechtigt wurden kann jeder aus dieser Gruppe die Dateien wieder entschlüsseln.
gpg –output icinga2Backup.tar.gz –decrypt icinga2Backup.tar.gz.gpg
 

Christoph Niemann
Christoph Niemann
Senior Consultant

Christoph hat bei uns im Bereich Managed Service begonnen und sich dort intensiv mit dem internen Monitoring auseinandergesetzt. Seit 2011 ist er nun im Consulting aktiv und unterstützt unsere Kunden vor Ort bei größeren Monitoring-Projekten und PERL-Developer-Hells.

MariaDB Upgrade von 10.0.x auf 10.2.x in 10 einfachen Schritten

Ein Upgrade von verschiedenster Software ist von Zeit zu Zeit nötig. So auch von MariaDB. Der Folgende Blogpost ist ein Leitfaden für ein normales Upgrade.
Wenn man noch MariaDB 10.0.x im Einsatz hat und möchte nun auf 10.2.x ist dies in der Regel kein Problem. Es gibt jedoch ein paar wenige Punkte auf die man dabei achten sollte.


  • Ab MariaDB 10.1.x  können unter „/etc/mysql/conf.d“ keine speziellen Parameter mehr für Multi-Instanzen angeben werden. Hier wurden in der Vergangenheit beispielsweise Parameter gespeichert, die man mit einem Config-Management Tool verwaltet hat und man nicht wollte, dass mehrere Server die gleichen Parameter bekommen. Bei manchen Setups kann dies etwas aufwendig werden, aber es gibt keinen Weg drum herum.
  • ich empfehle vorher auf jeden Fall ein Backup der Konfigurationsdateien und der Datenbanken anzufertigen.
  • Unbedingt erst die Slaves upgraden, dann die Master. Denn:
    Master MariaDB 10.0.x <=> Slave MariaDB 10.2.x funktioniert
    Master MariaDB 10.2.x <=> Slave MariaDB 10.0.x funktioniert nicht 
  • Es haben sich zwischen den Versionen verschiedene Default Parameter der DBs geändert. Es sollten also auch die Changelogs für die jeweils wichtigsten Parameter geprüft werden.

Sofern oben genannte Punkte berücksichtigt wurden, kann das eigentliche Upgrade vorbereitet werden. Als Zwischenschritt, empfehle ich MariaDB 10.1.x zu installieren, um eventuell anfallende Abhängigkeiten mitzunehmen:

  1. Parameter aus /etc/mysql/conf.d anpassen
    Wichtig dabei, auch die Instanznummerierungen [mysqldX] auszukommentieren 

    #[mysqld1]
    # * Basic Settings
    #read_only = 1
    #[mysqld2]
    # * Basic Settings
    #read_only = 1
  2. Datenbank Instanz(en) stoppen
    Mutli-Instanzen stoppen mit:

    mysqld_multi stop X
  3. Pins aus /etc/apt/preferences.d/mariadb.pref entfernen (oder die Datei löschen)
    rm /etc/apt/preferences.d/mariadb.pref
  4. Upgrade vorbereiten
    /etc/apt/sources.list.d/mariadb.list

    deb http://mirror2.hs-esslingen.de/mariadb/repo/10.1/ubuntu xenial main
    deb-src http://mirror2.hs-esslingen.de/mariadb/repo/10.1/ubuntu xenial main
  5. Upgrade durchführen
    Allgemein:

    apt-get update && apt-get upgrade

    mariadb-server upgraden

    apt-get upgrade mariadb-server
  6. Das gleiche nun nochmal mit der Version 10.2.x
    /etc/apt/sources.list.d/mariadb.list

    deb http://mirror2.hs-esslingen.de/mariadb/repo/10.2/ubuntu xenial main
    deb-src http://mirror2.hs-esslingen.de/mariadb/repo/10.2/ubuntu xenial main
  7. Upgrade dürchführen
    Allgemein:

    apt-get update && apt-get upgrade

    mariadb-server upgraden

    apt-get upgrade mariadb-server mysql-common
  8. Die Instanz(en) nun wieder anstarten
    Multi Instanzen werden gestartet mit:

    mysqld_multi start X
  9. Datenbank Upgrade durchführen
    Für eine Instanz: 

    mysql_upgrade

    Für multi-Instanzen:

    for sock in /var/run/mysqld/mysqld_33*.sock ; do mysql_upgrade -S $sock ; done
  10. Aufräumen
    dpkg -l | grep maria

    Hier kann alles entfernt werden, was mit „rc“ gekennzeichnet ist. 

    apt-get purge $alte-mariadb-versionen && apt-get autoremove

 
Nun sollten die Instanzen alle mit der aktuellen MariaDB 10.2.x laufen. Dieser Artikel ist nur ein Vorschlag, wie das Upgrade durchgeführt werden kann. Letztendlich kann es sein, dass in Einzelfällen noch Pakete nachinstalliert werden müssen. Ein mir bekannter Kandidat ist zum Beispiel: libmariadb3

Fast ein halbes Jahr NETWAYS

Am 01.September 2017 haben wir (Afeef, Killian, Philipp) bei NETWAYS unsere Ausbildung zum Fachinformatiker angefangen. Um uns auf die bevorstehende Arbeit im Professional Service vorzubereiten, bekamen wir gleich in der ersten Woche eine „Linux Basic“-Schulung. Damit wir das Gelernte weiter festigen können, wurde uns ein „LAMP“-Projekt aufgetragen. Ziel war es einen hochverfügbaren Webserver mit einer WordPress-Installation zur Verfügung zu stellen. Wir haben das Projekt in drei Teilbereiche aufgeteilt: LoadBalancer, Webserver und Datenbank. Nach erfolgreicher Zusammenarbeit haben wir das Projekt fertiggestellt und gemeinsam präsentiert.
Um Einblicke in andere Abteilungen zu bekommen, wurden wir in den daurauffolgenden Wochen aufgeteilt. Afeef durfte Managed Service unterstützten indem er ein automatisiertes Grafana-Dashboard mittels Puppet konfigurieren sollte. Besonders gefallen hat Ihm dabei die Hilfsbereitschaft der Kollegen aus Managed Service die Ihm bei Fragen über Puppet sofort geholfen haben. Meine Aufgabe war es derweil einen Maillserver aufzusetzen. Damit der Mailserver auch leicht zu benutzen ist, implementierte ich eine WebGUI mittels Roundcube.  Schon stand ein Wechsel zur Events-Abteilung an, die ich im November besuchen durfte. Ich half dabei, dass Schulungen richtig geplant und durchgeführt werden. In dieser Zeit kümmerte sich Killian um die neuen Schulungslaptops und konfigurierte ein neues Backupsystem namens ReaR. Schon stand auch die OSMC im November an, eine Premiere für uns Drei. Damit alle Vorträge auch aufgenommen werden hatten wir die ehrenvolle Aufgabe, als Raumwächter jeden Vortrag aufzunehmen, damit man diesen später anschauen kann.
Das nächste große Highlight war die Teilnahme unserer ersten NETWAYS-Schulung. „Fundamentals for Puppet“ stand auf dem Programm, welche eine komplett neue Erfahrung für uns drei war. Damit meine ich nicht nur die technische Seite sondern auch, wie von NETWAYS gewohnt, die herzliche Umgangsweise und die super Verpflegung.
Damit wir das Gelernte gleich umsetzen können, stand das nächste große Projekt an und zwar mit Puppet. Killians Aufgabe ist es einen LAMP-Stack mittels Puppet zu realisieren, dazu benötigt er das Wissen von unserem ersten gemeinsamen LAMP-Projekt. Afeef kümmert sich derzeit weiter um das Grafana-Dashboard und ich realisiere meinen zuvor erstellten Mailserver mittels Puppet.