0

I have a mysql database with a table that has a data for over 6 months which goes to 1.5 GB of data . I want to take a backup of only 4 days .And then put that in some other database in the same system.

Please suggest me how can i take a selective backup of only 4 days . I am using mysql gui tools .

Thanks,

edit

CREATE TRIGGER backup BEFORE INSERT ON table_from  FOR EACH ROW BEGIN
    INSERT INTO table_to SET (col1,col2,col3,col4) = (NEW.col1,new.col2,new.col3,new.col4);
      END;

where do i put the query to insert the required number of rows .I tried running your query alone , it shows error in the query.

user1896796
  • 731
  • 3
  • 9
  • 25

1 Answers1

0

First make sure there is a DateTime/Timestamp column in the table with the last modification time so that you know what data you need to back up or not. MySQL doesn't store this information by default with each record so if you need it you need to create it, and with that size of a table you may want to index it.

If the database you are copying to is on the same system then all you would need to do is run a single query. Something like this would work. This copies all the data from db_from.my_cool_table into db_to.my_cool_table

INSERT (col1, col2, col3 ...) INTO db_to.my_cool_table
SELECT col1, col2, col3 FROM db_from.my_cool_table WHERE modification_time > DATE_SUB(Now(), INTERVAL -4 DAY)

You may also think of adding a Trigger that will automatically insert/update/delete every record in db_from.my_cool_table to db_to.my_cool_table in real time so you never need to run this backup script. See here: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Benjamin Oman
  • 1,654
  • 1
  • 17
  • 19
  • I tried creating index ,but the system hanged .I ran that query for almost an hour , then i stopped .NOw in the catalog i am seeing that there is 1.2 gb of data and 512 mb of index created . I am trying to drop the index as i m thinking there is some problem ,but not able to do so . And yeah my table is having time stamp – user1896796 Jul 05 '13 at 06:21
  • A trigger may be a better way to go then since it would copy over row by row and thus not even need an index to search by. – Benjamin Oman Jul 05 '13 at 06:43
  • Here is where you can learn all about triggers http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html A trigger is an automatic query you can set to run each time a table is changed, so you'd just set the trigger to include a query similar to the one I wrote above but instead of referencing db_from.my_cool_table you would use a column prefix "NEW" (such as NEW.col1, NEW.col2, etc) that is available when you write a query inside of a trigger. – Benjamin Oman Jul 05 '13 at 06:57
  • please check my edit .Where do i put the query to insert the required number of rows .I tried running your query alone , it shows error in the query. – user1896796 Jul 05 '13 at 07:25
  • Try this: `CREATE TRIGGER backup BEFORE INSERT ON table_from FOR EACH ROW INSERT INTO table_to (col1,col2,col3,col4) VALUES (NEW.col1,new.col2,new.col3,new.col4)` You should use the 'SET' keyword in an insert. You'll also want a trigger for an UPDATE and DELETE which should work pretty much the same. – Benjamin Oman Jul 05 '13 at 17:21