Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a mysql server hosting a production database, and routinely I make snapshots of it, which we can also use as a staging environment.

Sometime bad things happen, e.g. we delete a row (and all those connected to it through foreign keys) from production, and after fixing the bad code I procede to import the backed up data.

But, being a noob at ops, I am not sure of what the proper process would be.

As of now I have a script that does something like:

  • get the row indexed by key K (with mysqldump -w)
  • get all the rows where row_key = K from a list of tables (again with mysqldump -w)
  • insert all these rows in the prod db (with mysql )

This seems hackish, and is always open to some inconsistency both on the data finding part (e.g. the list of "related" tables in my database is incorrect) and on the data importing part (e.g. constraints get violated because the DBs have "diverged" and I must fix the INSERT code by hand ).

Is there a proper way to do this kind of things?

I can see related questions on SF, e.g. moving data from one MYSQL db to another but this seem related to merging whole dbs, or importing a whole backup, and not restoring a small "self contained" slice of data.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

No, the way you've got it is pretty much the way it has to be done. The correct solution to the problem is to never delete data -- flag it as inactive, or at worst move it to a "deleted" table. Audit trails work well, too.

share|improve this answer
    
yeah flagging the data seems better, but I have an issue with that: basically, wherever I have an unique index on column C I need to change it to a multiple one. The "deleted_x" table seem to present the same issues of importing from a backup (because I end up having a whole duplicate of a schema subset prefixed with "deleted_", when cross-table relations are involved). Or possibly there is something else I am not thinking of? –  riffraff Jul 16 '12 at 9:06
    
No, you're on the money. It's a pest of a problem. –  womble Jul 16 '12 at 9:18

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.