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.

Requierements
And a SQL database of your own choice, in this example:

Installation
Emerge syslog-ng version 3.0 or higher with   USE flag enabled:

Emerge php-syslog-ng, which ships the database structure for the SQL server, with USE flag enabled:

SQL Database Configuration
Change to the directory where the initial sql script  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

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 file. mysql> SOURCE dbsetup.sql;

Now the SQL database should be setup properly, Verify it with command. mysql> SHOW TABLES; +--+ +--+ +--+ 6 rows in set (0.00 sec)
 * Tables_in_syslog |
 * actions         |
 * cemdb           |
 * logs            |
 * search_cache    |
 * user_access     |
 * users           |

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 database user mysql> GRANT INSERT ON `syslog`.* TO 'syslogwriter'@'localhost' IDENTIFIED BY 'syslogwriter-password';}}

Create the database user mysql> GRANT SELECT ON `syslog`.* TO 'syslogreader'@'localhost' IDENTIFIED BY 'syslogreader-password';}}

Close the SQL session mysql> EXIT

Syslog-ng Configuration
Edit syslog-ng config appropriately

Restart syslog-ng daemon

Troubleshooting
If logging into database doesn't start, and such string(s) in /var/log/message appears No such DBI driver; type='mysql' you have to enable USE flag(s) for database driver library: