While migrating table I accidentally dropped database before migration. I have roughly 250+GB database when du -sh /data/mysql but now showing only 20G. Binlog and innodb_file_per_table ON at my database setting. Can anybody help to
Use any successfully completed backup before drop database.
restore it to new server (to be safe),
ensure binary logs which are generated after the above point 1 successful backup are available.
restore binlogs before the drop database.
###example / hypothetical scenario just for understanding:
Incident date and time : 9-july-2020 11:00:00 AM
Last successful backup : 2-july-2020 01:00:00 AM
First: you need to ensure after last successful backup (2-July-2020 01:00:00 AM), all the binarylogs are available till 09-July-2020 11:00:00 AM
steps:
copy backup ((2-July-2020 01:00:00 AM) to new server
restore backup successfully
restart new server and login to database and verify error log and critical tables etc (to be extra-cautions)
copy binlogs to new server, binlogs from 2-july-2020 01:00:00 AM to 9-july-2020 11:00:00 AM (ensure any binlog between this timeframe should not be missing, else you will not able able to perform successful recovery)
apply/restore binlog to new database just before drop database happened.