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

Best to handle an issue that accidentally deleted/truncated a production db table

paolooopaolooo EntrantCurrent User Role Beginner
Hi,

I recently truncated a database table. I thought, it was my local db but it turns out it was not.
Luckily it was not our production db too. It was one of our dev db. So, it was just a little problem
but I wonder what if this happened on production, it will be a disaster.

How can we best handle this? Please advice.

Thanks

Comments

  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hello, thanks for your question. What version of MySQL or Percona Server for MySQL are you using? And which version of Percona XtraBackup? Are the tables InnoDB?

    To get you going, though... In this post, my colleague Shahriyar Rzayev addresses exactly that question: https://www.percona.com/blog/2017/03/15/restore-single-innodb-table-full-backup-accidentally-dropping/

    If your scenario is different from this then do post a follow up with details of your versions and situation. Hope this helps!
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi paolooo;

    Another great way to help in situations like this is by using a delayed replication slave. This gives you a few more options:

    1) If the table is small, you could do a logical dump from the delayed slave and reload it on the master pretty quickly.
    2) If whatever happened on the master was a big deal and would take a long time to fix, you could failover to the delayed replication slave instead (by rolling replication forward on the delayed slave to just before the point at which the bad thing happened and then do the transfer)
    3) If the table is large, you could use it to backup the single table to reload on the primary (similar idea to the link Lorraine gave you, but this way you could backup that single table and have a more recent backup potentially)

    If you are on MySQL/Percona 5.6+:
    https://dev.mysql.com/doc/refman/5.6...n-delayed.html
    https://dev.mysql.com/doc/refman/5.7...n-delayed.html

    If you're on an 5.5 or lower, you can use pt-slave-delay:
    https://www.percona.com/doc/percona-...ave-delay.html
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.