作者 主題: [筆記]Centralized syslog-ng to Mysql Installation Guide  (閱讀 40795 次)

0 會員 與 2 訪客 正在閱讀本文。

sam.lin

  • 懷疑的國中生
  • **
  • 文章數: 42
    • 檢視個人資料
    • http://samlin2004.myweb.hinet.net
此文件整理有關 syslog-ng + mysql + php-syslog-ng 的安裝筆記,用以集中數台Linux主機的 logs 於 Mysql database 上以利於檢視分析.
如有任何錯誤請不吝指正.

http://samlin2004.myweb.hinet.net/docs/log/syslog-ngInstallationGuide.htm



Centralized syslog-ng to Mysql Installation Guide
--------------------------------------------------------------------------------

1.Requirement

OS: Red Hat 9.0
Database: MySQL 4.0.20
Web Server: Apache2
PHP Supported.

Packages:
syslog-ng-1.6.5.tar.gz
libol-0.3.14.tar.gz
php-syslog-ng-2.5.1.tar.gz
 

2.Installation

2.1 Install libol-0.3.14.tar.gz

The Libol package contains support libraries needed by Syslog-ng.

2.1.1 Prepare Libol for compilation
#tar zxvf libol-3.0.14.tar.gz
#./configure --prefix=/usr --enable-shared


2.1.2 Compile Libol
#make

2.1.3 Install
#make install

2.2 Install syslog-ng-1.6.5.tar.gz

2.2.1 Prepare Syslog-ng for compilation
#tar zxvf syslog-ng-1.6.5.tar.gz
#./configure --prefix=/usr --sysconfdir=/etc


2.2.2 Compile Syslog-ng
#make

2.2.3 Install
#make install 

2.3 Extract php-syslog-ng-2.5.1.tar.gz

Extract php-syslog-ng-2.5.1.tar.gz under Apache's document root (ex: /usr/local/apache2/htdocs)
 

3.Configuration

3.1 Create Database Schema

3.1.1 Edit syslog-ng.sql script for creating log database schema

=== syslog-ng.sql script start here ===

CREATE DATABASE syslog;

USE syslog;

CREATE TABLE logs (
host varchar(32) default NULL,
facility varchar(10) default NULL,
priority varchar(10) default NULL,
level varchar(10) default NULL,
tag varchar(10) default NULL,
date date default NULL,
time time default NULL,
program varchar(15) default NULL,
msg text,
seq int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) TYPE=MyISAM;
 

=== syslog-ng.sql script end here ===

3.1.2 Run the command to install the database into mysql.

#mysql -u YOURACCOUNT -p < syslog-ng.sql 

3.2 Edit syslog-ng.conf

Edit syslog-ng.conf (default installation path will be /usr/local/etc/syslog-ng/ depends on your installation prefix argument)

=== Configuration file start here ===

options
{
chain_hostnames(no);
create_dirs (no);
dir_perm(0755);
dns_cache(yes);
keep_hostname(yes);
log_fifo_size(2048);
log_msg_size(8192);
long_hostnames(on);
perm(0644);
stats(3600);
sync(0);
time_reopen (10);
use_dns(yes);
use_fqdn(yes);
};

#----------------------------------------------------------------------
# Sources
#----------------------------------------------------------------------
# For Linux
#----------------------------------------------------------------------
source s_stream
{ unix-stream("/dev/log"); };

source s_internal
{ internal(); };

source s_kernel
{ pipe("/proc/kmsg" log_prefix("kernel: ")); };

source s_tcp
{ tcp(port(4800) keep-alive(yes) max_connections(100)); };

#----------------------------------------------------------------------
# Piping method
#----------------------------------------------------------------------
destination database { pipe("/tmp/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, date, time, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") template-escape(yes)); };

#----------------------------------------------------------------------
# Logging to a database
#----------------------------------------------------------------------

log { source(s_stream);
source(s_internal);
source(s_kernel); destination(database); };

=== Configuration file end here === 

3.3 Pipe and startup script

3.3.1 Setup syslog-ng run as a daemon

(1)Edit /etc/rc.d/init.d/syslog-ng as below,

=== syslog-ng script start here ===

