MySQL-Schemaänderungen im laufenden Betrieb

© auremar, 123RF

Änderungen maßgeschneidert

Schemaänderungen in MySQL können eine Herausforderung sein. Aber mit den richtigen Tools lässt sich die Aufgabe selbst bei großen Datenbanken effizient lösen.
Mit den Tipps und Workshops im ADMIN-Magazin 03/2013 sichern Administratoren ihre Webserver und Netze gegen Angriffe ab: gegen Abhören sensibler Informationen, ... (mehr)

Ob im Umgang mit dem Smartphone oder auf der Bank – relationale Datenbanken begegnen einem überall. Sie speichern für uns alle Arten von Daten in einem speziellen, wohl definierten Format. Im Gegensatz zu ihren jüngeren NoSQL-Abkömmlingen verwenden sie dabei ein festes Schema und akzeptieren keine ungeordneten Daten. In der Theorie entwickelt ein smarter Programmierer dieses Schema, das dann in dieser Form in die Produktion übernommen wird.

In der Praxis erfährt das Schema allerdings zahlreiche Änderungen, beispielsweise, weil sich einige der ersten Entscheidungen später als Irrtum herausstellen. Daraus resultierende Fehler, die sich zuweilen erst sehr spät zeigen, müssen korrigiert werden. In anderen Fällen ändern sich die Anforderungen im Laufe der Zeit und erzwingen Änderungen, selbst wenn der erste Entwurf vollkommen korrekt war. Derartiges ist meist unvorhersehbar.

Im Ergebnis stehen Admins nicht selten vor der Aufgabe, ein existierendes Datenbankschema ändern zu müssen. Im einfachsten Fall ist das trivial, alle Datenbanken inklusive MySQL halten die nötigen Werkzeuge parat. Mit einem einfachen ALTER-TABLE-Statement lassen sich Spalten hinzufügen oder löschen, Datentypen ändern und so fort. Das funktioniert so lange problemlos, so lange die Datenbank relativ klein ist.

Hinter den Kulissen blockt MySQL alle Aktivitäten, die die zu ändernde Tabelle betreffen. Will oder muss man mehrere Tabellen ändern, erzeugt man so mehrere Locks. Selbst aufeinanderfolgende Änderungen derselben Tabelle können zu mehrfachen Locks führen. So lange das Lock besteht, kann kein User und keine Applikation die gesperrte Tabelle verwenden – die Datenbank ist mehr oder weniger nutzlos (jede Datenbank behandelt die Situation allerdings ein wenig anders).

In manchen Situationen mag man die Locks akzeptieren können, sehr große Tabellen können aber viele Minuten, Stunden, ja sogar Tage brauchen, bis alle Änderungen durchgeführt sind. Dann ist der bisher diskutierte Ansatz nicht praktikabel. Was soll der Admin stattdessen tun?

Online-Schema-Änderungen

Das Hauptproblem mit dem eingebauten ALTER-TABLE-Kommando ist, dass es immer mit einer kompletten Tabelle arbeitet. Weil dabei große und sehr große Datenmengen betroffen sein können, kann das sehr lange dauern. Andererseits tangiert das Ändern einer leeren Tabelle den Betrieb überhaupt nicht. Der Weg besteht deshalb darin, zuerst eine leere Kopie der korrekturbedürftigen Tabelle zu erzeugen. Das leistet in MySQL ein Statement wie

CREATE TABLE xyz LIKE eine-andere-Tabelle;

Hat man diese leere Kopie angelegt, kann man deren Struktur ändern und zum Schluss die Daten herüberkopieren. Dabei ist es typischerweise nötig, die Daten langsam und Stück für Stück zu übernehmen. Versucht man alle auf einmal zu kopieren, resultiert das typischerweise in einer sehr schlechten Performance.

Das kommt daher, dass InnoDB die so genannte Multi Version Concurrency Control (MVCC) unterstützt. MVCC bewirkt, das umfangreiche Transaktionen (wie das Kopieren einer großen Tabelle) als einzelne, konsistente Aktion gehandhabt werden. Wenn so ein Statement 100 000 Zeilen kopieren soll, von denen einige modifiziert wurden, speichert es sowohl das Original als auch die neue Version. Das erzeugt einen Overhead, der oft kritisch wird. Deshalb ist das portionsweise Kopieren häufig schneller.

