Although, these days, I generally avoid mucking around with production DBs directly. It's all manually scripted migrations, and testing said scripts on a backup or unused slave, which is by far the safest, and also helps to avoid running queries that might adversely affect production's performance.

1: http://dev.mysql.com/doc/refman/5.7/en/mysql-command-options...

My command in the buffer might go through these steps:

1. "Delete from" 2. "-- delete from" 3. "-- delete from table where condition limit n;" 4. (Generally either ask a co-worker or make a Jira with the exact command I have at this point, so there's a sanity check and/or permanent record, but for very low risk/especially mundane/especially time critical updates, do it) 5. Delete the "--" and run it. 6. Think hard about adding some functionality in the app for doing it in app code unsteady of in database code.

Generally do the same for "UPDATE".

This is an alternate form of the flag --safe-updates, this option prevents MySQL from performing update operations unless a key constraint in the WHERE clause and / or a LIMIT clause are provided.

When this isn't possible, I do a select for the PK and delete based upon only the PK. This way I can review the rows to be deleted and back them up manually if needed.

All of this is way overkill considering the backups available these days.

abort;

begin;

Just to be doubly sure. Then notice the number of deleted rows, and select from the table afterwards.

Start your .sql file with "use xxxxx" (non-existent database name; will prevent execution if you fat-finger F5.) Always write your deletes as a SELECT first:

SELECT *

-- delete

FROM blah

WHERE blah = 1

Never uncomment the "delete" line; just select the portion of the query after the comment with your mouse and F5 it.

And of course: always design your database with support for soft-deletes, because sooner or later you'll need to add them in.

  BEGIN TRAN
  DELETE (without where clause)
  UUPS!
  ROLLBACK