SQL-Blog

MariaDB, PostgreSQL, MySQL, … Erfahrungsberichte, Meinungen, …

19. November 2013
nach FTH
Keine Kommentare

Dump-Datei splitten

MySQL ist vor allem deswegen so weit verbreitet, weil es gemeinsam mit PHP quasi zur Standardausrüstung jedes Webhosting-Pakets gehört. Die meisten Webhoster bieten den Zugang zur Datenbank über eine Weboberfläche wie phpMyAdmin an. Solche Weboberflächen bieten eine einfache Verwaltungs- und Bearbeitungsmöglichkeit der Datenbank an, die es auch technisch nicht so versierten Usern ermöglicht, die wichtigsten Datenbankoperationen auszuführen. Doch leider gibt es selten nur Vorteile.

Je nach Web-Server – Konfiguration können nicht beliebig große Dateien hochgeladen werden, oder die Ausführung einer Anfrage darf eine gewisse Zeit nicht überschreiten. Das kann vor allem dann zu Problemen führen, wenn man gezwungen ist große Datenmengen in die Datenbank zu importieren.

Die Lösung für dieses Problem ist es oft, die zu importierende Datei auf mehrere kleinere Dateien aufzuteilen. Für Windows gibt es eigene Tools, die man sich dafür installieren kann, eine Beschreibung dafür kann man u.a. hier finden.

Unter Linux, oder auch unter Mac OS X ist das auch mit Boardmitteln zu bewerkstelligen. Das Zauberwort dafür heißt split.

split

Split ist ein Programm zum Aufteilen großer Dateien in beliebig viele kleine. Es kann sowohl binär-Dateien, als auch Text-Dateien splitten. Letzteres machen wir uns für das Aufteilen von SQL Dump – Files zu nutzen. Über die genaue Verwendung von split gibt wie immer auch die Hilfefunktion Auskunft:

split --help

Hier musste ich feststellen, dass das Split von Linux etwas mehr Parameter und Funktionen bietet als jenes von OS X, was jedoch für unsere Zwecke völlig egal ist.

Welche Parameter sind für uns relevant?

-p pattern

Das reicht auch für den ersten Versuch schon.

Ich habe zum Testen eine kleine Datenbank mit nur 3 Tabellen exportiert, die für die Erklärung der Funktionen aber ausreichend ist. Beim Export habe ich sowohl die Struktur, als auch die Daten in eine Datei (exp.sql) schreiben lassen, so dass das zu teilende File wie folgt aussieht:


CREATE TABLE IF NOT EXISTS `tbl_a` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...);

INSERT INTO `tbl_a` (...) VALUES (...),
(...),
(...) ...

CREATE TABLE IF NOT EXISTS `tbl_b` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...);

INSERT INTO `tbl_b` (...) VALUES (...),
(...),
(...) ...

CREATE TABLE IF NOT EXISTS `tbl_c` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...);

INSERT INTO `tbl_c` (...) VALUES (...),
(...),
(...) ...

Will ich jetzt zum Beispiel für jede Tabelle eine eigene Datei haben, so brauche ich nur zu schreiben:

split -p CREATE exp.sql tab_

Dieser Befehl bedeutet, dass split in der Datei exp.sql nach dem Pattern ‚CREATE‘ sucht und genau ab diesem Wort eine neue Datei anlegt, die den Inhalt bis zum nächsten ‚CREATE‘ beinhaltet. Standardmäßig benennt split die neuen Dateien unter OS X einfach aa, ab, ac, usw. Man kann allerdings dieser Nummerierung ein Präfix voranstellen, was ich mit tab_ getan habe, so dass meine Dateien nun tab_aa, tab_ab und tab_ac heißen.

Nun haben wir für jede Tabelle der Datenbank eine eigene Datei. Ist diese noch immer zu groß, so könnten wir einen weiteren Parameter von Split zum Einsatz bringen:

-l line_count

Das heißt wir können uns z.B. die Datei tab_ac in z.B. lauter Dateien zerlegen lassen, die genau 100 Zeilen haben. Das erreicht man mit:

split -l 100 tab_ac 100tab_ac_

Jetzt habe ich lauter 100 Zeilen lange Dateien, mit den Namen 100tab_ac_aa, 100tab_ac_ab, …

Was will man mehr? 😉

Fazit

Unter Unix – basierten Betriebssystemen, kann man ganz einfach SQL-Dumps manipulieren, die ja nichts anderes als reine Text-Dateien sind.

 

15. November 2013
nach FTH
Keine Kommentare

Datenbankstruktur unter PostgreSQL exportieren

