Mysql监控方法之一 -

javaPrimary

浏览: 61327 次
性别:
来自: 深圳

最近访客更多访客>>

qdd188

ALongLostPenpal_

卖火柴的小驴子

hyesky

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

Mysql监控方法之一

博客分类：

MYSQL 存储过程

mysql sql 监控

业务系统最终都会反馈到DB上，观察DB变化，可以知道业务系统的处理逻辑和处理方法。下面的代码，用来对MYSQL所有表建立触发器，结合JSP可以观察到DB的变化。附件是效果图。

引用

下面的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脚本，用来观察数据变化

<!--首先导入一些必要的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.jsp" style="margin:0 5 0 5;">查询数据变化</a>
</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>

引用

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

<!--首先导入一些必要的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"%>

 <div style="margin:10px;width:100%;heigth:50px;">
    <a href="del_modify.jsp"  style="margin:0 5 0 5;">清理所有数据 </a>
    <a href="comp.jsp" style="margin:0 5 0 5;">查询数据变化</a>
</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.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();
	%>