- check Event ON state.
show variables where Variable_name like ‘%event%’;
- if not set ON
SET GLOBAL event_scheduler = ON;
- 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 ; - check Event
select EVENT_DEFINITION from information_schema.events where event_name='<#event_name>’;
- check Event state
show events from <#database name>
- 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.
-
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE ‘/tmp/orders.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’;
How about remove old data from mysql.
Lechuck Park 