mysql 监控方法_Mysql监控方法之一

引用

下面的SQL脚本，做两件事，生成存储记录变化的表，和生成触发器SQL的存储过程

drop TABLE if EXISTS zz_modify;

CREATE TABLE `zz_modify` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`content` text,

`createtime` datetime DEFAULT NULL,

`tablename` varchar(4000) DEFAULT NULL,

`oprtype` varchar(20) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 存储过程，用来生成删除触发器的SQL。

drop PROCEDURE if EXISTS p_droptrgs;

CREATE PROCEDURE `p_droptrgs`()

BEGIN

-- 定义变量

declare tab_name varchar(400);

declare tmp_tabschema varchar(400) DEFAULT 'rmp_2';

declare v_sql_all text DEFAULT '';

declare cur_tab_done int DEFAULT 0;

-- 定义所有表的遍历游标

DECLARE cur_tab cursor for select table_name from information_schema.`TABLES` where TABLE_SCHEMA = tmp_tabschema and TABLE_NAME != 'zz_modify';

-- 将结束标志绑定到游标

declare continue handler FOR SQLSTATE '02000' SET cur_tab_done = 1;

-- 打开游标

OPEN cur_tab;

REPEAT

FETCH cur_tab into tab_name;

-- call p_createtrgsbytable(tmp_tabName, tmp_tabschema);

set v_sql_all = CONCAT(v_sql_all,'drop trigger if EXISTS trg_insert_', tab_name, '; \n');

set v_sql_all = CONCAT(v_sql_all,'drop trigger if EXISTS trg_update_', tab_name, '; \n');

set v_sql_all = CONCAT(v_sql_all,'drop trigger if EXISTS trg_delete_', tab_name, '; \n');

UNTIL cur_tab_done

end REPEAT;

CLOSE cur_tab;

select v_sql_all;

END;

drop FUNCTION if EXISTS f_createtrgsbytable;

CREATE FUNCTION `f_createtrgsbytable`(tab_name varchar(400), tab_schema varchar(400)) RETURNS text CHARSET utf8

DETERMINISTIC

BEGIN

-- 定义变量

declare tmp_col_name varchar(4000);

DECLARE v_sql_all longtext DEFAULT '';

declare v_sql_insert longtext DEFAULT '';

declare v_sql_update longtext DEFAULT '';

declare v_sql_delete longtext DEFAULT '';

declare cur_tab_done int DEFAULT 0;

-- 定义所有表的遍历游标

DECLARE cur_tab cursor for select COLUMN_NAME from information_schema.`COLUMNS` where table_name = tab_name and TABLE_SCHEMA = tab_schema;

-- 将结束标志绑定到游标

declare continue handler FOR SQLSTATE '02000' SET cur_tab_done = 1;

set v_sql_insert = CONCAT(v_sql_insert,'drop trigger if EXISTS trg_insert_', tab_name, ';');

set v_sql_update = CONCAT(v_sql_update,'drop trigger if EXISTS trg_update_', tab_name, ';');

set v_sql_delete = CONCAT(v_sql_delete,'drop trigger if EXISTS trg_delete_', tab_name, ';');

set v_sql_insert = concat(v_sql_insert, 'create trigger trg_insert_', tab_name, ' after insert on ', tab_name, ' for each row begin insert into zz_modify(content, createtime, tablename, oprtype) value(concat(');

set v_sql_update = concat(v_sql_update, 'create trigger trg_update_', tab_name, ' after update on ', tab_name, ' for each row begin insert into zz_modify(content, createtime, tablename, oprtype) value(concat(');

set v_sql_delete = concat(v_sql_delete, 'create trigger trg_delete_', tab_name, ' after delete on ', tab_name, ' for each row begin insert into zz_modify(content, createtime, tablename, oprtype) value(concat(');

-- 打开游标

OPEN cur_tab;

REPEAT

FETCH cur_tab into tmp_col_name;

set v_sql_insert = CONCAT(v_sql_insert, '\'#&old.', tmp_col_name, '=\',', '\'-\'', ',\'~new.', tmp_col_name, '=\',', 'IFNULL(new.', tmp_col_name, ', \'nu-ll\'),');

set v_sql_update = CONCAT(v_sql_update, '\'#&old.', tmp_col_name, '=\',', 'IFNULL(old.', tmp_col_name, ', \'nu-ll\') ,\'~new.', tmp_col_name, '=\',', 'IFNULL(new.', tmp_col_name, ', \'nu-ll\'),');

set v_sql_delete = CONCAT(v_sql_delete, '\'#&old.', tmp_col_name, '=\',', 'IFNULL(old.', tmp_col_name, ', \'nu-ll\') ,\'~new.', tmp_col_name, '=\',', '\'-\'', ',');

UNTIL cur_tab_done

end REPEAT;

CLOSE cur_tab;

-- 截取处理

set v_sql_insert = CONCAT(SUBSTRING(v_sql_insert,1,LENGTH(v_sql_insert) - 1), ') ,now(), \'', tab_name, '\', \'insert\' ); end;\n');

set v_sql_update = CONCAT(SUBSTRING(v_sql_update,1,LENGTH(v_sql_update) - 1), ') ,now(), \'', tab_name, '\', \'update\' ); end;\n');