################################################################################
#
# Program: syslog-ng init script for Red Hat
#
################################################################################
# the following information is for use by chkconfig
# if you are want to manage this through chkconfig (as you should), you must
# first must add syslog-ng to chkconfig's list of startup scripts it
# manages by typing:
#
# chkconfig --add syslog-ng
#
# DO NOT CHANGE THESE LINES (unless you know what you are doing)
# chkconfig: 2345 12 88
# description: syslog-ng is the next generation of the syslog daemon. \
# syslog-ng gives you the flexibility of logging not only by facility and \
# severity, but also by host, message content, date, etc. it can also replace \
# klogd's function of logging kernel messages
#
# This following block of lines is correct, do not change! (for more info, see
# http://www.linuxbase.org/spec/refspecs/LSB_1.1.0/gLSB/facilname.html)
### BEGIN INIT INFO
# Provides: $syslog
### END INIT INFO
################################################################################
#
# This is an init script for syslog-ng on the Linux platform.
#
# It totally relies on the Redhat function library and works the same
# way as other typical Redhat init scripts.
#
#
# Platforms (tested): Linux (Redhat 7.3)
#
#
# Author: Gregor Binder <gbinder@sysfive.com>
# Changed: October 10, 2000
#
# Last Changed: September 27, 2002
# Updated by: Diane Davidowicz
# changes: Brought the start script up to snuff as far as compliance
# with managing the startup script through chkconfig;
# added PATH variable ability to hook in path to syslog-ng (if
# its necessary); converted init script format to the
# standard init script format in Red Hat (7.3 to be exact)
# including using the /etc/sysconfig/syslog-ng file to
# managed the arguments to syslog-ng without changing this
# script, and disabled klogd but noted where and under what
# conditions it should be enabled. HAPPY LOGGING.
#
# Copyright (c) 2000 by sysfive.com GmbH, All rights reserved.
#
#
################################################################################
#
# configuration
#

INIT_PROG="/usr/local/sbin/syslog-ng"                # Full path to daemon
INIT_OPTS=""                                         # options passed to daemon

#
# Source Redhat function library.
#
. /etc/rc.d/init.d/functions

# Tack on path to syslog-ng if not already in PATH
SYSLOGNG_PATH=":/usr/local/sbin"

PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin
INIT_NAME=`basename "$INIT_PROG"`

# /etc/sysconfig/ is the standard way to pull in options for a daemon to use.
# Source config
if [ -f /etc/sysconfig/syslog-ng ] ; then
. /etc/sysconfig/syslog-ng
else
SYSLOGNG_OPTIONS=
fi

RETVAL=0

umask 077
ulimit -c 0

# See how we were called.
start() {
echo -n "Starting $INIT_PROG: "
#daemon $INIT_PROG $SYSLOGNG_OPTIONS
daemon --check $INIT_PROG "$INIT_PROG $INIT_OPTS"
RETVAL=$?
echo

[ $RETVAL -eq 0 ] && touch "/var/lock/subsys/${INIT_NAME}"
return $RETVAL
}

stop() {

echo -n "Stopping $INIT_PROG: "
killproc $INIT_PROG
RETVAL=$?
echo

[ $RETVAL -eq 0 ] && rm -f "/var/lock/subsys/${INIT_NAME}"
return $RETVAL

}

rhstatus() {
status $INIT_PROG
}

restart() {
stop
start
}

case "$1" in
start)
start
;;
stop)
stop
;;
status)
rhstatus
;;
restart|reload)
restart
;;
condrestart)
[ -f /var/lock/subsys/syslog-ng ] && restart || :
;;
*)
echo $"Usage: $0 {start|stop|status|restart|reload}"
exit 1
esac

exit $?

=== syslog-ng script end here ===

(2)Set as startup script

#chmod ugo+x /etc/rc.d/init.d/syslog-ng
#chkconfig --add syslog-ng
 

3.3.2 Setup mysql-pipe file run as startup

(1)Edit /etc/rc.d/init.d/sqlsyslogd as below,

=== sqlsyslogd script start here ===

#!/bin/bash
#
# sqlsyslogd This is a daemon that takes syslog-ng input and pipe it into
# a MySQL database.
#
# chkconfig: 2345 98 10
# description: sqlsyslogd bridges syslog-ng and mysql.
# author: Josh Kuo Thu 2004/08/12 13:21:56 PDT

. /etc/rc.d/init.d/functions

case "$1" in
start)
if [ -x /tmp/mysql.pipe ]; then
mkfifo /tmp/mysql.pipe
else
# if the service is already running, do not start another one
PIDS=`pidofproc mysql`
if [ "$PIDS" ]; then
echo "sqlsyslogd is already running."
exit 1
fi
mysql -u YOURACCOUNT -h YOURMYSQLSERVERNAME -pYOURPASSWORD syslog < /tmp/mysql.pipe &

