Fix for transferring problem MySql databases

Author- Julian Blundell, w34u
Published. Monday 27th Jan 2020, Updated. Monday 27th Jan 2020

The Problem I Encountered

I was trying to transfer a MySql database from one hosting to another and no matter what I tried, various encodings in phpMyAdmin, using command line dumps and imports, the accented characters were coming out with £ and other corrupt elements both in the database admin and in the resulting website. The database versions were identical, 5.6, and there was no obvious differences in the installed character encodings.

The main result of this was having to leave the site on the old hosting till I found a solution.

The Solution

In the new year I started playing with a single table to see if I could at least import that without problems, no soap until I just tried importing the data into an already created table and It worked! No character corruptions!

Next I tried it with the whole database:-

  1. Exported the original database structure without the data and imported that into the new database.
  2. Exported the original database data without the structure definition and imported that into the new database.

That worked as well, I just used the utf-8 as the export and import character set.

Why splitting the structure and data import removed the problems but I suspect that the encoding for the database structure clashed with that for the actual data in some way.

So if you are having problems with moving a MySql database give this a go, do not know if similar things happen with MariaDb but perhaps it might be worth a go with similar problems.