I have an old database (MySQL 5.1.58) that I want to migrate to a new installation (MySQL 8.0.26). When I just use
oldserver ~$ mysqldump mydatabase > file.sql newserver ~$ mysql mydatabase < file.sql
This works fine, except for the character set. I have some records with non-ASCII characters, and I think the old server is on iso-8859-1 (how can I know for sure). The new server is UTF-8, because I have set
[mysqld] character-set-server = utf8
I tried to convert the resulting file before import using
~$ iconv -f iso-8859-1 -t utf-8 file.sql -o file.utf8.sql
But that didn't seem to work (I only get more
À characters). I can hexdump the dumpfile, and I see hex characters
c3 a2 e2 82 ac cb 9c being rendered as
â€˜ on my terminal.
How can I correctly dump this database into a new UTF-8 based server?
I am in full control of the target database server, but (as it is still in use) I do not want to touch the source.
Update I dumped my database with
oldserver ~$ mysqldump --default-character-set=latin1 mydatabase > file.sql
and it now dumps correctly. When inspecting the file, I see valid UTF-8 characters in the dump.
Importing it still fails though.