mysql.conf乱码_loganalyzer+rsyslog搭建日志服务器(解决中文乱码、记录ip等问题)

安装脚本如下，文章最后有打包下载，遇到的问题在脚本中有注释

#!/bin/sh

# mysql conf

mysql_user=root #mysql root 用户名

mysql_pwd=fuckmysqlintruder #mysql root 密码

run_user=user #syslog库用户

run_pwd=user #syslog库用户密码

# Install env.

yum -y install mysql mysql-devel mysql-server php php-mysql php-pdo php-common php-gd httpd zlib-devel gcc gcc-c++

# Install rsyslog..

tar xvf rsyslog-5.6.2.tar.gz

cd rsyslog-5.6.2

./configure --enable-mysql --prefix=/usr/local/rsyslog #支持mysql,指定安装路径

make && make install

cd ..

# creat rsyslog config..

sed -e "s/ommysql:localhost,Syslog,root,/ommysql:localhost,Syslog,${run_user},${run_pwd};dbFormat/g" rsyslog.conf > /etc/rsyslog.conf

#这里设置rsyslog.conf中的mysql连接账户和密码

#rsyslog支持数据模板,我定义了一个插入db的模板,在rsyslog.conf中

#$template dbFormat,"insert into SystemEvents (Message, Facility,FromHost, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values (%msg%, %syslogfacility%, %fromhost-ip%,%syslogpriority%, %timereported:::date-mysql%, %timegenerated:::date-mysql%, %iut%, %syslogtag%)",sql

#其中红色字体部分如果是%HOSTNAME%,那么库中记录的就是hostname,不是ip;所以如果要改成显示ip,那么使用%fromhost-ip%

# stop system syslog..

service syslog stop #因为有rsyslog了,系统自带的syslog停掉

chkconfig syslog off

# create rsyslog startup script

cp /etc/init.d/{syslog,rsyslog} #rsyslog没有启动脚本,复制syslog脚本

# edit rsyslog startup script

sed -i s/syslog/rsyslog/g /etc/init.d/rsyslog #修改syslog字符为rsyslog

chmod 700 /etc/init.d/rsyslog

chkconfig --add rsyslog #添加rsyslog服务

chkconfig rsyslog on

# create rsyslog bin ln

ln -sv /usr/local/rsyslog/sbin/rsyslogd /sbin/rsyslogd #创建软连结,rsyslog脚本启动的是/sbin下的rsyslogd

# conf mysql

service mysqld start

chkconfig mysqld on

mysqladmin -u root -p password ${mysql_pwd} #设置mysql的root密码,脚本执行到这里要点下回车,默认root密码为空

# create sql

cat > createDB.sql << EOF #创建Syslog数据库和表,并授权,为什么给all privileges,因为安装时会有create 操作,装完以后降权

CREATE DATABASE Syslog default character set utf8;

USE Syslog;

CREATE TABLE SystemEvents

(

ID int unsigned not null auto_increment primary key,

CustomerID bigint,

ReceivedAt datetime NULL,

DeviceReportedTime datetime NULL,

Facility smallint NULL,

Priority smallint NULL,

FromHost varchar(60) NULL,

Message text,

NTSeverity int NULL,

Importance int NULL,

EventSource varchar(60),

EventUser varchar(60) NULL,

EventCategory int NULL,

EventID int NULL,

EventBinaryData text NULL,

MaxAvailable int NULL,

CurrUsage int NULL,

MinUsage int NULL,

MaxUsage int NULL,

InfoUnitID int NULL ,

SysLogTag varchar(60),

EventLogType varchar(60),

GenericFileName VarChar(60),

SystemID int NULL

) DEFAULT CHARSET=utf8;

CREATE TABLE SystemEventsProperties

(

ID int unsigned not null auto_increment primary key,

SystemEventID int NULL ,

ParamName varchar(255) NULL ,

ParamValue text NULL

) DEFAULT CHARSET=utf8;

grant all privileges on Syslog.* to ${run_user}@localhost identified by ${run_pwd};

EOF

# import sql

mysql -u${mysql_user} -p${mysql_pwd} < createDB.sql #导入sql

# start rsyslog

service rsyslog restart #启动rsyslog

# install loganalyzer

tar xvf loganalyzer-3.0.4.tar.gz

cd loganalyzer-3.0.4

cp -r src/ /var/www/html/loganalyzer

cp -r contrib/* /var/www/html/loganalyzer

cd ..

chown -R apache.apache /var/www/html/loganalyzer

# use web install loganalyzer.

/bin/sh /var/www/html/loganalyzer/configure.sh

/bin/sh /var/www/html/loganalyzer/secure.sh

# start httpd

service httpd start

chkconfig httpd on

# edit src/include/functions_common.php  function GetStringWithHTMLCodes

sed -i s/htmlentities.*/htmlentities($myStr,ENT_QUOTES,"UTF-8");/g /var/www/html/loganalyzer/include/functions_common.php

#loganalyzer乱码的原因是使用了,htmlentities对输出进行编码,如果有中文会出现问题,应该指定字符集,修改这个函数为

#return htmlentities($myStr,ENT_QUOTES,"UTF-8");

# reduce the privilege

read -n 1 -p Please go to install web, when finished, press Enter...  #到这里先进web安装loganalyzer,完毕以后再来降权

cat > priv.sql << EOF #发现浏览日志详细日志的时候,会用到alter,加grant进去了.

revoke all privileges on Syslog.* from ${run_user}@localhost;

grant insert,delete,update,select,alter on Syslog.* to ${run_user}@localhost identified by ${run_pwd};

EOF

mysql -u${mysql_user} -p${mysql_pwd} < priv.sql

# clean

rm -rf priv.sql createDB.sql loganalyzer-3.0.4 rsyslog-5.6.2

完