Not the answer you need?
Register and ask your own question!

Recover Accidentally dropped database schema

JoyexpJoyexp Current User Role Contributor
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

Comments

  • vaibhav_upadhyay40vaibhav_upadhyay40 Contributor Current User Role Patron
    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. 



Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.