Lange habe ich die Umstellung von mySQL 4.0 auf 5.0 vor mir hergeschoben. Aber da immer mehr andere Software von 5.0 abhängt, habe ich letztes Wochenende diesen relativ aufwändigen Schritt getan.
Der grobe Weg und die wesentlichen Probleme habe ich hier mal zusammengefasst ... 1. Zunächst könnte man meinen, dass man für die Umstellung einfach mySQL 5.0 installieren und dabei das /var/lib/mysql-Verzeichnis beibehalten kann. Nach einem "mysql_fix_privilege_tables" und den fälligen REPAIRs auf allen Tabellen (siehe phpmyadmin!) sieht u. U. zunächst auch alles sehr gut aus - aber freuen darf man sich erst, wenn man mySQL-Server, Webserver, etc. wenigstens einmal neugestartet hat und revdep-rebuild nichts mehr bzgl. der beteiligten Programme meldet.
Denn es könnte ja sein, dass z. B. das PHP eine shared-library für mySQL benutzt, die inzwischen gar nicht mehr existiert und bis zu einem Reload des Apaches funktioniert alles wie vor der mySQL-Umstellung. Da sich der Apache ab und an automatisch "reloaded", wird irgendwann erstmal PHP nicht mehr funktionieren und nach einem neukompilieren von PHP hat man dann seine Zeichensatz-Probleme.
Fazit: Einige Anwendungen überleben diesen Schritt und machen keine Probleme. Aber viele - insbesondere ältere - Anwendungen erfordern ein Konvertieren per "iconv -f latin1 -t utf8 dump.sql > dump-utf8.sql" und dann einen import per "mysql -u user -p passwd DBname < dump-utf8.sql". Da dies nicht immer so reibungslos funktioniert, sollte man jede Datenbank einzeln abarbeiten und insbesondere niemals mit dem vi die Dateien verändern (siehe unten).
2. vi oder vim eignet sich nicht zum editieren von mySQL-Dumps. Wenn der Zeichensatz im mySQL-Dump nicht überall eindeutig ist (beispielsweise liegt eine Tabelle bereits in UTF-8 und manch andere in Latin1 vor), so einigt sich vi auf einen Zeichensatz und erzeugt ein riesiges Chaos. Da gibts bestimmt eine vi-Option dagegen (nein, set encoding hilft nicht), aber einfacher ist es, die Finger vom vi zu lassen.
3. Auch wenn es nicht "best practice" ist, so gibt es doch jede Menge Anwendungen, die in auto_increment-Spalten der Datenbank unbedingt einen Wert "0" wollen. Das macht auch keine Probleme beim sqldump, aber beim Einspielen wird 0 automatisch auf einen auto-Wert gesetzt und wenn dies z. B. 5 ist, dann hat man zum einen das Problem, dass 5 nun nicht mehr vergeben werden kann ("duplicate entries") und zum anderen es keinen Eintrag 0 mehr gibt.
Um dieses Problem zu umgehen, gibt es die mySQL-Option NO_AUTO_VALUE_ON_ZERO. Schreibt man diese Zeile in den sql-Export, so läuft das Importieren ohne Probleme. (Vorsicht! Nach meinem Punkt 2. darf man nicht einfach mit dem vi die Datei bearbeiten. Einfache Lösung: Die folgende Zeile in eine Datei neu.sql schreiben und dann mit "cat dump.sql >> neu.sql" den Dump anhängen.
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';
Hat man nur sehr wenige Tabellen auf auto_increment, so kann man auch
a. den 0-Wert auf einen unbenutzten temporären Wert setzen
b. Daten importieren
c. Den Wert manuell wieder auf 0 setzen (oder die Applikation anpassen, dass 0 nicht mehr benötigt wird)
4. Gibt man beim Importieren ein CHARSET=utf8 pro Tabelle an, so kann es zur Fehlermeldung "Specified key was too long; max key length is 1000 bytes" kommen. Meiner Erfahrung nach (mit einem vorher aus mySQL 4 exportierten Dump) kann man CHARSET einfach komplett weglassen und bekommt diese Fehlermeldung nicht.
5. Man wundert sich manchmal, was in Datenbanken so alles definiert ist. z. B. darf KEY(ID, ID) zwar existieren und expotiert werden, aber importieren ist nicht mehr OK. Man sollte auf jeden Fall damit rechnen, dass nicht alles so einfach in die Datenbank wieder hineingeht, wie es rausgekommen ist.
Der Ansatz per
mysqldump --opt --password=xxxxx --allow-keywords --flush-logs --hex-blob --max_allowed_packet=16M --quote-names --all-databases > /root/$(date +%F)_full_backup.sql
alle Datenbanken zugleich zu exportieren ist dafür allerdings ziemlich unhandlich. Zu empfehlen ist, nur einzelne DBs zu exportieren und auch einzeln wieder einzuspielen. An vielen Stellen ist beim zurückspielen manuell etwas zu ändern und das geht umso leichter, je kleiner die einzelnen Dumps sind.
Hat man nur einen großen Dump, so kann man sich behelfen mit "grep -n 'Current Data' full_backup.sql", welches einem die Zeilen anzeigt, in denen die einzelnen Dumps anfangen. Per "head -n ZeileDerNächstenDB full_backup.sql | tail -n ZeileDerNächstenDB-ErsteZeileDerGewünschtenDB > geeklog.sql" filtert man genau die DB heraus, die man gerade importieren will.
6. Versucht man doch einfach die Daten aus /var/lib/mysql zu übernehmen, so muss man u. U. feststellen, dass ISAM-Datenbanken von mySQL 5.0 nicht mehr unterstützt werden. Man MUSS diese Datenbanken VORHER exportieren (oder nachträglich aus dem Backup) und manuell wieder importieren ("mysql -u abc --password=test abc < /tmp/abc.sql"). Wenigstens erstellt mySQL beim Import bei TYPE=ISAM automatisch myISAM. Fehlermeldungen in diesem Zusammenhang:
#1017 - Can't find file: 'produkte' (errno: 2)
#6 - Error on delete of './abc/abc.MYI' (Errcode: 2)
7. Viele (Web-)Anwendungen sind auf latin1 konfiguriert und zeigen nach der Umstellungen schöne Multi-Byte-Zeichen (insbesondere das A mit der Tilde) an. Man sollte sich vorher im Klaren sein, dass u. U. einige Anwendungen manuell anzupassen sind. Bei manchen Weblogs muss man z. B. statt der bisherigen ISO-8859-1-Sprachdatei (z. B. german.php bei Geeklog oder serendipity_lang_de.inc.php bei Serendipity) die UTF-8-Sprachdateien aktivieren. Auch gibt es u. U. irgendwelche CHARSET-Optionen in der Programm-Konfiguration, die man auf UTF-8 umstellen muss (z. B. _CONF['default_charset'] = 'iso-8859-15'; im Geeklog).
Einige Anwendungen haben überhaupt keine Charset-Definition im Content-Type:
$ nc einserver.net 80
HEAD /login.php HTTP/1.1
Host: einserver.de
HTTP/1.1 200 OK
Date: Mon, 03 Sep 2007 14:16:25 GMT
Server: Apache
Content-Type: text/html
und hier muss man entweder die Anwendung ändern oder kann im Apache per AddDefaultCharset einen Wert fest eintragen. Letzteres ist allerdings etwas starr und schlecht zu pflegen. Besser wäre es z. B. die Zeile <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> in die entsprechenden Webseiten einzutragen.
Zeigt Netcat statt "Content-Type: text/html" weiterhin "Content-Type: text/html; charset=ISO-8859-1" an, so ist die Anwendung noch nicht auf UTF-8 umgestellt (oder man hat ein altes AddDefaultCharset in der Apache-Config). Erst wenn man ein "Content-Type: text/html; charset=UTF-8" sieht ist der Stress vorbei ;-)
8. Meldet sich mySQL beim importieren und behauptet, dass die "max_allowed_packet" Größe überschritten wurde, so findet man im /etc/mysql/my.cnf diese Option und kann sie relativ problemlos hochsetzen.
Ach ja ... ist alles nicht so einfach ;-)
