Seite wählen

NETWAYS Blog

Alle User in MySQL anzeigen

Oftmals wachsen Datenbankinstallationen im Laufe der Zeit und man legt immer wieder für neue Projekte neue Datenbanknutzer an. Um hier den Überblick zu behalten, zeige ich kurz, wie man sich die jeweiligen Nutzer anzeigen lassen kann.

Voraussetzungen:

  • Command line/Terminal
  • MySQL oder MariaDB installiert
  • Benutzer in MySQL:Benutzer mit Sudo- oder Root-Rechten

Bei der Installation von MySQL wird bei der Installation als erster Benutzer der Root-Benutzer erstellt – der MYSQL-Administrator. Der Root-Benutzer ist berechtigt, alles in der MySQL-Datenbank zu tun – Eine einfache und zuverlässige Möglichkeit, die MySQL-Sicherheit zu erhöhen, besteht darin, Benutzer mit eingeschränkten Berechtigungsbeschränkungen für die Datenbank zu erstellen.

Wenn Sie Zugriff auf Ihren Server haben, müssen Sie die MySQL-Konsole aufrufen. Dafür benötigen wir Root-Rechte. Geben Sie dies in die Befehlszeile ein:

sudo mysql -u root -p

root@galeria-1:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 78
Server version: 10.4.13-MariaDB-1:10.4.13+maria~bionic-log mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Dann müssen Sie Ihr MySQL-Root-Passwort eingeben. Es sollte sich vom System-Root-Passwort unterscheiden. Sobald Sie in der MySQL-Konsole root sind, können Sie Abfragen und Befehle ausführen. MySQL-Benutzer anzeigen: Jetzt können Sie alle Benutzer in MySQL mit dem folgenden MySQL-Befehl auflisten:

MariaDB [(none)]> SELECT user FROM mysql.user;
+-------------+
| User |
+-------------+
| mariadb.sys |
| mysql |
| root |
| testuser | 
+-------------+
4 rows in set (0.004 sec)
MariaDB [(none)]>

Hot Backups mittels Xtrabackup

Wie Markus in seinem letzten Blogpost schon beschrieben hat, hatte er die zweifelhafte Ehre ein Backup einer verhältnismäßig großen MySQL-Datenbank (MariaDB) zu machen. Das große Problem bei ihm war allerdings, dass das Kopieren auf Dateiebene eine Downtime der Datenbank voraussetzte und somit der laufende Betrieb eingestellt werden musste. Man legt sozusagen die Datenbank auf Eis und deshalb nennt man dieses Verfahren auch Cold Backup (Offline Backup). Dies war zumindest meine Schlussfolgerung zur Namensgebung…

Als wissensdurstiger Azubi bei NETWAYS wollte ich wissen ob es vielleicht eine Möglichkeit gibt, ein Backup einer MySQL-Datenbank während  des laufenden Betriebes zu machen. Bei meinen Recherchen bin ich letztlich auf ein heiß diskutiertes Thema gestoßen. Wie der Name schon sagt, auf das „heiße“, sogenannte Hot Backup (Online Backup). Bei diesem Backupverfahren ist es möglich während des laufenden Betriebes einer MySQL-Datenbank ein Backup zu erstellen und somit eine Downtime der Datenbank zu vermeiden. Das Online Backup kann somit mehrmals am Tag durchgeführt werden, des Weiteren ist es deutlich schneller als das Offline Backup. Allerdings birgt ein Hot Backup auch ein gewisses Risiko, da im Gegensatz zum Cold Backup, weiter Daten in die Datenbank geschrieben werden und diese somit nicht im Backup enthalten wären. Das wäre aber im Ernstfall (Ausfall der Datenbank) zu verschmerzen, da somit beispielsweise womöglich nur Daten der letzten paar Stunden und nicht von einem ganzen Arbeitstag verloren wären.

