Tag: event

How about remove old data from mysql.

Posted on by lechuck park
  1. check Event ON state.

    show variables where Variable_name like ‘%event%’;

  2. if not set ON

    SET GLOBAL event_scheduler = ON;

  3. Example for Event Job – delete data which was inserted over a week ago.

    delimiter |
    CREATE EVENT IF NOT EXISTS <#event_name>
    ON SCHEDULE
    EVERY 1 DAY
    DO
    BEGIN
    delete from <#table_name> where stime < DATE_SUB(NOW(), INTERVAL 7 Day );
    END |
    delimiter ;

  4. check Event

    select EVENT_DEFINITION from information_schema.events where event_name='<#event_name>’;

  5. check Event state

    show events from <#database name>

  6. How to backup to csv. ( how about adding this event before delete.. with where phase) – this example is not concerned with  event example on this page.

  7. SELECT order_id,product_name,qty
    FROM orders
    INTO OUTFILE ‘/tmp/orders.csv’
    FIELDS TERMINATED BY ‘,’
    ENCLOSED BY ‘”‘
    LINES TERMINATED BY ‘\n’;