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)
/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
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
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