MySQL Datenbank importieren und abgleichen (Linux)

[Corin Corvus]

Hallo,

ich habe eine kleine Frage, da ich im Netz keine eindeutigen Antworten auf meine Fragen erhielt.

Zuerst einmal mein Ziel: Wir sind eine kleine Community, die bereits beginnt zu wachsen. Wir haben mehrere Dienste wie, Teamspeak, einen jabber Messenger, unsere Webseite und eine Cloud auf der jeder User Speicher erhält.

Da es ein größerer Aufwand für den User und auch für mich ist, sich auf jedem Dienst zu registrieren und Freizuschalten, möchte ich gerne eine Automatisierung und einen Abgleich der Datenbank vornehmen lassen, der stündlich die Datenbank von der Webseite sichert, der Server diese Abholt und entpackt. Im Nachhinein wird diese importiert auf eine zweite Datenbank namens "webdb". Diese soll sich dann im Anschluss möglichst schnell abgleichen. Wichtig dabei sind ausschließlich User, da nur der Useranteil gezogen wird.

User_login
User_pass
User_mail

Dies sind nun einfach beschrieben die Daten, die rüber gehen sollen. Inwiefern die einzelnen Datenbanken aktuell laufen, kann ich zu diesem Zeitpunkt noch nicht sagen. Allerdings kann ich die Infos heute Nachmittag nachreichen, wenn gewünscht.


Was schon funktioniert.

Script:

#!/bin/sh
############################
# Information
############################

localzippath="/X/db/dbzip/";
localunzippath="/X/db/dbunzip/";

localzipdata="/X/db/dbzip/webdb*"
localunzipdata="/X/db/dbunzip/webdb.sql";

ftpuser="USER";
ftppw="PASSWORT";

remotepath="PFAD";

remoteadress="www.X.de";

db="webdb";
mysqluser="USER";

#############################
# Vorgang
#############################

# FTP Abholung
cd $localzippath
wget -c ftp://$ftpuser:$ftppw@$remoteadress/$remotepath

# Entpackvorgang
cd $localunzippath
tar -xvzf $localzipdata

# MySQL Import
cd /home/pi/db/dbunzip/
mysql -u $mysqluser -pPASSWORT $db < $localunzipdata


