I'm having a serious problem with restoring data from a mysqlpump export (note: not mysqldump).
Situation: MySQL 8.0.19 running on FreeBSD 12.1 running on ESXi. I have made a clone of that machine. So they are fully identical in OS and MySQL version and settings (except IP of course). Let's call the first machine "source" and the second "target".
I run a full DB backup like this:
mysqlpump --set-gtid-purged=ON -u root -p DBName --result-file=DBName.sql
I copy the resulting DBName.sql file to the target host, and import it into mysql by using the command line client and source command in it.
Some of the tables have now corrupted gibberish data in it (collation of fields:utf8mb4_bin). The structure of the table is fully correct (including the collation of the fields).
But if I export only the specific table with a command like this:
mysqlpump --set-gtid-purged=ON -u root -p DBName TABLEName --result-file=TABLEName.sql
I copy this to the target and import it exactly the same way, everything is correct.
I already spent one full day debugging this, as the datasets involved are massive it's not an easy task.
Anybody has a hint for me what could be the cause of this, how to resolve it or any approach to efficiently debug this?
Thanks!
You can use the parameter --default-character-set-set to specify the exported character set, and then try again.