Apache and syslog-ng
While logging to a database back-end has its benefits, the setup as it stands leaves us wanting. Some applications, such as Apache, do not log via syslog-ng by default. The good news is that this can be easily remedied, and there are a couple of different ways of doing this. First, the less good way:
Method #1: Changing the Apache configuration file.
First, we need to setup syslog-ng appropriately by creating a new source for apache, such as the following:
source s_apache {
unix-stream("/var/log/apache2/apache_log.socket"
max-connections(512)
keep-alive(yes));
};
log { source(s_apache); destination(d_pgsql); };
This recycles the original destination for PostgreSQL and upon restarting syslog-ng, will create the /var/log/apache2/apache_log.socket which will now need to be referenced in httpd.conf:
CustomLog "|/usr/bin/logger -s -t 'Apache' -p info -u /var/log/apache2/apache_log.socket" Combined
ErrorLog "|/usr/bin/logger -s -t 'Apache' -p err -u /var/log/apache2/apache_log.socket"
The “-t ‘Apache’” portion of the above lines will act as the $PROGRAM value defined in the d_pgsql destination above, and may be tailed to suit your preferences. After restarting Apache, your logs should now be sent to the PostgreSQL database along with other system logs.
The problem with this method is that the services must be started in a specific order, syslog-ng first, then Apache. If syslog-ng is restarted for any reason and Apache is not started again afterwards, no Apache logs will be sent to syslog-ng. This is why I prefer the next method:
Method #2: file();
This time, let’s leave Apache alone (no changes to your httpd.conf), and just adjust the syslog-ng configuration. First, a source needs to be made, like before, but employing a different method for gathering the logs. Let’s call it “s_apache” again:
source s_apache {
file("/var/log/apache2/access_log");
file("/var/log/apache2/error_log");
};
Also, a new destination, “d_pgsql_apache”….
destination d_pgsql_apache {
sql(type(pgsql)
host("ip.of.you.host") username("logwriter") password(“logwriterpassword") port("5432")
database("syslog")
table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid
varchar(10)", "message varchar(800)")
values("$R_DATE", "$HOST", "Apache", "$PID", "$MSG")
indexes("datetime", "host", "program", "pid", "message"));
};
The old destination could be used, however the program name will not be entered correctly. Here, the variable $PROGRAM is replaced with “Apache”, so that we always know what program is producing the log. Finally, we need a new log line:
log { source(s_apache); destination(d_pgsql_apache); };
MySQL and syslog-ng
The same format can be applied for MySQL logs:
source s_mysql {
file("/var/log/mysql/mysqld.sql");
};
destination d_pgsql_mysql {
sql(type(pgsql)
host("ip.of.you.host") username("logwriter") password(“logwriterpassword") port("5432")
database("syslog")
table("logs_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
columns("datetime varchar(16)", "host varchar(32)", "program varchar(20)", "pid
varchar(10)", "message varchar(800)")
values("$R_DATE", "$HOST", "MySQL", "$PID", "$MSG")
indexes("datetime", "host", "program", "pid", "message"));
};
log { source(s_mysql); destination(d_pgsql_mysql); };
Please notice that again, the $PROGRAM variable has been changed, this time to “MySQL” to make our lives easier.
Viewing Logs
So, obviously we could run a simple select statement for “Apache” or “MySQL”, and it would probably give us way more information than we really want to see. Let’s say though, that we are interested in seeing all database connections from all users, in the event that we suspect an account has been compromised. I’ll use my trusty server “Louis” as an example. A simple query like this would do the trick:
select * from logs_louis_20110824 where program = ‘MySQL’ and message like ‘%Connect%’
That’s quite a bit of activity in such a short amount of time, but a useful query all the same. Now that we can monitor Apache and MySQL logs for all of our servers from a central location using PostgreSQL, let’s take a look at syslog-ng’s other capabilities for remote logging. Take a look at Logging to a Remote Host with Syslog-ng for more information on logging over TCP and UDP.
Relevant posts:
Basic Apache and MySQL Performance Tuning: Part1: Apache
Basic Apache and MySQL Performance Tuning: Part 2: MySQL
101 Tips to MySQL Tuning and Optimization
LAMP Security: 21 Tips for Apache
25 Apache Performance Tuning Tips
Integrate Apache Monitoring into Monitis.com
PlanetMySQL Voting:
Vote UP /
Vote DOWN
