About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/How to recover accidentally deleted database data to the state just before it was deleted

How to recover accidentally deleted database 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) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for 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 error or a DELETE where condition is not met due to an application bug, causing all records to be deleted.
For the MySQL service we manage, in addition to daily full backups using Percona XtraBackup, we also back up the binlog file every five minutes.
We will show you how to recover using these backup files.
 
This article mainly covers those who are running MySQL in an on-premise environment.
The story is a little different for Amazon Aurora, so please refer to the article on the CyberAgent Developers Blog below.
Is Aurora MySQL backup really enough? | CyberAgent Developers Blog
This is @rarirureluis Hasegawa from the Service Reliability Group (SRG) in the Technology Division. The #SRG (Service Reliability Group) primarily provides cross-sectional support for our media service infrastructure, improving existing services, launching new ones, and contributing to open source software. Also, let's talk about Amazon Aurora MySQL (hereafter referred to as Aurora MySQL). I don't know why I'm writing so many articles about Aurora MySQL. The previous article on best practices for upgrading Aurora MySQL is here. Considering how to switch to RDS Graviton2 with minimal risk [Upgrade Edition] | CyberAgent Developers Blog. This time, we'll be talking about backups. For example, if you accidentally drop a table, can you revert to the state just before the drop? Or is there a documented method for doing so? This isn't limited to public clouds like AWS, but even if you only have a full backup, you can't revert to a specific query. That's where binary logs come in. With the most recent full backup and any binary logs taken since that backup, you can restore a specific query. Aurora MySQL has two recovery features: Backtrack and Point-in-Time Recovery. These two features do not restore to the point in time of a specific query, but rather restore to a specified date and time. Therefore, it is not possible to return to the point just before an incorrect query. Backtrack restores the cluster to a specified time.
https://developers.cyberagent.co.jp/blog/archives/29925/
 

Overall flow

  1. Check the location of the full backup data and backup binlog
  1. Check which queries were executed by mistake and for how long
  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 currently in service
  1. Apply recovery queries to all DBs after SET sql_log_bin = 0
  1. Finally, rebuild the replication
 

procedure

Check the location of the full backup data and backup binlog

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

Check which queries were executed by mistake and for how long

Determines the previous execution of the 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 binlog cannot be read as it is, you need to convert it back to SQL format using the mysqlbinlog command and then search for the relevant query.
end_log_pos 286332780

Generate recovery queries from binlog files

Now that we have confirmed the "position at which the backup was taken" and the "position at the time we want to return to," we will generate a query to apply the full backup to the restored DB.
This generates SQL that applies up to just before the DELETE statement.
 
If you want to go back to a certain time, rather than just going back to a specific query, 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 currently 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 extrabackup data to that slave server
    1. Once the deployment is complete, copy the DB data in /var/lib/mysql on the 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 simply run the recovery query on the DB master, it will take even longer for the replication delay to resolve, so we will set sql_log_bin = 0 and then run the query on all DBs at the same time.

    Finally, rebuild the replication

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

    Conclusion

    I think restoring from backup data will be an operation that occurs once every few years, if at all.
    We recommend that you document and practice recovery methods on a regular basis so that you can operate calmly in the event of an emergency.
     
    SRG is looking for people to work with us. If you're interested, please contact us here.