监控库中存储和函数变更记录存储

监控库中存储和函数变更记录存储

1、建表

create table etl_log
select now()          etl_time,
       routine_type   object_type,
       specific_name  object_name,
       routine_schema schema_name,
       count(1)       version,
       created        ddl_time
from information_schema.Routines
where routine_schema = 库名
group by specific_name;

 

2、存储

# 有添加新的存储或函数插入新的
insert into etl_log
select s.* from
(select t.* from
(select now()          etl_time,
       routine_type   object_type,
       specific_name  object_name,
       routine_schema schema_name,
       count(1)       version,
       created        ddl_time
from information_schema.Routines
where routine_schema = 库名
group by specific_name) as t left join etl_log
on t.ddl_time = etl_log.ddl_time
where etl_log.etl_time is null) as s left join etl_log
on s.object_name = etl_log.object_name
where etl_log.object_name is null;


# 有更改的存储或函数插入新的，并增加版本号
insert into etl_log
select s.etl_time, s.object_type, s.object_name, s.schema_name, max(etl_log.version)+1 version, s.ddl_time from
(select t.* from
(select now()          etl_time,
       routine_type   object_type,
       specific_name  object_name,
       routine_schema schema_name,
       count(1)       version,
       created        ddl_time
from information_schema.Routines
where routine_schema = 库名
group by specific_name) as t left join etl_log
on t.ddl_time = etl_log.ddl_time
where etl_log.etl_time is null) as s left join etl_log
on s.object_name = etl_log.object_name
where etl_log.object_name is not null
group by etl_log.object_name;
end;

 

3、将存储设置定时存储，时间间隔根据实际情况调整

 

注意：如果是要监控多个库，需要等号换成in，里面填对应的库名。当前存储只能监视插入和修改的存储，删除的存储和函数监控不到