Importieren von Daten in eine MySQL DB-Instance

Für den Import von Daten in eine DB-Instance von RDS for MySQL stehen verschiedene Techniken zur Verfügung. Die beste Herangehensweise ist von der Quelle der Daten, der Menge der Daten sowie der Frage abhängig, ob der Import einmalig oder kontinuierlich erfolgt. Wenn Sie eine Anwendung mit den Daten migrieren, müssen Sie zudem die Ausfallzeit berücksichtigen, die Sie in Kauf zu nehmen bereit sind.

Übersicht

Die folgende Tabelle enthält Techniken zum Importieren von Daten in eine DB-Instance von RDS for MySQL.

Quelle	Datenmenge	Einmalig oder kontinuierlich	Ausfallzeit der Anwendung	Technik	Weitere Informationen
Lokal oder auf Amazon EC2 vorhandene MySQL-Datenbank	Alle	Einmalig	Etwas	Erstellen Sie ein Backup der Datenbank vor Ort, speichern Sie es auf Amazon S3 und stellen Sie die Sicherungsdatei anschließend auf einer neuen Amazon RDS-DB-Instance wieder her, auf der MySQL ausgeführt wird.	Wiederherstellen eines Backups in einer MySQL-DB-Instance
Alle vorhandenen Datenbanken	Alle	Einmalig oder kontinuierlich	Minimal	Wird verwendet AWS Database Migration Service , um die Datenbank mit minimaler Ausfallzeit zu migrieren und bei vielen Datenbank-DB-Engines die fortlaufende Replikation fortzusetzen.	Was ist AWS Database Migration Service und Verwenden einer MySQL-kompatiblen Datenbank als Ziel für AWS DMS im AWS Database Migration Service -Benutzerhandbuch
Bestehende MySQL-DB-Instance	Alle	Einmalig oder kontinuierlich	Minimal	Erstellen Sie eine Read Replica für die laufende Replikation. Stufen Sie die Read Replica für die einmalige Erstellung einer neuen DB-Instance hoch.	Arbeiten mit DB-Instance-Lesereplikaten
Vorhandene MariaDB- oder MariaDB-Datenbank	Small	Einmalig	Etwas	Kopieren Sie die Daten mit einem Befehlszeilen-Dienstprogramm direkt in die MySQL-DB-Instance.	Importieren von Daten aus einer externen MariaDB- oder MySQL-Datenbank in eine DB-Instance von RDS für MariaDB oder RDS für MySQL
Nicht in einer vorhandenen Datenbank gespeicherte Daten	Medium	Einmalig	Etwas	Erstellen Sie Flatfiles und importieren Sie sie mithilfe von LOAD DATA LOCAL INFILE MySQL-Anweisungen.	Importieren von Daten aus einer beliebigen Quelle zu einer MariaDB- oder MySQL-DB-Instance
Lokal oder auf Amazon EC2 vorhandene MariaDB- oder MySQL-Datenbank	Any	Kontinuierlich	Minimal	Konfigurieren Sie die Replikation mit einer vorhandenen MariaDB- oder MySQL-Datenbank als Replikationsquelle.	Konfigurieren der Replikation der Binärprotokolldateiposition mit einer externen Quell-Instance Importieren von Daten in eine Amazon-RDS-MariaDB- oder MySQL-Datenbank mit reduzierter Ausfallzeit

Anmerkung

Die Systemdatenbank 'mysql' enthält Authentifizierungs- und Autorisierungsinformationen, die für die Anmeldung bei der DB-Instance und für den Zugriff auf die Daten erforderlich sind. Das Verwerfen, Verändern, Umbenennen oder Verkürzen von Tabellen, Daten oder anderen Inhalten der 'mysql'-Datenbank in der DB-Instance kann zu Fehlern führen und den Zugriff auf die DB-Instance und die Daten verhindern. In diesem Fall können Sie die DB-Instance mithilfe des AWS CLI restore-db-instance-from-db-snapshot Befehls aus einem Snapshot wiederherstellen. Sie können die DB-Instance mit dem AWS CLI restore-db-instance-to-point-in-time Befehl wiederherstellen.

