Avatar of andieje
andieje asked on

backing up data that matches certain criteria

Hi

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
MySQL Server

Avatar of undefined
Last Comment
andieje

8/22/2022 - Mon
Cornelia Yoder

Have you considered creating a temporary table containing only the data you want to backup, then using the dump on the temporary table?
ASKER
andieje

i was hoping i could just back up parts of the relevant table without having to create a temporary table. Then i could reinstate the data back to the temporary table rather than having to reinstate the data into the temporary table and then move it from the temporary table to the original table.

It seemed simpler that way to me
ASKER
andieje

how about doing a 'select into' statement on the table rows that I want and then import these back in using a mysqlimport statement?

Any other solutions welcomed
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
andieje

that drops the table and all the data in it though doesnt it?

i can use that with the no-create-db and no-create-info options i guess then?
Guy Hengel [angelIII / a3]

yes, exactly
ASKER
andieje

thanks
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.