I am trying to do an event with the backup of all the database tables

Viewed 33

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 // 
0 Answers
Related