MySQL Backup & Restore

MySQL-Datenbank von latin1 nach utf8 konvertieren

Es ist oft empfehlenswert, utf8 als Zeichensatz in Datenbanken zu verwenden. Beispielsweise forciert TYPO3 dies ab Version 4.5. Ausgangslage (mydb ist der Datenbankname, mytable eine beliebige Tabelle darin):

  • Die Tabellen haben den Datentyp latin1_... (z.B. latin1_swedish). Man kann dies kontrollieren mit use mydb;show table status; in der mysql-Konsole.
  • Die Attribute weichen ebenfalls nicht von latin1 ab bzw. ist hier kein anderes charset gesetzt. Man kann dies kontrollieren mit show full columns from 2bis10_typo3.tt_content; in der Konsole. In der Spalte Collation sollte überall latin1_swedish_ci stehen.
  • Die Tabellen sind momentan korrekt befüllt. Dies kann man kontrollieren, indem man in der Konsole SET NAMES latin1;SET CHARACTER SET latin1;SET SESSION character_set_server=latin1;select * from mytable limit 100; schreibt. Alle Zeichen müssen korrekt dargestellt werden.

Ziel:

  • Tabellen in utf8 umwandeln
  • MySQL-Server auf utf8 umstellen (optional)
  • Tabellen in InnoDB umwandeln (optional, man kann die sed-Zeile weiter unten einfach weglassen)

Getestet mit debian lenny (5.0), mysql 5.0.51 Zunächst sollten alle Daten gesichert werden: 

mysqldump --all-databases > all_dbs.sql

Jetzt kann der Server umgestellt werden auf utf8 (optional):

Editieren in /etc/mysql/my.cnf: 

[mysqld]
# * Charset
default-character-set = utf8
collation_server=utf8_unicode_ci
character_set_server=utf8

MySQL-Neustart: 

/etc/init.d/mysql restart

Daten zurücksichern: 

mysqldump < all_dbs.sql

Hiermit wäre der Server auf utf8 umgestellt. Für jede Datenbank ist folgendes zu tun: 

echo "ALTER DATABASE mydb CHARACTER SET utf8 COLLATE utf8_general_ci;" | mysql
mysqldump --default-character-set=latin1 --databases mydb > a.sql
cp a.sql b.sql
sed -i 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/g' b.sql
sed -i 's/CHARACTER SET latin1/CHARACTER SET utf8/g' b.sql
sed -i 's/ENGINE=MyISAM/ENGINE=InnoDb/g' b.sql
<*>
grep -v character_set_client <b.sql > c.sql
mysql --default-character-set=utf8 < c.sql

Letztlich sollte noch das client encoding umgestellt werden in der /etc/mysql/my.cnf: 

[client]
default-character-set=utf8
[mysqldump]
default-character-set=utf8
[mysql]
default-character-set=utf8

Wenn man nun die mysql-Konsole ohne Parameter startet, sollten alle Daten korrekt angezeigt werden. Alternativ kann man das Encoding auch mit SQL angeben (z.B. in TYPO3 in der Variable setDBinit): 

SET NAMES utf8;
SET CHARACTER SET utf8;
SET SESSION character_set_server=utf8;

Troubleshooting: Bei Import-Fehlern sollte man die Ausgabe der Statements aktivieren. 

mysql -v -v --default-character-set=utf8 < c.sql

Fehler äußern sich beispielsweise dergestalt: 

CREATE TABLE `sys_registry` (
  `uid` int(11) unsigned NOT NULL auto_increment,
  `entry_namespace` text NOT NULL,
  `entry_key` text NOT NULL,
  `entry_value` blob,
  PRIMARY KEY  (`uid`),
  UNIQUE KEY `entry_identifier` (`entry_namespace`(256),`entry_key`(127))
) ENGINE=InnoDb DEFAULT CHARSET=utf8
--------------

ERROR 1071 (42000) at line 1333: Specified key was too long; max key length is 767 bytes
Bye

Hier sollte man die Größe der Keys verkleinern, indem man bei <*> eine weitere sed-Zeile verwendet: 

sed -i 's/`entry_namespace`(256)/`entry_namespace`(127)/g' b.sql

Hinweis: Die maximale Schlüssellänge bei latin1 ist

  • bei InnoDB 767.
  • bei MyIsam 1000.

Zu beachten ist, dass ein UTF8-Zeichen 3 normale Zeichen verbrauchen kann. Bei UTF8 muss man also die maximale Schlüssellänge immer durch 3 teilen.