mysql源码添加审计功能

来源：互联网发布：独木成林未卖网前软件编辑：程序博客网时间：2024/04/29 21:33

mysql缺乏审计功能，慢查询主要是针对性能调优的，针对某一用户或某一IP来审计，慢查询有些乏力，

修改mysql源码可以做到这一点。

[root@os3 tools]# diff -u mysql-5.5.31/sql/mysqld.h mysql-5.5.31patch/sql/mysqld.h
--- mysql-5.5.31/sql/mysqld.h 2013-03-25 21:14:58.000000000 +0800
+++ mysql-5.5.31patch/sql/mysqld.h 2014-04-03 07:49:25.000000000 +0800
@@ -218,7 +218,9 @@
extern char err_shared_dir[];
extern TYPELIB thread_handling_typelib;
extern my_decimal decimal_zero;
-
+extern char *opt_audit_user;
+extern char *opt_audit_ip;
+extern ulong opt_audit;
/*
THR_MALLOC is a key which will be used to set/get MEM_ROOT** for a thread,
using my_pthread_setspecific_ptr()/my_thread_getspecific_ptr().
@@ -410,6 +412,9 @@
OPT_SSL_KEY,
OPT_UPDATE_LOG,
OPT_WANT_CORE,
+ OPT_AUDIT,
+ OPT_AUDIT_USER,
+ OPT_AUDIT_IP,
OPT_ENGINE_CONDITION_PUSHDOWN,
OPT_LOG_ERROR,
OPT_MAX_LONG_DATA_SIZE

[root@os3 tools]# diff -u mysql-5.5.31/sql/mysqld.cc mysql-5.5.31patch/sql/mysqld.cc
--- mysql-5.5.31/sql/mysqld.cc 2013-03-25 21:14:58.000000000 +0800
+++ mysql-5.5.31patch/sql/mysqld.cc 2014-04-03 07:57:59.000000000 +0800
@@ -880,6 +880,9 @@
static pthread_t select_thread;
static uint thr_kill_signal;
#endif
+char *opt_audit_user;
+char *opt_audit_ip;
+ulong opt_audit;

/* OS specific variables */

@@ -5720,6 +5723,13 @@

struct my_option my_long_options[]=
{
+{"audit_user", OPT_AUDIT_USER, "the user you want to audit.",&opt_audit_user,&opt_audit_user, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+{"audit_ip", OPT_AUDIT_IP, "the ip you want to audit.",&opt_audit_ip, &opt_audit_ip, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0,0, 0},
+{"options_audit", OPT_AUDIT, "This is option just for test",&opt_audit,&opt_audit,
+ 0, GET_ULONG,
+ REQUIRED_ARG, 2, 1, 1000,
+ 0, 1, 0
+ },
{"help", '?', "Display this help and exit.",
&opt_help, &opt_help, 0, GET_BOOL, NO_ARG, 0, 0, 0, 0,
0, 0},
[root@os3 tools]#

[root@os3 tools]# diff -u mysql-5.5.31/sql/sys_vars.cc mysql-5.5.31patch/sql/sys_vars.cc
--- mysql-5.5.31/sql/sys_vars.cc 2013-03-25 21:14:58.000000000 +0800
+++ mysql-5.5.31patch/sql/sys_vars.cc 2014-04-03 08:01:53.000000000 +0800
@@ -2268,6 +2268,13 @@
SESSION_VAR(net_wait_timeout), CMD_LINE(REQUIRED_ARG),
VALID_RANGE(1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT)),
DEFAULT(NET_WAIT_TIMEOUT), BLOCK_SIZE(1));
+static Sys_var_ulong Sys_opt_audit(
+ "options_audit",
+ "The number of seconds the server waits for activity on a "
+ "connection before closing it",
+ GLOBAL_VAR(opt_audit), CMD_LINE(REQUIRED_ARG),
+ VALID_RANGE(0,1),
+ DEFAULT(0), BLOCK_SIZE(1));

