Question: How to properly convert MySQL 5.6 backups (tar.gz, SQL dump) for compatibility with MySQL 8.0?

[ArTheSiiK 0]

Hi,

I have database backups from MySQL 5.6 in the form of SQL dumps or archives (e.g., tar.gz) and need to import them into MySQL 8.0. However, I’m encountering various errors during import because MySQL 8.0 has stricter rules (for example, it does not allow default values like 0000-00-00 00:00:00 for TIMESTAMP, has different SQL modes, changes in authentication, and more).

I’m wondering if there is a reliable way to convert or adjust these older backups so they can be imported into MySQL 8.0 without issues.

For example:

How to modify the dump to be compatible with the new rules?

Does it make sense to do an intermediate upgrade to MySQL 5.7 before moving to 8.0?

What tools or commands should be used for export/import to make the process smooth?

What are common errors to watch out for?

Thanks for any advice and shared experience!

[Syreldar 2739]

10 hours ago, ArTheSiiK said:

Hi,

I have database backups from MySQL 5.6 in the form of SQL dumps or archives (e.g., tar.gz) and need to import them into MySQL 8.0. However, I’m encountering various errors during import because MySQL 8.0 has stricter rules (for example, it does not allow default values like 0000-00-00 00:00:00 for TIMESTAMP, has different SQL modes, changes in authentication, and more).

I’m wondering if there is a reliable way to convert or adjust these older backups so they can be imported into MySQL 8.0 without issues.

For example:

How to modify the dump to be compatible with the new rules?

Does it make sense to do an intermediate upgrade to MySQL 5.7 before moving to 8.0?

What tools or commands should be used for export/import to make the process smooth?

What are common errors to watch out for?

Thanks for any advice and shared experience!

Some heads-up and a guide for this:

1. Prefer a logical dump over a physical tarball.

You should know that physical copies of the MySQL 5.6 data directory (tar.gz) aren’t guaranteed to work on 8.0 data‐file formats and InnoDB internals change. Instead:

2. Create a clean SQL dump from 5.6:

mysqldump \
  --single-transaction \
  --routines \
  --events \
  --triggers \
  --set-gtid-purged=OFF \
  --column-statistics=0 \
  --default-character-set=utf8mb4 \
  -u root -p \
  your_database > dump-5.6.sql

--set-gtid-purged=OFF avoids GTID errors.

--column-statistics=0 skips unsupported stats in older clients.

 

3. Sanitize the dump for 8.0 compatibility (if needed):

1. Replace any CHARSET=utf8 with CHARSET=utf8mb4.
2. Remove or revise deprecated SQL (e.g. old YEAR(2) columns, TYPE=InnoDB -> ENGINE=InnoDB).
3. Check for use of sql_mode flags no longer supported (e.g. NO_AUTO_CREATE_USER).

 

4. Load into 8.0:

mysql -u root -p \
  --default-character-set=utf8mb4 \
  your_database < dump-5.6.sql

If you get 'permission denied' on DEFINERs, you can strip or rewrite them with sed beforehand:

sed -E 's/DEFINER=`[^`]+`@`[^`]+`//g' dump-5.6.sql > clean-dump.sql

 

5. Run the upgrade checker and mysql_upgrade:

mysqlsh root@localhost -- util check-for-server-upgrade
mysql_upgrade -uroot -p

This finds any incompatibilities and updates system tables to the 8.0 format.

 

6. Verify and adjust configuration:

In your my.cnf, compare 5.6 vs. 8.0 defaults (look for removed/renamed options), then restart MySQL 8.0 and give it a test.

 

If you only have the tar.gz of the 5.6 datadir and cannot re-dump logically:

1. Restore that tarball into a MySQL 5.6 datadir.
2. Start 5.6 and perform a clean logical dump as above.
3. Then follow steps 3-6.

That sequence (5.6 -> dump -> 8.0) is the safest, bullet-proof path imo.

 

Quote

How to modify the dump to be compatible with the new rules?

Does it make sense to do an intermediate upgrade to MySQL 5.7 before moving to 8.0?

What tools or commands should be used for export/import to make the process smooth?

1. Dump flags/tools

You can also use mysqlpump --exclude-databases=… --defaults-file=… which is multithreaded and by default omits DEFINER clauses.

Always include --set-gtid-purged=OFF, --column-statistics=0 (5.7+ only), --skip-lock-tables or --single-transaction for InnoDB.

 

2. Sanitizing for 8.0:

Zero‐dates: strip or convert 0000-00-00 00:00:00 defaults by adding ALLOWED_HOSTS SQL mode (NO_ZERO_DATE) or run

SET sql_mode='ALLOW_INVALID_DATES';

at top of your dump.

CHARSET: s/CHARSET=utf8 /CHARSET=utf8mb4 /g

DEFINER: sed -E 's/DEFINER=[^]+@[^]+//g'

Deprecated syntax: change TYPE=InnoDB → ENGINE=InnoDB, remove NO_AUTO_CREATE_USER from any sql_mode settings.

 

3. Regarding the intermediate 5.7 upgrade..:

It's not required if you dump logically. Only helpful if you’re doing physical upgrades of data files (never recommended cross‐major).

Running 5.7’s mysql_upgrade can catch some issues earlier, but you’ll hit the same SQL errors at import time in 8.0.

 

4. Import & post-load checks:

mysqlsh root@localhost -- util check-for-server-upgrade
mysql_upgrade -uroot -p