Überlegungen zum Importieren von Daten

Nachstehend finden Sie zusätzliche technische Informationen zum Laden von Daten in MySQL. Diese Informationen sind für fortgeschrittene Benutzer vorgesehen, die bereits mit der MySQL-Server-Architektur vertraut sind.

Binärprotokoll

Datenladevorgänge erzeugen Leistungseinschränkungen und erfordern zusätzlichen freien Speicher (bis zum Vierfachen), wenn die binäre Protokollierung aktiviert ist, im Vergleich zum Laden derselben Daten, während die binäre Protokollierung deaktiviert ist. Der Schwergrad der Leistungseinschränkung und der erforderliche freie Speicherplatz stehen in direkter Proportion zu der Größe der Transaktionen, die für die Datenladevorgänge verwendet werden.

Transaktionsgröße

Die Transaktionsgröße spielt eine wichtige Rolle bei den MySQL-Datenladevorgängen. Sie hat einen wesentlichen Einfluss auf den Ressourcenverbrauch, die Festplattenspeichernutzung, den Fortschritt des Vorgangs, die Wiederherstellungszeit und das Eingabeformat (flache Dateien oder SQL). In diesem Abschnitt wird beschrieben, wie sich die Transaktionsgröße auf die binäre Protokollierung auswirkt, und verdeutlicht, welche Vorteile das Deaktivieren der binären Protokollierung bei umfangreichen Datenladevorgängen mit sich bringen kann. Wie vorher bereits erwähnt, wird die binäre Protokollierung bei der Einstellung des automatischen Aufbewahrungszeitraums für Backups in Amazon RDS aktiviert und deaktiviert. Nicht-Null-Werte aktivieren die binäre Protokollierung und Null deaktiviert diese. Wir beschreiben auch die Auswirkung von großen Transaktionen auf InnoDB und, warum es so wichtig ist, die Transaktionsgröße klein zu halten.

Kleine Transaktionen

Bei kleinen Transaktionen sorgt die binäre Protokollierung für eine Verdopplung der Schreibvorgänge auf der Festplatte, die für das Laden der Daten erforderlich sind. Dieser Effekt kann die Leistung für andere Datenbanksitzungen signifikant herabsetzen und die zum Laden der Daten erforderliche Zeit erhöhen. Wie stark die Leistungseinbuße ist, hängt teilweise von der Datenübertragungsrate beim Hochladen, anderen Datenbankaktivitäten während des Hochladens und der Kapazität der Amazon RDS-DB-Instance ab.

Die binären Protokolle benötigen zudem fast soviel Festplattenspeicher wie die Datenladevorgänge, bis sie gesichert und entfernt werden. Glücklicherweise minimiert Amazon RDS diese Sicherungsvorgänge und entfernt Binärprotokolle in regelmäßigen Abständen.

Große Transaktionen

Große Transaktionen verursachen eine dreifache Einschränkung von IOPS und einen ebenso hohen Festplattenspeicherverbrauch mit aktivierter Binärprotokollierung. Dies geschieht aufgrund einer "Flutung" der Festplatte, des Verbrauchs von Festplattenspeicher und einhergehenden zusätzlichen I/O für jeden Schreibvorgang durch den Cache für die Binärprotokollierung. Der Cache kann nicht in das Binärprotokoll geschrieben werden, bis die Transaktion übertragen oder zurückgegeben wurde. Daher wird der Festplattenspeicher proportional zu den Datenladevorgängen verbraucht. Wenn eine Transaktion übertragen wird, muss der Cache in das Binärprotokoll kopiert werden, wodurch eine dritte Kopie der Daten auf die Festplatte geschrieben wird.

