If you want to avoid database poisoning (ie want to recover back to a point-in-time quickly) which methodology do you prefer?

Let me define data poisoning. You insert some things in your database which totally messes up the internal structure and interdependencies. I know it means that database design probably also needs to be revisited, but the damage is done.

The methods I have in mind are

  1. Somehow setup replication in which the slave is passive and is X hours behind. If I have a failure all I have to do is reset the application and point it to the slave as my new master. I suspect that it is possible.
  2. Do a hot backup of MySQL every few hours and when failure is detected restore to a backup from X hours before. This would mean a downtime for the application since I cannot let the current application keep on running. One could use innobackup or percona for quick backup and recovery steps.
  3. Design the application and database specifically so that newly added data gets nuked (or shelved). This means I store all events/states (I guess this is the most difficult and theorotical solution)

If the first option is possible and it also stores all the relay logs (ie what ever happened on Master gets transfered to Slave at the same instant but is applied in a few hours automagically) then it would be a perfect solution. Perhaps one could setup multiple slaves in a setup to recover from both an outage and data poisoning

  • 1
    How is it possible to "insert some things in your database which totally messes up the internal structure and interdependencies"? Does your database does not enforce referential integrity or other guarantees of consistency? Sep 28, 2011 at 17:19
  • 1
    Yes, due to bug in the application (ie missing constraints, conflicting requirements implemented by two teams, some thing which missed Dev/QA and ends up in production) you can mess up calculations. Some times they are not apparent and it is easy(only option) to go back.
    – geoaxis
    Sep 28, 2011 at 19:08
  • Such is life :-( Sep 28, 2011 at 19:11

4 Answers 4


You can use the pt-slave-delay tool from Percona Toolkit to keep a replica delayed by the amount of time you choose.

  • Short answer, but a silver bullet !!! +1 !!! Sep 29, 2011 at 0:16
  • OMG I really thought about it. That's actually the most brilliant answer. I'll be swtiching to Percona Toolkit very soon. Thank You !!! Sep 29, 2011 at 0:19
  • @baron I was searching the mysql docs because I remember reading recently about how to delay a slave...what I forgot was that it was a percona tool. Sep 29, 2011 at 2:48

I normally set up a slave specifically for backups. That way I can STOP SLAVE, take backup, and START SLAVE. Doing this method would allow you to take as many backups you want during the day without downtime on your master.

If you wanted, you could shut the slave off manually after it catches back up on the logs. You would have some downtime copying it back over to the master, but then you can replay the binary logs up to the point where your data was 'poisoned'.

  • Like I said in my answer, my answer sounds paranoid. This has more clarity and brevity but answers just as well. +1 !!! Sep 28, 2011 at 19:03
  • DTest and RolandoMySQLDBA, you guys both rock
    – geoaxis
    Sep 28, 2011 at 19:10

You will definitely need Master/Master and the use of a DBVIP

First, let's setup Circular Replication

Let db1 : Master, db2 : Slave

  1. Use existing db1 as Master
  2. install mysql on db2 (Same version aon db1 and db2)
  3. make sure mysql is not running on db2
    • Add server-id=1 to db1:/etc/my.cnf
    • Add log-bin=mysql-bin to db1:/etc/my.cnf
    • Add log-slave-updates to db1:/etc/my.cnf
    • Add skip-slave-start to db1:/etc/my.cnf
    • service mysql restart on db1
  4. copy db1:/etc/my.cnf to db2:/etc/my.cnf
  5. change server-id=1 to server-id=2 in db2:/etc/my.cnf
  6. RESET MASTER; on db1
  7. service mysql stop on db1
  8. rm -f /var/lib/mysql/mysql-bin.* on db1
  9. rsync db1:/var/lib/mysql to db2:/var/lib/mysql
  10. service mysql start on db2
  11. CHANGE MASTER TO MASTER_HOST='HostIP of db1',MASTER_PORT=3306, MASTER_USER='replusername',MASTER_PASSWORD='replpassword', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=<number>; on db2 (Note : MASTER_LOG_POS is 98 before MySQL 5.1, 106 for MySQL 5.1, and 107 for MySQL 5.5)
  12. service mysql start on db2
  13. START SLAVE; on db2 and let replication catchup (Seconds_Behind_Master=0)
  14. CHANGE MASTER TO MASTER_HOST='HostIP of db2',MASTER_PORT=3306, MASTER_USER='replusername',MASTER_PASSWORD='replpassword', MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=<number>; on db1
  15. START SLAVE; on db1 and let replication catchup (Seconds_Behind_Master=0)

Once you have circular replication in place, you can now freeze db2 at a certain point in time by running STOP SLAVE; on db2

Next, create a DBVIP on db1 (Example :

ip addr add dev eth1

Perform all DB reads and writes using DBVIP

If you want db2 to be updated simply run START SLAVE; on db2 and let replication catchup.

If you want to wind back in time to the point db2 was, you can perform there steps

  • service mysql stop on db1
  • rm -f mysql-bin.* on db1
  • rm -f master.info on db1
  • ip addr del dev eth1 on db1
  • ip addr add dev eth1 on db2
  • RESET MASTER on db2 (Hoses binary logs)
  • service mysql stop on db2
  • rsync db2:/var/lib/mysql to db1:/var/lib/mysql
  • service mysql start on db2
  • service mysql start on db1

If could speed up these steps by skipping the mysql shutdown of the DB server you are rsyncing from. That just requires a little extra cleanup on the DB target server.

These steps are simply guides. You may find some of my steps a little paranoid. Feel free to use what you like.


Another option besides using Percona's pt-slave-delay tool, is to set up history/audit table for each base table, which would track all changes to data content and would contain insert, update, and delete timestamps and actual content. You would need to create triggers which fire on every insert, update, and delete statement and then inserts into the history/audit table(s). To reverse the damage, you would read the history/audit tables and apply the changes to base tables.

For example, DBIx::Class::Journal is a perl module that implements this type of schema automatically from the code side. If you visit the URL, you will see that this module goes into quite a bit of depth for capturing data content changes. The journal schema contains tables such as:


For your set up you may want to start with just having a history table for each base table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.