On 19 Jul 2021, hyjinx said the following...
Hi All,
Hey hyjinx =)
Surely, there is a way I can do some scripting or some tool can automate 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)