Scheduler in mysql

Question

Hi,

Is there any option in MySQL to run a scheduler job? At end of the day take some records from table1 (last 3 weeks days via sql query) and transfer to another db's table.

Please give some heads up on this.

Thanks.

Answer 1

no, but you can use a cron job to execute the mysql command :

mysql <my_db_name> -u<user_name> -p<password> <mysql.sql>

Answer 2

Yes, there's the event scheduler (since MySQL 5.1.6)

CREATE EVENT myevent
    ON  SCHEDULE EVERY 1 DAY
    DO
      insert into mydb.mytable select something,anything from otherdb.othertable where ..... ;

Answer 3

I tried with this syntax but it never transferred the data.

CREATE EVENT event_test1 ON SCHEDULE EVERY 1 MINUTE STARTS '2010-09-02 15:19:25' ON COMPLETION NOT PRESERVE ENABLE DO insert into schema2.table1 (ID) select ID from schema1.table1 Limit 1000

I don't see the o/p in the table1 of schema2, what did i miss?