Im Folgenden zeige ich anhand eines kleinen Beispiels, wie so ein Hot Backup vonstatten geht. Das Tool meiner Wahl ist Xtrabackup von Percona. Xtrabackup kann ein Hot Backup erstellen indem es sich die sog. crash-recovery Funktion von InnoDB-Datenbanken zu Nutze macht. Die Installation von Xtrabackup ist in der Dokumentation hervorragend beschrieben.

Zunächst benötigt man einen Datenbankuser mit folgenden Rechten:
mysql> CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'SuperGeheim!';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'backupuser'@'localhost';

Der nächste Schritt ist ein Backup anzustoßen:
$ xtrabackup --backup --user=backupuser --password=SuperGeheim! --target-dir=/var/test_backup
„–target-dir“: In diesem frei zu wählenden Ordner werden die Backups gespeichert.

Man kann bei der Ausgabe sehr gut erkennen, dass Xtrabackup sich die sog. LSN (Log Sequence Number) „merkt“ und ab diesem Zeitpunkt die Tabellen der Datenbank sperrt sowie alle weiteren Schreiboperationen „puffert“. Nach dem Kopieren der Daten werden die Tabellen wieder frei gegeben und der Betrieb ab der „gemerkten“ LSN wieder aufgenommen:
190829 10:27:43 >> log scanned up to (146090017)
xtrabackup: Generating a list of tablespaces
190829 10:27:43 [01] Copying ./ibdata1 to /var/test_backup/ibdata1
190829 10:27:44 [01] ...done
190829 10:27:44 >> log scanned up to (146090017)
190829 10:27:44 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
190829 10:27:44 Executing FLUSH TABLES WITH READ LOCK...
190829 10:27:44 Starting to backup non-InnoDB tables and files
[...]
[...]
190829 10:27:46 Finished backing up non-InnoDB tables and files
190829 10:27:46 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '146090233'
xtrabackup: Stopping log copying thread.
.190829 10:27:46 >> log scanned up to (146090233)
190829 10:27:46 Executing UNLOCK TABLES
190829 10:27:46 All tables unlocked
190829 10:27:46 Backup created in directory '/var/test_backup'
190829 10:27:46 [00] Writing /var/test_backup/backup-my.cnf
190829 10:27:46 [00] ...done
190829 10:27:46 [00] Writing /var/test_backup/xtrabackup_info
190829 10:27:46 [00] ...done
xtrabackup: Transaction log of lsn (146086198) to (146090233) was copied.
190829 10:27:46 completed OK

Wenn das Backup erfolgreich war und man dieses wieder einspielen will, muss man dieses zuvor „vorbereiten“. Da die kopierten Daten womöglich nicht mit den aktuellen Daten übereinstimmen:
$ xtrabackup --prepare --target-dir=/var/test_backup

Hat man ein InnoDB: Shutdown completed; log sequence number 146093758
190829 10:45:05 completed OK!
erhalten, lässt sich das Backup wiederherstellen

ACHTUNG: Es müssen beim Wiederherstellen alle Daten aus /var/lib/mysql/ gelöscht werden. Hierbei sollte man sehr aufpassen, ansonsten kann es zum kompletten Datenverlust führen!!!

