这个脚本是用来在mysql数据库中创建一个myawr数据库，记录数据库中记录active session。

cat /root/user/myawr.sql

--先创建myawr数据库
create database myawr DEFAULT CHARACTER SET utf8mb4;

use myawr;

CREATE TABLE myawr.`processliststatus` (
  `ID` bigint(21) unsigned NOT NULL DEFAULT '0',
  `USER` varchar(32) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext,
  `clock` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `snapid` bigint(20) DEFAULT NULL,
  KEY `idx_clock` (`clock`),
  key idx_snapid(snapid)
);


CREATE TABLE myawr.`mysqldumplog` (
  `ID` bigint unsigned NOT NULL DEFAULT '0',
  `USER` varchar(32) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext,
  `clock` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `idx_clock` (`clock`)
);


CREATE TABLE myawr.`lockstatus` (
  `clock` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `wating_trx_state` varchar(255) DEFAULT NULL,
  `waiting_trx_id` bigint DEFAULT NULL,
  `waiting_thread` bigint DEFAULT NULL,
  `waiting_query` varchar(2000) DEFAULT NULL,
  `blocking_trx_state` varchar(255) DEFAULT NULL,
  `blocking_trx_id` bigint DEFAULT NULL,
  `blocking_thread` bigint DEFAULT NULL,
  `blocking_query` varchar(2000) DEFAULT NULL,
  `snapid` bigint(20) DEFAULT NULL,
  KEY `idx_clock` (`clock`),
  key idx_snapid(snapid)
);


DELIMITER $$
CREATE PROCEDURE myawr.`partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))
BEGIN
        DECLARE PARTITION_NAME VARCHAR(16);
        DECLARE RETROWS INT(11);
        DECLARE FUTURE_TIMESTAMP TIMESTAMP;
        /*
         * Check if any partitions exist for the given SCHEMANAME.TABLENAME.
         */
        SELECT COUNT(1) INTO RETROWS
        FROM information_schema.partitions
        WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL;
        /*
         * If partitions do not exist, go ahead and partition the table
         */
        IF RETROWS = 1 THEN
                /*
                 * Take the current date at 00:00:00 and add HOURLYINTERVAL to it.  This is the timestamp below which we will store values.
                 * We begin partitioning based on the beginning of a day.  This is because we don't want to generate a random partition
                 * that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
                 * end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
                 */
                SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00'));
                SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00');
                -- Create the partitioning query
                SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(UNIX_TIMESTAMP(`clock`))");
                SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));");
                -- Run the partitioning query
                PREPARE STMT FROM @__PARTITION_SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        END IF;
END $$



CREATE PROCEDURE myawr.`partition_create` ( SCHEMANAME VARCHAR ( 64 ), TABLENAME VARCHAR ( 64 ), PARTITIONNAME VARCHAR ( 64 ), CLOCK INT ) BEGIN
/*
SCHEMANAME = The DB schema in which to make changes
TABLENAME = The table with partitions to potentially delete
PARTITIONNAME = The name of the partition to create
*/
/*
Verify that the partition does not already exist
*/
DECLARE
RETROWS INT;
SELECT
COUNT( 1 ) INTO RETROWS 
FROM
information_schema.PARTITIONS 
WHERE
table_schema = SCHEMANAME 
AND table_name = TABLENAME 
AND partition_description >= CLOCK;
IF
RETROWS = 0 THEN
/*
1. Print a message indicating that a partition was created.
2. Create the SQL to create the partition.
3. Execute the SQL from #2.
*/
SELECT
CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg;
SET @SQL = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );
PREPARE STMT 
FROM
@SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END $$



CREATE PROCEDURE myawr.`partition_drop` ( SCHEMANAME VARCHAR ( 64 ), TABLENAME VARCHAR ( 64 ), DELETE_BELOW_PARTITION_DATE BIGINT ) BEGIN
/*
SCHEMANAME = The DB schema in which to make changes
TABLENAME = The table with partitions to potentially delete
DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
*/
DECLARE
done INT DEFAULT FALSE;
DECLARE
drop_part_name VARCHAR ( 16 );
/*
Get a list of all the partitions that are older than the date
in DELETE_BELOW_PARTITION_DATE.  All partitions are prefixed with
a "p", so use SUBSTRING TO get rid of that character.
*/
DECLARE
myCursor CURSOR FOR SELECT
partition_name 
FROM
information_schema.PARTITIONS 
WHERE
table_schema = SCHEMANAME 
AND table_name = TABLENAME 
AND CAST( SUBSTRING( partition_name FROM 2 ) AS UNSIGNED ) < DELETE_BELOW_PARTITION_DATE;
DECLARE
CONTINUE HANDLER FOR NOT FOUND 
SET done = TRUE;
/*
Create the basics for when we need to drop the partition.  Also, create
@drop_partitions to hold a comma-delimited list of all partitions that
should be deleted.
*/
SET @alter_header = CONCAT( "ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION " );
SET @drop_partitions = "";
/*
Start looping through all the partitions that are too old.
*/
OPEN myCursor;
read_loop :
LOOP
FETCH myCursor INTO drop_part_name;
IF
done THEN
LEAVE read_loop;
END IF;
SET @drop_partitions =
IF
( @drop_partitions = "", drop_part_name, CONCAT( @drop_partitions, ",", drop_part_name ) );
END LOOP;
IF
@drop_partitions != "" THEN
/*
                   1. Build the SQL to drop all the necessary partitions.
                   2. Run the SQL to drop the partitions.
                   3. Print out the table partitions that were deleted.
                */
SET @full_sql = CONCAT( @alter_header, @drop_partitions, ";" );
PREPARE STMT 
FROM
@full_sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SELECT
CONCAT( SCHEMANAME, ".", TABLENAME ) AS `table`,
@drop_partitions AS `partitions_deleted`;
ELSE /*
                   No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
                   that no changes were made.
                */
SELECT
CONCAT( SCHEMANAME, ".", TABLENAME ) AS `table`,
"N/A" AS `partitions_deleted`;
END IF;
END $$



CREATE PROCEDURE myawr.`partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT)
BEGIN
        DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16);
        DECLARE PARTITION_NAME VARCHAR(16);
        DECLARE OLD_PARTITION_NAME VARCHAR(16);
        DECLARE LESS_THAN_TIMESTAMP INT;
        DECLARE CUR_TIME INT;
        CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);
        SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'));
        SET @__interval = 1;
        create_loop: LOOP
                IF @__interval > CREATE_NEXT_INTERVALS THEN
                        LEAVE create_loop;
                END IF;
                SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);
                SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00');
                IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
                        CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
                END IF;
                SET @__interval=@__interval+1;
                SET OLD_PARTITION_NAME = PARTITION_NAME;
        END LOOP;
        SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000');
        CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
