Mysql – Migrate MySQL 5.7 to 8.0 via thesqldump

MySQLmysql-5.7mysql-8.0mysqldump

I have an Ubuntu server 18.04 LTS with MySQLl 5.7

I have new Ubuntu backup server 20.04 LTS with MySQL 8.0

MySQL 5.7 backup created:

mysqldump --all-databases > "filename" -u root -p

Restore to 8.0 fails on MySQL database due to ISAM tables.

Restoring individual databases, except MySQL, works as expected.

Once I have an 8.0 backup I'll upgrade the 18.04 server to 20.04 with MySQL 8.0

Is it possible to modify the MySQL 5.7 dump to load on 8.0?

Best Answer

Recreate the dump from MySQL 5.7

Personally, I would recreate the dump without mysql schema.

SQL="SET group_concat_max_len = 1024 * 1024;"
SQL="${SQL} SELECT GROUP_CONCAT(schema_name separator ' ')"
SQL="${SQL} FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql','sys')"
DBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u... -p... -B ${DBLIST} > mydbs.sql

Then, you can load mydbs.sql in the MySQL 8.0 instance.

What about the dump you already have ?

First, find out the lines in the dump where each database starts and the number of line in the dump

grep -in "^USE" mydbs.sql
wc -l < mydbs.sql

As an example, I created a dump from a MySQL 5.7 vagrant instance.

root@LAPTOP-57FSQ99U:~# grep -in "^USE" mydbs.sql
24:USE `DMP492`;
122:USE `DMP551`;
190:USE `DMP579`;
228:USE `abeersul`;
262:USE `dbname`;
292:USE `karma`;
300:USE `mysql`;
1212:USE `pch_prod`;
1220:USE `pchcom_prod`;
1286:USE `tryout`;
root@LAPTOP-57FSQ99U:~# wc -l < mydbs.sql
1341
root@LAPTOP-57FSQ99U:~#

Next, create a new dump. Copy everything above and below the mysql schema

In this case, everything before line 300

Then, copy everything from the first DB after mysql schema

In this case, subtract 1212 (first line of the next DB after the mysql schema) from 1341 (the number of lines in the original dump you made) and add 1. Then copy that number of lines from the bottom of the original dump into the new dump:

head -299 mydbs.sql > newdump.sql
(( LINES = 1341 - 1212 + 1 ))
tail -${LINES} mydbs.sql >> newdump.sql

As a demonstration, note how the new dump will not have the mysql schema

root@LAPTOP-57FSQ99U:~# head -299 mydbs.sql > newdump.sql
root@LAPTOP-57FSQ99U:~# (( LINES = 1341 - 1212 + 1 ))
root@LAPTOP-57FSQ99U:~# tail -${LINES} mydbs.sql >> newdump.sql
root@LAPTOP-57FSQ99U:~# grep -in "^USE" newdump.sql
24:USE `DMP492`;
122:USE `DMP551`;
190:USE `DMP579`;
228:USE `abeersul`;
262:USE `dbname`;
292:USE `karma`;
300:USE `pch_prod`;
308:USE `pchcom_prod`;
374:USE `tryout`;
root@LAPTOP-57FSQ99U:~#

Now, just load newdump.sql into MySQL 8.0

Related Question