Restore (reinstall?) MySQL databases "mysql" and "sys"

[csdude55]

I'm running MariaDB 10.3.29, which seems to be virtually identical in code to MySQL. I say "virtually" as a precaution, but so far I haven't found any coding differences at all.

A brief history...
I had some sort of hard drive corruption on May 15 that led to the InnoDB tables in MySQL being corrupted. The server provider was able to fix (?) the drive with e2fsck, but have more or less blown me off and left me on my own with the MySQL trauma.

They did modify my.cnf to use:

innodb_force_recovery=4

which got it back up running, but apparently caused me to lose about half of my data (total deletion) and then left all remaining InnoDB tables as read-only.

I "fixed" the hosted accounts by exporting their databases to .SQL, editing the file in Notepad++ to replace ENGINE=InnoDB to Engine=MyISAM, creating a new database in the account using cPanel and assigning the same user(s) to it, importing the backed up .SQL to it, deleting the old one manually via FTP (I got an error when trying to drop it via cPanel or PMA), and then modifying Wordpress (wp-config.php) to point to the new database. I couldn't just rename it to the old database name, because MySQL kept recognizing the tables as InnoDB (presumably from cache).

I got the last accounts modified last night at around 3:30am, but I still couldn't get MySQL to restart without innodb_force_recovery! After I tried, the error log had thousands of these:

2021-05-21 3:27:05 0 [ERROR] InnoDB: Page [page id: space=0, page number=313] log sequence number 690626571434 is in the future! Current system log sequence number 690626204880.
2021-05-21 3:27:05 0 [ERROR] InnoDB: Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. Please refer to https://mariadb.com/kb/en/library/innodb-recovery-modes/ for information about forcing recovery.

which led me here:

B/1.2 InnoDB Time-Traveling & Log Sequence Number Errors
[support.cpanel.net...]

Which is great information, but doesn't help me find where the problem actually IS, or how to fix it.

And that leads me to the purpose for this thread.


The purpose of the thread, restoring or reinstalling "mysql" and "sys" databases

After I restarted at 3:30am, the error log looked like it restarted MySQL every 10 minutes or so because I had the same groups of errors over and over. Everything was working, though, so I went to bed at 4am. When I got up today, I see that the last errors were at 4:30am... no clue why it stopped at that point, but everything is running and seems OK.

But that last series of errors points to the "sys" and "mysql" databases:

# These first lines were at 3:55am
2021-05-21 3:55:26 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`servers` in the cache. Attempting to load the tablespace with space id 31
2021-05-21 3:55:26 0 [Note] Server socket created on IP: '::'.
2021-05-21 3:55:26 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_leap_second` in the cache. Attempting to load the tablespace with space id 12
2021-05-21 3:55:26 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_name` in the cache. Attempting to load the tablespace with space id 8
2021-05-21 3:55:26 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone` in the cache. Attempting to load the tablespace with space id 9
2021-05-21 3:55:26 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_transition_type` in the cache. Attempting to load the tablespace with space id 11
2021-05-21 3:55:26 0 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`time_zone_transition` in the cache. Attempting to load the tablespace with space id 10
2021-05-21 3:55:26 0 [Note] InnoDB: Buffer pool(s) load completed at 210521 3:55:26
2021-05-21 3:55:26 2 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`gtid_slave_pos` in the cache. Attempting to load the tablespace with space id 27
2021-05-21 3:55:26 0 [Note] Reading of all Master_info entries succeeded
2021-05-21 3:55:26 0 [Note] Added new Master_info '' to hash table
2021-05-21 3:55:26 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '10.3.29-MariaDB-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server

# this error was by itself at 4:14
2021-05-21 4:14:31 5996 [ERROR] InnoDB: Failed to find tablespace for table `sys`.`sys_config` in the cache. Attempting to load the tablespace with space id 21

# then these errors were at 4:30
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`server_cost` in the cache. Attempting to load the tablespace with space id 19
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`innodb_table_stats` in the cache. Attempting to load the tablespace with space id 30
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`transaction_registry` in the cache. Attempting to load the tablespace with space id 32
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_master_info` in the cache. Attempting to load the tablespace with space id 16
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`innodb_index_stats` in the cache. Attempting to load the tablespace with space id 29
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`help_keyword` in the cache. Attempting to load the tablespace with space id 7
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_relay_log_info` in the cache. Attempting to load the tablespace with space id 15
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`gtid_executed` in the cache. Attempting to load the tablespace with space id 18
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`help_relation` in the cache. Attempting to load the tablespace with space id 6
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`help_topic` in the cache. Attempting to load the tablespace with space id 4
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`slave_worker_info` in the cache. Attempting to load the tablespace with space id 17
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`engine_cost` in the cache. Attempting to load the tablespace with space id 20
2021-05-21 4:30:08 9215 [ERROR] InnoDB: Failed to find tablespace for table `mysql`.`help_category` in the cache. Attempting to load the tablespace with space id 5

Based on that, I'm guessing that the corruption is in the main MySQL tables instead of account databases?

How do I restore (reinstall?) these? Obviously I can't do them the same way that I did the hosted clients; I could write them to new databases with new names and convert the tables to MyISAM, but I don't know how to tell MySQL to look for the new database names.