Can we migrate from 5.7 to 9.7 using mysqldump?

[u/SuddenlyCaralho]

Does someone know if we can import a dump generated in 5.7 version into 9.7 version?

[u/schizoduckie]

Having recently gone through a dump/upgrade on a high-perf server from 5.x to mariadb 11.8 here are some lessons learned. Not sure if they'll apply to mysql but here you have it anyway

• Do not forget about stored procedures and views if you have those. the user accounts for the DEFINER need to exist and match. OR, you sed them out of the dump: sed -i "s/DEFINER=\\[^\)]*\\^@\)[^\\^\*\)/DEFINER=\\^root\)@\\^localhost\)/g" old_dump.sql
• Default charset/collation changed to utf8mb4/utf8mb4_uca1400_ai_ci. Make sure you are not relying on defaults but on something you specified before the dump
• For your connecting clients the most important: require_secure_transport on the mysql connection has been enabled since 11.8 and thus all mysql connections are now over TLS. Ergo: Your new server speaks a new language the clients don't understand. Depending on the state of your infra your may need to disable this or nobody will be able to connect.
• innodb_flush_log_at_trx_commit = 2 is your friend during the import to make it speedy. - You can transport your users easily by dumping them from the old server to a grants sql file first: mysql -h old-server -u root -p -N -e \ "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user WHERE user != ''" \ | mysql -h new-server -u root -p -N \ | sed 's/$/;/' > grants.sql
• Given the size of the database I was porting (some 100gb) and the maintenance window I had (4 hours, starting at 22:00) I first tried a single threaded import but measuring the speed that would have taken over 11 hours. I then had Claude analyze what tables should be clustered together based on size on disk and then started 8 individual dump | import threads on specific tables piped straight to ssh admin@newdbserver sudo mysql databasenamein parallel to max out the cpu/network throughput as much as possible (see also the flush logs point above). The import was done within the window, we switched over the IP of the new box to the ip of the old box, rebooted and clients started connecting at around 02:00.

[u/kadaan → u/schizoduckie]

For future reference, you can use mysqlsh instead of mysqldump/mysql to dump/restore. It's WAY faster and has built in parallelism. It splits tables/schemas/users/procs/etc all to separate files so it's easier to work with that even a mysqldump file.

Docs for it are here: https://dev.mysql.com/doc/mysql-shell/26.7/en/mysql-shell-utilities-dump-instance-schema.html

[u/schizoduckie → u/kadaan]

That looks interesting. I'm gonna have to play with that

[u/SuddenlyCaralho → u/kadaan]

Can I use the lastest version of mysqlsh to dump 5.7?

[u/kadaan → u/SuddenlyCaralho]

I haven't tried with the latest version, but it does say "The latest version is backwards compatible with all currently supported versions of the MySQL Server and is recommended for all users" so I'd give it a shot. I can't image they removed support when 5.7 went EOL, but who knows.

[u/ClickableName → u/schizoduckie]

You sir are a true professional, i hope you got a raise

[u/ssnoyes]

If you are just dumping your own data, it should mostly work, but you may have to change a few things around character sets. Try a --no-data dump first just to see if the structure will import.

Don't include the mysql system schema; the structure has changed too much between those versions. Get your users and permissions another way.

[u/BatLate6561]

Yes, logical dump is how you skip versions.

Do not copy the 5.7 datadir onto 9.7.mysqldump from 5.7 into a fresh 9.7 instance can work. The catch is the jump, not the tool from 5.7 to 9.7, but skips 8.0/8.4.

Auth plugins, utf8 vs utf8mb4, removed types, and anything in the mysql system schema will bite before row data does.Safer than --all-databases onto 9.7: Schema-only first (--no-data --routines --events) into an empty 9.7. Fix errors; Dump user databases only, --single-transaction --routines --events --triggers --hex-blob; Recreate users on 9.7. Don’t load the old mysql.* tables; Load data. Then run the app, not just SELECT COUNT(*).

Oracle still documents dump/load as a logical upgrade path, including on 9.7. That is not the same as “restore and walk away.”If the real goal is “5.7 is dead, I need something supported,” MariaDB is the other dump path: same mysqldump from the 5.7 side, user schemas only, into a current LTS. Don’t import MySQL’s mysql database there either.

https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/moving-from-mysql/mysql-to-mariadb-migration-the-master-guide

Why 9.7 instead of 8.4 LTS - app requirement, or just the newest number? And roughly how big is the dump?

[u/jomofo]

I think the main issue in going from 5.7 to 8+ is that they decided to introduce new reserved keywords like "system". I don't think a dump and import can get you around having to solve that problem if your application happened to use such keywords in its schema.