Oracle & MySQL Old driver says we're going to use (delayed replication)

In the recent work has encountered the production Environment database table was deleted, in fact, such things should not have happened.

A few small suggestions:

1. Production environment database developers can only have query permissions, even low-level development does not have permission to check the production system, similar to the deletion of the table to the professional DBA to operate, of course, some units do not have the so-called DBA.
   

2. A full-time DBA basically has a property, that is, every step of the operation will take into account the consequences, so the deletion of the table will have a backup.

3. Establishment of audit system, truncate, DROP, RM such operations may be fatal, must be audited.

4. You can rename the table first, such as the table rename into Bak_date_tablename, regularly to clean up.

5. Database daily backup is necessary, whether he logical backup or physical backup, are necessary to see if your home database, data loss is never a trivial matter.

Next, a small feature of the database, "deferred replication"

Oracle Dataguard has delayed replication, MySQL 5.6 also has delayed replication, the old driver recommends that you can turn on delayed replication in case of conditions, once the data is lost, we can also recover data in a timely manner.

--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, can be set according to the hour

Database altered.

--alter*.log

RFS[2]: Selected log 4 for thread 1 sequence 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 added for thread 1 sequence-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;

How to use delayed replication to recover data, you need to do your own testing ha

As a qualified DBA, there is not only one way to recover data. As far as possible to avoid accidental deletion, after all, the process of recovering data, business will be interrupted, the loss is unavoidable.

Say there is no love to play basketball brothers, the weekend can Exchange database technology and Skills ha ~

This article is from the "ROIDBA" blog, make sure to keep this source http://roidba.blog.51cto.com/12318731/1969843

Oracle & MySQL Old driver says we're going to use (delayed replication)