END$$



CREATE PROCEDURE myawr.proc_awr_killdump( )
BEGIN
declare dump_id INT default false;
DECLARE done INT DEFAULT FALSE;
DECLARE myCursor CURSOR FOR SELECT id FROM information_schema.PROCESSLIST WHERE INFO like 'SELECT /*!40001 SQL_NO_CACHE */ * FROM%';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET @dump_id ='';
OPEN myCursor;
read_loop :
LOOP
FETCH myCursor INTO dump_id;
IF
done THEN
LEAVE read_loop;
END IF;
insert into myawr.mysqldumplog(ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO) select ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO from information_schema.processlist where id=dump_id;
call mysql.rds_kill(dump_id);
END LOOP;
END
$$




CREATE procedure myawr.proc_awr_getstatus()
begin
declare insertSessionCount INT default 0; -- 声明insert会话的计数器  
set @ha=unix_timestamp(now());
-- 查看是否有insert 会话
select count(*) into insertSessionCount  from  information_schema.processlist where INFO like 'insert into myawr.processliststatus%';
-- 如果之前存在未结束的insert会话，则本次不insert
IF  insertSessionCount <1   THEN  
-- 排除非活跃会话和系统会话,并做截断操作
insert into myawr.processliststatus(ID,USER,HOST,DB,COMMAND,TIME,STATE,INFO,snapid) select ID,USER,HOST,DB,COMMAND,TIME,STATE,substring(INFO,1,3000), @ha from information_schema.processlist  where COMMAND not in ('Sleep','Daemon','Binlog Dump GTID') and INFO not like 'insert into myawr.processliststatus%';   
END IF;  
 
