Character set mismatch in database migration

Question

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 .my.cnf to:

[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.

Answer 1

Specify the default character set on both commands. For flexibility, use utf8mb4, not latin1.

Probably you also changed versions in the process. At some point MySQL changed the default from latin1 to utf8mb4. Were those two commands done on different servers? Or were they done across the wire? I ask because my version comment applies to both mysqldump and mysql.