Mysql Question posted in
The official MySQL documentation can be found here.

mysql dump produces strange binary blobs that cause problems on import

i am running backups of my mysql database with this command:

mysqldump -u admin -p`cat /etc/psa/.psa.shadow` --hex-blob --skip-opt --single-transaction --add-drop-table --create-options --quick --extended-insert --max_allowed_packet=32M --default-character-set=utf8 --disable-keys databasename -r database.sql

In an inconsistent way this dump produces in some cases things like that:

Screenshot of the dumpfile in texteditor

or copied via clipboard:
21-3′,1,’2𩐂 𩐂  珂 p参 XªP ªP @ ªP 1 08:42:08′),(1

the problem is that if I want to restore the file it creates errors.

It is inconsistent as it happens in every database field independently of its datatype or encoding.

does anybody has an idea why this happens?

thanks for your help

I have searched a lot and added for example –hex-blob to the dump statement, but it did not help to dump the contents correctly in utf8.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    first of all many thanks for your answer. I followed all steps and changed all tables with a little script to the values you indicated. Anyway even if I change it manually in MySQL Workbench it shows utf8mb4_0900_ai_ci as Collation. is this correct?

    But finally the result is still the same. It produces these strange binary blobs and running the restore script in order to import it in my local mysql server it gives this result:

    ERROR at line 48: ASCII '' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '' is expected.

    Do you have any other tipp to solve this?

    Greetings


  2. 0

    To resolve the issue with inconsistent encoding in your mysqldump, follow these steps precisely:

    1. Use --default-character-set=utf8mb4 in Your Command

    Update your mysqldump command to explicitly set the character set to utf8mb4, ensuring compatibility with all Unicode characters. Use the following updated command:

    mysqldump -u admin -pcat /etc/psa/.psa.shadow 
--hex-blob --skip-opt --single-transaction --add-drop-table 
--create-options --quick --extended-insert 
--max_allowed_packet=64M --default-character-set=utf8mb4 
--disable-keys databasename -r database.sql

    2. Check the Database and Table Encoding

    Run the following SQL queries to verify that your database and tables use the correct encoding:

    SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW CREATE DATABASE databasename;
SHOW CREATE TABLE tablename;
    • Ensure the character set for the database and tables is utf8mb4.
    • If it’s not, alter the database or table encoding: 
      ALTER DATABASE databasename CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

    3. Increase max_allowed_packet

    Ensure both the MySQL server and client have sufficient packet size to handle large data. Edit the my.cnf file:

    [mysqld]
max_allowed_packet=64M

[client]
max_allowed_packet=64M

    Restart MySQL:

    sudo systemctl restart mysql

    4. Validate and Restore

    Once the dump file is created, validate it before restoring:

    1. Inspect the dump file: Open it in a text editor or use a hex editor to check for corrupted or invalid characters.
    2. Restore with the correct character set: 
      mysql -u admin -pcat --default-character-set=utf8mb4 databasename < database.sql

    5. Verify Data Integrity

    After restoration, verify the restored data matches the source:

    • Run queries on critical tables to ensure the data is intact.
    • Check for any unusual characters in text fields.

    Why This Works

    • --default-character-set=utf8mb4: Ensures consistent handling of Unicode data during the dump process.
    • Increasing max_allowed_packet: Prevents data truncation for large rows.
    • Validating encodings: Ensures all database and table encodings are compatible with the data being dumped.

    Let me know if you encounter any issues during these steps!

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search