Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.