Aus diesem Grund muss mindestens das Dreifache des freien Speicherplatzes für die Datenladevorgänge vorhanden sein, als wenn die Binärprotokollierung deaktiviert ist. 10 GiB Daten, die im Rahmen einer einzelnen Transaktion geladen werden, belegen während des Ladens mindesten 30 GiB Datenträgerspeicher. 10 GiB werden für die Tabelle, 10 GiB für den Binärprotokoll-Cache und weitere 10 GiB für das Binärprotokoll selbst benötigt. Die Cache-Datei bleibt auf der Festplatte, bis die Sitzung, von der sie erstellt wurde, beendet ist, oder die Sitzung füllt ihren Binärprotokoll-Cache erneut während einer anderen Transaktion. Das Binärprotokoll muss bis zur Sicherung auf der Festplatte bleiben. Daher kann es einige Zeit dauern, bevor die zusätzlichen 20 GiB wieder freigegeben werden.

Falls die Daten mithilfe von LOAD DATA LOCAL INFILE geladen wurden, wird nochmals eine Kopie erstellt, wenn die Datenbank aus einem vor dem Laden erstellten Backup wiederhergestellt werden muss. Während der Wiederherstellung extrahiert MySQL die Daten aus dem Binärprotokoll in eine Flat-File. MySQL führt dann LOAD DATA LOCAL INFILE wie in der ursprünglichen Transaktion aus. Dieses Mal ist die Eingabedatei jedoch für den Datenbankserver lokal. Ausgehend vom vorstehenden Beispiel schlägt die Wiederherstellung fehl, wenn nicht mindestens 40 GiB freier Speicherplatz verfügbar ist.

Deaktivieren der Binärprotokollierung

Wann immer es möglich ist, sollten Sie die Binärprotokollierung bei großen Datenladevorgängen deaktivieren, um den Ressourcenaufwand und zusätzliche Speicherplatzerfordernisse zu vermeiden. In Amazon RDS ist das Deaktivieren der Binärprotokollierung so einfach wie das Einstellen des Aufbewahrungszeitraums für Backups. Der Wert muss einfach auf Null gesetzt werden. Wenn Sie dies tun, empfehlen wir das Erstellen eines DB-Snapshots der Datenbank-Instance unmittelbar vor dem Laden. Das ermöglicht bei Bedarf eine schnelle und problemlose Rückgängigmachung der im Rahmen des Ladens vorgenommenen Änderungen.

Setzen Sie nach dem Ladevorgang den Aufbewahrungszeitraum für Backups wieder auf einen angemessenen (Nicht-Null-)Wert.

Sie können den Aufbewahrungszeitraum für Backups nicht auf Null setzen, wenn die DB-Instance eine Quell-DB-Instance für Lesereplikate ist.

InnoDB

In diesem Abschnitt wird erläutert, warum es sich bei der Verwendung von InnoDB lohnt, Transaktionsgrößen klein zu halten.

Rückgängig

InnoDB generiert Reversionen, um Unterstützung für Funktionen, wie beispielsweise Rollback und MVCC zu bieten. Die Rückgängigmachungen werden im InnoDB-Systemtabellenraum (üblicherweise ibdata1) gespeichert und werden aufbewahrt, bis sie vom Bereinigungs-Thread entfernt werden. Der Bereinigungs-Thread kann nicht über die Rückgängigmachung der letzten aktiven Transaktion hinausgehen. Daher ist er effektiv blockiert, bis die Transaktion übertragen wurde oder ein Rollback abgeschlossen hat. Wenn die Datenbank andere Transaktionen während des Ladevorgangs verarbeitet, sammelt sich ihre Rückgängigmachung auch im System-Tabellenraum an und kann nicht entfernt werden, sogar wenn sie übertragen wurde und keine andere Transaktion die Rückgängigmachung für MVCC benötigt. In diesem Fall werden alle Transaktionen (einschließlich schreibgeschützter Transaktionen) verlangsamt, die auf eine der durch eine Transaktion (nicht nur die Ladetransaktion) geänderten Zeilen zugreifen. Ursache der Verlangsamung ist die Verarbeitung des Rückgängig-Protokolls, das möglicherweise gelöscht werden kann, wenn es sich nicht auf die Ladetransaktion mit langer Laufzeit bezieht.

