About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/Articles/How to recover accidentally deleted DB data to the state just before it was deleted

How to recover accidentally deleted DB data to the state just before it was deleted

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to the entire company.
I hope this helps in some way.
 

overview

This is a summary of how to recover MySQL to the point just before an operation such as a TABLE DROP due to an operational mistake, or a DELETE WHERE condition being invalidated due to an application bug, causing all records to be deleted.
For the MySQL services we manage, in addition to daily full backups using Percona XtraBackup, we also back up the binlog files every 5 minutes.
We will show you how to recover using those backup files.
 
The content of this article mainly applies to those who are running MySQL in an on-premise environment.
The story is a little different for Amazon Aurora, so please refer to the CyberAgent Developers Blog article below.
Is Aurora MySQL backup really enough? | CyberAgent Developers Blog
This is @rarirureluis Hasegawa from the Service Reliability Group (SRG) of the Technology Division. #SRG (Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new services, and contributing to OSS. Also, about Amazon Aurora MySQL (hereinafter referred to as Aurora MySQL). I don't know why I'm writing so many articles about Aurora MySQL. Here is the best practice for upgrading Aurora MySQL from the previous article. Considering how to switch to RDS Graviton2 with low risk [Upgrade Edition] | CyberAgent Developers Blog This time, it's about backups. For example, if you accidentally drop a table, can you restore it to the state just before you dropped it? Or is there a documented way to restore it to the state just before? This is not limited to public clouds such as AWS, but even if you only take a full backup, you cannot restore it to a specific query. That's where the binary log comes in. As long as you have the latest full backup and the binary log taken after that backup, you can restore to a specific query. Aurora MySQL has two features for recovery: Backtrack and Point-in-Time Recovery. These two features do not restore to the point of a specific query, but rather restore to a specified date and time. Therefore, it is not possible to go back to just before an incorrect query. Backtrack restores a cluster to a specified time.
https://developers.cyberagent.co.jp/blog/archives/29925/
 

Overall flow

  1. Check the location of full backup data and backup binlog
  1. See which queries were executed by mistake and when
  1. Check the binlog position at the time of the full backup taken before the query was executed
  1. Check the binlog position immediately before the mistakenly executed query
  1. Generate recovery queries from binlog files
  1. Deploy full backup data to all databases in service
  1. Apply recovery queries to all DBs after SET sql_log_bin = 0
  1. Finally, re-establish the replication
 

procedure

Check the location of full backup data and backup binlog

Check where you have your backup data stored.
You should always be aware of whether you are uploading to S3 or to a physical server somewhere.

See which queries were executed by mistake and when

Determines the previous execution of any query to which data is returned.
Check with the service provider or developer.

Check the binlog position at the time of the full backup taken before the query was executed

xtrabackup_binlog_info

Check the binlog position immediately before the mistakenly executed query

If you want to specify a point immediately before a specific query, you must identify that query from the backed up binlog file.
Since the binlog cannot be read as is, you need to convert it back to SQL format using the mysqlbinlog command before searching for the relevant query.
end_log_pos 286332780

Generate recovery queries from binlog files

Now that we have confirmed the "position from which the backed up was taken" and the "position to which we want to return", we will generate a query to apply the full backup to the restored DB.
This generates the SQL that applies up to just before the DELETE statement mentioned earlier.
 
If you want to go back to a certain time, rather than going back to a specific query as above, you can write it like this:
In this case, it is an example of specifying from the full backup data to 13:05 on March 4th.

Deploy full backup data to all databases in service

The full backup data from XtraBackup will be deployed to the DB server.
If you have a backup on an external network such as S3, the following method is recommended.
  1. Download the full backup data from S3 to one of the slave servers
  1. Extract the xtrabackup data on that slave server
    1. Once the deployment is complete, copy the DB data in /var/lib/mysql on that slave server to all DBs.

    Run the recovery query with SET sql_log_bin = 0 and then apply it to all DBs

    Once the full backup data has been deployed to all DBs, the SQL extracted from the binary logs will be applied to all DBs.
    If you normally run the recovery query only on the DB master, it will take even more time for the replication delay to converge, so we will set sql_log_bin = 0 and then run the query on all DBs at the same time.

    Finally, re-establish the replication

    Once all DB data has been copied, configure replication between the master and slave.
    The DB has now been restored to the state it was in just before the specified query was executed.
     

    Conclusion

    I think restoring backup data will be an operation that occurs once every few years, if that.
    We recommend that you document your recovery methods and practice implementing them on a regular basis so that you can remain calm when an emergency occurs.
     
    SRG is looking for people to work with us. If you are interested, please contact us here.