Um das „vorbereitete“ Backup einzuspielen, muss der MySQL (MariaDB) Dienst gestoppt sein (was bei einem Ausfall sowieso der Fall sein dürfte). Der nächste Schritt wäre den Benutzer der Daten anzupassen und den Dienst erneut zu starten:
$ systemctl stop mariadb.service
$ rm -rf /var/lib/mysql/*
$ xtrabackup --move-back --target-dir=/var/test_backup
$ chown -R mysql:mysql /var/lib/mysql/
$ systemctl start mariadb.service

Als Fazit kann man sagen, dass sich Hot Backups sehr lohnen, aber auch ein gewisses Risiko beherbergen können. Cold Backups sind dazu im Gegensatz sehr „starr“ und können nur während einer Downtime durchgeführt werden. Sie bieten jedoch die höhere Chance auf Datenkonsistenz und sind deutlich einfacher zu handhaben. Empfehlenswert ist es auf jeden Fall sich eine geeignete Datensicherungsstrategie aus beiden Varianten zu überlegen, bei der man zum Beispiel am Ende des Tages ein Cold Backup und während der Geschäftszeiten mehrere Hot Backups macht.

rsync und was dann?

Diese Woche hatte ich die zweifelhafte Ehre die mit 1,6TB schon etwas größere MySQL-Datenbank (MariaDB) eines Kunden auf den zweiten Datenbankknoten zu spielen. Dabei war die Herausforderung das die ganze Show außerhalb der Geschäftszeiten von 17:30 Uhr bis max. 5:00 Uhr stattfindet. Ein Dump der Datenbank dauert erfahrungsgemäß zu lange um das Wartungsfenster einzuhalten. Kein Problem dachte ich mir, dann halt rsync auf Dateiebene. Also die Datenbankzugriffe pünktlich zu Beginn des Wartungsfensters unterbunden, die Datenbank gestoppt und den rsync vom Zielsystem aus wie folgt gestartet:

# rsync -avPz --exclude 'ib_logfile*' root@a.b.c.d:/var/lib/mysql/ /var/lib/mysql/

Eine kurze Erklärung der gesetzten Parameter:

  • a – kopiert rekursiv unter Beibehaltung der Dateiberechtigungen
  • v – sorgt für eine ausführlichere Ausgabe (verbose)
  • P – zeigt eine Fortschrittsanzeige (progress) und setzt den Transfer bei einem evtl. Abbruch fort (partial)
  • z – aktiviert die Komprimierung, meistens bei einer Übertragung via Netzwerk sinnvoll

Die beiden InnoDB Logfiles (ib_logfile0 und ib_logfile1) mit jeweils 11GB wurden für eine schnellere Übertragung ausgeschlossen, da sie beim Anstarten eh wieder neu erstellt werden.

Leider hat sich relativ schnell herausgestellt dass das nicht der Weisheit letzter Schluss war, da die Übertragung mit ca. 15MB/s an die 32 Stunden gedauert und damit das Wartungsfenster überschritten hätte. Auch eine Anpassung der Parameter und der Synchronisationsvorgang auf ein schnelleres Storage mit max. 40MB/s und damit fast 15 Stunden wären zu lange gewesen.

Nach einer kurzen Internetrecherche bin ich auf eine mögliche Lösung mit mbuffer gestossen. Der „measuring buffer“ steht bereits als kleines Paket für die gängigen Linux-Distributionen zur Verfügung und sorgt dafür das es durch einen Puffer nie zu einem Leerlauf des Datenstroms kommt und die Verbindung somit nicht abreißen kann. Mit Komprimierungsfunktionalität und etwas „Bash-Magic“ außenrum kann das dann so aussehen:

# tar cf - * | mbuffer -m 1024M | ssh a.b.c.d '(cd /var/lib/mysql; tar xf -)'

Dem zuzusehen war schon fast eine Freude, hätte nur noch eine Tüte Chips und vielleicht ein passendes Kaltgetränk gefehlt. Mit Transferraten von bis zu 350MB/s hat der Kopiervorgang so gerade mal über 2 Stunden gedauert (Durchschnitt 216MB/s) und die Umgebung bis zum Ende des Wartungsfensters längst wieder im Normalzustand. Das in vielen Fällen schon hilfreiche rsync kommt v.a. bei sehr vielen oder sehr großen Dateien durch die Checksummenberechnung an seine Grenzen, sodass mbuffer hier durchaus mehr als nur eine Alternative sein kann.

Markus Waldmüller
Markus Waldmüller
Head of Strategic Projects

Markus war bereits mehrere Jahre als Sysadmin in Neumarkt i.d.OPf. und Regensburg tätig. Nach Technikerschule und Selbständigkeit ist er nun Anfang 2013 bei NETWAYS als Senior Manager Services gelandet. Seit September 2023 kümmert er sich bei der NETWAYS Gruppe um strategische Projekte. Wenn er nicht gerade die Welt bereist, ist der sportbegeisterte Neumarkter mit an Sicherheit grenzender Wahrscheinlichkeit auf dem Mountainbike oder am Baggersee zu finden.

Icinga 2 – Monitoring automatisiert mit Puppet Teil 12: Profile Part IV

This entry is part 12 of 14 in the series Icinga 2 Monitoring automatisiert mit Puppet

Heute geht es mit der Konfiguration von Icinga Web 2 weiter, nach dem im letzte Teil als Voraussetzung PHP konfiguriert wurde. Als Backend zur Speicherung von Authentifizierungs- und Benutzerdaten, kommt eine eigene MySQL Datenbank auf dem gleichen lokalen System zum Einsatz. Der Datenbank- und der Benutzername, sowie dass Passwort soll mittels Parameter festgelegt werden könnten, das gleiche gilt für den zum Senden von Kommandos an Icinga benötigten API-Benutzer und man soll sich als Webserver zwischen einem Apache bzw. einem Nginx entscheiden können. Die Konfiguration letztgenannter, wird im nächsten Teil der kommenden Woche behandelt.
[ruby]class profile::icinga2::server(
String $web_db_pass,
String $web_api_pass,

String $web_db_user = ‚icingaweb2‘,
String $web_db_name = ‚icingaweb2‘,
String $web_api_user = ‚icingaweb2‘,
Enum[‚apache‘, ’nginx‘] $web_server = ‚apache‘,
) {

mysql::db { $web_db_name:
host => $web_db_host,
user => $web_db_user,
password => $web_db_pass,
grant => [‚ALL‘],
before => Class[‚icingaweb2′],
}
if $web_server == ’nginx‘ {
$manage_package = true
# $web_conf_user =
} else {
$manage_package = false
# $web_conf_user =
package { ‚icingaweb2‘:
ensure => installed,
}
}
class { ‚icingaweb2‘:
db_username => $web_db_user,
db_password => $web_db_pass,
import_schema => true,
config_backend => ‚db‘,
conf_user => $web_conf_user,
manage_package => $manage_package,
}
::icinga2::object::apiuser { $web_api_user:
ensure => present,
password => $web_api_pass,
permissions => [ ’status/query‘, ‚actions/*‘, ‚objects/modify/*‘, ‚objects/query/*‘ ],
target => ‚/etc/icinga2/conf.d/api-users.conf‘,
}
class { ‚::icingaweb2::module::monitoring‘:
ido_db_host => ‚127.0.0.1‘,
ido_db_name => $ido_db_name,
ido_db_username => $ido_db_user,
ido_db_password => $ido_db_pass,
commandtransports => {
‚icinga2‘ => {
transport => ‚api‘,
username => $web_api_user,
password => $web_api_pass,
}
}
}
}[/ruby]

Lennart Betz
Lennart Betz
Senior Consultant

Der diplomierte Mathematiker arbeitet bei NETWAYS im Bereich Consulting und bereichert seine Kunden mit seinem Wissen zu Icinga, Nagios und anderen Open Source Administrationstools. Im Büro erleuchtet Lennart seine Kollegen mit fundierten geschichtlichen Vorträgen die seinesgleichen suchen.

Icinga 2 – Monitoring automatisiert mit Puppet Teil 10: Profile Part II

This entry is part 10 of 14 in the series Icinga 2 Monitoring automatisiert mit Puppet

In Weiterführung vom letzten Post dieser Serie, beschäftigen wir uns zuerst damit dem Icinga-Server eine CA hinzuzufügen. Dies erledigt die Deklaration der Klasse icinga2::pki::ca. Sie erzeugt auch noch gleich ein Zertifikat für den eigenen Server.
Das ist auch der Grund, warum im Folgenden der Parameter pki des Features API mit none belegt werden muss, da genau dies verhindert, dass nochmals versucht wird ein Zertifikat zu generieren. Dieser Wert für pki ist also nur sinnvoll für Hosts mit einer Icinga-2-CA.
[ruby]class profile::icinga2::server {

#
# CA / API
#
include ::icinga2::pki::ca
class { ‚::icinga2::feature::api‘:
pki => ’none‘,
accept_commands => true,
}
[/ruby]
Als nächstes widmen wir uns dem Feature IDO, welches die IDO-DB befüllt, hier eine MySQL-Datenbank, die ebenfalls per Puppet verwaltet werden soll und sich auch dem gleichen Server befindet. Hierfür ist zusätzlich das MySQL-Puppetmodule erforderlich. Das Datenbank-Schema kann vom Icinga2-Modul automatisch angelegt werden. Hierfür ist dann zu den üblichen Berechtigungen auch CREATE für den Benutzer, den auch Icinga für den Zugriff verwendet, erforderlich, da auch dieser zum initalen Erzeugen der Tabellen vom Icinga2-Modul verwendet wird.
In Bezug auf die Reihenfolge der Abarbeitung unserer Ressourcen, muss nur dafür Sorge getragen werden, dass die Datenbank für die IDO vor dem IDO-Feature dran kommt.
Für den zu verwenden Benutzernamen, das zugehörige Passwort und den eigentlichen Datenbanknamen fügen wir der Profilklasse Parameter hinzu. Im Gegensatz zum Datenbank- und Benutzernamen, die beide als Default icinga2 gesetzt bekommen, ist das Passwort als Parameter vom Endbenutzer immer selbst anzugeben.
[ruby]class profile::icinga2::server(
String $ido_db_pass,
String $ido_db_name = ‚icinga2‘,
String $ido_db_user = ‚icinga2‘,
) {
case $::osfamily {
‚redhat‘: {

package { [ ’nagios-plugins-all‘, ‚icinga2‘, ‚icinga2-ido-mysql‘ ]:
ensure => installed,
before => User[‚icinga‘],
}

}

}
#
# Icinga 2
#
class { ‚::icinga2‘:
manage_package => $manage_package,
manage_repo => $manage_repo,
}
#
# IDO database
#
include ::mysql::server
mysql::db { $ido_db_name:
user => $ido_db_user,
password => $ido_db_pass,
host => ‚127.0.0.1‘,
grant => [‚SELECT‘, ‚INSERT‘, ‚UPDATE‘, ‚DELETE‘, ‚DROP‘, ‚CREATE VIEW‘, ‚CREATE‘, ‚INDEX‘, ‚EXECUTE‘, ‚ALTER‘],
before => Class[‚icinga2::feature::idomysql‘]
}
class{ ‚::icinga2::feature::idomysql‘:
user => $ido_db_user,
password => $ido_db_pass,
database => $ido_db_name,
import_schema => true,
}
[/ruby]
Auf RedHat-Systemen musste, wie in vorherigen Teil zu sehen war, da Paketmanagement aus dem eigentlichen Icinga-Modul herausgezogen werden, um den Benutzer icinga zwischen Paketinstallation und Icinga-Klasse verwalten zu können. Das bezieht sich nun ebenfalls auf das Paket icinga2-ido-mysql, das für das IDO-Feature erforderlich ist. Debianbasierte Systeme sind hiervon nicht betroffen.

Lennart Betz
Lennart Betz
Senior Consultant

Der diplomierte Mathematiker arbeitet bei NETWAYS im Bereich Consulting und bereichert seine Kunden mit seinem Wissen zu Icinga, Nagios und anderen Open Source Administrationstools. Im Büro erleuchtet Lennart seine Kollegen mit fundierten geschichtlichen Vorträgen die seinesgleichen suchen.