Resolving MySQL Character Encoding Issues When Restoring Databases

Software Development
, , , ,
1

Resolving MySQL Character Encoding Issues When Restoring Databases
Resolving MySQL Character Encoding Issues When Restoring Databases1200×630 194 KB

I recently backed up a MySQL database from my old host (the server is now down) and tried to restore it on a new host. But all special characters—like French accented letters (é, è, ê)—got replaced with weird symbols like ‘A?~’ or question marks. Even the .sql file itself shows corrupted characters. The original charset was latin1_swedish_ci if I recall correctly.

I know this is a classic character encoding issue, but I’m stuck. The backup was made via cPanel’s phpMyAdmin export, and I tried restoring through phpMyAdmin on the new host. Copy-pasting the SQL content into the SQL query box didn’t work either.

Has anyone dealt with this? Any tips to get the data back intact? Thanks!

Note: The original database is no longer accessible, so I only have this corrupt backup.

2

Topic Summary: I recently backed up a MySQL database from my old host (the server is now down) and tried to restore it on a new host. But all special characters—like French accented letters (é, è, ê)—got replaced with weird symbols like ‘A?~’ or question marks.

:movie_camera: Video Tutorial:

:books: Official Documentation & Reference Links:

---
title: MySQL Encoding Recovery Process
---
flowchart TD
    A[Start] --> B[Identify encoding issue in restored data]
    B --> C{Check original charset and collation}
    C -->|Known| D[Use mysqldump with --default-character-set]
    C -->|Unknown| E[Detect charset using SELECT CHARSET]
    E --> F[Determine matching collation]
    D --> G[Drop and recreate database with correct charset]
    F --> G
    G --> H[Restore dump with specified charset]
    H --> I{Verify data integrity}
    I -->|Issues remain| J[Convert tables with ALTER TABLE CONVERT TO CHARACTER SET]
    I -->|OK| K[Test application compatibility]
    J --> K
    K --> L[End]
3

This sounds like a classic mismatch between the source and target character sets. A few things to try:

  1. Check the backup file header – If you open the .sql file in a text editor (like VS Code or Notepad++), the first lines often contain SET NAMES or CHARSET statements. For example:

    SET NAMES 'latin1';

    Change latin1 to utf8mb4 (or utf8 if your MySQL version doesn’t support utf8mb4).

  2. Use phpMyAdmin’s import feature – Do NOT copy-paste the SQL into the query window. Use the “Import” tab and upload the file directly. This preserves binary content and avoids encoding issues during copy.

  3. Convert the file’s encoding – Use a command-line tool like iconv to convert the file from latin1 to utf-8:

    iconv -f latin1 -t utf-8 database_dump.sql > database_dump_utf8.sql

    Then import the converted file.

  4. Set the session charset before importing – In phpMyAdmin, you can execute SET NAMES 'utf8mb4'; before running the import to tell MySQL to expect UTF-8 data.

If you still see issues, check the collation of each table. You may need to alter tables to use utf8mb4_unicode_ci after restoring.

Hope that helps!

4

I had a similar issue a while back. The key turned out to be the charset of the database connection when exporting. I used mysqldump with the --default-character-set=latin1 flag and then converted the resulting file to UTF-8 before importing.

If you’re stuck with a backup that already has corrupted characters, try this approach:

  1. Create a new database with utf8mb4 charset.
  2. Import the backup into that new database (it will still look wrong).
  3. Then run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; on each table. This actually re-encodes the data and often fixes the issue.

Also, always use the Import feature in phpMyAdmin rather than pasting raw SQL – as jlhaslip mentioned, that makes a huge difference.

One more thing: make sure your phpMyAdmin session and MySQL connection are both set to UTF-8. You can check the “Server connection collation” setting in phpMyAdmin and set it to utf8_unicode_ci before importing.

Good luck!