ERROR 1812 (HY000): Tablespace is missing for table `test2`.`test`.

By Mistake one of my developer delete the data folder “TEST2” from the MYSQL Database Server. It having MySQL datafiles. On connection with TEST2 database. I tired to fetch result and getting the following error:
Error

mysql> select * from test;
ERROR 1812 (HY000): Tablespace is missing for table `test2`.`test`.

On Checking, we found some one delete the TEST2 folder of MYSQL database.

Solution
We must have backup for restore, We have taken on Sunday, deletion of TEST2 happen on Monday.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -u root -p --all-databases --single-transaction --master-data --flush-logs > D:\backupSunday.sql

Note:
–Flush-logs change the log sequence before start for incomplete recovery.
–Single-transaction means take consistent backup
–master-data Help to note the log file name shown below:
CHANGE MASTER TO MASTER_LOG_FILE='IXC1-LP48ZJ622-bin.000008', MASTER_LOG_POS=155;

Recover Steps for TEST2 database

1. Drop the test2 database by login as root user in MySQL.

Drop database TEST2;

2. Create empty new database.

Create database test2;

3. Restore the TEST2 database from the scripts.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql -u root -p test2 < D:\backupsunday.sql
Enter password: *****

4. Restore the backup from sunday to monday by logging information.
Note: Check created date on windows machine.

mysql> SHOW BINARY LOGS;

+-----------------------------+-----------+-----------+
| Log_name                    | File_size | Encrypted |
+-----------------------------+-----------+-----------+
| IXC1-MYSQLSERVER-bin.000001 |       178 | No        |
| IXC1-MYSQLSERVER-bin.000002 |      2209 | No        |
| IXC1-MYSQLSERVER-bin.000003 |     15635 | No        |
| IXC1-MYSQLSERVER-bin.000004 |       211 | No        |
| IXC1-MYSQLSERVER-bin.000005 |       178 | No        |
+---------------------------+-----------+-------------+
9 rows in set (0.11 sec)

5. Restore the logfiles for incomplete recovery.

C:\Program Files\MySQL\MySQL Server 8.0\bin> mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\IXC1-LP48ZJ622-bin.000005" | mysql -u root -p
Enter password: *****

6. Verify the database TEST2.