?
Solved

backing up data that matches certain criteria

Posted on 2010-11-16
7
Medium Priority
?
321 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:andieje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 34150149
Have you considered creating a temporary table containing only the data you want to backup, then using the dump on the temporary table?
0
 

Author Comment

by:andieje
ID: 34151403
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
0
 

Author Comment

by:andieje
ID: 34151613
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
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 34154434
please see this option in the MySQLDUMp tool:
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
--where='where_condition'       where       Dump only rows selected by the given WHERE condition
0
 

Author Comment

by:andieje
ID: 34154700
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?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34154714
yes, exactly
0
 

Author Closing Comment

by:andieje
ID: 34155348
thanks
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question