Home / server / Questions / 1063344
Accepted
BioRod
Asked: 2021-05-13 07:44:41 +0800 CST

Restore of mysql dump looks different on ubuntu 20.04

  • 772

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?

ubuntu mysql ubuntu-14.04

1 Answers

  1. Best Answer

    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.

    • 1