If technical personnel often operate DB online, they will inevitably have mistakes after walking along the river for a long time:
(1) Update error;
(2) Delete wrong data;
(3) drop the wrong data;
Do how? Go to DBA to restore the data, even if you can’t recover, someone has to take the blame.
Voice-over: Delete all the data, what to do, what to do?
Zero, which solution cannot achieve data recovery?
Restore data from slave Library.
In general, database clusters are master-slave:
If you manually delete a database, the command will be synchronized to other secondary libraries. As a result, all data on all libraries will be deleted and cannot be restored. Therefore, this solution does not work.
First, what is the most common solution if dbAs have not done their homework?
If you don’t have a data security plan, the most common way to deal with deletions is to run away. Cut out the company’s most important asset and get out of here.
Second, what happens if a DBA does a full backup and incremental backup daily?
The most common skill for dbAs is: full backup + incremental backup.
Full backup: Periodically (for example, one month) back up all library files.
Incremental backup: Periodically (for example, daily) incremental backup of binlog.
If you accidentally delete a library, you can restore it like this:
(1) Find the full library of the last full backup, copy it back (files are generally large), decompress it and apply it;
(2) Find the incremental binlog of each day after the last full backup, copy it back (there are many files), and replay it successively;
(3) After the last incremental backup, find the binlog before “Delete all database” and replay it;
Recovery complete.
To ensure the reliability of the solution, regular drills are required.
Gee, I don’t think I’ve heard of dbAs doing these exercises regularly.
It is very likely that the solution is only theoretical, and if it does go wrong, the effect is only theoretically recoverable. At this point, return to plan one, run.
The recovery period for full backup and incremental backup is also very long, possibly in days.
Voice-over: It took a long time to transfer terabytes of data.
Third, if the DBA does “1 hour delay from library”, what should be done?
What is 1 hour delay from library?
As shown in the figure above, add a slave library, which does not synchronize with the master library in real time, but synchronizes with the master library every hour. After the synchronization, immediately disconnect for 1 hour, and the slave library will maintain a 1-hour data gap with the master library.
How can I use the 1-hour Delay from the Database to quickly recover data when a database deletion accident occurs?
(1) Application of 1 hour delay from;
(2) Delay 1 hour from the latest synchronization time to the binlog before “Delete all database” is found and played again
Quick recovery completed.
The advantage of this scheme is that data can be retrieved quickly. The potential disadvantage is that there is no recovery in the event of a “delete” accident occurring during a short period of time when the “1-hour delay slave” is being connected to the master for synchronization.
4. What happens if the DBA makes a “double 1-hour delay from the library”?
What is the double 1 hour delay from?
As shown in the figure above, the two 1-hour delay slave libraries are “diverged by half an hour” from the synchronization time of the master library.
In this way, even if a delay occurs during a short period of synchronization from the master database, there is still another delay to keep the data half an hour ago, so that fast recovery can be implemented.
The advantage of this scheme is that data can be quickly recovered without any contingency. The potential disadvantage is that the resource utilization rate is a little low. In order to ensure data security, two more delayed slave servers are added, which reduces the slave database utilization rate.
How to improve slave library utilization efficiency?
For some services that “allow delay”, you can use the 1-hour delay from:
(1) Operation background, product background;
(2) DATA synchronization by BI;
(3) Research and development for data sampling and investigation;
However, it is important to note that this is a library and only a “read only” service is available.
Five,
Ensuring data security is a top priority for dbAs:
(0) It is theoretically possible to restore + running;
(1) Full backup + incremental backup + regular drill;
(2) 1-hour delay from the library;
(3) Double 1-hour delay from library + improve resource utilization;
Related recommendations:
Why InnoDB concurrency is so high?
InnoDB, Seven Locks
InnoDB index, Finally understood
InnoDB: Four Transaction Isolation Levels implemented
InnoDB, debug deadlock method!
DBA magic. Did you learn that? Transfer.
Research:
What kind of plan does your company use?