Migrate a MySQL database without special character hell
 

I have two systems at hand. One a Debian box, the other a RHEL5 box.

On the Debian box there is a MySQL server (5.0.32-Debian_7etch1) running which hosts Databases from a customized content management system.

This system has grown over time and the tables are stored with different collations.

Now I want to export and reimport the data on the RHEL5 MysQL server (5.0.45).

And here the special character hell begins. It doesn't matter if I reimport the data as utf8, as latin1 or latin1_swedish_ci. Everytime the special characters are wrong and distorted.

Surprisingly the data that is shipped with the unmodified CMS displays the special chars correctly after installation.

But I have to import the modified version from the old server as there are lots of modifications to import.

Does anyone know how to export and reimport such data?

Old server:
New server:

I've not had to deal with that particular problem, but two things come to mind that you might want to try.

You don't say how you're exporting the data, but are you using mysqldump? That should just dump everything to a file, which SHOULD let you read it right back in with "mysql -u <user id> -D <database name> < <export file name>", such as "mysql -u root -D mydatabase < mydatabase.sql".

If that doesn't work...you could try to low-tech it, by shutting down mysql, and tar'ring up the /var/lib/mysql directory (at least that's where mysql lives on my system), and copying it to the new server, then un-tar it. That should copy the database files, with everything intact.