Drop table and copy one table look alike in MYSQL weekly

Question

I have a problem where I need to copy a table from MYSQL using this:

CREATE TABLE newtable LIKE oldtable; 
INSERT newtable SELECT * FROM oldtable;

I need to drop that table weekly and create them again because I dont want to lose the original data. Can anyone help me to find a solution where it can be done automatically. I really have no idea about that

Answer 1

What if you have a java program, where you're having the MySQL query and let the query be executed automatically through a cron job periodically.

Where you can maybe have two separate threads running, or you could do it within the same thread it self. ie, executing the INSERT into the newtable and then maybe you can simply drop the old ones.

OR you could go with the MySQL Event Scheduler. This SO pretty much explains the effective way of going ahead with this as per your requirement.

Answer 2

You can use MySQL EVENT Scheduler for this very purpose like below. You can as well consider using CREATE TABLE AS(CTAS) construct instead

delimiter |
CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 WEEK
    DO
BEGIN
      DROP TABLE IF EXISTS newtable;
      CREATE TABLE newtable AS  
         SELECT * FROM oldtable;
END |
delimiter ;