Das Rückgängig-Protokoll wird im Systemtabellenraum gespeichert und die Größe des Systemtabellenraums wird nie verringert. Große Datenladetransaktionen können dazu führen, dass der Systemtabellenraum sehr groß wird und viel Datenträgerspeicher beansprucht, der nicht zurückgewonnen werden kann, ohne die Datenbank ganz neu zu erstellen.

Rollback

InnoDB ist für Übertragungen optimiert. Das Zurücksetzen einzelner Verarbeitungsschritte (Rollback) einer großen Transaktion kann ausgesprochen viel Zeit in Anspruch nehmen. In einigen Fällen kann es schneller sein, eine point-in-time Wiederherstellung durchzuführen oder einen DB-Snapshot wiederherzustellen.

Format der Eingabedaten

MySQL akzeptiert eingehende Daten in einem der zwei Formate: flache Dateien und SQL. In diesem Abschnitt werden einige Hauptvorteile und -nachteile jedes Formats beleuchtet.

Flache Dateien

Das Laden von flachen Dateien mithilfe von LOAD DATA LOCAL INFILE kann die schnellste und kostengünstigste Methode für das Laden von Daten sein, solange die Transaktionen relativ klein gehalten werden. Verglichen mit dem Laden derselben Datenmenge mit SQL erfordern flache Dateien üblicherweise weniger Netzwerkverkehr, senken Übertragungskosten und laden aufgrund der reduzierten Auslastung der Datenbank viel schneller.

Eine große Transaktion

LOAD DATA LOCAL INFILE lädt die komplette flache Datei als eine Transaktion. Das ist nicht unbedingt schlecht. Wenn die Größe der einzelnen Dateien aber klein gehalten werden kann, hat dies viele Vorteilen:

• Fortgesetzte Leistungsfähigkeit: Das Nachverfolgen der Dateien, die geladen wurden, ist einfach. Wenn während des Ladevorgangs ein Problem auftritt, können Sie ohne großen Aufwand dort fortfahren, wo Sie aufgehört haben. Einige Daten müssen eventuell erneut an Amazon RDS übertragen werden. Bei kleinen Dateien ist die Menge der erneut zu übertragenden Daten allerdings minimal.

• Paralleles Laden der Daten: Wenn Sie die nötigen IOPs und die erforderliche Netzwerkbandbreite für das Laden einer einzelnen Datei haben, kann paralleles Laden Zeit sparen.

• Drosseln der Übertragungsrate beim Laden: Wirken sich Datenladevorgänge auf andere Prozesse aus? Drosseln Sie den Ladevorgang, indem Sie das Intervall zwischen den Dateien erhöhen.

Achtung

Die Vorteile von LOAD DATA LOCAL INFILE gehen schnell verloren, wenn die Transaktionsgröße steigt. Wenn die Aufteilung einer größeren Datenmenge in mehrere kleine Datenmengen nicht möglich ist, kann SQL die bessere Wahl sein.

SQL

SQL hat einen Hauptvorteil gegenüber flachen Dateien: Damit ist es einfach, Transaktionen klein zu halten. Jedoch kann ein Ladevorgang mit SQL deutlich länger dauern als mit flachen Dateien und es könnte schwer sein, zu bestimmen, wo der Ladevorgang nach einem Fehler fortgesetzt werden soll. So können beispielsweise mysqldump-Dateien nicht neu gestartet werden. Wenn während des Ladens einer mysqldump-Datei ein Fehler auftritt, muss die Datei geändert oder ersetzt werden, bevor der Ladevorgang fortgesetzt werden kann. Alternativ kann eine Wiederherstellung auf einen Zeitpunkt vor dem Ladevorgang durchgeführt werden und die Datei kann erneut wiedergegeben werden, sobald der Fehler behoben wurde.

Erstellen von Kontrollpunkten mithilfe von Amazon RDS-Snapshots

