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

MySQL & MariaDB Percona XtraBackup
1

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

2

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: [url]https://www.percona.com/blog/2017/03/15/restore-single-innodb-table-full-backup-accidentally-dropping/[/url]

If your scenario is different from this then do post a follow up with details of your versions and situation. Hope this helps!

3

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+:
[URL=“MySQL :: MySQL 5.6 Reference Manual :: 17.3.10 Delayed Replication”]https://dev.mysql.com/doc/refman/5.6...n-delayed.html[/URL]
[URL=“MySQL :: MySQL 5.7 Reference Manual :: 16.3.10 Delayed Replication”]https://dev.mysql.com/doc/refman/5.7...n-delayed.html[/URL]

If you’re on an 5.5 or lower, you can use pt-slave-delay:
[URL=“pt-slave-delay — Percona Toolkit Documentation”]https://www.percona.com/doc/percona-...ave-delay.html[/URL]