set v_sql_delete = CONCAT(SUBSTRING(v_sql_delete,1,LENGTH(v_sql_delete) - 1), ') ,now(), \'', tab_name, '\', \'delete\' ); end;\n');

return CONCAT(v_sql_insert,v_sql_update,v_sql_delete);

END;

-- 执行存储过程，用来生成产生触发器的SQL。

drop PROCEDURE if EXISTS p_createtrgs;

CREATE PROCEDURE `p_createtrgs`()

BEGIN

-- 定义变量

declare tmp_tabName varchar(400);

declare tmp_tabschema varchar(400) DEFAULT 'rmp_2';

declare v_sql_all longtext DEFAULT '';

declare cur_tab_done int DEFAULT 0;

-- 定义所有表的遍历游标

DECLARE cur_tab cursor for select table_name from information_schema.`TABLES` where TABLE_SCHEMA = tmp_tabschema and TABLE_NAME != 'zz_modify';

-- 将结束标志绑定到游标

declare continue handler FOR SQLSTATE '02000' SET cur_tab_done = 1;

-- 打开游标

OPEN cur_tab;

REPEAT

FETCH cur_tab into tmp_tabName;

-- call p_createtrgsbytable(tmp_tabName, tmp_tabschema);

set v_sql_all = CONCAT(v_sql_all, f_createtrgsbytable(tmp_tabName, tmp_tabschema), '\n');

UNTIL cur_tab_done

end REPEAT;

CLOSE cur_tab;

select v_sql_all;

END;

引用

JSP脚本，用来观察数据变化

数据变动观察

清理所有数据

查询数据变化

Connection con;

Statement stmt;

ResultSet rs;

//加载驱动程序，下面的代码为加载MySQL驱动程序

Class.forName("com.mysql.jdbc.Driver");

//注册MySQL驱动程序

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

//用适当的驱动程序连接到数据库

//String dbUrl = "jdbc:mysql://172.16.34.12:3306/cec?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";

//String dbUser = "cec"; //用户名

//String dbPwd = "cec"; //密码

String dbUrl = "jdbc:mysql://192.168.9.139:3306/cec_yxt?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";

String dbUser = "root"; //用户名

String dbPwd = "123456"; //密码

//建立数据库连接

con = java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);

//创建一个JDBC声明

stmt = con.createStatement();

//查询记录

rs = stmt.executeQuery("select id,content,createtime,tablename,oprtype from zz_modify");

//输出查询结果

int idx = 0;

while (rs.next())

{

String content = rs.getString("content");

String createtime = rs.getString("createtime");

String tablename = rs.getString("tablename");

String oprtype = rs.getString("oprtype");

out.println("

out.println(MessageFormat.format("

第【{0}】次表【{1}】在【{2}】被修改【{3}】。", ""+(++idx), tablename, createtime, oprtype));

if (content == null || "".equals(content))

{

//打印所显示的数据

out.println("

"

+ "

没有获取到修改内容。"

+ "

");

}

else

{

// 打印 表头

out.println("

"

+ "

表字段 "

+ "

旧的值 "

+ "

新的值 "

+ "

");

StringTokenizer st = new StringTokenizer(content, "#&");

while (st.hasMoreTokens())

{

String row = st.nextToken();

String[] rowarr = row.split("~");

String oldContent = rowarr[0];

String newContent = rowarr[1];

String fieldName = oldContent.substring(oldContent.indexOf('.') + 1, oldContent.indexOf('='));

String oldValue = oldContent.substring(oldContent.indexOf('=') + 1);

String newValue = newContent.substring(newContent.indexOf('=') + 1);

if (newValue.equals(oldValue))

{

//打印所显示的数据

out.println("

"

+ "

" + fieldName + ""

+ "

" + oldValue + ""

+ "

" + newValue + ""

+ "

");

}

else

{

//打印所显示的数据

out.println("

"

+ "

" + fieldName + ""

+ "

" + oldValue + ""

+ "

" + newValue + ""

+ "

");

}

}

}

out.println("

");

}

//关闭数据库连结

rs.close();

stmt.close();

con.close();

%>

引用

JSP脚本，用来删除记录表zz_modify中所有的数据的。

清理所有数据

查询数据变化

Connection con;

Statement stmt;

ResultSet rs;

//加载驱动程序，下面的代码为加载MySQL驱动程序

Class.forName("com.mysql.jdbc.Driver");

//注册MySQL驱动程序

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

//用适当的驱动程序连接到数据库

//String dbUrl = "jdbc:mysql://172.16.34.12:3306/cec?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";

//String dbUser = "cec"; //用户名

//String dbPwd = "cec"; //密码

String dbUrl = "jdbc:mysql://192.168.9.12:3306/cec_for_yxt_test?user=root&password=abcd1001&useUnicode=true&characterEncoding=UTF-8";

String dbUser = "cec"; //用户名

String dbPwd = "n2h@5B_AoP"; //密码

//建立数据库连接

con = java.sql.DriverManager.getConnection(dbUrl, dbUser, dbPwd);

//创建一个JDBC声明

stmt = con.createStatement();

//查询记录

stmt.execute("delete from zz_modify");

stmt.close();

con.close();

%>

大小: 27.8 KB

分享到：

2015-12-01 11:13

浏览 527

论坛回复 / 浏览 (0 / 2549)

评论