Restore of mysql dump looks different on ubuntu 20.04
Every day I backup my wiki database with the following command on a Linux 14.04 on mysql 5.5.62
/usr/bin/mysqldump -u root my_wiki > wiki.`date +"%m-%d-%Y_%T"`.sql
When I restore the dump to the same machine, the restore is successful. But when I restore the dump to a new ubuntu 20.04 box, I see the following in this table.
mysql> select user_name from user;
+------------------------------------------+
| user_name |
+------------------------------------------+
| 0x412E706172646F |
| 0x4162656C2E6564756172646F |
| 0x4164616D2E676F746368 |
| 0x416C6578616E6465722E6275646E6576696368 |
where it should look like
mysql> select user_name from user;
--------------
select user_name from user
--------------
+---------------------+
| user_name |
+---------------------+
| A.bardo |
| Abel.smith |
| Adam.Mo |
| Alexander.bud |
So, what am I not getting here?
Solution 1:
What you get there are Hexadecimal Literals. You could select a human readable representation of the stored values by prepending a Character Set Introducer like this:
SELECT _utf8 0x412E706172646F;
which would yield
+------------------------+
| _utf8 0x412E706172646F |
+------------------------+
| A.pardo |
+------------------------+
For your query above you could respectively use
SELECT _utf8 user_name from user;
So far as to your question. ;-)
To further examine this behavior I would recommend to check the character set of your connection to the database with
SHOW VARIABLES LIKE '%character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
Then check the character set of the databases on both systems with
SHOW CREATE DATABASE `your_db_name`;
They should be the same.