#If you need to collect apache logs into mysql, uncomment the following two lines.
#tail -f /usr/local/apache2/logs/access_log | logger -p info -t apache &
#tail -f /usr/local/apache2/logs/error_log | logger -p notice -t apache &


fi
;;
stop )
killproc mysql
#If you need to collect apache logs into mysql, uncomment the next line.
#killproc tail

;;

*)
echo "Usage: sqlsyslogd {start|stop}"
exit 1;
esac
exit 0;
=== sqlsyslogd script end here ===

(2)Set as startup script

#chmod ugo+x /etc/rc.d/init.d/sqlsyslogd
#chkconfig --add sqlsyslogd


3.4 Start the services

Run the following command or reboot the system.

#service syslog-ng start
#service sqlsyslogd start

 

4.Syslog-ng Monitor
4.1 Modify db_fns.php

(1)Configure the database function of php include file. YOURDOCROOT/YOURFOLDERNAME/includes/db_fns.php

=== db_fns.php start here ===

<?php

function db_connect_syslog()
{
$result = mysql_pconnect("YOURMYSQLSERVERNAME", "YOURACCOUNT", "YOURPASSWORD");
if (!$result)
return false;
if (!mysql_select_db("syslog"))
return false;

return $result;
}

?>

=== db_fns.php end here ===

(2)Monitor from browser

http://YOURHOST/YOURFOLDERNAME/index.php



5.Reference
http://www.campin.net/syslog-ng/faq.html
http://vermeer.org/projects.php
--------------------------------------------------------------------------------
Written by Sam Lin 2004/09/16

abelyang

  • 酷!學園 學長們
  • 俺是博士!
  • *****
  • 文章數: 1097
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #1 於: 2004-09-16 16:05 »
引用
#If you need to collect apache logs into mysql, uncomment the following two lines.
#tail -f /usr/local/apache2/logs/access_log | logger -p info -t apache &
#tail -f /usr/local/apache2/logs/error_log | logger -p notice -t apache &

建議這段改一下,
apache 本就可以將 log 寫到 syslog 中...這些是可以自訂的
到 apache 的網站去查您即可明白,會更完美哦

sam.lin

  • 懷疑的國中生
  • **
  • 文章數: 42
    • 檢視個人資料
    • http://samlin2004.myweb.hinet.net
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #2 於: 2004-09-16 16:20 »
引述: "abelyang"
引用
#If you need to collect apache logs into mysql, uncomment the following two lines.
#tail -f /usr/local/apache2/logs/access_log | logger -p info -t apache &
#tail -f /usr/local/apache2/logs/error_log | logger -p notice -t apache &

建議這段改一下,
apache 本就可以將 log 寫到 syslog 中...這些是可以自訂的
到 apache 的網站去查您即可明白,會更完美哦


OK~
我去看看...謝謝嚕~ :wink:

sam.lin

  • 懷疑的國中生
  • **
  • 文章數: 42
    • 檢視個人資料
    • http://samlin2004.myweb.hinet.net
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #3 於: 2004-09-16 17:58 »
關於將 apache log 轉到 syslog/syslog-ng 的方法如下:
P.S: syslog 與 syslog-ng 並存時,syslog-ng 可以不更改設定.

Set Apache log to syslog-ng (Optional)

(1) Modify the httpd.conf (/usr/local/apache2/conf/httpd.conf) put the syslog instead of the file name /logs/error_log as below,
=== parts of httpd.conf here ===
LogLevel notice
ErrorLog syslog

=== parts of httpd.conf here ===
(2) Modify the httpd.conf about the access Log setting as below,
=== part of httpd.conf here ===
CustomLog "| /usr/bin/logger -p local.info" common
=== part of httpd.conf here ===
(3) Add the corresponding entry required in /etc/syslog.conf (In order to keep a copy in local system)
=== part of syslog.conf here ===
local7.*            /var/log/local7.log
local1.*            /var/log/local1.log

=== part of syslog.conf here ===
(4) Restart the httpd service and syslog service
#service syslog restart
#/usr/local/apache2/bin/apachectl -k restart

abelyang

  • 酷!學園 學長們
  • 俺是博士!
  • *****
  • 文章數: 1097
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #4 於: 2004-09-16 18:09 »
sam.lin 兄~
非常讚哦 ! 給你拍手...

您既然會會 logger , 其實可以寫得更好呀...
搭配您的syslog ng ,將主機其他的重要訊息傳回到 mysql 中
詳細這個功能給大家知道如何 ?!


 :evil: 您大概要打我了吧...

