FWIW: Setting "safe-updates=1" in ~/.my.cnf will require UPDATE and DELETE statements in the client to have a WHERE clause which references a key. It's not perfect protection, but it will save you from a lot of mistakes.

That's awesome,

My worst DELETE fail however was:

  DELETE * FROM table WHERE [long condition that resolves to true for all records]

Now i write SELECT or SELECT COUNT(*) over and over again until i see the data i expect and then change it to a DELETE/UPDATE.

It's not my personal habit but some folks I know turn off auto commit and BEGIN a transaction every time they enter an interactive SQL sessions. They then default to ROLLBACK at least once before COMMITing them.

That and having a user with read-only permissions or a read replica

Is there a collection of data safety tips like this somewhere? I never knew this existed. What else am I missing?

hmm, that's kinda cool. I'm in a MS shop and I don't know if SMSS has the same feature. My manager just looked at me and said "welp, go restore the table and be more careful next time." I was a new DBA at the time, still, kinda new.

USE BEGIN TRANS as mentioned above

I once brought down our entire production XenServer cluster group by issuing a "shutdown now" in the wrong SSH window. Needless to say it was a bad feeling watching Nagios go crazy and realizing what had just happened.

    root@baz # shutdown now
    W: molly-guard: SSH session detected!
    Please type in hostname of the machine to shutdown: foo
    Good thing I asked; I won't shutdown baz ...

Surprising to see such a simple protection neglected.

I don't know how well-known molly-guard is, but I've never heard of it before. Definitely enabling it on my servers next week.

Interesting. Up until now I've considered it well-known to the point of ubiquity :)

Oh crap! Yeah I bet you were pretty panicked. My update statement destroyed data that my team used all the time so I was worried I'd get fired. Luckily that wasn't the case.

Fat fingers are just nature's way of making sure you test your back-up & restore procedures periodically :-)

When I first started using Linux and wanted to do some housecleaning, I did "rm -r (asterix) in a folder. Cleaned up everything, no prob. Then went to some more folders, hit the up arrow on my keyboard fast to get to a command I had used before. Hit 'enter' before my brain realized I had landed on rm -r (asterix) and not the right command. Never used that command again.