Mysql监控方法之一

业务系统最终都会反馈到DB上，观察DB变化，可以知道业务系统的处理逻辑和处理方法。下面的代码，用来对MYSQL所有表建立触发器，结合JSP可以观察到DB的变化。
<div class="quote_title">引用</div><div class="quote_div">SQL脚本</div>
<pre name="code" class="java">
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;


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;

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;
</pre>
<div class="quote_title">引用</div><div class="quote_div">JSP脚本</div>
<pre name="code" class="java"><!--首先导入一些必要的packages-->
<%@page import="java.text.MessageFormat"%>
<%@ page import="java.io.*"%>
<%@ page import="java.util.*"%>
<!--告诉编译器使用SQL包-->
<%@ page import="java.sql.*" %>
<%@ page import="com.mysql.*" %>
<!--设置中文输出-->
<%@ page contentType="text/html; charset=UTF-8"%>

<html>
<head>
<title>数据变动观察</title>
<link rel="shortcut icon" href="http://a.fsdn.com/con/img/sftheme/favicon.ico">
</head>
<body>
<div style="margin:10px;width:100%;heigth:50px;">
    <a href="del_modify.jsp"  style="margin:0 5 0 5;">清理所有数据 </a>
    <a href="comp_channelshow.jsp" style="margin:0 5 0 5;">对比排期数据 </a>
    <a href="comp.jsp" style="margin:0 5 0 5;">查询数据变化</a>
</div>
<div  style="margin:10px;width:100%;heigth:50px;">
<h3>对比排期数据 </h3>
<form action="/dup/comp">
渠道编码：<input type="text" name="channelCode" />
影院编码：<input type="text" name="cinemaCode" />
<input type="submit" value="对比" />
</form>
</div>
<%
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("<table border=1  width='80%' style='margin-left:auto;margin-right:auto;margin-top:10px;margin-bottom:10px;'>");
out.println(MessageFormat.format("<caption style='margin-top:10px;margin-bottom:10px;'>第【{0}】次表【{1}】在【{2}】被修改【{3}】。</caption>", ""+(++idx), tablename, createtime, oprtype));

if (content == null || "".equals(content))
{
//打印所显示的数据
out.println("<tr width='100%'>"
+ "<td width='100%'>没有获取到修改内容。</td>"
+ "</tr>");
}
else
{
// 打印 表头
out.println("<tr width='100%' style='word-break: break-all;'>"
        + "<th width='5%'> 表字段 </td>"
+ "<th width='20%'> 旧的值 </td>"
+ "<th width='10%'> 新的值 </td>"
+ "</tr>");

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("<tr width='100%' style='word-break: break-all;'>"
        + "<td width='5%'>" + fieldName + "</td>"
+ "<td width='20%'>" + oldValue + "</td>"
+ "<td width='10%'>" + newValue + "</td>"
+ "</tr>");
}
else
{
//打印所显示的数据
out.println("<tr width='100%' style='word-break: break-all;color:red;'>"
        + "<td width='5%'>" + fieldName + "</td>"
+ "<td width='20%'>" + oldValue + "</td>"
+ "<td width='10%'>" + newValue + "</td>"
+ "</tr>");
}
}

}

out.println("</table>");
}


//关闭数据库连结
rs.close();
stmt.close();
con.close();
%>
</body>
</html>
</pre>