sam.lin

  • 懷疑的國中生
  • **
  • 文章數: 42
    • 檢視個人資料
    • http://samlin2004.myweb.hinet.net
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #5 於: 2004-09-16 18:44 »
哈哈..力有未逮~

fifa2000

  • 可愛的小學生
  • *
  • 文章數: 1
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #6 於: 2005-03-09 18:19 »
syslog-ng将日志写入一使用mkfifo得到的pipe文件,后台一damon进程实时对该文件中内容导入mysql数据库中。该文件内容被访问一次后即被删除。而在shell脚本中却不能使用重定向符输入该文件。请问在shell有没有方法可以实现对该类文件的读写操作?
     另外,如果想要实现对router和switch等设备的日志信息进行管理,还需进行哪些具体操作?

hauhau

  • 可愛的小學生
  • *
  • 文章數: 20
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #7 於: 2005-04-17 23:24 »
請問大大們client端怎麼設定呢?
小弟的client端設定如下tar安裝
vi /usr/local/etc/syslog-ng/syslog-ng.conf

source s_sys { pipe ("/proc/kmsg" log_prefix("kernel: ")); unix-stream ("/dev/log"); internal(); };

destination d_logserver { tcp("192.168.1.100"port(10514)); };
log { source(s_sys); destination(d_logserver); };

小弟的server端設定如下rpm安裝
vi /etc/syslog-ng/syslog-ng.conf

source s_sys { pipe ("/proc/kmsg" log_prefix("kernel: ")); unix-stream ("/dev/log"); internal(); };
source s_net {tcp(ip(0.0.0.0)port(10514));
udp();  };
destination d_mysql { pipe("/tmp/mysql.pipe" template("INSERT INTO logs (host, facility, priority, level, tag, date, time, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") template-escape(yes)); };

log { source(s_sys); source(s_net); destination(d_mysql); };


server端打開php-syslog-ng畫面
可以看到server的系統日誌
但是client的日誌檔傳不進來
可以請教各位大大們
怎麼讓client端的日誌檔傳入server端的主機呢?

