How to Migrate a WordPress MySQL Database with Special Characters

2 weeks ago at re:publica I wanted to migrate my personal blog from an old server running MySQL 1.5.73 to a new one on 5.5.40 and failed miserably. I tried dumping the database with the mysqldump utility and importing it on the new server with the mysql utility which is usually the fastest way of accomplishing this easy task. Not this time.

After the German special characters (umlauts) were broken after the import on the new server I set the –default-character-set=latin1 flag and tried various other stuff to no avail. Next I tried with phpMyAdmin switching between utf8 and latin1 encoding. Didn’t work. Then I installed MySQLDumper and read the extensive article The umlaut problem – How To Successfully Back Up And Restore MySQL Databases With Special Characters Using MySQLDumper by Daniel Schlichtholz, the developer of MySQLDumper. Guess what? It did not do the trick or maybe I just didn’t get it?

I then had a lengthy chat with our server administrator and tried a couple more things without any success whatsoever. Then I bounced it to another hosting company’s email support. They’re still looking into it. So today I gave it another shot and it looks like I finally found an approach that worked for my particular situation, but it might probably also help you in a similar situation.

I used the mysqldump utility  to dump my WordPress blog database from the old server with the following command

mysqldump -uusername -ppassword wordpress_database --default-character-set=latin1 > dump-latin1.sql

and made sure that the umlauts were looking good in the dump file. I used phpMyAdmin to  dump another WordPress database from the new server in order to compare the conditional execution tokens at the top and bottom of the dump file, as well as the DEFAULT CHARSET Settings at the end of the CREATE TABLE statements.

Conditional Execution Tokens

Dump Old Server

Top

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES latin1 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Bottom

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

I replaced those with the ones from the new server’s dump which were a bit less complex.

Dump New Server

Top

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

Bottom

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Default Charset

At the end of each CREATE TABLE statement my old dump file looked like this

ENGINE=MyISAM AUTO_INCREMENT=1542 DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;

Ignoring the collation I simply replaced this with the following

ENGINE=MyISAM DEFAULT CHARSET=utf8;

And that already did the trick. I imported the modified dump file via phpMyAdmin to the new server and checked my WordPress frontend which finally showed all umlauts and special characters correctly. Now I can finally write another article about re:publica!

(Picture courtesy of Alexander Vasenin – A pod of spinner dolphins in the Red Sea)