Jul 9 17:51:38 amdgentoo logger: 4 Jul 9 17:51:38 amdgentoo syslog-ng[15407]: Error running SQL query; type='mysql', host='localhost', port='', user='syslogappend', database='syslog', error='2006: MySQL server has gone away', query='INSERT INTO logs (crtm, seq, host, addr, facility, level, pri, program, pid, msghdr, msg) VALUES (\'2011-07-09T17:51:38+09:00\', \'5\', \'amdgentoo\', \'127.0.0.1\', \'1\', \'5\', \'13\', \'logger\', \'\', \'logger: \', \'4\')' Jul 9 17:51:38 amdgentoo syslog-ng[15407]: Error running SQL query; type='mysql', host='localhost', port='', user='syslogappend', database='syslog', error='2006: MySQL server has gone away', query='INSERT INTO logs (crtm, seq, host, addr, facility, level, pri, program, pid, msghdr, msg) VALUES (\'2011-07-09T17:51:38+09:00\', \'5\', \'amdgentoo\', \'127.0.0.1\', \'1\', \'5\', \'13\', \'logger\', \'\', \'logger: \', \'4\')' Jul 9 17:51:38 amdgentoo syslog-ng[15407]: Error running SQL query; type='mysql', host='localhost', port='', user='syslogappend', database='syslog', error='2006: MySQL server has gone away', query='INSERT INTO logs (crtm, seq, host, addr, facility, level, pri, program, pid, msghdr, msg) VALUES (\'2011-07-09T17:51:38+09:00\', \'5\', \'amdgentoo\', \'127.0.0.1\', \'1\', \'5\', \'13\', \'logger\', \'\', \'logger: \', \'4\')' Jul 9 17:51:38 amdgentoo syslog-ng[15407]: Multiple failures while inserting this record into the database, message dropped; attempts='3'
デフォルト値を変更しても大丈夫であれば回避できそう。
$ php -r '$l=mysql_connect("localhost","syslogappend","syslogappendpasswd");var_dump($l);$s=0;do{$end=true;if($res=mysql_query("SELECT {$s}")){while($row=mysql_fetch_assoc($res)){print array_pop($row)."\n";$end=false;}}if($end){die(mysql_errno($l).": ".mysql_error($l)."\n"."\$s={$s}. end.\n");}sleep($s);$s+=10;}while(1);'
resource(4) of type (mysql link)
0
10
20
30
40
50
60
2006: MySQL server has gone away
$s=70. end.$ grep -i timeout /etc/mysql/my.cnf|grep "[5-7]0" innodb_lock_wait_timeout = 50 wait_timeout = 60 $ mysqladmin variables|grep -i timeout|grep "[5-7]0" | innodb_lock_wait_timeout | 50 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | | wait_timeout | 60 |
参考URL。
http://en.gentoo-wiki.com/wiki/Syslog-ng_directly_to_MySQL
再インストール。
$ sudo USE="sql mysql" emerge --oneshot -avt syslog-ng
設定。
$ rcsdiff /etc/config-archive/etc/syslog-ng/syslog-ng.conf,v /etc/syslog-ng/syslog-ng.conf
===================================================================
RCS file: /etc/config-archive/etc/syslog-ng/syslog-ng.conf,v
retrieving revision 1.18
diff -r1.18 /etc/syslog-ng/syslog-ng.conf
64a65,74
> destination d_mysql {
> sql(type(mysql)
> host("localhost") username("syslogappend") password("syslogappendpasswd")
> database("syslog")
> table("logs")
> columns("crtm", "seq", "host", "addr", "facility", "level", "pri", "program", "pid", "msghdr", "msg")
> values("$ISODATE", "$SEQNUM", "$HOST", "$SOURCEIP", "$FACILITY_NUM", "$LEVEL_NUM", "$PRI", "$PROGRAM", "$PID", "$MSGHDR", "$MSG")
> indexes());
> };
>
126a137
> log { source(src); source(kernsrc); source(net); filter(f_database); destination(d_mysql); };テーブル作成。
$ head -n2 bin/syslog.sql
-- CREATE DATABASE syslog;
-- GRANT SELECT,INSERT ON syslog.* TO syslogappend@localhost IDENTIFIED BY 'syslogappendpasswd';
$ cat bin/syslog2.sql
DROP TABLE IF EXISTS logs;
CREATE TABLE logs (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
crtm TIMESTAMP NOT NULL,
seq INT UNSIGNED NOT NULL,
host VARBINARY(63) NOT NULL,
addr VARBINARY(15) NOT NULL,
addrnum INT UNSIGNED NOT NULL,
facility TINYINT NOT NULL,
level TINYINT NOT NULL,
pri INT NOT NULL,
program VARBINARY(31) NOT NULL,
pid SMALLINT UNSIGNED NOT NULL,
msghdr VARBINARY(63) NOT NULL,
msg BLOB NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=binary;
DROP TRIGGER IF EXISTS trigger_logs_before_insert;
DROP TRIGGER IF EXISTS trigger_logs_before_update;
DROP TRIGGER IF EXISTS trigger_logs_before_delete;
DELIMITER |
CREATE TRIGGER trigger_logs_before_insert BEFORE INSERT ON logs
FOR EACH ROW BEGIN
SET NEW.addrnum = INET_ATON(NEW.addr);
END;
|
CREATE TRIGGER trigger_logs_before_update BEFORE UPDATE ON logs
FOR EACH ROW BEGIN
CALL ERROR_UPDATE_NOT_ALLOWED();
END;
|
CREATE TRIGGER trigger_logs_before_delete BEFORE DELETE ON logs
FOR EACH ROW BEGIN
CALL ERROR_DELETE_NOT_ALLOWED();
END;
|
DELIMITER ;
$ cat bin/syslog2.sql | mysql -uroot syslog再起動。
$ sudo /etc/init.d/syslog-ng reload
記録する。
$ logger 111 $ logger 222 $ logger 333
確認する。
$ mysql -uroot syslog ... mysql> select * from logs order by id desc; +----+---------------------+-----+-----------+-----------+------------+----------+-------+-----+---------+-----+----------+-----+ | id | crtm | seq | host | addr | addrnum | facility | level | pri | program | pid | msghdr | msg | +----+---------------------+-----+-----------+-----------+------------+----------+-------+-----+---------+-----+----------+-----+ | 3 | 2011-07-09 12:25:33 | 3 | amdgentoo | 127.0.0.1 | 2130706433 | 1 | 5 | 13 | logger | 0 | logger: | 333 | | 2 | 2011-07-09 12:25:30 | 2 | amdgentoo | 127.0.0.1 | 2130706433 | 1 | 5 | 13 | logger | 0 | logger: | 222 | | 1 | 2011-07-09 12:25:28 | 1 | amdgentoo | 127.0.0.1 | 2130706433 | 1 | 5 | 13 | logger | 0 | logger: | 111 | +----+---------------------+-----+-----------+-----------+------------+----------+-------+-----+---------+-----+----------+-----+ 3 rows in set (0.00 sec)
seqは同じcrtmで並べかえをする場合に必要かもしれない。(order by crtm, seq)
addrnumは検索で必要かもしれない。(addrnum between inet_aton('127.0.0.0') and inet_aton('127.255.255.255'))
pri, msghdr は必要ないかもしれない。
pri => facility, level
$ php -r '$pri=13;printf("%04b\n%04b\n%04b\n%d\n---\n%010b\n%010b\n%010b\n%010b\n%d\n",$pri,0x07,$x=($pri & 0x07),$x,$pri,0x03f8,($pri & 0x03f8),$x=(($pri & 0x03f8) >> 3),$x);'
1101
0111
0101
5
---
0000001101
1111111000
0000001000
0000000001
1
0 件のコメント:
コメントを投稿