1、mysql配置文件$ cat mysql.cfg
[3306]
ip=xxx.xxx.xxxx.xxx
user=root
pass=xxxxx
port=3306
auditlog=/home/mysql/data/mysql-audit.json
socket=/tmp/mysql.sock
2、函数文件,主要是日志函数[mysql@trs bin]$ cat common.sh
#!/bin/sh
logger(){
local info="$*"
echo "[`date +'%Y/%m/%d %H:%M:%S'`] $info"
}
logger
3、获取my.cnf中的配置#!/bin/sh
. /etc/profile
. ~/.bash_profile
#根据配置文件、section、item,得到item对应的value
#其中$1 为section,$2为item,$3为配置文件
getvalue()
{
awk -F '=' '/\['$1'\]/{a=1}a==1&&$1~/'$2'/{print $2;exit}' $3 |sed 's/ *$//g'|sed 's/^ *//g'
}
getconfig()
{
SECTION=$1
CONFILE=$2
#一个段中的名值对,必须实现把所有的都列出来
ENDPRINT="user pass port auditlog ip socket"
dd=`date +'%Y%m%d'`
for loop in $ENDPRINT
do
case $loop in
"user")
#user=`awk -F '=' '/\['$SECTION'\]/{a=1}a==1&&$1~/'$loop'/{print $2;exit}' $CONFILE |sed 's/ *$//g'|sed 's/^ *//g'`
user=`getvalue $SECTION $loop $CONFILE`
;;
"pass")
pass=`getvalue $SECTION $loop $CONFILE`
;;
"port")
port=`getvalue $SECTION $loop $CONFILE`
;;
"auditlog")
auditlog=`getvalue $SECTION $loop $CONFILE`
;;
"ip")
host=`getvalue $SECTION $loop $CONFILE`
;;
"socket")
socket=`getvalue $SECTION $loop $CONFILE`
;;
esac
#auditlog=`grep $auditlog config.ini |awk -F '=' '{print $2}'|sed 's/ *$//g'|sed 's/^ *//g'`
done
logdir=$AUD_HOME/logs/$host:$port
[ ! -d $logdir ] && mkdir -p $logdir
( $AUD_HOME/bin/audit_logrotate.sh $user $pass $auditlog $host $port $socket >>$logdir/${host}:${port}_$dd 2>&1 )
}
bdir=`dirname $0`
cd $bdir >/dev/null
AUD_HOME=`dirname $PWD`
cd - >/dev/null
PATH=$AUD_HOME/bin:$PATH
export PATH AUD_HOME
config=$AUD_HOME/config/mysql.cfg
dd=`date +'%Y%m%d'`
#获取配置文件中的段名称,如存在[3306]、[3307],会得到3306和3307
section=`grep "^\[.*\]$" $config|grep -o "[[:digit:]]\{4,\}" | xargs`
for OneCom in $section
do
getconfig $OneCom $config
done
#删除过期的日志
find $AUD_HOME/mysql_audit/* -ctime +10 |xargs rm -rf;
find $AUD_HOME/logs -ctime +10 -exec rm {} \;
4、根据my.cnf的值生成审计日志$ cat audit_logrotate.sh
#!/bin/bash
AUD_HOME=${AUD_HOME:-/home/mysql/audit_archive}
. $AUD_HOME/bin/common.sh
tt=`date +%Y%m%d%H%M%S`
user=$1
pass=$2
auditlog=$3
host=$4
port=$5
socket=$6
dd=`date +'%Y%m%d'`
root=mysql_audit
logserver=172.30.4.104
logrotate(){
if [ -s $3 ]; then
mv $3 $4
chmod o+r $4
echo "set global audit_json_file_flush=ON;" | mysql -u$1 -p$2 -S $5
else
logger $3 is empty, not need rotate!
exit
fi
}
logger $host $port
dir=$AUD_HOME/$root/${host}:${port}/$dd
[ ! -d $dir ] && mkdir -p $dir
file=mysql-audit.json$tt
out=$dir/$file
logrotate $user $pass $auditlog $out $socket
logger rotate mysql audit to file $out
( $AUD_HOME/bin/rsynclog.sh $AUD_HOME/$root/${host}:${port} $logserver $root)