Das letzte Teil im Puzzle bildet die Aufgabe, dafür zu sorgen, dass die Daten stets aktuell bleiben, auch wenn Anwender beispielsweise die Quelltabelle während des Kopierens ändern. Die einfachste Methode das zu erreichen, stellen Trigger dar. Damit sorgt dann MySQL selbst dafür, dass die Daten up-to-date bleiben. Wenn das alles nach sehr viel Arbeit klingt – nicht verzweifeln: Es gibt gute Tools, die den gesamten Prozess automatisieren.

Das Percona Toolkit

Für den oben beschriebenen Workflow existieren eine Reihe von Tools. Sowohl OpenArk als auch das Percona Toolkit [1] bieten Werkzeuge für Online-Änderungen an Tabellen, die »oak-online-alter-table« beziehungsweise »pt-online-schema-change« heißen. Dieser Artikel behandelt das Werkzeug von Percona, weil es mehr Features bietet, darunter etwa die Unterstützung für Primary Keys, die keine Integer-Werte enthalten, oder das automatische Verlangsamen des Slaves.

Wer mit Ruby on Rails arbeitet, für den ist der Large Hadron Migrator [2] eine passende Lösung. Er arbeitet ähnlich wie die erwähnten Tools, aber auf Basis der in Ruby on Rails eingebauten Migrationsmechanismen.

Bevor demonstriert werden kann, wie »pt-online-schema-change« funktioniert, müssen ein paar Daten her. Das erledigt das Skript in Listing 1. Es braucht übrigens das Gem »progressbar« , das man im Bedarfsfall mit

Listing 1

create_test_data.rb

01 require 'rubygems'
02 require 'active_record'
03 require 'progressbar'
04
05 number_of_sample_records = ENV['number_of_records'] || 10_000
06
07 ActiveRecord::Base.establish_connection(
08   :adapter  => 'mysql',
09   :host     => 'localhost',
10   :username => 'root',
11   :password => 'your_mysql_password_goes_here',
12   :database => 'test')
13
14 class ExampleModel < ActiveRecord::Base; end
15
16
17 if ExampleModel.table_exists?
18
19   ActiveRecord::Schema.drop_table :example_models
20
21 end
22
23 ActiveRecord::Schema.define do
24
25   create_table :example_models do |t|
26
27     t.string :test_string_1
28     t.string :test_string_2
29     t.integer :test_integer
30
31   end
32
33 end
34
35
36 pbar = ProgressBar.new('creating sample records', number_of_sample_records)
37
38 number_of_sample_records.times do |i|
39
40   ExampleModel.create(
41     :test_string_1=>"Test String 1 - ##{i}",
42     :test_string_2=>"Test String 2 - ##{i}",
43     :test_integer=>"Test Integer - ##{i}"
44   )
45
46
47   pbar.inc
48
49 end
gem install progressbar

installiert. Nun kann man das Skript mit

ruby create_test_data.rb

laufen lassen.

Progressbar ist übrigens bei vielen lang laufenden, nicht unterbrechbaren Tasks nützlich. Es wird einfach mit einer Beschreibung und der Anzahl der abzuarbeitenden Einheiten aufgerufen. Nach Abschluss jeder Einheit wird seine Methode »inc« benutzt.

Ähnliche Artikel

comments powered by Disqus

Artikel der Woche

Zertifikatsmanagement mit Certmonger

Zertifikate werden dazu verwendet, um Benutzer, Services und Hardware mit der Hilfe eines signierten Schlüssels zu verifizieren. Hierfür existieren Public-Key-Infrastrukturen (PKI). Aber wie gelangen die Zertifikate eigentlich auf das Ziel-Gerät? Der Open-Source-Tipp in diesem Monat beschreibt, wie Sie für diese Aufgabe das Tool certmonger verwenden können. (mehr)
Einmal pro Woche aktuelle News, kostenlose Artikel und nützliche ADMIN-Tipps.
Ich habe die Datenschutzerklärung gelesen und bin einverstanden.

Linux-Backup

Welche Backup-Lösung setzen Sie für Linux im professionellen Umfeld ein?

  • keine
  • eigene Scripts
  • rsnapshot
  • rdiff-backup
  • Bacula
  • Bareos
  • Borg
  • Duplicity
  • Amanda
  • Burp
  • Clonezilla
  • Acronis
  • Arkeia
  • SEP sesam
  • Veeam
  • Redo Backup
  • Relax-and-Recover
  • andere kommerzielle Software
  • andere Open-Source-Software

Google+

Ausgabe /2017

Microsite