The old driver of Oracle & MySQL said we should use it (delayed copying)
Recently, I have encountered the situation that the tables in the production environment database have been deleted, which should not have happened.
A few tips:
Database developers in the production environment can only have the right to query, and even low-level developers do not have the authority to check the production system at all, similar table deletion to the professional DBA to operate, of course, some units do not have the so-called DBA.
Full-time DBA basically has an attribute, that is, each step of the operation will consider the consequences, so there will be a backup before deleting the table.
To establish an audit system, operations such as truncate, drop and rm can be fatal and must be audited.
You can first rename the table, such as rename the table into bak_date_tablename, and clean it up regularly.
Daily backup of the database is essential, no matter whether it is logical backup or physical backup, it is necessary to see if your database is available, data loss is never a small matter.
Let's talk about a small function of the database, "delayed replication."
There are delayed replication in Oracle dataguard and delayed replication in MySQL 5.6. the old driver suggests that you can enable delayed replication if you have conditions. Once the data is lost, we can recover the data in time.
-- Oracle
SQL > select switchover_status, open_mode from v$database
SWITCHOVER_STATUS OPEN_MODE
--
NOT ALLOWED READ ONLY WITH APPLY
SQL > alter database recover managed standby database cancel
Database altered.
SQL > alter database recover managed standby database delay 5 disconnect from session;-5 minutes, which can be set by hour
Database altered.
-- alter*.log
RFS [2]: Selected log 4 for thread 1 sequence 51 dbid 1483481154 branch 955700418
Fri Sep 29 20:39:39 2017
Media Recovery Delayed for 5 minute (s) (thread 1 sequence 50)
Fri Sep 29 20:39:47 2017
Archived Log entry 48 added for thread 1 sequence 51 ID 0x58707be4 dest 1:
Fri Sep 29 20:39:47 2017
-- MySQL
Mysql > stop slave
Mysql > CHANGE MASTER TO MASTER_DELAY = 600,
Mysql > start slave
Mysql > show slave status\ G
Exactly how to use delayed replication to recover data, you need to do your own test, ha.
As a qualified DBA, there is not only one way to recover data. Try to avoid erroneous deletion operations, after all, the process of restoring data, business will be interrupted, losses are inevitable.
Is there any brother who loves playing basketball? you can exchange database technology and ball skills on weekends.
