Datensätze exportieren und importieren

[haes]

Ich habe je eine MySQL-DB auf 2 verschiedenen Servern laufen. Ein Server dient als Datenbankserver für eine Anwendung, der zweite als backup-Speicher.

Server 1 soll jetzt immer nur die aktuellen Daten der letzten 4 Wochen speichern damit die Datenbank nicht zu groß wird und die Performance gut bleibt. Server 2 hat eine riesige Festplatte und soll sämtliche alten Daten der letzten Jahre speichern.

Die Idee ist folgende: Ich lasse täglich einen cronjob laufen, der die Daten > 4 Wochen exportiert und anschliessend auf dem anderen Server importiert.

Meine Frage ist wie ich die Daten am geschicktesten exportiere. Als Textdateien (csv) oder im SQL-Format? Was ist besser/schneller/sicherer wenn ich sie danach direkt in die andere Datenbank importieren will.

Mir gehts nur um die Vor- und Nachteile der beiden Formate bei obigem Vorhaben.

Code: Select all

mysqldump -h Server1 db1 tabelle "--where=(datum>4wochen)" > daten.sql &&
mysql -h Server2 db2 < daten.sql &&
mysql -h Server1 db1 -e "DELETE FROM tabelle where (datum > 4wochen);" 

oder

Code: Select all

mysql -h Server1 db1 -e "select *  into outfile 'daten.csv'  FIELDS TERMINATED BY ';' enclosed by '"' lines terminated by 'rn' from tabelle where (datum > 4wochen) ;" &&
mysql -h Server2 db2 -e "LOAD DATA LOCAL INFILE 'daten.txt' INTO TABLE tabelle;" &&
mysql -h Server1 db1 -e "DELETE FROM tabelle where (datum > 4wochen);" 

[haes]

Danke für deine Antwort. Mit Stored Procedures und Trigger habe ich momentan noch keine grosse Erfahrung. Von daher werde ich es trotzdem eher mit Load Data auf meine "blödsinnige" Art versuchen :-)

[haes]

Die Tabellen haben alle ein Datum-Attribut. Wenn ich jetzt täglich immer die Datensätze mit dem Datum von vor 4 Wochen exportiere, importiere und danach lösche müßten doch alle Datensätze genau einmal vorkommen? Vorausgesetzt der Delete-Befehl wird nur ausgeführt, wenn der Import-Befehl erfolgreich war und der Import-Befehl nur wenn der Export-Befehl erfolgreich war.

[haes]

Ok ich glaube jetzt hab ich verstanden was du meinst. Danke!

[isotopp]

Server 1 soll jetzt immer nur die aktuellen Daten der letzten 4 Wochen speichern damit die Datenbank nicht zu groß wird und die Performance gut bleibt. Server 2 hat eine riesige Festplatte und soll sämtliche alten Daten der letzten Jahre speichern.

Wenn Du Daten hast, die als Zeitreihe vorliegen und Du alte Daten löschen möchtest, dann ist es immer am sinnvolltest, die Tabelle als Partition anzulegen. In MySQL bis einschließlich 5.0 kann man das nur manuell tun oder in MyISAM MyISAM_MRG "Merge" Tabellen verwenden.

Code: Select all

CREATE TABLE data_template (
  id SERIAL,
  created DATE NOT NULL,
  -- weitere daten
) ENGINE = MyISAM;
CREATE TABLE IF NOT EXISTS data_200904 LIKE data_template;
INSERT INTO data_200904 ( ... ) VALUES ( ... );

Die template-Tabelle erlaubt es Dir, konsistent und bequem Monatstabellen zu erzeugen. Du könntest bei MyISAM nun eine Merge-Tabelle erzeugen (siehe Manual).

Der Vorteil von partitionierten Zeitreihen ist, daß Du einmal die Daten so schnell durch DROP TABLE statt DELETE FROM löschen kannst.

Ein Gegenbeispiel, bei dem dies zur Zeit nicht passiert, ist der MySQL Enterprise Manager von MySQL selbst. Bei meinem Arbeitgeber setzen wir MEM ein, um etwa 150 Datenbanken mit Statistikdaten zu überwachen und von einigen Servern auch Query Analyse zu betreiben. MEM sammelt so etwa 1.2GB Statistikdaten pro Tag ein und weitere ca. 5GB an Query Samples. Da die Daten in Sammeltabellen ohne Partitioniert nach Zeit gespeichert werden, bedeutet dies, daß jeden Tag 6.2GB an Daten per DELETE FROM gelöscht werden müssen - das ist sehr langsam und vermindert die Qualität und den Packungsgrad der Indexbäume in der Datenbank auch sehr stark.

Würde MEM ein DROP TABLE messdaten_20090417 machen können, wäre dies ein Vielfaches effizienter (und MEM 2.2. wird dies auch so tun).

In Deinem Fall ist ein weiterer Vorteil, daß Du dann einfach Replikation aufsetzen kannst, um die Tabellen von Server 1 nach Server 2 zu replizieren. Das Löschen machst Du mit

Code: Select all

SET SQL_LOG_BIN = 0; -- privilege required
DROP TABLE IF EXISTS data_200904;
SET SQL_LOG_BIN = 1; -- priv req'd

aus einem Cronjob auf jedem Server separat.

In MySQL 5.1 und höher kannst Du außerdem das neue Feature PARTITIONS verwenden, um eine Anzahl von Partitionstabellen zu einer virtuellen Tabelle zusammenzufassen.

[isotopp]

Blöde Frage,
funktioniert das mit der Partitionierung auch zuverlässig und performant?

Ich weiß nicht, ob es für Dich funktioniert - für mich tut es fein.