Mysql Replikation neu aufsetzen als Bash Skript
Wenn eine bestehende Mysql Replikation nicht mehr funktioniert, so half mir etliche Male im Laufe der Berufszeit als Webmaster beim Schweizer Radio DRS / Sysadmin Daseins an der Uni Bern ein top-down-Skript weiter. Wenn gerade Stress ist und man in der Situation nicht erst alle Kommandos zur Wiederinbetriebnahme des Slave nachschlagen mag, dann ist ist man froh, wenn man ein Skript dafür parat liegen hat.
reinit_mysql_replication.sh
Es gibt keine Parameter.
Installation:
Man benötigt einen Mysql Client. Ich hatte daher die Skripte am Mysql-Slave unterhalb /root zu liegen. Daher ist die Verbindung zum Slave ohne Passwort (wird aus der /root/.my.cnf gelesen).
Es wird auch vom eigenen Rechner aus funktionieren, der die Mysql-Ports von Master und Slave erreichen kann. Es ist nur “einen Tick” langsamer (sprich: bei vielen und/ oder grossen Datenbanken > 1 GB Gesamtgrösse nicht zu empfehlen).
Konfiguration:
Die *dist Datei ist umzukopieren und die Hostnamen und Root-Passwörter für Master + Slave sind zu setzen.
Start:
Das Skript reinit_mysql_replication.sh führt der Reihe nach folgende Aktionen aus: es …
- zeigt aktuellen Slave Status
- wartet dann auf ein RETURN, bevor die Replikation neu aufgesetzt wird
Aktionen zum Neuaufsetzen der Replikation: das Skript …
- liest die Position des Binlog am Master (per SQL “SHOW MASTER STATUS G;”)
- sperrt den Master für Schreibaktionen (”FLUSH TABLES WITH READ LOCK;”)
- holt aktuelle Datenbank-Dumps vom Master (mysqldump –all-databases –lock-all-tables …)
- hebt Schreibsperre am Master auf (”UNLOCK TABLES;”)
- stoppt den Slave (”STOP SLAVE G;”)
- importiert Dumps des Master auf dem Slave (cat $dumpMaster | mysql $paramdbSlave)
- setzt am Slave binfile und Position (”CHANGE MASTER TO MASTER_LOG_FILE = …” ; “CHANGE MASTER TO MASTER_LOG_POS = …”)
- startet den Slave (”START SLAVE G;”)
weiterführende Links:
Linux Bash - Zufallspasswort für Mysql-Monitoring User
Ich möchte Mysql/ MariaDB Server monitoren. Dazu legt man einen Monitoring-User in der Datenbank an, der dann Select Rechte auf mysql.* bekommt, um aus der Datenbank Statusinformationen zu lesen. Der User soll ein langes Zufallspasswort bekommen. Die Installation muss mit dem root User auf der Datenbank erfolgen.
Im Monitoring Modus will ich kein Passwort als Parameter übergeben, damit es nicht in der Prozessliste erscheinen kann. Dies lässt sich mit einer .my.cnf im HOME Verzeichnis bewerkstelligen.
Schritt 0: Vorbereitung
Initial setze ich mein HOME und die Variable cfgfile auf die .my.cnf:
# --- set HOME HOME=/etc/icinga2-passive-client # --- other vars... cfgfile=$HOME/.my.cnf myuser=icingamonitor datafile=/tmp/mysql-status.txt
Schritt 1: Ein Zufalls-Passwort erzeugen.
Ich will nur parametriesieren können, wie lang das zu erstellende Passwort ist … daher die Variable vorab.
Das Passwort kommt durch eine Ausgabe des Zufallsgenerators /dev/urandom zustande - wobei mittels tr nur Ziffern und Buchstaben gefiltert werden.
pwlength=64 mypw=$( head /dev/urandom | tr -dc A-Za-z0-9 | head -c $pwlength )
Schritt 2: Mysql-User mit Rechten anlegen.
Dieser Aufruf funktioniert nur mit dem root (oder anderen zusätzl. angelegten Admin-) User.
Wenn Linux-Root passwortlosen Zugriff auf den Datenbank-Root-User hat, muss man das HOME auf /root setzen, damit dessen .my.cnf gefunden wird.
HOME=/root mysql -e "CREATE USER $myuser@localhost IDENTIFIED BY '$mypw';" if [ $? -ne 0 ]; then echo "ERROR: mysql command to create user failed." exit 1 fi echo "- grant SELECT on mysql tables ..." mysql -e "GRANT SELECT ON mysql.* TO $myuser@localhost;" if [ $? -ne 0 ]; then echo "ERROR: mysql command to grant permissions failed." exit 1 fi echo "- flush privileges ..." mysql -e "FLUSH PRIVILEGES;"
OK, damit habe ich nun meinen Datenbank-User. Dessen Passwort ich selbst nicht kenne, aber ich weiss, dass es 64 Zufalls-Zeichen besitzt und “halbwegs” safe sein dürfte. Damit erspare ich mir bei zig-zig lokalen Mysql/ MariaDb Services die Verwaltung der Kennwörter für den Monitoring-User.
Schritt 3: Mysql-User-Konfigurationsdatei anlegen.
Die Konfigurationsdatei ist in einem Verzeichnis des Monitoring-Users anzulegen. Zum Erzeugen der Datei wird hier nur auf $cfgfile zurückgegriffen.
cat >$cfgfile <<EOF # # generated on `date` # [client] user=$myuser host=localhost password=$mypw EOF ls -l $cfgfile if [ $? -ne 0 ]; then echo "ERROR: creation of config file failed." exit 1 fi
Nachdem Schritte 1..3 als root erfolgten, sollte bei Aufruf des Skripts mit dem Monitoring User das $HOME wie in Schritt 0 umgebogen sein, damit die soeben erzeugte .my.cnf angezogen wird. Und voila: dann hat der Monitoring Aufruf einen passwortlosen Zugriff.
Man kann den Status lesen, dies in eine Datei umleiten … und dann die gewünschten Variablen per grep lesen.
function _mysqlreadvars(){
mysql -e "SHOW GLOBAL VARIABLES ;" --skip-column-names >$datafile
mysql -e "SHOW STATUS ;" --skip-column-names >>$datafile
}
function _mysqlgetvar() {
local sVarname=$1
grep "^$sVarname[^_a-z]" ${datafile} | awk '{ print $2 }'
}
# init
_mysqlreadvars
_mysqlgetvar max_connections
_mysqlgetvar Max_used_connections
# cleanup
rm -f $datafile
Die Codeschnipsel sind zur Veranschaulichung aus dem Gesamtkontext herausgerissen. Das komplette Skript ist unten verlinkt.
weiterführende Links:
Xampp: Port 3306 belegt - durch Firefox
Schon komisch: ein Programm krallt sich einen Port. Ich dachte, das wäre die Domäne von Skype.
Abhilfe:
- Firefox beenden
- Mysql im Xampp starten
- Firefox starten
Unterbinden von Load_file() in Mysql und MariaDB
Ich benötige zum Betreiben von Applikationen keine Dateizugriffe via Mysql. Wenn es weder Dateien vom Server noch vom Client braucht, fühle ich mich wohler, wenn die Funktion komplett deaktiviert ist, weil das ein offenes Scheunentor sein kann.
Um das Laden von Dateien innerhalb SQL zu vermeiden, gibt es in Mysql die Möglichkeit, in der Konfiguration den Dateizugriff zu unterbinden
[mysqld] local-infile = 0|1 oder ON|OFF secure_file_priv = [Wert]
Kurz in der Dokumentation die Werte nachlesen, ins Puppet kippen und auf alle Server verteilen.
Als Test, ob ich erfolgreich bin, dient der Aufruf … dieser muss NULL ausgeben und keinen Dateiinhalt.
# mysql -e "SELECT load_file('/etc/passwd');"
+--------------------------+
| load_file('/etc/passwd') |
+--------------------------+
| NULL |
+--------------------------+
Das war die Idee. Eigentlich klingt es einfach.
Leider verhalten sich aber die Einstellungen von Mysql und MariaDB unterschiedlich.
Der Wert für local-infile muss bei beiden 0 oder OFF sein. local-infile ist aber eine dynamische Variable - das ist aber lediglich der Startwert, der mit einem SQL Befehl in der laufenden Instanz neu gesetzt werden kann.
Heisst: es braucht zwingend den Wert für secure_file_priv, der sich wiederum NICHT zur Laufzeit verändern lässt.
(1)
Variante für Mysql:
[mysqld] (...) local-infile = 0 secure_file_priv = NULL (...)
Danach einmal den Service neu starten systemctl restart mysql … dann habe ich das Verhalten wie in meinem Testaufruf.
Dieses Setup funktioniert nicht auf MariaDB - es wird mit einem Fehler in der Variable secure_file_priv beendet.
Die Dokumentation sagt: wenn secure_file_priv fehlt, würde der Dateizugriff verhindert werden…
Description: LOAD DATA, SELECT … INTO and LOAD FILE() will only work with files in the specified path. If not set, the default, the statements will work with any files that can be accessed.
Aber das ist nicht korrekt! Wenn ich secure_file_priv nicht setze, und es aus der laufenden Datenbank abfrage, ist der Wert leer
# mysql -e "SHOW VARIABLES LIKE 'secure_file_priv';" +------------------+------------+ | Variable_name | Value | +------------------+------------+ | secure_file_priv | | +------------------+------------+
… und ein SELECT load_file(’/etc/passwd’); zeigt mir den Dateiinhalt an!
Wer Percona oder einen anderen Mysql-Abkömmling verwendet, sollte das Verhalten austesten.
MariaDB erwartet einen existierenden Verzeichnisnamen. Ich habe mich entschieden, /dev/null zu nehmen, weil es sowohl existiert als auch ein User hier keine Datei ablegen kann:
(2)
Variante für MariaDB:
[mysqld] (...) local-infile = 0 secure_file_priv = /dev/null (...)
(3)
Bliebe noch die Software-Verteilung… wenn in Puppet die Mysql-Klasse aufgerufen wird, muss diese von irgendwoher wissen, ob das Produkt am Zielsystem MariaDB oder Mysql ist. Die Lazy Variante wäre, auf das OS zu reagieren, weil Defaults in den Repos beispielsweise von Debian oder CentOS sich unterscheiden. Besser und sicherer ist natürlich ein echtes Flag.
Update zu (3):
Meine Variante für ein vom Mysql Daemon nicht verwendbares, aber stets existierendes Verzeichnis für beide Mysql-Varianten ist … “/root”
[mysqld] (...) local-infile = 0 secure_file_priv = /root (...)
weiterführende Links:
Datenbank-Optimierung - mein Highlight der Woche
Bei meiner Arbeit an der Uni Bern war ein Grossteil der Zeit in die Optimierung von Datenbank-Zugriffen geflossen. Das betraf hauptsächlich ein System für ein Portal für Dozierende und Studierende mit Stundenplänen, Kurseinschreibungen, Feeds zu etlichen Themen und nach Studienjahr getrennt, Kalendersynchronisation von personalisierten Kalendern uvm. Jenes Portal ist uralt und wurde einmal mit PHP4 entwickelt und sollte abgelöst werden. Wurde es aber nicht, sondern es wurde vor vielen Jahren mühsam unter PHP5 lauffähig gemacht, aber der Code blieb noch immer zumeist prozedural, mit vielen Includes und gespickt mit allen erdenklichen Highlights an Programmiersünden, sei es Wartbarkeit, Caching/ Performance, SQL-Injection oder Verständnis von Algorithmen und Gestaltung von Datenbankabfragen. Seit dem ersten Ablösetermin sind nun 8..9 Jahre vergangen … aber Herbst 2019 wird es endlich(!!) abgelöst. Heisst aber: bis dahin muss es weiter am Leben erhalten werden.
Es wurden 5 Applikationsteile, die “am meisten weh tun” ausgemacht und jene optimiert. Letzten Dienstag wurde es eingespielt. Doch, es hat sich gelohnt - da ist so ein markanter Abfall:
Man könnte meinen: da ist was kaputt. Aber nein: es funktioniert genau gleich!!
Ich hoffe, ich kann euch mit diesem Praxis-Beispiel motivieren: macht einmal eine Performance-Analyse und schaut genauer auf die Top 3!
Mysql-connect sehr langsam?
Für Windows gibt es fixfertige Pakete für die Kombination Apache + Php + Mysql + X, wie z.B. XAMPP oder Wamp.
Für ein kleines Projekt habe ich auf eine solche zurückgegriffen und irgendwie war es langsam. Was genau langsam war, war nach einigem Debugging lokalisiert:
(...) $iStart=microtime(true); mysql_connect($hostname . ":" . $hostport, $username, $password); echo microtime(true) - $iStart."s to open DB $database<br>"; (...)
Das mysql_connect() brauchte regelmässig 1 Sekunde - die anschliessenden Queries 0.00x Sekunden.
Ursache ist der Zugriff mit dem Hostnamen “localhost” auf die Loopback-Adresse. Wenn man den Mysql-Service auf eine IP-Adresse bindet, geht es massiv schneller. Konfiguriert wird dies in der my.ini im Installationsverzeichnis von Mysql. Oder man verbindet sich auf die IP-Adresse 127.0.0.1.
Ach, und unter Windows den Eintrag lower_case_table_names=2 nicht vergessen - daher schiebe ich es mal hinterher:
(...) bind-address="127.0.0.1" bind-address = ::1 # fuer ipv6 lower_case_table_names=2 (...)
Nach Änderung der Konfiguration muss man den Mysql-Dienst neu starten, damit es wirksam wird.
Weiterführende Links:
PHP mit Sqlite - mein erster Gehversuch
Ich habe mal einen Download-Zähler gebaut: mittels .htaccess werden alle Dateizugriffe auf ein PHP-Skript umgebogen, welches einmal die angeforderte Datei ausliefert und den Zugriff protokolliert.
In PHP5 ist Sqlite direkt mitgeliefert. Die Sqlite Datenbank ist eine Textdatei, auf die ohne einen laufenden Server zugegriffen wird. Für kleine Webauftritte, wo das Webroot nicht auf einem NFS- oder SMB-Share liegt, ist dies problemlos.
Der wesentliche Vorteil einer SQl-Datenbank zu einer (CSV-) Textdatei ist die Auswertung der Daten: mit SQL Queries kommt man schnell zu den gewünschten Informationen.
Ich definiere mal in PHP eine Variable mit dem Dateinamen zur Datenbank:
$sqliteDB = $_SERVER['DOCUMENT_ROOT']."/sqlite/downloads.sqlite";
In dieser Datenbank ist - weil es zum Einstieg einfacher ist: mit einem grafischen Tool - eine Tabelle angelegt worden:
CREATE TABLE "downloadcount" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , "time" DATETIME, "file" TEXT, "ext" TEXT, "ip" TEXT, "referrer" TEXT, "usaeragent" TEXT )
Dann muss man nur noch wissen, welcher Sqlite Treiber beim Provider vorhanden ist. Das kann wahlweise Sqlite2, Sqlite3 oder PDO Sqlite sein. Die Versionen unterscheiden sich bei den Kommandos zum Öffnen der Datenbank oder beim Aufruf zum Ausführen eines Queries.
Bei Sqlite 3
$db = new SQLite3($sqliteDB);
Bei PDO Sqlite 3:
$db = new PDO("sqlite:".$sqliteDB);
Ich beziehe mich mal auf die PDO Variante…
So öffnet man eine DB und fügt mit Ausführung eines SQL Statements einen Eintrag hinzu. Eingefügt werden hier die aktuelle Uhrzeit (des Requests), Dateiname und dessen Erweiterung, IP-Adresse des Aufrufers, Referrer und User-Agent.
// $filename enthält den Dateinamen der heruntergeladenen Datei
$db = new PDO("sqlite:".$sqliteDB);
if ($db) {
$path_info = pathinfo($filename);
$ext=$path_info['extension'];
$sql="INSERT INTO `downloadcount`
(`time`, `file`, `ext`, `ip`, `referrer`, `useragent`)
VALUES ('".date("Y-m-d H:i:s")."', '" . $filename . "', '".$ext."', '" . getenv("REMOTE_ADDR") . "', '" . getenv('HTTP_REFERER') ."','".getenv('HTTP_USER_AGENT')."');
";
// echo "SQL:<br>$sql<br>";
$db->exec($sql);
}
Weiterführende Links:
- DBeaver - Datenbankverwaltung für div. Datenbanken(kostenlos, Opensource; für Windows, Mac, Linux; unterstützte Datenbanken: Sqlite, Mysql, Postgres, Oracle u.v.a.)
- Sqlite Administrator (Freeware; Windows)
- www.php.net - PDO Sqlite
- www.php.net - Sqlite 3
HeidiSQL 5 - Mysql-GUI für Windows
HeidiSQL 5 ist eine kostenlose grafische Oberfläche für Mysql-Server. Es ist ein reiner Windows-Client.
Die neue Version 5 von HeidiSQL habe ich mir heute heruntergeladen und habe es als portable Version am Laufen.
Version 4 wollte unter Windows 7 immer das Admin-Passwort haben, warum auch immer (vielleicht hab auch ich das verbockt)…