Look for warnings about removed/renamed system tables or unsupported column types, then review MySQL-generated upgrade report.

 

Edited August 8, 2025 by Syreldar

[ArTheSiiK 0]

Hey, thanks a lot for replying on the forum and for the detailed explanation of what to change. You really broke things down well and made it easy to understand. I did have to look up a few things on my own, but about 90% of it worked exactly as you described. Appreciate the help!

[Syreldar 2739]

8 hours ago, ArTheSiiK said:

Hey, thanks a lot for replying on the forum and for the detailed explanation of what to change. You really broke things down well and made it easy to understand. I did have to look up a few things on my own, but about 90% of it worked exactly as you described. Appreciate the help!

I can add stuff for these things in this topic for completion if you can point out what precisely needed further explanation.

Edited August 9, 2025 by Syreldar

[ArTheSiiK 0]

3 hours ago, Syreldar said:

I can add stuff for these things in this topic for completion if you can point out what precisely needed further explanation.

As for making the dump, I knew what to do — the real problem came when it came to replacing characters, dealing with encoding, and stuff like that. I wasn’t sure what tool to use since I’m still new to this, so it turned out to be quite an interesting experience. On top of what you mentioned, I also had an issue where it wouldn’t create the column for the item_proto and event tables, even though the SQL dump had the commands for it (I ended up just creating those tables manually). Besides changing the CHARSET, I also had to change the collation because it was originally set to latin1. And finally, I was trying to figure out where to set the SQL mode — ChatGPT helped me with that.  

[Syreldar 2739]

2 minutes ago, ArTheSiiK said:

As for making the dump, I knew what to do — the real problem came when it came to replacing characters, dealing with encoding, and stuff like that. I wasn’t sure what tool to use since I’m still new to this, so it turned out to be quite an interesting experience. On top of what you mentioned, I also had an issue where it wouldn’t create the column for the item_proto and event tables, even though the SQL dump had the commands for it (I ended up just creating those tables manually). Besides changing the CHARSET, I also had to change the collation because it was originally set to latin1. And finally, I was trying to figure out where to set the SQL mode — ChatGPT helped me with that.  

Import using the original encoding, then convert in-place, If your dump was produced from latin1 tables, don’t find/replace bytes, rather let MySQL recode.

# This imports exactly as encoded
mysql --default-character-set=latin1 -u root -p dbname < dump.sql
-- or add at the very top of dump.sql:
--   SET NAMES latin1;

Then, you convert schema + data to utf8mb4 (8.0 default collation shown. Pick one and stick to it):

ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- For all tables (run the SELECT; copy/paste the generated ALTERs):
SELECT CONCAT('ALTER TABLE `', TABLE_NAME,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND TABLE_TYPE='BASE TABLE';

and then re-dump in utf8mb4:

mysqldump --default-character-set=utf8mb4 \
  --single-transaction --routines --events --triggers \
  -u root -p dbname > clean-utf8mb4.sql

 

Then, you control sql_mode per import.

You may use a relaxed session for the import, then restore strictness after.

mysql -u root -p --init-command="SET SESSION sql_mode='STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, ALLOW_INVALID_DATES';" \
  dbname < clean-utf8mb4.sql

If zero-date defaults still blow up, temporarily import with

sql_mode=''

and then:

Fix bad data (UPDATE … SET ts=NULL WHERE ts='0000-00-00 00:00:00'), adjust DDL (use DEFAULT NULL or DEFAULT CURRENT_TIMESTAMP), and re-enable strict mode on the server.

You may also use it as a persistent setting:

[mysqld]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

 

Then you check charset/collation rewrites (only if you must touch the file)

Prefer DB conversion, (which I've shown you earlier in this message). If you must rewrite the dump:

# Replace schema-level latin1/old collations
perl -0777 -pe "s/CHARSET=latin1(\s+COLLATE=\S+)?/CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci/gi" \
  dump.sql > dump-u8.sql

# Column-level collations
perl -0777 -pe "s/COLLATE\s+latin1_\w+/COLLATE utf8mb4_0900_ai_ci/gi" \
  -i dump-u8.sql

 

Regarding "Table didn’t get columns" error:

Usually the CREATE TABLE failed earlier, so later INSERTs/ALTERs ran against a non-matching structure.

Import with logging to see the first error:

mysql --show-warnings --verbose --force -u root -p dbname < clean-utf8mb4.sql 2>import.err
# Inspect where CREATE TABLE fails
grep -nE "ERROR|WARNING" import.err | head -50

Some of the causes I remember off the top of my mind:

• Invalid defaults (zero dates / invalid TIMESTAMP).
• Dump uses double quotes for identifiers while ANSI_QUOTES is set.
• Unquoted reserved identifiers (e.g., event). Ensure backticks in DDL.
• DEFINER/privileges on routines/views.

Quick fixes for the usual offenders:

# Strip DEFINERs (views/triggers/routines)
sed -E "s/DEFINER=`[^`]+`@`[^`]+`//g" -i clean-utf8mb4.sql

# Ensure backticks around risky table names (example: event)
perl -pe "s/\bCREATE TABLE\s+(event)\b/CREATE TABLE \`\1\`/i" -i clean-utf8mb4.sql

# If your client is old, avoid column stats warning
# (or use a MySQL 8 client to import)

If you share a tiny snippet of the failing CREATE TABLE for item_proto/event and an error line from import.err, I can pinpoint the exact rule that tripped it.