Missing mysql, sys, performance_schema

Question

I have Percona Mysql 8.0.19-10 on debian 10.

A year ago I had some issues restoring a backup that required me to mess with tablespaces. I forget what all I had done but eventually I got the database up and working and forgot it.

Fast forward to now, I just did a backup up my whole data-dir using percona's xtrabackup and transferred it to a new server. Copy the backup into my data-dir and attempt to start mysql. Fails with

Tablespace 1, name 'sys/sys_config', file './sys/sys_config.ibd' is missing!

mysqld: Can't create/write to file './performance_schema/cond_instances_2201.sdi' (OS errno 2 - No such file or directory)
mysqld: Can't create file './performance_schema/cond_instances_2201.sdi' (errno: 2 - No such file or directory)

I look at the the data-dir (/var/lib/mysql) and while I notice my main database folder, I don't see a sys or performance_schema folder. Curiously I do see a mysql folder but its full of files called slow_log and general_log.

My first thought is the backup failed so I checked the old server and it has the exact same thing! However, I do have a mysql.ibd on the old server. So I run show databases and it shows all 3 databases (sys, performance_schema, mysql). I try to go to sys/sysconfig and it complains about the table space but I am able to go into mysql database tables and perofrmance_schema tables.

Something is definitely up and I read people suggest doing an upgrade and mysql can self correct missing main tables (mysql_upgrade is deprecated so I must use mysqld and force it). I am scared to run update on my current server so I go back to the new server (the one I attempted to restore from backup)

I rename /var/lib/mysql/mysql to /var/lib/mysql/backup and execute sudo -u mysql mysqld --upgrade=FORCE --skip-grant-tables --skip-networking

and get in the error log

/usr/sbin/mysqld (mysqld 8.0.21-12) starting as process 4000
2020-12-13T23:41:31.040574Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-13T23:41:31.279802Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-13T23:41:31.281181Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'CREATE SCHEMA mysql DEFAULT COLLATE utf8mb4_0900_ai_ci' failed with error code = 1049, error message = 'System schema directory does not exist.'.
2020-12-13T23:41:31.281565Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2020-12-13T23:41:31.281986Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-12-13T23:41:32.579767Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-12)  Percona Server (GPL), Release '12', Revision '7ddfdfe'.

So I am not sure what to do, I would prefer to get my new server up and running instead of messing with my old server because it's currently live and being used.

I have innodb table per file Should I expect to see a mysql folder inside my data-dir that looks to be slow_log and general_log backups? Will the sys,performance_schma,mysql databases have their own folder in the data-dir?

I wish there was a way to tell mysql to disregard the sys,mysql,performance_schema tables and re-create them, I thought that's what the upgrade option would do but it fails

Edit 12/23/2020

I think its problem that only shows up with the newest mysql. The backup was done on Percona Mysql 8.0.19-10 and the failed restore was attempted on Percona Mysql 8.0.21-12. Normally I wouldn't think it was the difference in versions...

However, I just happen to upgrade the percona mysql to 8.0.22-13 on the the one that was previously 8.0.19-10 and the exact same issue occured. I believe the newer mysql version is more picky and fails to load if sys_config.ibd is missing where as 8.0.19 had no issues

Edit 12/24/2020

I uninstalled percona 8.0.21-12 on the target server and installed 8.0.19-10. Restored the backup with no issues (despite missing sys folder, etc). While the problem of missing a sys folder is still an issue it seems to only prevent startup with 8.0.2x

Answer 1

If you see the slow log and general log in /var/lib/mysql/mysql, it means that in the config files for the old server, these logs were specified without directories, and that in the old server, the datadir is actually configured to be /var/lib/mysql/mysql, and not the default /var/lib/mysql. That would explain the appearance of the logs there. Check the old servers .cnf file and make sure the datadir matches before attempting the restore again.

After you figure out where the correct datadir was on the old server, match that in the new server, and do the restore. Be sure to set ownership after restoring: "chown -R mysql:mysql /var/lib/mysql" (or wherever the datadir ended up)

Also, keep in mind that mysql_upgrade or update do not recreate much from scratch. They mostly modify system objects to match the new version, and leave others intact, so if something is outright missing (like an entire system schema) it probably won't work.