backing up data that matches certain criteria
Posted on 2010-11-16
Using mysql backup tools, is it possible to do something like a mysqldump for rows in a table that match certain criteria.
For example, lets say I have a database that has table A with 100 records. Table A has a field called TestValue. 50 records have a value 10 in this field and the rest have a value 20 in this field.
This is what I want to do:
1) Create a mysql dump for the 50 records where TestValue = 20
2) Delete the records where TestValue = 20
3) At a later date, restore from the mysql dump the 50 records where TestValue = 20 so that Table A has 100 records in it again
Basically the reason I am thinking of this solution is that I have a database that contains a mixture of public and private data. When this data is 'inhouse' both the public and private data should be in the database. The database can be sent out into the public domain and the private data must be removed. The database may well be returned to us with extra data in it. The private data then needs to be restored when it is back inhouse.
My solution to this was a 'selective' mysql dump but I'm not a database person so there may be a better option. My idea may not even be possible
thanks a lot