MySQL – Upgrading from MySQL 5.7 on FreeBSD to MariaDB or MySQL 8

First, I would try an index on (n_file, n_surn, n_type, n_id, n_surname) for this query. You may have to change the GROUP BY clause for the index to be used effectively by all versions of the optimizer:

GROUP BY n_file, n_surn COLLATE 'utf8_unicode_ci'

Second, why the derived table, the group by and the join? I think the query is equivalent to the much simpler:

SELECT SQL_CACHE 
    n2.n_surn, n1.n_surname, n1.n_id
FROM wt_name n1 
WHERE n_file = 4 
  AND n_surn NOT IN ('', '@N.N.')
  AND n_type <> '_MARNM' ;

I was finally successful restoring the data. My problem was, that I tried to restore the data with a docker-compose, which somehow didn't work.

So I took a different approach

1. Run a docker image of mysql:8 and mounted the "corrupt" folder to it

docker run -d -e MYSQL_ROOT_PASSWORD=root -v /path/to/corrupt/folder:/var/lib/mysql --name mrestore mysql:8

The mysql-server started without any problems and I was able to run mysql with

mysql -u root -p.

But when I run the command mysql>show databases; It gave me an error like:

The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

2. Fix user does not exist error

A quick google search led me to the answer of someone having the same problem: https://stackoverflow.com/questions/49992868/mysql-errorthe-user-specified-as-a-definer-mysql-infoschemalocalhost-doe

Which is:

mysql -u root -p
mysql> SET GLOBAL innodb_fast_shutdown = 1;
mysql_upgrade -u root -p

3. Dump databse

Finally I was able to start mysql and dump the database that I thought I've lost

mysqldump -u root -p databasename > databasename.sql