Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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? –  Nick Chammas Sep 28 '11 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 '11 at 19:08
    
Such is life :-( –  Jack Douglas Sep 28 '11 at 19:11
add comment

4 Answers

up vote 5 down vote accepted

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

share|improve this answer
    
Short answer, but a silver bullet !!! +1 !!! –  RolandoMySQLDBA Sep 29 '11 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 !!! –  RolandoMySQLDBA Sep 29 '11 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. –  Derek Downey Sep 29 '11 at 2:48
add comment

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'.

share|improve this answer
    
Like I said in my answer, my answer sounds paranoid. This has more clarity and brevity but answers just as well. +1 !!! –  RolandoMySQLDBA Sep 28 '11 at 19:03
    
DTest and RolandoMySQLDBA, you guys both rock –  geoaxis Sep 28 '11 at 19:10
add comment

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 : 192.168.10.1)

ip addr add 192.168.10.1/24 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 192.168.10.1/24 dev eth1 on db1
  • ip addr add 192.168.10.1/24 dev eth1 on db2
  • CHANGE MASTER TO MASTER_HOST=''; 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.

share|improve this answer
add comment

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:

ChangeSet
ChangeLog
AuditLog
AuditHistory

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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