/** propagates changes to the relevant flag of @@optimizer_switch */
static bool fix_engine_condition_pushdown(sys_var *self, THD *thd,
@@ -3069,6 +3076,16 @@
"that keeps a list of the last relay logs",
READ_ONLY GLOBAL_VAR(opt_relaylog_index_name), CMD_LINE(REQUIRED_ARG),
IN_FS_CHARSET, DEFAULT(0));
+static Sys_var_charptr Sys_audit_user(
+ "audit_user", "The location and name to use for the file "
+ "that keeps a list of the last relay logs",
+ GLOBAL_VAR(opt_audit_user), CMD_LINE(REQUIRED_ARG),
+ IN_FS_CHARSET, DEFAULT(0));
+static Sys_var_charptr Sys_audit_ip(
+ "audit_ip", "The location and name to use for the file "
+ "that keeps a list of the last relay logs",
+ GLOBAL_VAR(opt_audit_ip), CMD_LINE(REQUIRED_ARG),
+ IN_FS_CHARSET, DEFAULT(0));

static Sys_var_charptr Sys_relay_log_info_file(
"relay_log_info_file", "The location and name of the file that "

[root@os3 tools]# diff -u mysql-5.5.31/sql/log.cc mysql-5.5.31patch/sql/log.cc
--- mysql-5.5.31/sql/log.cc 2013-03-25 21:14:58.000000000 +0800
+++ mysql-5.5.31patch/sql/log.cc 2014-04-08 04:13:40.000000000 +0800
@@ -1206,22 +1206,31 @@
/* do not log slow queries from replication threads */
if (thd->slave_thread && !opt_log_slow_slave_statements)
return 0;
-
- lock_shared();
- if (!opt_slow_log)
+ if (!opt_slow_log)
{
unlock();
return 0;
}
+ if(opt_audit==0)
+ {
+ unlock();
+ return 0;
+ }
+ if((strcmp(sctx->user,opt_audit_user)!= 0 || strcmp(opt_audit_ip,sctx->host_or_ip)!= 0) && (strcmp(opt_audit_user,"all")!=0 || strcmp(opt_audit_ip,"all")!=0))
+ {
+ unlock();
+ return 0;
+ }

+ // printf("sctx->ip:%s",sctx->ip);
/* fill in user_host value: the format is "%s[%s] @ %s [%s]" */
+ //# User@Host: slave_xierqi[slave_xierqi] @ [114.112.91.98]
user_host_len= (strxnmov(user_host_buff, MAX_USER_HOST_SIZE,
sctx->priv_user ? sctx->priv_user : "", "[",
sctx->user ? sctx->user : "", "] @ ",
sctx->host ? sctx->host : "", " [",
sctx->ip ? sctx->ip : "", "]", NullS) -
user_host_buff);
-
current_time= my_time_possible_from_micro(current_utime);
if (thd->start_utime)
{

[root@os3 tools]# diff -u mysql-5.5.31/sql/sql_class.h mysql-5.5.31patch/sql/sql_class.h
--- mysql-5.5.31/sql/sql_class.h 2013-03-25 21:14:58.000000000 +0800
+++ mysql-5.5.31patch/sql/sql_class.h 2014-04-03 07:54:04.000000000 +0800
@@ -507,6 +507,7 @@
double long_query_time_double;

my_bool pseudo_slave_mode;
+// ulong opt_audit;

} SV;

@@ -2317,7 +2318,7 @@
void update_server_status()
{
ulonglong end_utime_of_query= current_utime();
- if (end_utime_of_query > utime_after_lock + variables.long_query_time)
+ if (end_utime_of_query >= utime_after_lock + variables.long_query_time)
server_status|= SERVER_QUERY_WAS_SLOW;
}
inline ulonglong found_rows(void)

生成的diff就是补丁，可以用patch 源文件补丁文件打补丁

make && make install

vim /etc/my.cnf

添加

audit_user=all
audit_ip=all
options_audit=0

默认审计所有IP和用户名

启动服务

mysql> set global audit_ip='10.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> set global audit_user='slave_xierqi';
Query OK, 0 rows affected (0.00 sec)

可以过滤user和ip，满足条件的可以审计，总开关为options_audit。

下一步，独立一个文件与slow query文件分离。

0 0