Ein Datenladevorgang, der mehrere Stunden oder sogar Tage dauert, ohne dass die Binärprotokollierung aktiviert wurde, ist keine attraktive Lösung, außer wenn sich temporär Kontrollpunkte erstellen lassen. Hierbei kommt die Amazon RDS-DB-Snapshot-Funktion sehr gelegen. Ein DB-Snapshot erstellt eine point-in-time konsistente Kopie Ihrer Datenbank-Instance, mit der Sie die Datenbank nach einem Absturz oder einem anderen Missgeschick wieder auf den aktuellen Stand bringen können.

Machen Sie einfach einen DB-Snapshot, um einen solchen Kontrollpunkt zu erstellen. Alle DB-Snapshots für vorherige Kontrollpunkte können entfernt werden, ohne das dies eine Auswirkung auf die Wiederherstellungszeit hat.

Snapshots werden auch schnell erstellt, sodass das Setzen von Kontrollpunkten keine beträchtliche Auswirkung auf die Ladezeit hat.

Reduzieren der Ladezeit

Hier sind einige Tipps zum Reduzieren von Ladezeiten:

• Erstellen Sie alle sekundären Indizes vor dem Ladevorgang. Das ist ungewöhnlich für alle, die sich mit anderen Datenbanken auskennen. Das Hinzufügen oder Ändern eines sekundären Indexes veranlasst MySQL, eine neue Tabelle mit Indexänderungen zu erstellen, Daten aus der bestehenden Tabelle in eine neue zu kopieren und die ursprüngliche Tabelle zu verwerfen.

• Laden von Daten in PK-Reihenfolge. Dies ist besonders bei InnoDB-Tabellen hilfreich, bei denen die Ladezeit um 75 – 80 % reduziert und die Dateigröße halbiert werden kann.

• Deaktivieren Sie auswärtige Schlüsselbeschränkungen foreign_key_checks=0. Für flache Dateien, die mit LOAD DATA LOCAL INFILE geladen werden, ist dies in vielen Fällen erforderlich. Das Deaktivieren von FK-Prüfungen bringt für jeden Datenladevorgang signifikante Leistungssteigerungen. Stellen Sie einfach sicher, die Beschränkungen zu aktivieren und die Daten nach dem Ladevorgang zu überprüfen.

• Führen Sie ein paralleles Laden durch, außer Sie befinden sich nahe an einem Ressourcenlimit. Verwenden Sie partitionierte Tabellen, falls angemessen.

• Verwenden Sie beim Laden mit SQL mehrwertige Einsätze, um die Auslastung beim Ausführen von Anweisungen zu minimieren. Bei der Verwendung von mysqldump wird dies automatisch gemacht.

• Reduzieren Sie InnoDB-Protokoll-IO innodb_flush_log_at_trx_commit=0

• Wenn Sie Daten in eine DB-Instance laden, in der keine Lesereplikate vorhanden sind, stellen Sie den Parameter "sync_binlog" während des Ladens auf 0 ein. Nach dem Laden der Daten können Sie den Parameter wieder auf 1 einstellen.

• Laden Sie die Daten, bevor die DB-Instance in eine Multi-AZ-Bereitstellung konvertiert wird. Wenn die DB-Instance jedoch bereits eine Multi-AZ-Bereitstellung verwendet, wird das Wechseln zu einer Single-AZ-Bereitstellung zum Laden der Daten nicht empfohlen, da dies nur geringe Vorteile bietet.

Anmerkung

Durch die Verwendung von innodb_flush_log_at_trx_commit=0 wird InnoDB veranlasst, bestehende Protokolle jede Sekunde zu bereinigen, anstatt sie zu übertragen. Das bringt einen entscheidenden Geschwindigkeitsvorteil, kann aber auch zu Datenverlust oder Ausfällen führen. Verwenden Sie es mit Bedacht.

Themen

• Wiederherstellen eines Backups in einer MySQL-DB-Instance
• Importieren von Daten aus einer externen MariaDB- oder MySQL-Datenbank in eine DB-Instance von RDS für MariaDB oder RDS für MySQL
• Importieren von Daten in eine Amazon-RDS-MariaDB- oder MySQL-Datenbank mit reduzierter Ausfallzeit
• Importieren von Daten aus einer beliebigen Quelle zu einer MariaDB- oder MySQL-DB-Instance