Restore tables in mysql database?

Question

linux

Good afternoon.
Ubuntu 18.04, MySQL 5.7.36

Missing half of mysql database tables. For example, such as:
mysql.gtid_executed
mysql.help_category
mysql.time_zone
And others. There are about 20 in total.

In the /var/lib/mysql/mysql/ directory there are .frm .ibd files with the corresponding table names. But if you do mysqlcheck, then he swears that they are not. If you do show databases, then there are no tables, respectively. Tried all sorts of --auto-repair, does not help.

What else I noticed: if you connect to the server using Navicat and update the list of tables, they appear for a second and immediately disappear. I don't know why, just for information.

What to do, how to fix?
Thanks to.

Answer 1

well as an option...

• make a backup of everything
  
• delete files
  
• create empty tables with the same names
  
• return files
  

not the fact that it will turn out (interfering with the work of the database at the file level is a so-so idea), but what if?

Answer 2

1. Move the table files.
2. Create an empty table by copying CREATE TABLE from another server of the closest possible version.
3. DISCARD TABLESPACE.
4. Replace files with saved ones.
5. IMPORT TABLESPACE.
If any table is not deleted or not created, try to perform the same operations by rebooting the server in disabled security mode.
If it still doesn't work, back up everything that is backed up, then delete the server and reinstall it, then return the backup. The backup of the MySQL database should be carefully reviewed beforehand and everything related to the problematic tables should be cleaned out into separate files. If necessary, carefully restore what was removed, but one request at a time, and carefully considering what consequences this may entail.

Answer 3

1. "In the /var/lib/mysql/mysql/ directory there are .frm .ibd files with corresponding table names"
If these files are not zero-sized, then this is an InnoDB table format and the innodb_file_per_table option must be enabled.
The mysql system database by default does not use InnoDB, but uses MyISAM and it is likely that someone converted them to InnoDB and probably also climbed the settings in my.cnf
The correct approach is to backup everything, and then either reinstall mysql completely and it will create a mysql database again, or transfer the database or tables from another host or even from a virtual machine with the same Ubuntu