Запись логов nginx в базу данных mysql через syslog-ng

Ставим необходимые пакеты

apt install -y syslog-ng  syslog-ng-json libdbi-dbd-mysql mysql-server

Создаем базу в mysql и таблицу

aCREATE DATABASE nginx_logs CHARACTER SET utf8 COLLATE utf8_bin;
CREATE USER 'nginx'@'%' IDENTIFIED BY 'yourpassword';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on nginx_logs.* TO 'nginx'@'%';
flush privileges;
Create table access_log (
   id int8 unsigned auto_increment primary key,
   unixtime int4 unsigned,
   year year(4),
   month int,
   day int,
   fulldate timestamp,
   remote_addr varchar(50),
   remote_user varchar(100),
   request_method varchar(20),
   request_uri varchar(2048),
   status varchar(10),
   body_bytes_send int4 unsigned,
   request_time float,
   http_referrer varchar(2048),
   http_user_agent varchar(500),
   http_x_forwareded_for varchar(50),
   INDEX index_access_log_unixtime(unixtime),
   INDEX index_access_log_month(month),
   INDEX index_access_log_day(day)
);

Настраиваем фомат логов в nginx

alog_format  main  '$remote_addr $remote_user [$time_local] $request_method "$request_uri" '
                      '$status $body_bytes_sent $request_time "$http_referer" '
                      '"$http_user_agent" "$http_x_forwarded_for"';

Отправляем логи в syslog-ng

aaccess_log  syslog:server=127.0.0.1 main;

Правим syslog-ng файл. Настраиваем парсинг и запись лога в базу данных

anano /etc/syslog-ng/syslog-ng.conf

aoptions {
    flush_lines (100);
    time_reopen (10);
    log_fifo_size (1000);
    chain_hostnames (off);
    use_dns (no);
    use_fqdn (no);
    create_dirs (yes);
    keep_hostname (yes);
};
source s_nginx_network{
    network(
       ip(127.0.0.1)
       port(514)
       transport("udp")
   );
};
parser p_nginx {
    csv-parser(
       columns("REMOTE_ADDR", "REMOTE_USER", "TIME_LOCAL", "REQUEST_METHOD", "REQUEST_URI",
                    "STATUS", "BODY_BYTES_SEND", "REQUEST_TIME",
                    "HTTP_REFERER","HTTP_USER_AGENT", "HTTP_X_FORWARDED_FOR")
       flags(escape-double-char,strip-whitespace)
       delimiters(" ")
       quote-pairs('""[]')
     );
};
destination d_sql {
      sql(
          type(mysql)
          host("mysql.itc-life.ru")
          port(3306)
          username("nginx")
          password("yourpassword")
          session-statements("SET COLLATION_CONNECTION='utf8_general_ci'")
          database("nginx_logs")
          table("access_log")
          columns("id int8 unsigned auto_increment primary key",
                  "unixtime int4 unsigned",
                  "year year(4)",
                  "month int",
                  "day int",
                  "fulldate timestamp",
                  "remote_addr varchar(50)",
                  "remote_user varchar(100)",
                  "request_method varchar(20)",
                  "request_uri varchar(2048)",
                  "status varchar(10)",
                  "body_bytes_send int4 unsigned",
                  "request_time float",
                  "http_referrer varchar(2048)",
                  "http_user_agent varchar(500)",
                  "http_x_forwareded_for varchar(50)")
          values(default,"${UNIXTIME}", "${YEAR}", "${MONTH}", "${DAY}", "${YEAR}-${MONTH}}-${DAY} ${HOUR}-${MIN}-${SEC}",
               "${REMOTE_ADDR}","${REMOTE_USER}","${REQUEST_METHOD}","${REQUEST_URI}",
               "${STATUS}","${BODY_BYTES_SEND}","${REQUEST_TIME}",
               "${HTTP_REFERER}","${HTTP_USER_AGENT}","${HTTP_X_FORWARDED_FOR}")
          indexes("unixtime")
          indexes("month")
          indexes("day")
          null("-")
        );
};
log { source(s_nginx_network); parser(p_nginx); destination(d_sql); };

Сохраняемся.

ЗАпускаем в режиме дебага

/usr/sbin/syslog-ng -Fvde

Перезапускаем после-то как ве проверили

/etc/init.d/syslog-ng restart