MySQL: Tabellentyp für viele Tabellen auf einmal ändern

Wer mit MySQL-Datenbankarbeiten arbeitet kommt gerne einmal in die Situation den Tabellentypen von Tabellen ändern zu müssen. So kann es im Rahmen der Performanceoptimierung sinnvoll sein einzelne Tabellen in das InnoDB-Format umzuwandeln. Das ist für eine einzelne Tabelle im Client mit einer Zeile Code oder per phpMyAdmin mit wenigen Mausklicks schnell erledigt. Doch was, wenn wir alle Tabellen eines Typs A in einen Typ B konvertieren möchten?

Das kann z.B. sinnvoll sein, wenn man eine Installation des freien Content Management Systems Drupal von Version 6 auf 7 upgradet, da das Standard-Tabellenformat für MySQL hier von MyISAM auf InnoDB geändert wurde. Allerdings konvertiert das Update-Skript vorhandene Tabellen nicht. Das führt dazu, dass neue Tabellen im InnoDB-Format verwaltet werden, bereits vorhandene aber ihr MyISAM-Format behalten. Dutzende Tabellen von Hand zu konvertieren ist ziemlich nervig. Zum Glück geht es auch einfacher.

Folgendes SQL-Statement erzeugt uns die notwendigen SQL-Kommandos um alle Tabellen in der Datenbank „amel“ auf InnoDB umzustellen, die derzeit noch MyISAM sind:

SELECT CONCAT('ALTER TABLE `',table_schema,'`.`',
table_name,'` engine=MyISAM;')
FROM information_schema.tables
WHERE engine = 'InnoDB' AND table_schema = 'amel';

.

Heraus kommt dann z.B. sowas (Screenshot aus phpMyAdmin):

Am Fuß der Seite findet man bei phpMyAdmin u.a. noch den Link „Druckansicht (vollständige Textfelder)“. Über den geht es auf eine Seite von der aus man die SQL-Statements bequem per Copy & Paste übernehmen kann. Von der Kommandozeile aus kann man sie sich natürlich auch schnell im MySQL-Client ausgeben und in eine Datei umleiten lassen, die man dann wiederum dem Client zur Ausführung „zu fressen“ geben kann.

Lässt man übrigens den Parameter table_scheme aus der Abfrage ganz heraus, dann bekommt man die ALTER-Statements für alle Tabellen, die im betreffenden Format sind, auf die der Datenbank-Benutzer Zugriff hat. Als root-Benutzer kann man so auch Tabellen in allen Datenbanken des Servers auf einen Schlag konvertieren.

Kommentar (1)

  1. Hi Alex, danke für die Hilfe:

    vielleicht könntest du noch folgendes Anpassen:
    Deine Bedingung bzgl. der Engine ist vertauscht! Außerdem hab ich noch an den Befehl angehängt, wie man die Befehle in eine Datei schreibt:

    SELECT CONCAT(‚ALTER TABLE `‘,table_schema,’`.`‘, table_name,’` engine=InnoDB;‘) FROM information_schema.tables WHERE engine = ‚MyISAM‘ AND table_schema = ‚otrs‘ INTO OUTFILE ‚/tmp/Tabellen.txt‘;

    Um die Befehle dann aus der Textdatei einzulesen genügt wenn man eingeloggt ist folgendes:
    source ‚/tmp/Tabelle.txt‘

    Wäre schön, wenn du das noch ergänzen könntest.

    Liebe Grüße
    Nils

Kommentare sind geschlossen.