1.新建访问量信息表
##创建表CheckDbStatus CREATE TABLE `tbCheckDbStatus` ( `check_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `com_select` int(10) unsigned DEFAULT NULL, `com_insert` int(10) unsigned DEFAULT NULL, `com_update` int(10) unsigned DEFAULT NULL, `com_delete` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`check_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
2.新建存储过程
-- 创建一个存储过来用来监控mysql的SELECT、INSERT、UPDATE、DELETE使用情况。 ## 创建存储过程pCheckDbStatus1() drop procedure if exists pCheckDbStatus1; delimiter // create procedure pCheckDbStatus1() begin select @COM_DELETE:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_DELETE'; select @COM_INSERT:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_INSERT'; select @COM_SELECT:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_SELECT'; select @COM_UPDATE:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_UPDATE'; select sleep(1); select @COM_DELETE1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_DELETE'; select @COM_INSERT1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_INSERT'; select @COM_SELECT1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_SELECT'; select @COM_UPDATE1:=variable_value FROM information_schema.GLOBAL_STATUS WHERE variable_name='COM_UPDATE'; INSERT INTO tbCheckDbStatus VALUES (NULL,now(), @COM_SELECT1-@COM_SELECT,@COM_INSERT1-@COM_INSERT,@COM_UPDATE1-@COM_UPDATE,@COM_DELETE1-@COM_DELETE ); end; // delimiter ;
3.创建定时任务执行存储过程
-- mySQL兼容问题处理 -- show variables like '%show_compatibility_56%'; -- set global show_compatibility_56=on;
-- 设置group_concategroup_concat
-- SET GLOBAL group_concat_max_len=102400;
-- SET SESSION group_concat_max_len=102400;
-- 查询定时任务是否开启 -- show variables like '%sche%'; -- set global event_scheduler=1; -- 定时任务 每隔59秒执行一次 CREATE EVENT if not exists e_test on schedule every 59 second on completion preserve do call pCheckDbStatus1();
4.数据定时清理
## 创建存储过程pClearDbStatus1 drop procedure if exists pClearDbStatus1; delimiter // create procedure pClearDbStatus1() begin DELETE FROM tbCheckDbStatus WHERE unix_timestamp(now())-unix_timestamp(time)>86400; end; // delimiter ; -- 定时任务 每天执行一次 CREATE EVENT if not exists e_test2 on schedule EVERY 1 DAY STARTS '2021-03-11 17:30:00' on completion preserve do call pClearDbStatus1();
5. 相关知识
-- 查询数据库增删改查次数
show global status where Variable_name in('com_select','com_insert','com_delete','com_update') ;
-- 查询所有的存储过程: select name from mysql.proc where db='数据库名'; -- 查询某个存储过程: show create procedure 存储过程名;
-- 查询事件(定时任务)
select * from information_schema.EVENTS;
-- 临时关闭事件(定时任务)
ALTER EVENT e_test DISABLE;
-- 开启事件(定时任务)
ALTER EVENT e_test ENABLE;
-- 删除事件(定时任务)
DROP EVENT [IF EXISTS] event_name
例:DROP EVENT e_test;
数据库定时任务配置
-- 查询数据库是否打开定时任务 show variables like '%event_scheduler%'; -- 开启定时任务 -- 语句方式(当前启动的实例有效,重启后无效) set global event_scheduler=1; -- 或配置my.cnf(windows下是my.ini)下添加如下选项(重启后有效) [mysqld] event_scheduler=ON