Subj : Re: Non in-place upgrade of MySQL 5.7 to 8
To   : hyjinx
From : MeaTLoTioN
Date : Mon Jul 19 2021 07:17:57

On 19 Jul 2021, hyjinx said the following...
 
 hy> Hi All,

Hey hyjinx =)

 hy> Surely, there is a way I can do some scripting or some tool can automate
 hy> themigration? Any ideas?

Have a look at this;
https://dba.stackexchange.com/questions/282215/migrate-mysql-5-7-to-8-0-via-mys
qldump

TLDR;

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


Hope this helps

---
|14Best regards,
|11Ch|03rist|11ia|15n |11a|03ka |11Me|03aTLoT|11io|15N

|07ÄÄ |08[|10eml|08] |15ml@erb.pw |07ÄÄ |08[|10web|08] |15www.erb.pw |07ÄÄÄ¿
|07ÄÄ |08[|09fsx|08]  |1521:1/158 |07ÄÄ |08[|11tqw|08] |151337:1/101 |07ÂÄÄÙ
|07ÄÄ |08[|12rtn|08] |1580:774/81 |07ÄÂ |08[|14fdn|08] |152:250/5 |07ÄÄÄÙ
|07ÄÄ |08[|10ark|08]  |1510:104/2 |07ÄÙ

--- Mystic BBS v1.12 A47 2020/12/04 (Linux/64)
 * Origin: thE qUAntUm wOrmhOlE, rAmsgAtE, uK. bbs.erb.pw (1337:1/101)

.