insert into  myawr.lockstatus(wating_trx_state,waiting_trx_id,waiting_thread,waiting_query,blocking_trx_state,blocking_trx_id,blocking_thread,blocking_query,snapid) SELECT r.trx_state wating_trx_state,r.trx_id waiting_trx_id,r.trx_mysql_thread_Id waiting_thread,r.trx_query waiting_query,b.trx_state blocking_trx_state,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query,@ha FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
end
$$


create procedure myawr.proc_awr_enable(proc_name varchar(64))
begin
/*declare proc_cursor varchar(64) default false;
DECLARE done INT DEFAULT FALSE;
DECLARE myCursor CURSOR FOR select name from mysql.event where db='myawr' and name like 'event\_awr\_%' and name not in ('event_awr_resetpartition');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;*/
if proc_name = 'all' then
/*SET @proc_cursor ='';
OPEN myCursor;
read_loop :
LOOP
FETCH myCursor INTO proc_cursor;
IF
done THEN
LEAVE read_loop;
END IF;
alter event proc_cursor enable;
END LOOP;*/
select "抱歉，该参数现在还没能实现，敬请期待" as msg;
elseif proc_name = 'killdump' then
alter event event_awr_killdump enable;
select "enable event_awr_killdump succeed ~" as msg;
elseif proc_name = 'getmysqlstatus' then
alter event event_awr_getMysqlStatus enable;
select "enable event_awr_getMysqlStatus succeed ~" as msg;
else
select "参量输入有误抑或没有布置awr脚本！" as msg;
end if;
end
$$



create procedure myawr.proc_awr_disable(proc_name varchar(64))
begin
/*declare proc_cursor varchar(64) default false;
DECLARE done INT DEFAULT FALSE;
DECLARE myCursor CURSOR FOR select name from mysql.event where db='myawr' and name like 'event\_awr\_%' and name not in ('event_awr_resetpartition');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;*/
if proc_name = 'all' then
/*SET @proc_cursor ='';
OPEN myCursor;
read_loop :
LOOP
FETCH myCursor INTO proc_cursor;
IF
done THEN
LEAVE read_loop;
END IF;
alter event proc_cursor enable;
END LOOP;*/
select "抱歉，该参数现在还没能实现，敬请期待" as msg;
elseif proc_name = 'killdump' then
alter event event_awr_killdump disable;
select "disable event_awr_killdump succeed ~" as msg;
elseif proc_name = 'getmysqlstatus' then
alter event event_awr_getMysqlStatus disable;
select "disable event event_awr_getMysqlStatus succeed ~" as msg;
else
select "参量输入有误抑或没有布置awr脚本！" as msg;
end if;
end
$$

CREATE PROCEDURE myawr.myawr_help()
BEGIN
select  "
1、实现功能：
1.1、默认每10s会收集一次mysql的processlist状态和锁等待情况。
1.2、默认每9s检查是否存在dump操作，如果存在将会kill线程，并且将此记录在mysqldump_log表中。
2、更改参数：
2.1、修改脚本收集或检查的频率
 最小时间不应该小于7s，否则可能会对数据库性能产生影响。
mysql> call proc_awr_changeint('killdump',5);
2.2、启用或停止脚本部分功能
mysql> call proc_awr_enable('all');
mysql> call proc_awr_enable('killdump');
mysql> call proc_awr_enable('getmysqlstatus');
mysql> call proc_awr_disable('all');
mysql> call proc_awr_disable('killdump');
mysql> call proc_awr_disable('getmysqlstatus');
3、dump操作：
3.1、停止非法dump监控事件
mysql> call proc_awr_disable('killdump');
4.2、进行dump操作
mysqldump --single-transaction ***
3.3、恢复监控事件
mysql> call proc_awr_enable('killdump');"  AS help_message;
end
$$


create event myawr.event_awr_getMysqlStatus
ON SCHEDULE
-- 每隔10秒运行
every 10 second
on completion preserve
do call proc_awr_getstatus();
$$



CREATE EVENT myawr.event_awr_killdump
ON SCHEDULE 
-- 每隔9秒运行
EVERY 9 SECOND 
ON COMPLETION PRESERVE
DO CALL proc_awr_killdump ();
$$




create event myawr.event_awr_resetpartition
ON SCHEDULE
every 1 day
on completion preserve
do
begin
call partition_maintenance('myawr','processliststatus',7,24,30);
call partition_maintenance('myawr','lockstatus',7,24,30);
end
$$