rm $localzippath/*
rm $localunzippath/*

Es wird stündlich ein Backup der User Tabelle aus der Datenbank, gepackt in einem Ordner abgelegt.

Ich habe ein kleines Script geschrieben, welches eine gepackte Datei über wget abholt. Im Anschluss wird direkt per tar die Datei in einen anderen Ordner entpackt. Von diesem Ordner aus wird ein Datenbank import ausgeführt.

mysql -u $mysqluser -pPASSWORT $db < $localunzipdata

Hier sind schon Fragen: Ich nutze diesen Befehl. Überschreibt er alle vorhandenen Daten oder ergänzt er nur nicht vorhandene oder löscht beispielsweise nicht mehr vorhandene? Wenn er alles überschreibt, hätte das negative Auswirkungen auf den Abgleich wenn der Vorgang mal länger dauern sollte?


Im weiteren bin ich noch etwas unerfahren, was MySQL angeht. Ich hatte in der Ausbildung leider nur 2 Stunden SQL Datenbanken und das ist ein wenig Mager. Auf der Arbeit nutzen wir das auch nicht so viel, wodurch ich nun selbst Kenntnisse aneignen möchte.

Wie kann ich am Sinnvollsten die Tabellen verknüpfen? Ich bezweifle, dass die Cloud oder der Messenger den gleichen Prefix nutzen geschweige die gleichen Bezeichnungen. Dementsprechend müsste ja eine Beziehung eingerichtet werden, wodurch der Abgleich stattfinden kann, oder?

Ich freue mich auf Antworten und auch auf Verbesserungsvorschläge des Scriptes. Versuche, das Script mit zu loggen, sind leider bisher nicht gelungen.

Gruß

 

[Madman1209]

Hi,

mal als generelle Frage: warum nicht gleich direkt die Datenbank replizieren? Gibt es einen Grund, warum das stündlich passieren soll?

VG,
Mad

 

[Corin Corvus]

Hi,

mal als generelle Frage: warum nicht gleich direkt die Datenbank replizieren? Gibt es einen Grund, warum das stündlich passieren soll?

VG,
Mad

Ich kann nicht direkt auf die Datenbank zugreifen und muss sie so als Dump ziehen. Zitat des Providers:"Ein Zugriff auf die Datenbank ist nur über unsere Weboberfläche möglich. Sie können allerdings ein Plugin nutzen, welches auf der Webseite ein Backup generiert, welches Sie dann per FTP abholen können". Die Webseite liegt bei meinem Domain Host, die viele Funktionen wie PHP Funktionen, die für einen direkten Abgleich nötig wären, nicht anbieten.

Wenn du den Umweg mit der webdb auf die maindb meinst, ist dies meiner Meinung nach sicherer, da nicht direkt in die maindb geschrieben wird (Bei Import in diese). Bei einer kompletten Überschreibung der Daten könnten andere Daten eventuell (Kann mich auch irren) in Mitleidenschaft gezogen werden, da die Dienste mehr Informationen in ihrer Datenbank haben als importiert wird. Wenn ich da falsch liege, lass ich mich gern belehren und würde dies auf meiner Testbasis einmal testen.

Stündlich weil der Abgleich zeitnah einer Registrierung passieren soll. Da ich auf der Webseite nicht unbedingt alle paar Minuten einen Cronjob mit Backup durchziehen wollte, war 1 Stunde das nächstliegende. So eilig haben wir es dann ja auch nicht.

 

[Madman1209]

Hi,

Die Webseite liegt bei meinem Domain Host, die viele Funktionen wie PHP Funktionen, die für einen direkten Abgleich nötig wären, nicht anbieten.

für Replikation ist PHP nicht notwendig, das manchen die MySQL Server direkt untereinander, ohne externe Programme oder dergleichen! Wenn du natürlich gar nicht von extern auf die Datenbank zugreifen kannst ist das natürlich hinfällig.

Bei Replikation wird auch nichts überschrieben sondern 1:1 auf deinem zweiten Server abgelegt. Das wäre in Punkto "Sicherheit wegen Überschreiben" in meinen Augen noch deutlich besser, weil du dann einen zweiten Datenbestand hast und von dort aus die Daten einfacher abziehen kannst, ohne irgendwelche Shell-Skripte.

Stündlich weil der Abgleich zeitnah einer Registrierung passieren soll. Da ich auf der Webseite nicht unbedingt alle paar Minuten einen Cronjob mit Backup durchziehen wollte, war 1 Stunde das nächstliegende. So eilig haben wir es dann ja auch nicht.

In meinen Augen gibt es da zwei Möglichkeiten, je nach technischer Machbarkeit:

1. Server wird wie angesprochen repliziert und auf dem zweiten Server wird direkt auf das Replikat zugegriffen
2. Im Produktivserver wird per Trigger bei einer Registrierung ein Ereignis ausgelöst, das den Datenabgleich anstößt.

Ich würde mir, falls der Hoster es so nicht zulässt, grundsätzlich überlegen, die Datenbank auf einen "besseren" Server umzuziehen, damit man die Funktionen des SQL-Servers auf vollumfänglich nutzen kann.

Nur mal so als Überlegung...

VG,
Mad

 

[X__]

Ob was überschieben wird, steht in der in der Backup-Datei drin.
Ich erwarte, dass da ganz normal sql-Anweisungen drin stehen werden,
die die Tabelle(n) möglichweise löschen, neu anlegen und mit inhalt füllen.
Vielleicht kann die Art des Backups diesbezüglich bei dem Provider eingestellt werden.

Wenn nicht und das Backup verhält sich so, wie man es nicht will (pauschales Überschreiben etc.)
könne man (so habe ich es vor kurzem erst gemacht) die Backupdatei über ein erweitertes Skripting
unter Zuhilfenahme von Cygwin und oder vbs vor dem Import anpassen. Z. B. so

(Datei "replace.vbs")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Const ForReading = 1
Const ForWriting = 2
' Parameter einlesen
inputFile = WScript.Arguments(0)
outputFile = WScript.Arguments(1)
searchText = WScript.Arguments(2)
replaceText = WScript.Arguments(3)

' Datei öffnen und Text einlesen und schließen
Set objFile = objFSO.OpenTextFile(inputFile, ForReading)
strText = objFile.ReadAll
objFile.Close

' Änderungen am Inhalt
strNewText = Replace(strText, searchText, replaceText)

' Neue Datei erstellen mit neuen Inhalten füllen
set resultFile = objFSO.CreateTextFile(outputFile, true)
resultFile.WriteLine strNewText
resultFile.Close

im Batchfile dann Sachen wie

replace.vbs ..\%TableName%.sql ..\%TableName%_neu.sql "CREATE TABLE" "CREATE TABLE IF NOT EXISTS"
replace.vbs ..\%TableName%_neu.sql ..\%TableName%_neu.sql "INSERT INTO" "REPLACE INTO"

oder auch

replace.vbs ..\%TableName%_neu.sql ..\%TableName%_neu.sql "INSERT INTO" "INSERT IGNORE INTO"

 

[Corin Corvus]

Das klingt super, wird aber leider nicht klappen. Ein Clustering oder eine Replizierung hatte ich auch schon im blick, müsste dazu aber Möglichkeiten auf deren Server haben, die ich leider nicht habe. Ich kann ausschließlich über "myphpadmin" auf die Datenbank auf dem Providerserver zugreifen. Aktionen wie "gleiche dich mit Datenbank X ab" sind nicht möglich.

Wenn ich die Datenbank auf einen anderen Server schieben würde, wäre dort ein weiteres Problem, was den Betrieb der Webseite stören könnte, sollte der Server mit der Datenbank mal ausfallen oder eine Verbindungsstörung zwischen den beiden ist. Die ganze Seite wollte ich nicht umziehen lassen, da sie direkt auf dem Webspace vorhanden ist, auf dem ich meine Domain laufen habe.

Ich sehe da wenig Chancen einen Abgleich anzustoßen, der von der Webseite oder der Datenbank auf deren Server kommt. Das hat der Provider von vornherein ausgeschlossen. Dementsprechend bleibt mir so nur die Dump Geschichte in eine zweite Datenbank auf meinem Produktivsystem, die sich mit meiner Haupt Datenbank abgleicht, sobald eine Veränderung eintritt.
Die Haupt Datenbank wird jeden Tag gesichert und 2 Wochen vorgehalten, um Datenverlust zu vermeiden und möglichst schnell wieder in Betrieb zu gehen.

Ergänzung (29. Januar 2016)

Ob was überschieben wird, steht in der in der Backup-Datei drin.
Ich erwarte, dass da ganz normal sql-Anweisungen drin stehen werden,
die die Tabelle(n) möglichweise löschen, neu anlegen und mit inhalt füllen.
Vielleicht kann die Art des Backups diesbezüglich bei dem Provider eingestellt werden.

Wenn nicht und das Backup verhält sich so, wie man es nicht will (pauschales Überschreiben etc.)
könne man (so habe ich es vor kurzem erst gemacht) die Backupdatei über ein erweitertes Skripting
unter Zuhilfenahme von Cygwin und oder vbs vor dem Import anpassen. Z. B. so

(Datei "replace.vbs")


im Batchfile dann Sachen wie

oder auch

Da bin ich nun völlig raus xD Batch geht so schon mal nicht, da ich shell scripte verwende.

Visual Basic Scripte laufen leider nur unter Windows. Ich nutze allerdings ein Debian System, wodurch das Script rausfällt.

 

[X__]

Naja, unter Linux wird es gleichwertige Möglichkeiten sicher auch geben - sonst wäre Linux ja nicht Linux
Aber vielleicht kommst du ja mit dem Prinzip weiter...

 

[Corin Corvus]

Das Prinzip ist schon mal besser ja. Wäre ja schlecht, wenn durch den blanken Import und das Überschreiben, User gelöscht und neu angelegt werden, wenn der abgleich simultan läuft.

Nun muss ich nur noch herausfinden, inwiefern das unter Linux klappt.

Ergänzung (29. Januar 2016)

Ich habe mir nun einfach mal das Dump gezogen und geöffnet.

Leider ist es so mit dem DROP TABLE IF EXISTS und er löscht erst die Tabelle. Dies würde dazu führen, dass beim Abgleich während dieses Vorgangs alle User ebenfalls gelöscht werden würden. Das geht natürlich gar nicht und wäre in Bezug auf die Cloud eine Katastrophe da dort dann gleich die Containervernichtung beginnt (Löschen aller Daten des Users).

-- Table structure for `X_users`
--

DROP TABLE IF EXISTS `X_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = 'utf8mb4' */;
CREATE TABLE `X_users` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_login` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_pass` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_nicename` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_url` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `user_status` int(11) NOT NULL DEFAULT '0',
  `display_name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`),
  KEY `user_login_key` (`user_login`),
  KEY `user_nicename` (`user_nicename`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

 

[X__]

Wie ich vermutet habe.

Prinzipiell kann man dem MySQL-Server mitteilen,
wie die Backups aufgebaut sein sollen -
zumindest kann man das bei der Nutzung von MySQL-Workbench einstellen unter:

"DataExport->Advanced Options->SQL->Create Options"
"DataExport->Advanced Options->Inserts->Insert Ignore"
"DataExport->Advanced Options->Inserts->replace"

Wenn dein Provider das aber mit seiner Software nicht hergibt,
musst du wohl ein Script basteln, das die Anpassungen vornimmt (falls du keine besseren Weg findest)

Getreu der Lebensweisheit: Irgendwas ist immer....

 

[Madman1209]

Hi,

dann musst du dir eben die SQL Statements selber bauen und nur die User einfügen, die noch nicht in deiner Datenbank stehen. Automatisch funktioniert da wohl eher nicht, bleibt nur händisch. Ist eigentlich keine große Sache nur die User einzufügen, die es noch nicht gibt, nachdem du ja eine ID hast.

VG,
Mad

 

[blablub1212]

Evtl. könnte man auch einen anderen Weg gehen. Du könntest ja auch eine kleine Registrierungsseite bauen, die die User auf allen Diensten anmeldet. Ich gehe mal davon aus, dass deine Cloud OwnCloud ist? https://doc.owncloud.org/server/8.0/admin_manual/configuration_user/user_provisioning_api.html

Und ich könnte mir vorstellen, dass auch dein Forum, TS-Server und Jabber-Server eine solche API bieten.

 

[Corin Corvus]

Händisch ist unpraktisch, da nur ich alleine soweit über Kenntnisse verfüge. Sollte ich mal in Urlaub fahren, und dieser kann mehrere Wochen dauern, wären die Admins aufgeschmissen. Daher muss zwingend ein Script her. Das von X___ muss also auch irgendwie unter Linux funktionieren, wenn auch mit anderen Anwendungen oder Paketen.

Es soll ja nicht nur hinzugefügt werden. Insofern ein User gelöscht wurde, weil er nicht mehr da ist, soll dies ebenso erkannt werden und entfernt werden. Das ist aber noch mal eine ganz andere Geschichte, die vom Dump zur webdb geregelt werden muss. Ich war sogar schon am überlegen, per Script direkt in die dump zu gehen und dort die Einträge erst zu vergleichen und dann anzupassen. Nur wie das genau geht, weiß ich noch nicht.

Ergänzung (29. Januar 2016)

Evtl. könnte man auch einen anderen Weg gehen. Du könntest ja auch eine kleine Registrierungsseite bauen, die die User auf allen Diensten anmeldet. Ich gehe mal davon aus, dass deine Cloud OwnCloud ist? https://doc.owncloud.org/server/8.0/admin_manual/configuration_user/user_provisioning_api.html

Und ich könnte mir vorstellen, dass auch dein Forum, TS-Server und Jabber-Server eine solche API bieten.

Meine Webseite bietet sowas nicht an. Der Jabber ja, TS nein, Owncloud ja.

Ich schaue es mir mal an

 

[X__]

Wegen Löschen nicht mehr existenter Accounts:

Falls du eine Programmiersprache beherrscht wäre es dann wahscheinlich am elegantesten
ein kleines Programm zu schreiben, dass den Abgleich vornimmt -
entweder in dem es von beiden Seite eine Datei bekommt, die dann zu einer
Import-Datei zusammengefasst werden, die du dann einspielst.
Oder noch einen Schritt weiter: Das Programm liest die Backupdsatei ein
und verbindet sich zum Zielserver und tätigt die notwendigen Änderungen direkt.

Denn das geht (in meinen Augen) dann bald über den sinnvollen Einsatz von Shells hinaus...

Bin gespannt, was du machst

 

[Corin Corvus]

Wegen Löschen nicht mehr existenter Accounts:

Falls du eine Programmiersprache beherrscht wäre es dann wahscheinlich am elegantesten
ein kleines Programm zu schreiben, dass den Abgleich vornimmt -
entweder in dem es von beiden Seite eine Datei bekommt, die dann zu einer
Import-Datei zusammengefasst werden, die du dann einspielst.
Oder noch einen Schritt weiter: Das Programm liest die Backupdsatei ein
und verbindet sich zum Zielserver und tätigt die notwendigen Änderungen direkt.

Denn das geht (in meinen Augen) dann bald über den sinnvollen Einsatz von Shells hinaus...

Bin gespannt, was du machst

Gibt es sowas in der art nicht schon? Denn Programmier Kenntnisse habe ich leider nicht. Die Frage ist da nur, wie der Oberbegriff eines solchen Programms heißt, dann könnte ich danach suchen.

 

[Madman1209]

Hi,

Händisch ist unpraktisch, da nur ich alleine soweit über Kenntnisse verfüge. Sollte ich mal in Urlaub fahren, und dieser kann mehrere Wochen dauern, wären die Admins aufgeschmissen. Daher muss zwingend ein Script her.

"händisch" meint auch nicht "jeden Datensatz von Hand" sondern ein Skript, in dem du eine Prüfung selbst durchführst. Klar soll das ein Skript machen, "händisch" sollte verdeutlichen, dass du eben nicht auf eine fertige SQL Routine setzen kannst.

VG,
Mad

 

[X__]

Mir ist da nichts bekannt und ich vermute auch mal,
dass es schwer sein dürfte da was zu finden, weils ja
doch ein recht spezifisches Problem ist.

Aber da kann ich leider auch nicht mehr helfen als eine Suchmaschine....

Hi,

"händisch" meint auch nicht "jeden Datensatz von Hand" sondern ein Skript, in dem du eine Prüfung selbst durchführst. Klar soll das ein Skript machen, "händisch" sollte verdeutlichen, dass du eben nicht auf eine fertige SQL Routine setzen kannst.

VG,
Mad

Das Integrieren von "Löschen nicht mehr existenter Accounts" per Shellskript, dass nur eine Backupdatei zur Verfügung gestellt bekommt, stelle ich mir recht aufwändig vor - meinst du nicht auch ? Zumal Corin Corvus über keine Programmierkenntnisse verfügt...

 

[Corin Corvus]

Hi,



"händisch" meint auch nicht "jeden Datensatz von Hand" sondern ein Skript, in dem du eine Prüfung selbst durchführst. Klar soll das ein Skript machen, "händisch" sollte verdeutlichen, dass du eben nicht auf eine fertige SQL Routine setzen kannst.

VG,
Mad

Meine Überlegung grad ist, die Vorgänge zu trennen.

Vorgang 1 importiert einfach mit DROP TABLES das Backup in die Datenbank webdb. Soweit so gut. Dies tut er stündlich per cron auf Minute 15.

Auf Minute 30 startet dann Script 2, welches die vorhandenen Einträge mit der Maindb1 vergleicht und bei nicht vorhanden löscht, bei neu hinzufügt. Die Maindb1 wird dann zur Sicherheit noch geclustert auf Maindb2, die produktiv genutzt wird. Maindb2 macht um minute 15 per Cron von sich ein Backup.

Kurz: Stündlicher Cronprozess
X:00 - Backup auf Webseite <- Plugin auf Webseite
X:15 - Import in webdb <- Script
X:15 - Backup der Maindb2 <- Script?!?
X:30 - Vergleich und Abgleich von webdb auf maindb1 <- ?? womit? xD

Wenn dann das Clustering läuft müsste ja genau auf Minute 30 oder je nachdem, wie lang das Script braucht (denke 1-3 sekunden), der Abgleich durch das Clustering direkt erfolgen.

Wenn ich mir das zu umständlich denke, bitte bescheid geben. Ich machs mir gerne schwerer, als es sein muss...

Ergänzung (29. Januar 2016)

Da habe ich bisher sowas gefunden, was sich durchaus in einem Shell script ausführen lässt

Prüfung

SELECT
    eintrag_pics.adid
FROM
    eintrag_pics
    LEFT JOIN eintrag
        ON eintrag_pics.adid = eintrag.adid
WHERE
    ISNUL

Löschen von Einträgen

DELETE
FROM
    eintrag_pics
WHERE
    adid IN (
                SELECT
                    eintrag_pics.adid
                FROM
                    eintrag_pics
                    LEFT JOIN eintrag
                        ON eintrag_pics.adid = eintrag.adid
                WHERE
                    ISNULL(eintrag.adid)
            );

Quelle
https://www.tutorials.de/threads/daten-aus-zwei-mysql-tabellen-vergleichen-und-loeschen.369880/

Ergänzung (29. Januar 2016)

Die Frage ist nur, ob das verlässlich klappt. Und dahingehend bin ich skeptisch.

 

[X__]

Hm, gar nicht mal so schlecht, der Ansatz.

Nur den Abgleich würde ich dann DB-intern machen.

Alle noch nicht existenten eintragen (vorsicht, sql aus der hohlen Hand):

insert ignore into MainDB2_table select * from webdb_table

Alle nicht mehr existierenden löschen:

delete from MainDB2_table where ID is not in (select ID from webdb_table)

Sollte natürlich nur gerufen werden, wenn webdb_table auch gefüllt ist - sonst wirds doof


Edit: Ah ja, hast du ja auch schon gefunden

Edit2: Vom Prinzip sollte das klappen. Was macht dir Sorgen ?

 

[Corin Corvus]

Ich mach mir immer Gedanken xD

Kann ich einen Referenzwert aus der webdb nehmen, der vorhanden sein muss, damit die Aktion überhaupt erst ausgeführt wird? Schließlich ist dort ja auch der Admin und mein User enthalten. Wenn einer der beiden nicht da ist, soll Aktion nicht ausgeführt werden, wäre eine Bedingung, die man integrieren kann. Sofern dann also die webdb nicht gefüllt wäre, würde nichts passieren.

Alternativ weiß ich, dass die webdb ja immer gefüllt ist. Nur beim Import wird die tabelle entfernt und neu angelegt.

 

[X__]

Vielleicht hiermit:

http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html
oder (vielleicht noch einfacher)
http://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html

vielleicht so (ungetestet) :

delete from MainDB2_table 
where ID not in (
             select ID from webdb_table) 
  and   exists (select ID from webdb_table where ID = ADMIN_ID)