I want to make an event that every 5 seconds create a backup of my tables in a tables_old.
I would also like to know how I can check if a table has not been modified and not make that copy.
This is my code:
create event ejer5
on schedule every 5 second
do
begin
DECLARE done INT DEFAULT FALSE;
DECLARE table_name varchar(30);
DECLARE cursor1 CURSOR FOR select table_name
from information_schema.tables
where table_type = 'BASE TABLE'
and table_schema not in ('information_schema', 'sys',
'performance_schema','mysql')
and table_schema = "world"
order by update_time desc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
open cursor1;
bucle:loop
fetch cursor1 into table_name;
IF done THEN
LEAVE bucle;
END IF;
SET @c = CONCAT('CREATE TABLE `',table_name,'_old', '` LIKE `',table_name,'`');
PREPARE stmt from @c;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end loop;
close cursor1;
end //