source s_net {tcp(ip(0.0.0.0)port(10514));
這一行的設定應該是監聽來自外部的主機訊號吧

但是小弟的client端的日誌資料似乎沒傳到主機上
小弟的client只安裝libo-0.3.14l跟syslog-ng-1.6.5.6
這兩個套件......還需要開啟mysql嗎?
跟mkfifo /tmp/mysql.pipe?
感謝各位大大的指導!

cjp

  • 懷疑的國中生
  • **
  • 文章數: 30
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #8 於: 2005-09-27 11:00 »
請問關於syslong-ng

我有將syslog-ng,libol,php-syslog-ng等套件裝起來,可是透過網頁
開啟http://x.x.x.x/web/index.php時內容都是空白,請問大概是哪裡設錯了?找了很久都try不出來.


謝謝......

duncanlo

  • SA 苦力組
  • 俺是博士!
  • *****
  • 文章數: 7312
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #9 於: 2005-09-27 22:10 »
看起來很有I社T牌的TEC軟體味道,真是不錯...

cjp

  • 懷疑的國中生
  • **
  • 文章數: 30
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #10 於: 2005-09-27 23:56 »
引述: "cjp"
請問關於syslong-ng

我有將syslog-ng,libol,php-syslog-ng等套件裝起來,可是透過網頁
開啟http://x.x.x.x/web/index.php時內容都是空白,請問大概是哪裡設錯了?找了很久都try不出來.


謝謝......



我有寫一個php的測試網頁去connect 到mysql的server和syslog DB都很正常
於是我又將db_fns.php的內容copy到index.php裡,取代掉db_connect_syslog();這行
然後開網頁,可以正常看到網頁那些選項.真不知是哪個環節出問題?

acty

  • 鑽研的研究生
  • *****
  • 文章數: 695
    • 檢視個人資料
    • UNIX 管理者的學習紀錄
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #11 於: 2005-09-28 05:32 »
目前剛好也看到這套軟體  也正在裝

我覺得比較可能問題出在 mysql 的問題

我用 fedora  core 4裡的 mysql5

但 php-syslog-ng 作者是用  mysql4 的
~~破窗計畫來囉~~~

學習與挑戰是我的樂趣... HIT!!
我知道的不多  但歡迎大家以起來討論

UNIX 管理者的學習紀錄 - http://actychen.wordpress.com

acty

  • 鑽研的研究生
  • *****
  • 文章數: 695
    • 檢視個人資料
    • UNIX 管理者的學習紀錄
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #12 於: 2005-09-28 06:24 »
sorry 不是 mysql 版本的關係

不過剛看到 LOGS 是 mysql 的保留字

若你下這樣的指令

mysql> describe logs-Jul;
 ERROR 1064: You have an error in your SQL syntax. Check the manual that
 corresponds to your MySQL server version for the right syntax to use near
 "-Jul" at line 1

會有問題

看起來是有方向可以解決問題了

不過人不在公司不方便測  等上班在去測測看
~~破窗計畫來囉~~~

學習與挑戰是我的樂趣... HIT!!
我知道的不多  但歡迎大家以起來討論

UNIX 管理者的學習紀錄 - http://actychen.wordpress.com

acty

  • 鑽研的研究生
  • *****
  • 文章數: 695
    • 檢視個人資料
    • UNIX 管理者的學習紀錄
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #13 於: 2005-09-28 13:19 »
找到原因了

是 查詢時 limit 為空值的原因

SELECT * FROM all_logs ORDER BY seq DESC LIMIT

加上一小段程式 在查詢前判斷 $limit 是否為零
如果是的話給它一個值就好了

在 phpsyslogng/includes/tailresult.php 裡找

if($where !="") {
        $query = $query."WHERE ".$where." ORDER BY seq DESC LIMIT ".$limit;
}
else {
        $query = $query."ORDER BY seq DESC LIMIT ".$limit;
}


在這段程式之前加上

# add by acty
# to fix $limit error

if($limit == 0) {
        $limit=100;
}


另外順便一提  為什麼我的資料會不進去呢

我的 syslog2mysql.sh 都不能動
有沒有人可以將跑成功的 log 貼出來看看
~~破窗計畫來囉~~~

學習與挑戰是我的樂趣... HIT!!
我知道的不多  但歡迎大家以起來討論

UNIX 管理者的學習紀錄 - http://actychen.wordpress.com

cjp

  • 懷疑的國中生
  • **
  • 文章數: 30
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #14 於: 2005-09-29 10:45 »
Wow~~~~try 出來了...新苦了將近一星期 <----笨
我裝php-syslog-ng-2.5版始終不成功,後來改2.8版就沒問題了. :lol:
感謝大家的幫忙...還有acty讓我更有靈感.
先整理一下資料,大家有須要再po出來.

acty

  • 鑽研的研究生
  • *****
  • 文章數: 695
    • 檢視個人資料
    • UNIX 管理者的學習紀錄
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #15 於: 2005-09-29 21:19 »
其實我也是到國外的 demo site

想了很久才得到的靈感

只是不解的是  demo site 不沒有這個問題

不過懶的去看程式了 demo site 為 2.6

最近逛網站得到的結論是  2.8 已經大改版過了

實在沒必要回去用舊板
~~破窗計畫來囉~~~

學習與挑戰是我的樂趣... HIT!!
我知道的不多  但歡迎大家以起來討論

UNIX 管理者的學習紀錄 - http://actychen.wordpress.com

acty

  • 鑽研的研究生
  • *****
  • 文章數: 695
    • 檢視個人資料
    • UNIX 管理者的學習紀錄
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #16 於: 2005-09-30 10:41 »
ok 了..

2.8 已經將 date 和 time 欄位合併為 datetime

所以請把 syslog-ng.conf 改一下

destination d_mysql { pipe("/tmp/mysql.pipe" template("INSERT INTO logs (host,facility, priority, level, tag, datetime, program, msg) VALUES ('$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC', '$PROGRAM', '$MSG');\n") template-escape(yes));};

不用多說了吧
~~破窗計畫來囉~~~

學習與挑戰是我的樂趣... HIT!!
我知道的不多  但歡迎大家以起來討論

UNIX 管理者的學習紀錄 - http://actychen.wordpress.com

xoolong

  • 可愛的小學生
  • *
  • 文章數: 1
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #17 於: 2007-03-19 14:07 »
安装步骤进行了安装,可是怎么看不到网页呢,提示空白,后装了phpsyslogng-2.8.tar.gz,提示:Query failed: Table 'syslog.users' doesn't exist,请问这是 怎么回事

lovey

  • 可愛的小學生
  • *
  • 文章數: 1
    • 檢視個人資料
[筆記]Centralized syslog-ng to Mysql Installation Guide
« 回覆 #18 於: 2007-08-15 09:53 »
11274 ?        S      0:00 mysql -u syslog1 -h localhost -px xxxx syslog

这个进程经常会丢失是什么问题啊?