Syslog-ng directly to MySQL
syslog-ng starting from version 3.0 offers a option of saving logs directly to following SQL databases:
- MySQL
- MariaDB
- Firebird,
- PostgreSQL
- sqlite
In comparison with the old way of saving logs to SQL, namely using a pipe and executing either a wrapper script or mysql client directly, the new way saves resources as syslog-ng does not need to start a process every time there is a log message to log.
Contents |
[edit] Requierements
And a SQL database of your own choice, in this example:
[edit] Installation
Emerge syslog-ng version 3.0 or higher with sql pcre tcpd USE flag enabled:
Emerge php-syslog-ng, which ships the database structure for the SQL server, with mysql USE flag enabled:
[edit] Configuration
[edit] SQL Database Configuration
Change to the directory /usr/share/webapps/phpsyslogng/2.9.8m-r1/htdocs/install/sql where the initial sql script dbsetup.sql for the database is located:
The directory should contain following files
-rw-r--r-- 2 root root 1.9M Jul 25 20:27 cemdb.sql.gz -rw-r--r-- 2 root root 2.4K Jul 25 20:27 dbsetup.sql -rw-r--r-- 2 root root 270 Jul 25 20:27 drop_table.sql -rw-r--r-- 2 root root 729 Jul 25 20:27 sample_data.sql
CREATE TABLE logs (
id bigint unsigned NOT NULL AUTO_INCREMENT,
host varchar(128) default NULL,
facility varchar(10) default NULL,
priority varchar(10) default NULL,
level varchar(10) default NULL,
tag varchar(10) default NULL,
datetime datetime default NULL,
program varchar(15) default NULL,
msg text,
seq bigint unsigned NOT NULL default '0',
counter int(11) NOT NULL default '1',
fo datetime default NULL,
lo datetime default NULL,
PRIMARY KEY (id),
KEY datetime (datetime),
KEY sequence (seq),
KEY priority (priority),
KEY facility (facility),
KEY program (program),
KEY host (host)
) ENGINE=MyISAM;
CREATE TABLE users (
username varchar(32) default NULL,
pwhash char(40) default NULL,
sessionid char(32) default NULL,
exptime datetime default NULL,
PRIMARY KEY (username)
) TYPE=MyISAM;
CREATE TABLE search_cache (
tablename varchar(32) DEFAULT NULL,
type ENUM('HOST','FACILITY','PROGRAM','LPD'),
value varchar(128) DEFAULT NULL,
updatetime datetime DEFAULT NULL,
INDEX type_name (type, tablename)
) TYPE=MEMORY;
CREATE TABLE user_access (
username varchar(32) DEFAULT NULL,
actionname varchar(32) DEFAULT NULL,
access ENUM('TRUE','FALSE'),
INDEX user_action (username, actionname)
) TYPE=MyISAM;
INSERT INTO user_access VALUES ('admin','add_user','TRUE'),('admin','edit_user','TRUE'),('admin','reload_cache','TRUE'),('admin','edit_acl','TRUE'),('admin','add_server','TRUE'),('admin','chg_auth','TRUE'),('admin','del_server','TRUE');
CREATE TABLE actions (
actionname varchar(32) NOT NULL,
actiondescr varchar(64) DEFAULT NULL,
defaultaccess ENUM('TRUE','FALSE'),
PRIMARY KEY (actionname)
) TYPE=MyISAM;
--
-- Table structure for table cemdb
--
CREATE TABLE cemdb (
id int(5) unsigned NOT NULL auto_increment,
name varchar(128) NOT NULL default '',
message text,
explanation text,
action text,
datetime datetime default NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name)
) ENGINE=MyISAM COMMENT='Cisco Error Message Database';
INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('add_user', 'Add users', 'TRUE');
INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('edit_user', 'Edit users (delete and change password)', 'TRUE');
INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('reload_cache', 'Reload search cache', 'TRUE');
INSERT INTO actions (actionname, actiondescr, defaultaccess) VALUES ('edit_acl', 'Edit access control settings', 'TRUE');
Log in to your SQL command line shell with a user which has administrative rights like root:
Create a database named f.e. "syslog"
mysql> CREATE DATABASE `syslog` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Use created database:
mysql> USE syslog;
Import the database structure from dbsetup.sql file.
mysql> SOURCE dbsetup.sql;
Now the SQL database should be setup properly, Verify it with SHOW TABLES; command.
mysql> SHOW TABLES; +------------------+ | Tables_in_syslog | +------------------+ | actions | | cemdb | | logs | | search_cache | | user_access | | users | +------------------+ 6 rows in set (0.00 sec)
Create 2 SQL database users for the "syslog" database with following access rights:
- User with write permission to write syslog messages to the syslog database
- User with read only access rights for searching within the syslog database from a web tool like php-syslog-ng
Run following command in the SQL command line to create the syslogwriter database user
mysql> GRANT INSERT ON `syslog`.* TO 'syslogwriter'@'localhost' IDENTIFIED BY 'syslogwriter-password';}}
Create the syslogreader database user
mysql> GRANT SELECT ON `syslog`.* TO 'syslogreader'@'localhost' IDENTIFIED BY 'syslogreader-password';}}
Close the SQL session
mysql> EXIT
[edit] Syslog-ng Configuration
Edit syslog-ng config appropriately
@version: 3.0
options {
stats_freq(3600);
keep_hostname(yes);
flush_lines(16);
log_fifo_size(16384);
};
source s_src {
unix-stream("/dev/log");
file("/proc/kmsg");
internal();
};
destination d_mysql {
sql(type(mysql)
host("localhost") username("syslogwriter") password("syslogwriter-password")
database("syslog")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST_FROM", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG", "$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC", "$PROGRAM", "$MSG")
indexes("host", "facility", "priority", "datetime", "program"));
};
log { source(s_src); destination(d_mysql); };
Restart syslog-ng daemon