Nachdem ich euch im letzten Beitrag gezeigt habe, wie man unter MySQL bzw. MariaDB einen Teil seiner Daten exportiert, so möchte ich euch heute erklären, wie ihr unter PostgreSQL seine Datenbankstruktur einfach in ein Textfile exportieren kann.

Das Zauberwort, bzw. Zauberprogramm dafür heißt pg_dump.

pg_dump Parameter

pg_dump kann natürlich viel viel mehr, als wie ich in der Überschrift geschrieben habe eine Datenbankstruktur in ein Textfile zu schreiben. Ihr könnt mit pg_dump alles exportieren, was man aus einer Datenbank exportieren möchte. Um einen Überblick über die Funktionen zu bekommen lohnt sich wie immer zunächst ein Blick in die Hilfe-Funktion des Programms, die ihr einfach mit

pg_dump --help

aufrufen könnt.

Die wichtigen Parameter für das Exportieren einer Datenbankstruktur, so wie ich es heute vor hatte sind dann eben die folgenden:

-f, --file
-s, --schema-only

Und natürlich die Grundanweisung, wie das Programm zu verwenden ist, nämlich:

pg_dump [OPTION]... [DBNAME]

Export

Setzt man jetzt die einzellnen Parameter zu einem Befehl zusammen, ergibt sich für das Exportiern einer Datenbankstruktur unter PostgreSQL folgender Befehl:

pg_dump -s -f /tmp/exp.sql Datenbankname

Ich rufe also das Programm pg_dump mit den Parametern -s für den Strukturexport und -f für die Angabe des Exportfiles Рin meinem Fall exp.sql im tmp РVerzeichnis Рauf und gebe am Schluß noch den Namen der zu exportierenden Datenbank an. Und schon habe ich ein fix und fertiges Textfile mit dem Namen exp.sql, das meine gesamte Datenbankstruktur enthält.

 

30. Oktober 2013
nach FTH
Keine Kommentare

Wie exportiert man einen Teil seiner Daten?

Wie versprochen gibts hier auf sqlblog.fth-it.net wieder Regelmäßiger Tipps und Tricks für den täglichen Umgang mit Datenbanken.

Gestern war es wieder einmal so weit und ich durfte Daten aus einer recht großen MySQL Tabelle auslagern. Das heißt es ging darum, Daten die vor 2012 gespeichert wurden aus der Tabelle zu entfernen und in ein Textfile zwecks Archivierung zu sichern.

Der für den Export notwendige Befehl lautet:

SELECT INTO OUTFILE

Das tolle an diesem Befehl im Vergleich zu einem mysql_dump ist der, dass man jede beliebige MySQL – Abfrage exportieren kann und eben nicht nur wie bei einem Dump ganze Tabellen oder Datenbanken. Wenn man jetzt also wie in meinem Fall alle Daten vor 2012 in eine Datei schreiben will, geht man wie folgt vor:

SELECT * INTO OUTFILE '/tmp/daten_vor_2012.exp'

FIELDS TERMINATED BY '\t'

ENCLOSED BY '\"'

FROM my_table

WHERE datum < '2012-01-01';

Wobei natürlich eine Spalte ‚datum‘ in der Tabelle my_table vorhanden sein muss. Zusätzlich habe ich hier noch angegeben, dass die einzelnen Datenbankfelder durch Tabulatoren getrennt werden sollen. Dies geschieht mit der Anweisung: FIELDS TERMINATED BY ‚\t‘
Natürlich kann man hier an Stelle von Tabulatoren auch andere Trennzeichen angeben.

Mit ENCLOSED BY ‚\“‚ erreichte ich noch, dass alle Textfelder schön von “ umgeben sind.

Dass für jeden Datensatz eine neue Zeile im File angelegt wird muss nicht extra angegeben werden. Und dass sich ‚/tmp/daten_vor_2012.exp‘ auf den Dateinamen des Files in das geschrieben wird bezieht, sollte auch selbsterklärend sein, und muss natürlich für nicht Unix-User entsprechend angepasst werden.

Hat man den Befehl eingegeben heißt es nur noch warten, bis alle Zeilen geschrieben sind. Als Kontrolle machte ich vor dem Löschen der Zeilen in der Tabelle noch ein


SELECT COUNT(*) FROM my_table WHERE datum < '2012-01-01';

und vergleichte das Ergebnis mit dem Ergebnis von

wc /tmp/daten_vor_2012.exp

um zu sehen, ob wohl wirklich alle Zeilen in die Datei geschrieben wurden. Das Löschen der nicht mehr in der Tabelle benötigten Zeilen ist dann mit einem einfachen


DELETE FROM my_table WHERE datum < '2012-01-01';

zu bewerkstelligen. Man sollte dabei aber nicht ausser Acht lassen, dass auch das Löschen von Datensätzen einiges an Zeit beansprucht.